Highest CPU consuming SQL Queries in Oracle

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')

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.