10gTop N Timed Events統計

我們可以透過awr去檢視 Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONETop 5 Timed Events，但粒度還是不夠細，對於秒殺類的活動，我們可能需要統計到分鐘甚至更細，我們也可能老是更改awr的粒度，我們可以採用一種變通的方式去實現類似更能。
在10g中，並沒有方便統計分鐘級的事件等待時間，為了方便統計分鐘級的等待事件等待時間，
可以採用如下方式
--建立v$sys_time_model備份表
create table SYSTIMEMODEL_LOG
(
  STAT_ID    NUMBER,
  STAT_NAME  VARCHAR2(64),
  VALUE      NUMBER,
  CREATETIME DATE
)
partition by range (CREATETIME)
(
  partition P_20131001 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20131101 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20131201 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20140101 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor
)
;
--建立v$system_event備份表
create table SYSTEMEVENT_LOG
(
  EVENT             VARCHAR2(64),
  TOTAL_WAITS       NUMBER,
  TOTAL_TIMEOUTS    NUMBER,
  TIME_WAITED       NUMBER,
  AVERAGE_WAIT      NUMBER,
  TIME_WAITED_MICRO NUMBER,
  EVENT_ID          NUMBER,
  WAIT_CLASS_ID     NUMBER,
  WAIT_CLASS#       NUMBER,
  WAIT_CLASS        VARCHAR2(64),
  CREATETIME        DATE
)
partition by range (CREATETIME)
(
  partition P_20131001 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20131101 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20131201 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor,
  partition P_20140101 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace monitor
)
;

--建立備份v$system_event與v$sys_time_model備份的儲存過程。
CREATE OR REPLACE PROCEDURE productmon.PRC_SYSTIMEMODEL IS
BEGIN
  insert into SYSTIMEMODEL_LOG
    select STAT_ID ,STAT_NAME,VALUE,sysdate from v$sys_time_model;
  commit;
END PRC_SYSTIMEMODEL;

CREATE OR REPLACE PROCEDURE PRC_SYSTEMEVENT IS
BEGIN
  insert into SYSTEMEVENT_LOG
    select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT,TIME_WAITED_MICRO,EVENT_ID,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,sysdate from v$system_event;
  commit;
END PRC_SYSTEMEVENT;

--建立job，每分鐘執行一次
begin
productmon.PRC_SYSTIMEMODEL;
productmon.PRC_SYSTEMEVENT;
end;

--按照awr中sql改寫查詢sql，這樣我們就可以統計人員粒度為分鐘的等待事件查詢了，這對於秒殺類的活動的負載統計將十分有效
SELECT EVENT,
       WAITS,
       TIME,
       DECODE(WAITS,
              NULL,
              TO_NUMBER(NULL),
              0,
              TO_NUMBER(NULL),
              TIME / WAITS * 1000) AVGWT,
       PCTWTT,
       WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT EVENT,
                       E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       ((SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log e
                          WHERE createtime=to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log b
                          WHERE  createtime=to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM PRODUCTMON.SYSTEMEVENT_LOG B, PRODUCTMON.SYSTEMEVENT_LOG E
                 WHERE B.createtime = to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                   AND E.createtime = to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                   AND B.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log e
                          WHERE e.createtime = to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log b
                          WHERE b.createtime = to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                            AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM PRODUCTMON.Systimemodel_Log e
                                WHERE e.createtime = to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM PRODUCTMON.Systimemodel_Log b
                                WHERE b.createtime = to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                                  AND b.STAT_NAME = 'DB CPU')) /
                       ((SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log e
                          WHERE e.createtime = to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log b
                          WHERE b.createtime = to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log e
                          WHERE e.createtime = to_date('2013-9-18 10:12:17','yyyy-mm-dd hh24:mi:ss')
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM PRODUCTMON.Systimemodel_Log b
                          WHERE b.createtime = to_date('2013-9-18 10:11:17','yyyy-mm-dd hh24:mi:ss')
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 5;

EVENT                                                                 WAITS       TIME      AVGWT     PCTWTT WAIT_CLASS
---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
CPU time                                                                    384.046156            45.9012772
db file sequential read                                              102380 235.221073 2.29752952 28.1136720 User I/O
db file scattered read                                                51499  97.292668 1.88921470 11.6284401 User I/O
read by other session                                                 73688  77.392337 1.05027055 9.24994838 User I/O
log file sync                                                         30880  60.359582 1.95464967 7.21418992 Commit

5 rows selected

Executed in 0.157 seconds

10:49:15 SQL>