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’)
Advertisements

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 )

w

Connecting to %s