1. 授权相应的视图权限
grant select on v_$statname to rudy ; grant select on v_$mystat to rudy ; grant select on v_$latch to rudy ; grant select on v_$timer to rudy ;
2. 创建临时表
CREATE OR REPLACE VIEW STATS
AS
SELECT 'STAT...'
||A.NAME NAME,
B.VALUE
FROM V$STATNAME A,
V$MYSTAT B
WHERE A.STATISTIC# = B.STATISTIC#
UNION ALL
SELECT 'LATCH.'
||NAME,
GETS
FROM V$LATCH
UNION ALL
SELECT 'STAT...Elapsed Time',
HSECS
FROM V$TIMER;
3.创建临时表
CREATE GLOBAL TEMPORARY TABLE RUN_STATS ( RUNID VARCHAR2(15), NAME VARCHAR2(80), VALUE INT) ON COMMIT PRESERVE ROWS;4.创建相应的runstat包
CREATE OR REPLACE PACKAGE RUNSTATS_PKG AS PROCEDURE RS_START; PROCEDURE RS_MIDDLE; PROCEDURE RS_STOP(P_DIFFERENCE_THRESHOLD IN NUMBER DEFAULT 0); END;
CREATE OR REPLACE PACKAGE BODY RUNSTATS_PKG
AS
G_START NUMBER;
G_RUN1 NUMBER;
G_RUN2 NUMBER;
PROCEDURE RS_START
IS
BEGIN
DELETE FROM RUN_STATS;
INSERT INTO RUN_STATS
SELECT 'before',
STATS.*
FROM STATS;
G_START := DBMS_UTILITY.GET_CPU_TIME;
END;
PROCEDURE RS_MIDDLE
IS
BEGIN
G_RUN1 := (DBMS_UTILITY.GET_CPU_TIME - G_START);
INSERT INTO RUN_STATS
SELECT 'after 1',
STATS.*
FROM STATS;
G_START := DBMS_UTILITY.GET_CPU_TIME;
END;
PROCEDURE RS_STOP
(P_DIFFERENCE_THRESHOLD IN NUMBER DEFAULT 0)
IS
BEGIN
G_RUN2 := (DBMS_UTILITY.GET_CPU_TIME - G_START);
DBMS_OUTPUT.PUT_LINE('Run1 ran in '
||G_RUN1
||' cpu hsecs');
DBMS_OUTPUT.PUT_LINE('Run2 ran in '
||G_RUN2
||' cpu hsecs');
IF (G_RUN2 <> 0) THEN
DBMS_OUTPUT.PUT_LINE('run 1 ran in '
||ROUND(G_RUN1 / G_RUN2 * 100,2)
||'% of the time');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
INSERT INTO RUN_STATS
SELECT 'after 2',
STATS.*
FROM STATS;
DBMS_OUTPUT.PUT_LINE(RPAD('Name',30)
||LPAD('Run1',12)
||LPAD('Run2',12)
||LPAD('Diff',12));
FOR X IN (SELECT RPAD(A.NAME,30)
||TO_CHAR(B.VALUE - A.VALUE,'999,999,999')
||TO_CHAR(C.VALUE - B.VALUE,'999,999,999')
||TO_CHAR(((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)),
'999,999,999') DATA
FROM RUN_STATS A,
RUN_STATS B,
RUN_STATS C
WHERE A.NAME = B.NAME
AND B.NAME = C.NAME
AND A.RUNID = 'before'
AND B.RUNID = 'after 1'
AND C.RUNID = 'after 2'
AND ABS((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)) > P_DIFFERENCE_THRESHOLD
ORDER BY ABS((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)))
LOOP
DBMS_OUTPUT.PUT_LINE(X.DATA);
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Run1 latches total versus runs -- difference and pct');
DBMS_OUTPUT.PUT_LINE(LPAD('Run1',12)
||LPAD('Run2',12)
||LPAD('Diff',12)
||LPAD('Pct',10));
FOR X IN (SELECT TO_CHAR(RUN1,'999,999,999')
||TO_CHAR(RUN2,'999,999,999')
||TO_CHAR(DIFF,'999,999,999')
||TO_CHAR(ROUND(RUN1 / DECODE(RUN2,0,TO_NUMBER(0),
RUN2) * 100,2),'99,999.99')
||'%' DATA
FROM (SELECT SUM(B.VALUE - A.VALUE) RUN1,
SUM(C.VALUE - B.VALUE) RUN2,
SUM((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)) DIFF
FROM RUN_STATS A,
RUN_STATS B,
RUN_STATS C
WHERE A.NAME = B.NAME
AND B.NAME = C.NAME
AND A.RUNID = 'before'
AND B.RUNID = 'after 1'
AND C.RUNID = 'after 2'
AND A.NAME LIKE 'LATCH%'))
LOOP
DBMS_OUTPUT.PUT_LINE(X.DATA);
END LOOP;
END;
END;
6.示例
SQL> set serveroutput on
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed
SQL> insert into t1 select level from dual connect by level <= 10000 ;
10000 rows inserted
SQL> exec runstats_pkg.rs_stop(100);
Run1 ran in 1 cpu hsecs
Run2 ran in 2 cpu hsecs
run 1 ran in 50% of the time
Name Run1 Run2 Diff
LATCH.SQL memory manager worka 271 137 -134
STAT...session logical reads 47 181 134
STAT...db block changes 46 186 140
STAT...Elapsed Time 983 822 -161
LATCH.cache buffers chains 166 672 506
STAT...bytes sent via SQL*Net 882 1,778 896
STAT...bytes received via SQL* 1,942 3,920 1,978
STAT...undo change vector size 3,340 26,968 23,628
STAT...session uga memory -28,736 0 28,736
LATCH.JS slv state obj latch 1 -32,730 -32,731
STAT...redo size 4,420 148,316 143,896
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,545 -30,854 -32,399 -5.01%
PL/SQL procedure successfully completed
