#!/usr/bin/env python
#
# http://www.oracledba.ru
#

"""
Check for bindless statements in library cache
"""

__revision__ = "$Id: bindless.py,v 1.1.1.1 2005/03/16 08:48:17 egor_starostin Exp $"
__version__  = __revision__.split()[2]

import os, sys, getopt, re

def displayHelp(msg):
  if msg:
    print >>sys.stderr, msg
  print >>sys.stderr, "bindless.py v%s" % __version__
  print >>sys.stderr, "usage: bindless.py [-b num] [-h]"
  print >>sys.stderr, "  -b set boundary (100 by default)"
  print >>sys.stderr, "  -h this screen"


def sortbyvalue(d):
  _swap2 = lambda (x,y): (y,x)
  mdict = map(_swap2, d.items())
  mdict.sort()
  mdict.reverse()
  mdict = map(_swap2, mdict)
  return mdict

def getSqlText(boundary):
  sqlhash = {}
  stringpat = re.compile(r"'[^']+'")
  digitpat  = re.compile(r"\b\d+\b")
  spacespat = re.compile(r"\s{2,}")
  prfx = "__sqltext_wo_constants%s" % os.getpid()
  print "Obtaining SQL statements from library cache..."
  cmdfile = open("%s.sql" % prfx,"w")
  cmdfile.write("""set head off
set pages 0
set lines 1000
set feedb off
set termout off
set trims on
set array 5000
set flush off
set feedb off
set trims on
spool %s
connect / as sysdba
select sql_text from v$sql;
exit
/
""" % prfx)
  cmdfile.close()
  if sys.platform == 'win32':
    os.system("sqlplus -s /nolog @%s.sql >nul" % prfx)
  else:
    os.system("sqlplus -s /nolog @%s.sql >/dev/null" % prfx)
  print "Done."
  for l in open("%s.lst" % prfx):
    l = l.rstrip("\n")
    sqlhash[l] = 1
  os.unlink("%s.sql" % prfx)
  os.unlink("%s.lst" % prfx)
  outHash = {}
  print "Processing SQL statements..."
  for s in sqlhash.keys():
    s = re.sub(stringpat,"'#'",re.sub(digitpat,'@',re.sub(spacespat,' ',s)))
    cnt = outHash.get(s,0)
    cnt += 1
    outHash[s] = cnt

  print "Done."
  print "\nDispaying results:"
  outDict = sortbyvalue(outHash)
  for sql,cnt in outDict:
    if cnt >= boundary:
      print sql,"\n",cnt,"\n"
#
# begin
#

if __name__ == '__main__':
  boundary = 100
  try:
    opts, args = getopt.getopt(sys.argv[1:],'hb:',['help','boundary='])
  except getopt.error, msg:
    displayHelp(msg)
    sys.exit(1)

  for opt,val in opts:
    if opt in ('-h','--help'): displayHelp(''); sys.exit(0)
    if opt in ('-b','--boundary'): boundary = int(val)

  getSqlText(boundary)