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.
