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
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;