DBA Troubleshoot Queries

Top Ten most CPU TIME Queries

SELECT sql_text,ROUND (CPU_TIME/1000000) "CPU_TIME in Seconds"
FROM (SELECT  sql_text, CPU_TIME,rank() over ( order by CPU_TIME DESC) rank_desc
FROM v$sql)
WHERE
rank_desc>10;

 

Query to get user etc. consuming high CPU

SELECT se.username, se.sid, se.MACHINE, se.TERMINAL, se.STATUS, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE  '%CPU used by this session%'
AND se.sid = ss.SID 
AND se.username IS NOT NULL
ORDER BY value DESC 

Query to get Full Sql and other info with the help of Unix PID:

select ss.USERNAME,ss.sid, ss.MACHINE, ss.TERMINAL, ss.STATUS,sa.sql_fulltext
from v$session ss, v$sqlarea sa, v$process p
where 
sa.HASH_VALUE=ss.PREV_HASH_VALUE
and p.addr = ss.paddr
and p.spid = &unix_pid;

Query to get Full Sql and other info with the help of session SID:

SELECT B.USERNAME, B.MACHINE, B.TERMINAL, B.STATUS, A.SQL_FULLTEXT, A.SQL_TEXT
FROM V$SQLAREA A, V$SESSION B
WHERE A.HASH_VALUE=B.PREV_HASH_VALUE   
AND B.SID= &oracle_sid;

–Top Most execution associated with SQL

SELECT sql_text, executions
FROM (SELECT  sql_text, executions, rank() over ( order by executions DESC) rank_desc
FROM v$sql)
WHERE
rank_desc >10
  
–Top Most Fetch associated with SQL  

SELECT sql_text, fetches
FROM
(SELECT sql_text, fetches, rank() over ( order by fetches DESC) rank_desc
FROM v$sql)
WHERE
rank_desc >10;

— locks that are currently imposed on library cache objects

SELECT a.object,a.type,a.sid,s.username,s.osuser,s.program
FROM   v$access a, v$session s
WHERE  a.sid   = s.sid
AND    a.owner = UPPER('&ENTER_SCHEMA_NAME')
AND    a.sid = &enter_session_id
ORDER BY a.object
/

–SQL's  running in current transaction:

select  ss.sid,ss.serial#,ss.username,ss.status,t.start_time,sq.sql_id,sq.sql_FULLTEXT,
ss.osuser,ss.process,ss.machine,ss.terminal,ss.program,ss.module,to_char(ss.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from  v$session ss,v$transaction t, v$sql sq
where  t.ses_addr  = ss.saddr
–and ss.prev_hash_value=sq.hash_value
and ss.sql_id = sq.sql_id
order by start_time;

 

get SID on locked table
 –Lock on Table


select a.sid, a.serial#
from v$session ss, v$locked_object lo, dba_objects do 
where lo.object_id = do.object_id 
and ss.sid = lo.session_id
and OBJECT_NAME='<object name>'
and username='<user name';

SELECT * FROM v$access WHERE OBJECT ='OBJECT_NAME';

 

You can leave a response, or trackback from your own site.

Leave a Reply