首页 > Linux操作系统 > Linux操作系统 > 耗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/,如需转载,请注明出处,否则将追究法律责任。