Most CPU consuming SQL Queries in Oracle.

Step 1: You need to find out the SID of the session which is consuming highest CPU with the following query

select    ss.username,   se.SID,   VALUE/100 cpu_usage_seconds from    v$session ss,   v$sesstat se,   v$statname sn where    se.STATISTIC# = sn.STATISTIC#  and NAME like '%CPU used by this session%'  and se.SID = ss.SID and    ss.status='ACTIVE'  and    ss.username is not null order by VALUE desc;


Step 2: For that SID you can find out the SQL hash value from v$session on which SQL query its working. From that SQL hash value you can get the sql_text from v$sqlarea view. It will provide you the completed SQL query executing by the session.

select sql_text from v$sqlarea where hash_value in
(select sql_hash_value from v$session where SID = '28')