Teaching an Oracle Database 10g Performance Tuning course this week, I introduced the 10g New Feature Active Session History (ASH) to the students. That was one major improvement – together with the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) – of the 10g version. Way better than STATSPACK was before!
Imagine you are a DBA on a production system and get an emergency call like „The Database is dead slow!“. You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. It is accessed most comfortable with the Enterprise Manager GUI from the Performance Page (Button ASH Report there). Or with little effort from the command line like this:
----------------------------------------- -- -- Top 10 CPU consumers in last 5 minutes -- ----------------------------------------- SQL> select * from ( select session_id, session_serial#, count(*) from v$active_session_history where session_state= 'ON CPU' and sample_time > sysdate - interval '5' minute group by session_id, session_serial# order by count(*) desc ) where rownum <= 10; -------------------------------------------- -- -- Top 10 waiting sessions in last 5 minutes -- -------------------------------------------- SQL> select * from ( select session_id, session_serial#,count(*) from v$active_session_history where session_state='WAITING' and sample_time > sysdate - interval '5' minute group by session_id, session_serial# order by count(*) desc ) where rownum <= 10;
These 2 queries should spot the most incriminating sessions of the last 5 minutes. But who is that and what SQL was running?
-------------------- -- -- Who is that SID? -- -------------------- set lines 200 col username for a10 col osuser for a10 col machine for a10 col program for a10 col resource_consumer_group for a10 col client_info for a10 SQL> select serial#, username, osuser, machine, program, resource_consumer_group, client_info from v$session where sid=&sid; ------------------------- -- -- What did that SID do? -- ------------------------- SQL> select distinct sql_id, session_serial# from v$active_session_history where sample_time > sysdate - interval '5' minute and session_id=&sid; ---------------------------------------------- -- -- Retrieve the SQL from the Library Cache: -- ---------------------------------------------- col sql_text for a80 SQL> select sql_text from v$sql where sql_id='&sqlid';
You may spot the cause of the current performance problem in very short time with the shown technique. But beware: You need to purchase the Diagnostic Pack in order to be allowed to use AWR, ADDM and ASH 🙂
Pickleball spielen 
#1 von Statistique am Juli 8, 2010 - 15:41
I always used the graphic interface. Those little SELECT are so nice, works perfectly !
Thanks for sharing !
#2 von Uwe Hesse am Juli 8, 2010 - 16:04
You’re welcome! Although the Enterprise Manager is a great tool in my view, many customers can’t use it or don’t want to use it. If you have just a terminal window to the DB server, you have to go with the command line 🙂 In the old days, we didn’t have even 1s and did everything with 0s – do you know the Dilbert strip? 🙂
#3 von Iggy Fernandez am Juli 12, 2010 - 04:54
http://www.dilbert.com/fast/1992-09-08/
Database Programmer: When I started programming, we didn’t have any of those sissy „icons“ and „windows.“ All we had were zeros and ones — and sometimes we didn’t even have ones. I wrote an entire database program using only zeros.
Dilbert: You had zeros? We had to use the letter „o.“
#4 von Uwe Hesse am Juli 12, 2010 - 17:32
Exactly this one 🙂 Thank you Iggy! Doesn’t everybody in the IT branch has got sometimes the impression to live in a Dilbert cartoon? At least I do from time to time …
#5 von Juan José am Juli 16, 2010 - 16:34
Hi Uwe, I try to use your querys ( v$active_session_history)but the view is empty…what should do??
#6 von Uwe Hesse am Juli 21, 2010 - 14:20
Juan José , probably you are on a standard edition or you have not licensed the diagnostic pack and therefore set the parameter CONTROL_MANAGEMENT_PACK_ACCESS to none. In this case, v$active_session_history is indeed empty. Sorry!
#7 von Babu am Juli 26, 2010 - 17:52
Nice Post Uwe..
BAbu
#8 von Marcin Przepiorowski am Juli 31, 2010 - 22:00
If you don’t have license for Performance Pack you can use free simulation of ASH – http://ashmasters.com/
I’m going to blog more about it
regards,
Marcin Przepiorowski
#9 von pan am August 29, 2010 - 17:14
But, how can I know when execution plan for a sql_id was changed (timestamp) ?. In 10.1 this column does not exist in this view
#10 von JB am April 2, 2013 - 01:51
Hi Uwe –
Technically the DB Performance page in Enterprise Manager does not use ASH data, but rather Time Model data (V$SYSMETRIC_HISTORY and V$WAITCLASSMETRIC_HISTORY)
However, the Top Activity page in EM is sourced entirely from ASH data and presents the same picture of Average Active Sessions broken down by Wait Class + CPU.
Having two separate visualizations of the same „picture“ source from independent instrumentation sources may seem redundant and wasteful, but in fact is extremely helpful in identifying and diagnosing potential issues in the instrumentation itself.
And all those 1’s and 0’s guys should check out the ASH Analytics Loadmap, our newest visualization of ASH data.
nice post.
JB
#11 von Uwe Hesse am April 2, 2013 - 07:45
JB, thank you for pointing out this interesting difference! As one of the engineers behind the EM Performance Pages, your input is of course especially appreciated 🙂
#12 von Lloyd am April 10, 2013 - 19:16
Great article. Unfortunately I am a Standard Edition user. Do you have any suggestions or articles about SE? I guess someone on SE would have to gather statistics on their own and analyze them in a similar manner as above.
#13 von Uwe Hesse am April 10, 2013 - 20:41
Lloyd, thanks for the nice words 🙂 You may have a look at Marcin Przepiorowski’s comment above – that tool should work with Standard Edition as well. Have not used it myself yet, though.