It has been pretty long that i had blogged.Past year was little busy on both personal and professional front. But this year i am planning to be more active in sharing and learning and with not only Oracle DBMS but could be few others too.
Now, coming back to this blog, i wanted to share certain sql scripts which i have been using mostly for doing the RCA for issues. Sometimes issues are reported lately and DBA are asked for RCA.In past few month i worked on multiple commit latency and high CPU spikes issue which lead to writing these scripts to identify the events, sqlids, module etc in order to catch the culprit.
If the issue is currently being worked on then V$ACTIVE_SESSION_HISTORY is the best place to start with along with v$lock and few other views. In case, spike was seen few hours/a day back, the data to diagnose can be retrieved from DBA_HIST_ACTIVE_SESS_HISTORY.
As we know the time when issue occurred we can use the below sql to identify the top most EVENTS which happened during that time frame. I am displaying the events which have count(*) > 50.

break on TIME skip 1
accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '
select to_char(sample_time,'DD-MM HH24:MI') time,event,count(*) from dba_hist_active_sess_history where sample_time between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI') group by to_char(sample_time,'DD-MM HH24:MI'),event having count(*) > 50 order by 1;

Output —

TIME	    EVENT						 COUNT(1)
----------- -------------------------------------------------- ----------
29-11 09:29 db file sequential read		                    143
29-11 09:31 db file sequential read		                    183
	    library cache: mutex X		                    120
	    log file sync			                    656
29-11 09:32 db file sequential read		                    153
	    library cache: mutex X		                    129
	    log file sync			                    285

To check the sqlid which were most active during that time frame we can use the below sql —

break on TIME skip 1
accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '
accept N prompt 'Enter rownumber to display [N]: '
col module for a45
col opname for a15
select time,sql_id,plan_hash_value,opname,module,count from
        (select to_char(sample_time,'DD-MM-YY HH24:MI') time,sql_id,sql_plan_hash_value plan_hash_value,module,SQL_OPNAME opname,count(1) count,
          ROW_NUMBER ()  OVER (PARTITION BY to_char(sample_time,'DD-MM-YY HH24:MI') order by count(1) DESC) as rownumber
          from  dba_hist_active_sess_history  where sample_time between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI') and sql_id is not nul
l group by to_char(sample_time,'DD-MM-YY HH24:MI'),sql_id,sql_plan_hash_value,module,SQL_OPNAME)
where rownumber <=&N;
undef start_time
undef end_time
undef N

Output —

TIME	       SQL_ID	     PLAN_HASH_VALUE OPNAME	     MODULE						COUNT
-------------- ------------- --------------- --------------- --------------------------------------------- ----------
10-01-14 07:35 aurvkajbfxr0z	  2569592323 SELECT	     ABC				         3
	       ccdks1ftnc7x5	   641461876 SELECT	     ABC				         3
	       27yu9pxlppscn	  2950873079 SELECT	     XYZ			                 2
	       38u8w2hohzhha		   0 INSERT	     DEF			                 2
	       89bqc3gp18zya	   438142338 INSERT	     XYZ				         2
10-01-14 07:36 0y95krfumnbr5	   488120578 DELETE	     XYZ				         5
	       1gqi6rs1nj113	  3888582233 INSERT	     DEF				         3
	       2sktfcq1vmd9r		   0 INSERT	     ABC				         2
	       3nbmuejym2ppk		   0 PL/SQL EXECUTE  ABC					    2
	       4vdjfsmzqbyhq	   324236703 SELECT	     ABC				         2

To find top executions between certain time frame —

col BEGIN_INTERVAL_TIME format a30
col module format a50;
col plan_hash_value for 99999999999999
break on TIME skip 1
accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '
accept N prompt 'Enter rownumber to display [N]: '
select to_char(BEGIN_INTERVAL_TIME,'DD-MM-YY HH24:MI') time,sql_id,PLAN_HASH_VALUE,module,"Executions","BG/exec","DR/exec","ET/exec","CT/exec" from (
select  hs.BEGIN_INTERVAL_TIME,
        hss.sql_id,
        hss.plan_hash_value,
        hss.MODULE,
        sum(hss.EXECUTIONS_DELTA) "Executions",
        round(sum(hss.BUFFER_GETS_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,sum(hss.EXECUTIONS_DELTA))) "BG/exec",
        round(sum(hss.DISK_READS_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))) "DR/exec",
        round(sum(hss.ELAPSED_TIME_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))/1000000) "ET/exec",
        round(sum(hss.CPU_TIME_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))/1000000) "CT/exec",
        ROW_NUMBER ()  OVER (PARTITION BY hs.BEGIN_INTERVAL_TIME ORDER BY sum(hss.EXECUTIONS_DELTA) DESC) as rownumber
from    dba_hist_sqlstat hss, dba_hist_snapshot hs
where   hss.snap_id=hs.snap_id
and     hs.BEGIN_INTERVAL_TIME between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI')
group by hs.BEGIN_INTERVAL_TIME, hss.sql_id, hss.plan_hash_value, hss.MODULE order by 1) where rownumber <= &N;
undef start_time
undef end_time
undef N

ASH and DBA_HIST are awesome views available to diagnose an issue.
I hope the scripts will be useful for you too!!!