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’)
(select sql_hash_value from v$session where SID = ’28’)
Advertisements
