Monitor oracle using Active Session History 11g

Prior to Oracle 10g the answer was not easily unless you did something
like run an SQL trace on the process. Oracle 10g introduced Active Session History,
which changed things for process monitoring. From the Oracle 11g Release 2 documentation:

“The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.
Active sessions are sampled every second and are stored in a circular buffer in SGA.
Any session that is connected to the database and is waiting for an event that does not
belong to the Idle wait class is considered as an active session. This includes any session
that was on the CPU at the time of sampling.”

So, with Active Session History (often abbreviated to ASH), Oracle samples the database
every second and stores details of every session that is considered “active”, meaning that
it is waiting on a non-idle wait event or is executing on a CPU. These details are exposed
via the data dictionary view v$active_session_history (or gv$active_session_history if you’re
operating a RAC environment).


Even when sessions are inactive they can be executing SQL’s in the background.
You have to identify the SQL’s which are executing.

Run this query as sysdba user.
If you are using RAC then replace v$active_session_history with gv$active_session_history

    ash.SQL_ID ,
    sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
    sum(decode(ash.session_state,’WAITING’,1,0)) -
    sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” ,
    sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” ,
    sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
    from v$active_session_history ash,v$event_name en
    where SQL_ID is not NULL and en.event#=ash.event#
    group by sql_id ;

You will see something like

    SQL_ID               CPU       WAIT         IO      TOTAL
    ————- ———- ———- ———- ———-------------------------------------------
    8tfvwyvfm5cjn          0          1          0          1
    1xpfbutrrzkph          0          1          0          1
    aykvshm7zsabd          0          1          0          1
    92f47aa2q2rmd          0          1          0          1
    92b382ka0qgdt          0          4          0          4
    1h50ks4ncswfn          0          3          1          4
    f99a23s9aba0z          0          0          1          1
    f6cz4n8y72xdc          0          0          1          1
    cvn54b7yz0s8u          0          0          1          1
    3s58mgk0uy2ws          0          1          0          1
    fnk7155mk2jq6          0          1          0          1
    c2p32r5mzv8hb          0          1          0          1
    5p6a1yss527ap          0          1          0          1
    6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID and run below query

    SELECT sql_text FROM v$sqlarea WHERE sql_id = ’1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing.
Maybe it is a poorly written SQL which is locking the table or looping on the table.
Do an explain plan for this statement.

    SELECT * FROM table(dbms_xplan.display_awr(’1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database.
This procedure was run on a 11gR2 database but is also valid for 10gR2.

Check ==> DB active_session_history


Leave a Reply

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

You are commenting using your 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