我们可以通过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>
10gTop N Timed Events统计
-
yangzhangyue
2013-09-18 11:08:29 -
Linux操作系统
- 原创