Which service is executing a given SQL statement

We have a shared PLSQL code base between several pieces of application services. Some of them use all of it, some only a few pieces. We also use database services for accounting reasons.

I needed to determine which database service was executing a given SQL statement while we plan a migration that may impact those that execute that SQL. I ended up using what is below. It isn’t perfect, but it will tell you the count of times a given service was found executing that SQL as recorded in DBA_HIST_ACTIVE_SESS_HISTORY. It is definitely useful for a high level “landscape” for planning.

#!/home/oracle/local/bin/python

import cx_Oracle, sys

conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
cursor = conn.cursor()

cursor.execute("select min(snap_id),max(snap_id) from dba_hist_snapshot")

for row in cursor:
  min_snap = row[0]
  max_snap = row[1]

while min_snap <= max_snap:
  cursor.execute("""
                  select count(*) cnt,service_hash
                    from dba_hist_active_sess_history
                    where snap_id = :1 
                      and sql_id = 'grjrw3a6z22k1'
                    group by service_hash
                 """,[min_snap])
  for row in cursor:
    print row[1],row[0]
    sys.stdout.flush()
  min_snap = min_snap + 1

We could also format the totals in python, but we just let them print out and then totaled them using awk.

09:32:10 oracle@wcdb01pxwe ~ >awk '{s[$1]+=$2} END {for (i in s) {print i,s[i]}}' l.txt
2217777499 9044
3540941183 2013
3493663839 1
09:32:14 oracle@wcdb01pxwe ~ >

You can then take the actual service_hash printed in the first column of the awk output and get its name with the following...

select name from dba_services where name_hash = 2217777499;