ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10gTop N Timed Events统计

10gTop N Timed Events统计

原创 Linux操作系统 作者:yangzhangyue 时间:2013-09-18 11:08:29 0 删除 编辑
我们可以通过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>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-772881/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219781