耗cpu sql ---013

SELECT   sql_text,
         spid,
         v$session.program,
         process
  FROM   v$sqlarea, v$session, v$process
 WHERE       v$sqlarea.address = v$session.sql_address
         AND v$sqlarea.hash_value = v$session.sql_hash_value
         AND v$session.paddr = v$process.addr
         AND v$process.spid IN ('');

  -----耗cpu的pid執行的sql

select sid,event,p1,p1text from v$session_wait;

  -----程式等待事件
 
select spid from v$process where addr in
 (select paddr from v$session where sid in(926));

  -----latch的等待都是什麼程式產生的

  SELECT   latch#,
           name,
           gets,
           misses,
           sleeps
    FROM   v$latch
   WHERE   sleeps > 0
ORDER BY   sleeps;

-------latch的等待找出最大的sleeps的latch

  SELECT   addr,
           latch#,
           gets,
           misses,
           sleeps
    FROM   v$latch_children
   WHERE   sleeps > 0 AND latch# = 122
ORDER BY   sleeps DESC;

SELECT   DISTINCT a.owner, a.segment_name, a.segment_type
  FROM   dba_extents a,
         (SELECT   dbarfil, dbablk
            FROM   x$bh
           WHERE   hladdr IN (SELECT   addr
                                FROM   (  SELECT   addr
                                            FROM   v$latch_children
                                        ORDER BY   sleeps DESC)
                               WHERE   ROWNUM < 5)) b
 WHERE       a.relative_fno = b.dbarfil
         AND a.block_id <= b.dbablk
         AND a.block_id + a.blocks > b.dbablk;

--檢視sleep多的子latch都有哪些物件

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

---耗cpu的sql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-703061/，如需轉載，請註明出處，否則將追究法律責任。