We generate a daily health check report for each database that contains high level summaries of the previous days activities. One element of this report is a list of the top ten non idle wait events (disclaimer: I know that sometimes idle wait events can be important, but if they are, they can usually be identified pretty quickly).
Whenever we see something like “library cache: mutex X”, it is useful to know those SQL statements that may have contributed to the wait. A quick high level way to do this is with the python script below. This can be called after installing the cx_Oracle python library (google it for how to do so).
#!/home/oracle/local/bin/python
import cx_Oracle, time, sys
conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
cursor = conn.cursor()
cursor.execute("select max(snap_id),min(snap_id) from dba_hist_snapshot where begin_interval_time between sysdate - :1 and sysdate - :2",[float(sys.argv[2]),float(sys.argv[3])])
for row in cursor:
maxSnap = row[0]
minSnap = row[1]
cursor.execute("select * from (select count(*),sql_id from dba_hist_active_sess_history where event = :1 and snap_id between :2 and :3 group by sql_id order by 1 desc) where rownum = 1",[sys.argv[1],minSnap,maxSnap])
for row in cursor:
sql_id = row[1]
cnt = row[0]
found = False
cursor.execute("select sql_text from dba_hist_sqltext where sql_id = :1",[sql_id])
for row in cursor:
found = True
print "sql_id = ",sql_id
print "waits = ",cnt
print "sql_text = ",row[0]
if found != True:
if sql_id == None:
print "No SQL_ID for event '" + sys.argv[1] + "'"
else:
print "SQL_ID ",sql_id," could not be found in dba_hist_sqltext"
You pass in the event in which you are interested, as well as the start time and stop time to use for consideration. It simply counts the number of occurrences of the event within the window, grouped by SQL_ID. It sorts the output, and prints the sql_text associated with the most frequently found SQL_ID tied to the event.
Below is a sample call.
09:41:19 oracle@oh1xpwcdb01 ~ >./eventSql.py "library cache: mutex X" 1 0
sql_id = agfyfk7auh8nf
waits = 2135
sql_text = BEGIN :1 := xwc_read.view_F(:2,:3,:4,:5,:6,:7,:8,:9,:10); END;
09:43:45 oracle@oh1xpwcdb01 ~ >