Friday, January 17, 2014

SQL History of an Oracle Session from ASH

Query help to find out the SQL history of an oracle session from Active Session History(ASH) . Here we can see the historical  Data available from AWR too.

SELECT SQL_ID,PLAN_HASH_VALUE,inst_id, sum(execs) "Execution", round(sum(tot_time)/1000000,2) "Elapsed_time",
(case when sum(execs) > 0 then sum(tot_time)/ sum(execs)/1000000 else 0 end) Average_exe_Second
FROM (SELECT C.SQL_ID ,c.PLAN_HASH_VALUE,b.inst_id,
        c.executions execs ,c.elapsed_time tot_time
 FROM gv$ACTIVE_SESSION_HISTORY A,gv$session b,
        gv$SQLAREA C where
        a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=131
    union all
SELECT C.SQL_ID  ,c.PLAN_HASH_VALUE ,b.inst_id ,c.executions_delta Execs,c.elapsed_time_delta tot_time
 from dba_hist_active_sess_history a, gv$session b, dba_hist_sqlstat c where
a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=12 )
GROUP BY SQL_ID,PLAN_HASH_VALUE,inst_id;

Thanks,
Jyothish

No comments:

Post a Comment