---TOP EVNET--- 不对
SELECT ROUND((TIMEWAIT / SUM(TIMEWAIT) OVER()) * 100, 2) AS PCT,
EVENT,
WAIT_CLASS
FROM (SELECT SUM(TIME_WAITED) AS TIMEWAIT, EVENT, WAIT_CLASS
FROM V$ACTIVE_SESSION_HISTORY
GROUP BY EVENT, WAIT_CLASS)
ORDER BY PCT DESC;
---TOP MODULE---
SELECT ROUND((SECWAIT / SUM(SECWAIT) OVER()) * 100, 2) AS PCT, MODULE
FROM (SELECT SUM(SECONDS_IN_WAIT) AS SECWAIT, MODULE
FROM GV$SESSION
GROUP BY MODULE)
ORDER BY PCT DESC;
---TOP MACHINE---
SELECT ROUND((SECWAIT / SUM(SECWAIT) OVER()) * 100, 2) AS PCT, MACHINE
FROM (SELECT SUM(SECONDS_IN_WAIT) AS SECWAIT, MACHINE
FROM GV$SESSION
GROUP BY MACHINE)
ORDER BY PCT DESC;
--TOP SESSION-- 不太对
SELECT ROUND((SECWAIT / SUM(SECWAIT) OVER()) * 100, 2) AS PCT,
SID,
USERNAME,
MODULE
FROM (SELECT SUM(SECONDS_IN_WAIT) AS SECWAIT, SID, USERNAME, MODULE
FROM GV$SESSION
GROUP BY SID, USERNAME, MODULE)
WHERE USERNAME IS NOT NULL
ORDER BY PCT DESC;
SELECT CASE
WHEN SUM(TIMEWAIT) OVER() = 0 THEN
0
ELSE
ROUND((TIMEWAIT / SUM(TIMEWAIT) OVER()) * 100, 2)
END AS PCT,
ROUND(TIMEWAIT / 1000 / 1000, 2) AS S,
DECODE(EVENT, NULL, 'DB CPU', EVENT) AS EVENT,
WAIT_CLASS
FROM (SELECT * FROM (SELECT DECODE(SUM(TM_DELTA_DB_TIME), NULL, 0, SUM(TM_DELTA_DB_TIME)) AS TIMEWAIT,
EVENT,
WAIT_CLASS
FROM V$ACTIVE_SESSION_HISTORY
GROUP BY EVENT, WAIT_CLASS
ORDER BY TIMEWAIT DESC) WHERE ROWNUM < 11)
ORDER BY PCT DESC;
--
SELECT ROUND((SECONDS_IN_WAIT / SUM(SECONDS_IN_WAIT) OVER()) * 100, 2) AS PCT,
TOTAL_WAITS,
ROUND(SUM(SECONDS_IN_WAIT) OVER() / 1000 / 1000 / TOTAL_WAITS, 2) AS AVG_WAIT_TIME,
DECODE(EVENT, NULL, 'DB CPU', EVENT) AS EVENT,
WAIT_CLASS
FROM (SELECT COUNT(*) TOTAL_WAITS,
TRUNC(SUM(TIME_WAITED)) SECONDS_IN_WAIT,
EVENT,
WAIT_CLASS
FROM V$ACTIVE_SESSION_HISTORY
WHERE SESSION_STATE = 'WAITING'
AND TIME_WAITED > 0
GROUP BY EVENT, WAIT_CLASS)
ORDER BY PCT DESC
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-2142878/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-2142878/