Performance Monitoring: Active Session History at work

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 :-)

About these ads

,

  1. #1 by Statistique on July 8, 2010 - 15:41

    I always used the graphic interface. Those little SELECT are so nice, works perfectly !

    Thanks for sharing !

  2. #2 by Uwe Hesse on July 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. #3 by Iggy Fernandez on July 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. #4 by Uwe Hesse on July 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. #5 by Juan José on July 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. #6 by Uwe Hesse on July 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. #7 by Babu on July 26, 2010 - 17:52

    Nice Post Uwe..

    BAbu

  8. #8 by Marcin Przepiorowski on July 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. #9 by pan on 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. #10 by JB on 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. #11 by Uwe Hesse on 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. #12 by Lloyd on 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. #13 by Uwe Hesse on 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,604 other followers

%d bloggers like this: