Month: August 2015

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