实现statspack包中比较重要事件的监视，其中包括:实例的命中率，Shared Pool Statistics 和Top Wait Events

参考资料:rdbms下的sp开头的sql，其中最重要的两个是:sprepins.sql和spcpkg.sql

sprepins.sql:生成报表中的计算公式.

spcpkg.sql:介绍计算中所需的涵数以及数据的来源

CREATE OR REPLACE PACKAGE PK_MONIDB AUTHID CURRENT_USER IS
PROCEDURE statspack;
FUNCTION total_event_time RETURN NUMBER;
END PK_MONIDB;
/
create or replace package body pk_moniDb IS
TYPE T_startValue IS TABLE OF NUMBER
INDEX BY VARCHAR2(64);
V_startValue T_startValue;
TYPE T_endValue IS TABLE OF NUMBER
INDEX BY VARCHAR2(64);
V_endValue T_endValue;
TYPE t_top5 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_enent IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE statspack
IS
TYPE C_startCur IS REF CURSOR;
TYPE C_endCur IS REF CURSOR;
/*实例命中率的变量*/
V_startCur C_startCur;
V_endCur C_endCur;
V_bufferNoWait NUMBER;
V_ebnwVal NUMBER;
V_sbnwVal NUMBER;
V_get NUMBER;
V_bufNoWaitSql VARCHAR2(1000);
V_Bufferhit NUMBER;
V_RedoNowait NUMBER;
V_InMemorySort NUMBER;
V_LibraryHit NUMBER;
V_SoftParse NUMBER;
V_ExecuteToParse NUMBER;
V_LatchHit NUMBER;
V_ParseCpuParse NUMBER;
V_NonParseCpu NUMBER;
V_startSql VARCHAR2(1000);
V_endSql VARCHAR2(1000);
v_libsql varchar2(1000);
V_latchsql varchar2(1000);
/*Shared Pool Statistics的变量*/
V_bspm NUMBER;
V_espm NUMBER;
V_bfrm NUMBER;
V_efrm NUMBER;
V_pmSql VARCHAR2(1000);
V_rmSql VARCHAR2(1000);
V_sMenUsage NUMBER;
V_eMenUsage NUMBER;
V_sExecuteMoreThan1 NUMBER;
V_eExecuteMoreThan1 NUMBER;
V_sMenWExec NUMBER;
V_eMenWExec NUMBER;
V_menSql VARCHAR2(2000);
/*top wait event的变量*/
V_startname VARCHAR2(200);
V_startVal NUMBER;
V_endname VARCHAR2(200);
V_endVal NUMBER;
V_eventSql VARCHAR2(1000);
V_ssysstatSql VARCHAR2(1000);
V_esysstatSql VARCHAR2(1000);
V_StartTcpu NUMBER;
V_EndTcpu NUMBER;
V_StartTwt NUMBER;
V_EndTwt NUMBER;
V_Event VARCHAR2(100);
V_wait NUMBER;
V_time NUMBER;
V_elatime NUMBER;
TYPE C_enevtCur IS REF CURSOR;
V_eventCur C_enevtCur;
V_endTime DATE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE previous_events tablespace nmctsm AS SELECT SYSDATE timestamp, v$system_event.* FROM v$system_event';
EXECUTE IMMEDIATE 'create table previous_sysstat tablespace nmctsm as select SYSDATE timestamp,v$sysstat.* from v$sysstat';
EXECUTE IMMEDIATE 'create table previous_waitstat tablespace nmctsm as select * from v$waitstat';
EXECUTE IMMEDIATE 'create table previous_librarycache tablespace nmctsm as select * from v$librarycache';
EXECUTE IMMEDIATE 'create table previous_latch tablespace nmctsm as select * from v$latch';
EXECUTE IMMEDIATE 'create table previous_sgastat tablespace nmctsm as SELECT * FROM v$sgastat';
--EXECUTE IMMEDIATE 'create table previous_sql tablespace nmctsm as select max(sql_text) sql_text, sum(sharable_mem) sharable_mem, sum(sorts) sorts, min(module) module, sum(loaded_versions) loaded_versions, sum(executions) executions, sum(loads) loads, sum(invalidations) invalidations, sum(parse_calls) parse_calls, sum(disk_reads) disk_reads, sum(buffer_gets) buffer_gets, sum(rows_processed) rows_processed, max(command_type) command_type, address address, hash_value hash_value, count(1) version_count, sum(cpu_time) cpu_time, sum(elapsed_time) elapsed_time, max(outline_sid) outline_sid, max(outline_category) outline_category, max(is_obsolete) is_obsolete from v$sql group by hash_value, address';
EXECUTE IMMEDIATE 'CREATE TABLE previous_statsql(total_sql, total_sql_mem, single_use_sql, single_use_sql_mem) TABLESPACE nmctsm AS SELECT count(1), sum(sharable_mem), sum(decode(executions, 1, 1,0)), sum(decode(executions,1,sharable_mem,0)) FROM v$sql where is_obsolete = ''N''';
V_ssysstatSql:='SELECT VALUE FROM previous_sysstat WHERE NAME=''CPU used by this session''';
EXECUTE IMMEDIATE V_ssysstatSql INTO V_StartTcpu;
V_startSql:='SELECT NAME,VALUE FROM previous_sysstat';
OPEN V_startCur FOR V_startSql;
LOOP
FETCH V_startCur INTO V_startname,V_startVal;
V_startValue(TRIM(V_startname)):=V_startVal;
EXIT WHEN V_startCur %NOTFOUND ;
END LOOP;
--dbms_lock.sleep (1800);
V_endTime:=SYSDATE+1/48;
IF SYSDATE>V_endTime THEN
NULL;
END IF ;
/*实例命中率的计算*/
V_endSql:='SELECT NAME,VALUE FROM v$sysstat';
OPEN V_endCur FOR V_endSql;
LOOP
FETCH V_endCur INTO V_endname,V_endVal;
V_endValue(TRIM(V_endname)):=V_endVal;
EXIT WHEN V_endCur %NOTFOUND ;
END LOOP;
V_ebnwVal:= V_endValue('session logical reads');
V_sbnwVal:= V_startValue('session logical reads');
V_get:= V_ebnwVal-V_sbnwVal;
V_bufNoWaitSql:='select round(sum(b.count-a.count)/'||V_get||',4)*100 from previous_waitstat a,V$waitstat b';
EXECUTE IMMEDIATE V_bufNoWaitSql INTO V_bufferNoWait;
V_Bufferhit:=(1-trunc(((V_endValue('physical reads')-V_startValue('physical reads'))-((V_endValue('physical reads direct')-V_startValue('physical reads direct'))+(V_endValue('physical reads direct (lob)')-V_startValue('physical reads direct (lob)'))))/((V_endValue('db block gets')-V_startValue('db block gets'))+(V_endValue('consistent gets')-V_startValue('consistent gets'))-((V_endValue('physical reads direct')-V_startValue('physical reads direct'))+(V_endValue('physical reads direct (lob)')-V_startValue('physical reads direct (lob)')))),4))*100;
V_RedoNowait:=trunc(1-((V_endValue('redo log space requests')-V_startValue('redo log space requests'))/(V_endValue('redo entries')-V_startValue('redo entries'))),4)*100;
v_libsql:='SELECT round((1-sum(a.pinhits-b.pinhits)/sum(a.pins-b.pins))*100,2) FROM v$librarycache a,previous_librarycache b';
EXECUTE IMMEDIATE v_libsql INTO v_Libraryhit;
V_InMemorySort:=round((V_endValue('sorts (memory)')-V_startValue('sorts (memory)'))/((V_endValue('sorts (memory)')-V_startValue('sorts (memory)'))+(V_endValue('sorts (memory)')-V_startValue('sorts (memory)'))),4)*100;
V_SoftParse:=round(1-((V_endValue('parse count (hard)')-V_startValue('parse count (hard)'))/(V_endValue('parse count (total)')-V_startValue('parse count (total)')))*100,2);
V_ExecuteToParse:=round((1-(V_endValue('parse count (total)')-V_startValue('parse count (total)'))/(V_endValue('execute count')-V_startValue('execute count')))*100,2);
v_latchsql:='SELECT round((1-sum(a.misses-b.misses)/sum(a.gets-b.gets))*100,2) FROM v$latch a,previous_latch b';
EXECUTE IMMEDIATE v_latchsql INTO v_latchhit;
V_ParseCpuParse:=round((1-(V_endValue('parse time cpu')-V_startValue('parse time cpu'))/(V_endValue('parse time elapsed')-V_startValue('parse time elapsed')))*100,2);
V_NonParseCpu:=round((1-(V_endValue('parse time cpu')-V_startValue('parse time cpu'))/(V_endValue('parse count (total)')-V_startValue('parse count (total)')))*100,2);
dbms_output.put_line('buffer nowait: '||V_bufferNoWait);
dbms_output.put_line('buffer hit='||V_Bufferhit);
dbms_output.put_line('redo nowait='||V_RedoNowait);
dbms_output.put_line('Library Hit='||V_LibraryHit);
dbms_output.put_line('In-memory sort ratio='||V_InMemorySort);
dbms_output.put_line('Soft Parse='||V_SoftParse);
dbms_output.put_line('Execute to Parse='||V_ExecuteToParse);
dbms_output.put_line('Latch Hit='||V_LatchHit);
dbms_output.put_line('Parse CPU to Parse Elapsd='||V_ParseCpuParse);
dbms_output.put_line('Non-Parse CPU='||V_NonParseCpu);
/*Shared Pool Statistics计算*/
V_pmSql:='SELECT sum(a.bytes),sum(b.bytes) FROM previous_sgastat a,v$sgastat b WHERE a.pool IN (''shared pool'',''all pools'') AND b.pool IN (''shared pool'',''all pools'') AND a.name=b.name';
EXECUTE IMMEDIATE V_pmSql INTO V_bspm,V_espm;
V_rmSql:='SELECT a.BYTES,b.BYTES FROM previous_sgastat a,v$sgastat b WHERE a.pool IN (''shared pool'',''all pools'') AND b.pool IN (''shared pool'',''all pools'') AND a.name=b.name AND a.name=''free memory''';
EXECUTE IMMEDIATE V_rmSql INTO V_bfrm,V_efrm;
V_sMenUsage:=round(100*(1-V_bfrm/V_bspm),2);
V_eMenUsage:=round(100*(1-V_efrm/V_espm),2);
EXECUTE IMMEDIATE 'CREATE TABLE end_statsql(total_sql, total_sql_mem, single_use_sql, single_use_sql_mem) TABLESPACE nmctsm AS SELECT count(1), sum(sharable_mem), sum(decode(executions, 1, 1,0)), sum(decode(executions,1,sharable_mem,0)) FROM v$sql where is_obsolete = ''N''';
V_menSql:='select round(100*(1-b.single_use_sql/b.total_sql),2),round(100*(1-e.single_use_sql/e.total_sql),2),round(100*(1-b.single_use_sql_mem/b.total_sql_mem),2),round(100*(1-e.single_use_sql_mem/e.total_sql_mem),2) from previous_statsql b, end_statsql e';
EXECUTE IMMEDIATE V_menSql INTO V_sExecuteMoreThan1,V_eExecuteMoreThan1,V_sMenWExec,V_eMenWExec;
dbms_output.put_line('Memory Usage %: '||V_sMenUsage||' '||V_eMenUsage);
dbms_output.put_line('% SQL with executions>1: '||V_sExecuteMoreThan1||' '||V_eExecuteMoreThan1);
dbms_output.put_line('% Memory for SQL w/exec>1: '||V_sMenWExec||' '||V_eMenWExec);
/*Top Wait Events计算 */
V_esysstatSql:='select value from v$sysstat where NAME=''CPU used by this session''';
EXECUTE IMMEDIATE V_esysstatSql INTO V_EndTcpu;
V_eventSql:='select * from(SELECT A.event,A.total_waits - NVL(B.total_waits, 0) total_waits,A.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0) time,round(decode(('||V_EndTcpu||'-'||V_StartTcpu||') + ('||V_EndTcpu||'-'||V_StartTcpu||')*10000, 0, 0,100* (a.time_waited_micro - nvl(b.time_waited_micro,0))/(('||V_EndTcpu||'-'||V_StartTcpu||') + ('||V_EndTcpu||'-'||V_StartTcpu||')*10000)),2) ela_time FROM v$system_event A, previous_events B WHERE a.event not in (select event from sm_idle_event) and B.event(+) = A.event ORDER BY time desc) where rownum<=5'; OPEN V_eventCur FOR V_eventSql;
LOOP
FETCH V_eventCur INTO V_Event,V_wait,V_time,V_elatime;
EXIT WHEN V_eventCur%NOTFOUND;
dbms_output.put_line('事件:'||V_Event||' total_wait:'||V_wait||' time:'||V_time||' ela time:'||V_elatime);
END LOOP;
CLOSE V_eventCur;
EXECUTE IMMEDIATE 'drop table previous_events';
EXECUTE IMMEDIATE 'drop table previous_sysstat';
EXECUTE IMMEDIATE 'drop table previous_waitstat';
EXECUTE IMMEDIATE 'drop table previous_librarycache';
EXECUTE IMMEDIATE 'drop table previous_latch';
EXECUTE IMMEDIATE 'drop table previous_sgastat';
EXECUTE IMMEDIATE 'drop table previous_statsql';
EXECUTE IMMEDIATE 'drop table end_statsql';
END statspack;
FUNCTION total_event_time RETURN NUMBER
IS
V_TwtVale NUMBER;
V_twtSql VARCHAR2(2000);
BEGIN
V_twtSql:='select sum(time_waited_micro) from v$system_event where event not in (select event from sm_idle_event)';
EXECUTE IMMEDIATE V_twtSql INTO V_TwtVale;
END total_event_time;
end pk_moniDb;
/