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!!!
4 thoughts on “Few Scripts for Identify Performance Issues using DBA_HIST view”
HI Anand,
Nice to talk to you .. I read several blogs from ur end. I wanted some info on index range scan, index fast full scan, index full scan, index skip scan, seq scan, heap scan. when these come to the picture in the explain plan. can I get one select query for each of them.
Otherwise, please provide me nice info. on this. (urls). It’s very nice of you. My email id is [email protected].
Regards
Prabhu
Hello Prabhakar,
Thank you for visiting the blogs and hope you find them useful.
Please have a look at below link
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
What is row number here
Rownumber defines the number of output rows for each sample time. If suppose rownumber <= 10 , you would see top 10 rows ordered by count for each sample time.If you want to see top 15 , then rownumber <= 15.