SQL QUERY TO FIND TOP SQL ORACLE

My job was at stake due to performance issue from a financial application database.Client was bleeding money due to report deadline with in financial year.

There should be a quick way to identify the top SQL which eats up the database bit by bit and crash the database. Thats why its always keep a handy query like this often in your script list

The below sql will identify the top sql which burns CPU and have high elapsed time

SQL Query:

col EVENT format a20
col AVERAGE_WAIT format 99999999
col TIME_WAITED format 99999999
column WAIT_CLASS format a15
col sid format 999999
col sql_text format a40
select * from (select se.sid,se.event,se.AVERAGE_WAIT,se.TIME_WAITED,se.WAIT_CLASS,sq.sql_text
from v$session_event se
inner join v$session s on s.sid = se.sid
inner join v$sql sq on sq.sql_id = s.sql_id
where sq.sql_text not like '%select se.sid,se.event,se .AVERAGE_WAIT%'
order by AVERAGE_WAIT desc)
where rownum < 6;

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 )

Google photo

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

Connecting to %s