So you don’t have diagnostic or the tuning pack for your databases, but you have started collecting information using Oracle Statspack that i mentioned in one of my previous posts. As well as running normal reports, you can also run sql directly against the tables to gather information on SQL Performance. If you log into your DB using the user collecting the stats and run the following sql.
col TEXT_SUBSET format a32 col SQL_TEXT format a90 set linesize 200 set pagesize 2000 alter session set nls_date_format = 'DD-MON-YY hh24:mi:ss'; select * from ( select SQL_ID, OLD_HASH_VALUE, EXECUTIONS -- , READS/EXECUTIONS READS_PER_EXEC -- , WRITES/EXECUTIONS WRITES_PER_EXEC -- , (CPU/1000000)/EXECUTIONS CPU_SEC_PER_EXEC , (ELAPSED/1000000)/EXECUTIONS ELAPSED_SEC_PER_EXEC , TEXT_SUBSET -- , SQL_TEXT from ( select SQL_ID, OLD_HASH_VALUE, TEXT_SUBSET, SQL_TEXT, sum(EXECUTIONS) EXECUTIONS, sum(DISK_READS) READS, sum(DIRECT_WRITES) WRITES, sum(CPU_TIME) CPU, sum(ELAPSED_TIME) ELAPSED from PERFSTAT.STATS$SQL_SUMMARY sum, PERFSTAT.STATS$SNAPSHOT snap where sum.snap_id=snap.snap_id and -- Last hour -- SNAP_TIME > sysdate -1/24 -- Last day -- SNAP_TIME > sysdate -1 -- Yesterday SNAP_TIME between trunc(sysdate -1)+8/24 and trunc(sysdate -1)+20/24 --SNAP_TIME between to_date('14-MAY-2015 08:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('14-MAY-2015 20:00:00','DD-MON-YYYY HH24:MI:SS') group by SQL_ID, OLD_HASH_VALUE, TEXT_SUBSET, SQL_TEXT ) where EXECUTIONS > 0 order by 4 desc ) -- limit the amount of rows returned where rownum < 75 ;
You will see a list like this:
SQL_ID OLD_HASH_VALUE EXECUTIONS ELAPSED_SEC_PER_EXEC TEXT_SUBSET
------------- -------------- ---------- -------------------- --------------------------------
ba9xcjfhwgfum 3360270111 1 511.96071 INSERT /*+APPEND*/ INTO S_ETL_I
c16hxw57t3tck 1238139196 1 468.807817 select trunc(a.last_upd),a.evt_
cwy7xav47930r 2774232425 1 462.835921 insert into siebel.email_resp_2
fg7jqcyf81nz0 404877695 1 366.523411 SELECT /*+ ALL_ROWS */ T9
d7cz4p5b6nq64 931021206 1 326.530129 INSERT /*+APPEND*/ INTO S_ETL_I
63tfvvwpn9wyq 3808620702 10 257.67863 SELECT T33.CONFLICT_ID,
0qg98ks85f5mv 4182116347 3 228.984559 select * from CX_ERROR_MSG m wh
7ap024kdpncy4 2300540696 3 155.879721 select * from CX_ERROR_MSG m wh
0j4jsdd258tyn 1522532907 1 153.883791 INSERT /*+APPEND*/ INTO S_ETL_I
aa9ggv3h5dfrw 1185165122 1 144.548821 SELECT /*+ ALL_ROWS */ T9
f0p618u0609sz 1397351685 9 140.105373 SELECT T31.CONFLICT_ID,
cqmwa23np60dg 484508739 2 121.701425 update S_ORDER o set O.STATUS_C
g38k23ujhtqzh 3885134350 1 114.190877 INSERT /*+APPEND*/ INTO S_ETL_I
7jrnvpmkbu1r8 358282260 1 112.995241 INSERT /*+APPEND*/ INTO S_ETL_I
Ideally you want to import this in Excel, then filter. This allow you to order on any of the columns such as the sql that took the longest, to what sql was being executed repeatedly and taking time. Thereafter find the sql using the sql hash value to see the full sql. You can amend the sql to choose how many days you want to go back, the above sql looks back 24 hours.