Active Session History 11g

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

select
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

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
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.

Advertisements

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