監控指令碼

---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/，如需轉載，請註明出處，否則將追究法律責任。