sql for awr summary

PROCEDURE REPORT_SUMMARY(L_DBID IN NUMBER,

L_INST_NUM IN NUMBER,

L_BID IN NUMBER,

L_EID IN NUMBER,

L_OPTIONS IN NUMBER,

TO_HTML IN BINARY_INTEGER,

INIT_RPT IN BINARY_INTEGER DEFAULT TRUE_I)

IS

OUTPUT OUTPUT_TYPE;

EVENT_FMT OUTPUT_TYPE;

WAITS_FMT OUTPUT_TYPE;

TIME_FMT OUTPUT_TYPE;

PCTW_FMT OUTPUT_TYPE;

CLASS_FMT OUTPUT_TYPE;

AVGWT_FMT OUTPUT_TYPE;

COLOFFSET NUMBER;

SUMM_STATS_1 STAT_MAPPING;



L_DBTIME_MINS NUMBER;

BSUS NUMBER;

ESUS NUMBER;

BSUSM NUMBER;

ESUSM NUMBER;

NUM_INST1 NUMBER;

NUM_INST2 NUMBER;

----- SELECT VALUE FROM DBA_HIST_SYS_TIME_MODEL

-----WHERE DBID=L_DBID AND SNAP_ID=I_SNAP_ID AND

INSTANCE_NUMBER=L_INST_NUM AND STAT_NAME=I_NAME;

--------------RPT_STATS(STAT_DBTIME) = 'DB time'

--------------RPT_STATS(STAT_CPU_TIME) = 'DB CPU'

CURSOR WAIT_EVENT_CUR(TOP_N_EVENTS NUMBER) IS

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_NAME 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)) /

RPT_STATS(STAT_DBTIME) PCTWTT,

E.WAIT_CLASS WAIT_CLASS

FROM DBA_HIST_SYSTEM_EVENT B,

DBA_HIST_SYSTEM_EVENT E

WHERE B.SNAP_ID(+) = L_BID

AND E.SNAP_ID = L_EID

AND B.DBID(+) = L_DBID

AND E.DBID = L_DBID

AND B.INSTANCE_NUMBER(+) = L_INST_NUM

AND E.INSTANCE_NUMBER = L_INST_NUM

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,

RPT_STATS(STAT_CPU_TIME)/1000000 TIME,

100 * RPT_STATS(STAT_CPU_TIME) /

RPT_STATS(STAT_DBTIME) PCTWTT,

NULL WAIT_CLASS

FROM DUAL

WHERE RPT_STATS(STAT_CPU_TIME) > 0)

ORDER BY TIME DESC, WAITS DESC)

WHERE ROWNUM <= TOP_N_EVENTS;

WAIT_EVENT_OUT WAIT_EVENT_CUR%ROWTYPE;

ROW_NUM NUMBER := 0;

BEGIN

IF (INIT_RPT = TRUE_I) THEN ----这个判断是判断选择生成的类型是文本还是 html

REPORT_INIT(L_DBID, L_INST_NUM, L_BID, L_EID, RPT_STATS, RPT_PARAMS,

RPT_TIME_VALS);

END IF;

----- SELECT VALUE FROM DBA_HIST_SYS_TIME_MODEL


INSTANCE_NUMBER=L_INST_NUM AND STAT_NAME=I_NAME;

--------------RPT_STATS(STAT_TIMEDSTAT_B) = 'timed_statistics'

--------------RPT_STATS(STAT_STATLEVEL_B) = 'statistics_level'

--------------RPT_STATS(STAT_TIMEDSTAT_E) = 'timed_statistics'

--------------RPT_STATS(STAT_STATLEVEL_E) = 'statistics_level'

IF ((RPT_PARAMS(STAT_TIMEDSTAT_B) = 'FALSE') OR

(RPT_PARAMS(STAT_TIMEDSTAT_E) = 'FALSE')) THEN ------此判断是检测开始时间与结束时间是否检测不到

APPEND_ROW('WARNING: timed_statistics setting was set to ''FALSE'' ' ||

'in the snapshot ');

APPEND_ROW('period: TIMINGS ARE INVALID');

APPEND_ROW(BLANK_LINE);

IF (TO_HTML = TRUE_I) THEN

APPEND_ROW(LINE_BREAK);

END IF;

END IF;

IF ((RPT_PARAMS(STAT_STATLEVEL_B) = 'BASIC') OR

(RPT_PARAMS(STAT_STATLEVEL_E) = 'BASIC')) THEN --------此判断是检测时间与结束时间

APPEND_ROW('WARNING: statistics_level setting was set to ''BASIC'' ' ||

'in the snapshot ');

APPEND_ROW('period: TIME MODEL DATA IS INVALID');




END IF;

END IF;


APPEND_ROW('<H1 '|| AWR_CLASS ||'>');

END IF;

APPEND_ROW('WORKLOAD REPOSITORY report for');



APPEND_ROW('</H1>');

END IF;


INST_INFO_HTML(L_DBID,L_INST_NUM);

ELSE

INST_INFO_TEXT(L_DBID,L_INST_NUM);


END IF;

------ 输出数据库头信息 _NAME,dbid,INSTNAME,INST_NUM, VERSION,PARALLEL,HOSTNAME

L_DBTIME_MINS := (RPT_STATS(STAT_DBTIME) / 1000000) / 60;


SNAPSHOT_INFO_HTML(L_BID,L_EID,L_DBTIME_MINS);

APPEND_ROW(BEGIN_SECTION_TITLE ||

A_NAME_PREFIX || SUMMARY || A_SUFFIX|| END_A ||

'Report Summary' || END_SECTION_TITLE);

ELSE

SNAPSHOT_INFO_TEXT(L_BID,L_EID,L_DBTIME_MINS);


END IF;

--------- SNAPSHOT_INFO_HTML(L_BID,L_EID,L_DBTIME_MINS);

---------- elapsed:

----------SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME -

B.END_INTERVAL_TIME) * 1440

---------- + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -

B.END_INTERVAL_TIME) * 60

---------- + EXTRACT(MINUTE FROM E.END_INTERVAL_TIME -

B.END_INTERVAL_TIME)

---------- + EXTRACT(SECOND FROM E.END_INTERVAL_TIME -

B.END_INTERVAL_TIME) / 60,

---------- FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E

---------db_time := (value / 1000000) / 60

----------结束节点值-开始快照节点值----- SELECT VALUE FROM DBA_HIST_SYS_TIME_MODEL


INSTANCE_NUMBER=L_INST_NUM

-----AND STAT_NAME=I_NAME;

--------------I_NAME = 'DB time'

------sessions

----- SELECT VALUE FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID=L_DBID

---- AND SNAP_ID=I_SNAP_ID AND INSTANCE_NUMBER=L_INST_NUM AND

STAT_NAME=I_NAME;

--------------sessions= 'logons current';

-------cursors

----- SELECT VALUE FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID=L_DBID

---- AND SNAP_ID=I_SNAP_ID AND INSTANCE_NUMBER=L_INST_NUM AND

STAT_NAME=I_NAME;

--------------cursors=’ opened cursors current’

SUMM_STATS_1.DELETE;

SUMM_STATS_1(SM_BUFF_CACHE_B) :=

ROUND(RPT_STATS(STAT_BUF_CACHE_B)/1024/1024);

SUMM_STATS_1(SM_BUFF_CACHE_E) :=

ROUND(RPT_STATS(STAT_BUF_CACHE_E)/1024/1024);

SUMM_STATS_1(SM_STD_BLK_SZ) := RPT_PARAMS(STAT_DBBLK_SIZE)/1024;

SUMM_STATS_1(SM_SH_POOL_SZ_B) :=

ROUND(RPT_STATS(STAT_SHRD_POOL_B)/1024/1024);

SUMM_STATS_1(SM_SH_POOL_SZ_E) :=

ROUND(RPT_STATS(STAT_SHRD_POOL_E)/1024/1024);

SUMM_STATS_1(SM_LOG_BUFF) := RPT_STATS(STAT_LOG_BUFFER)/1024;


CACHE_SIZES_HTML(SUMM_STATS_1);

ELSE

CACHE_SIZES_TEXT(SUMM_STATS_1);

END IF;

--------- CACHE_SIZES_HTML(SUMM_STATS_1);

--------Buffer Cache: begin end

---------SELECT BYTES FROM DBA_HIST_SGASTAT

--------- WHERE SNAP_ID = I_BEID ---(修改为对应的快照结点)--------- AND DBID=L_DBID AND INSTANCE_NUMBER=L_INST_NUM

--------- AND NAME=I_NAME AND POOL IS NULL;

---------I_NAME :=’buffer_cache’

-------------Shared Pool Size: begin end

SELECT SUM(BYTES)

INTO TOTAL_BYTES

FROM DBA_HIST_SGASTAT

WHERE SNAP_ID = I_BEID ---(修改为对应的快照结点) AND DBID = L_DBID

AND INSTANCE_NUMBER = L_INST_NUM

AND POOL IN ('shared pool','all pools');

-----db_block_size




SUMM_STATS_1(SM_REDO_SZ) :=

ROUND(RPT_STATS(STAT_REDO_SIZE)/RPT_STATS(STAT_ELAPSED),

2);

-----redo size per second :select round(((SELECT sum(value)

FROM DBA_HIST_SYSSTAT e

WHERE e.SNAP_ID = &end_snap

AND e.DBID = &L_DBID

AND e.INSTANCE_NUMBER = &L_INST_NUM

AND e.STAT_NAME in ('redo size')) -

(SELECT sum(value)

FROM DBA_HIST_SYSSTAT b

WHERE b.SNAP_ID = &beg_snap

AND b.DBID = &L_DBID

AND b.INSTANCE_NUMBER = &L_INST_NUM

AND b.STAT_NAME in ('redo size'))) /

(SELECT EXTRACT(DAY FROM

E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *

86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -

B.END_INTERVAL_TIME) * 3600 +

EXTRACT(MINUTE FROM

E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +

EXTRACT(SECOND FROM

E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)

FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E

where e.snap_id = &end_snap

and b.snap_id = &beg_snap




AND e.INSTANCE_NUMBER = &L_INST_NUM),

2)

from dual

SUMM_STATS_2(SM_REDO_SZ) :=

ROUND(RPT_STATS(STAT_REDO_SIZE)/RPT_STATS(STAT_TXN),2);

-----redo size per transaction select round(((SELECT sum(value)






(SELECT sum(value)






((SELECT sum(value)





AND e.STAT_NAME in ('user rollbacks', 'user commits')) -

(SELECT sum(value)





AND b.STAT_NAME in ('user rollbacks', 'user commits'))),

2)

from dual

SUMM_STATS_1(SM_LOGI_READS) :=

ROUND(RPT_STATS(STAT_LOGC_READ)/RPT_STATS(STAT_ELAPSED),

2);

----- Logical reads: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('session logical reads')) -

(SELECT sum(value)





AND b.STAT_NAME in ('session logical reads'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_LOGI_READS) :=

ROUND(RPT_STATS(STAT_LOGC_READ)/RPT_STATS(STAT_TXN),2)

;

----- Logical reads: per transaction select round(((SELECT sum(value)






(SELECT sum(value)






((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_BLK_CHNG) :=

ROUND(RPT_STATS(STAT_DBBLK_CHNG)/RPT_STATS(STAT_ELAPSED)

, 2);

----- Block changes: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('db block changes')) -

(SELECT sum(value)





AND b.STAT_NAME in ('db block changes'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_BLK_CHNG) :=

ROUND(RPT_STATS(STAT_DBBLK_CHNG)/RPT_STATS(STAT_TXN),2

);

----- Block changes: per transaction select round(((SELECT sum(value)






(SELECT sum(value)






((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_PHYS_READS) :=

ROUND(RPT_STATS(STAT_PHYR)/RPT_STATS(STAT_ELAPSED), 2);

----- Physical reads: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('physical reads')) -

(SELECT sum(value)





AND b.STAT_NAME in ('physical reads'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_PHYS_READS) :=

ROUND(RPT_STATS(STAT_PHYR)/RPT_STATS(STAT_TXN),2);

----- Physical reads: per transaction select round(((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('physical reads'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_PHYS_WRITE) :=

ROUND(RPT_STATS(STAT_PHYW)/RPT_STATS(STAT_ELAPSED), 2);

----- Physical reads: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('physical writes')) -

(SELECT sum(value)





AND b.STAT_NAME in ('physical writes'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_PHYS_WRITE) :=

ROUND(RPT_STATS(STAT_PHYW)/RPT_STATS(STAT_TXN),2);

----- Physical reads: per transaction select round(((SELECT sum(value)





AND e.STAT_NAME in ('physical writes')) -

(SELECT sum(value)





AND b.STAT_NAME in ('physical writes'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_USR_CALLS) :=

ROUND(RPT_STATS(STAT_USER_CALL)/RPT_STATS(STAT_ELAPSED),

2);

----- User calls: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('user calls')) -

(SELECT sum(value)





AND b.STAT_NAME in ('user calls'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_USR_CALLS) :=

ROUND(RPT_STATS(STAT_USER_CALL)/RPT_STATS(STAT_TXN),2);

----- User calls: per transaction select round(((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('user calls'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_PARSES) :=

ROUND(RPT_STATS(STAT_PRSE)/RPT_STATS(STAT_ELAPSED), 2);

----- Parses: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('parse count (total)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('parse count (total)'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_PARSES) :=

ROUND(RPT_STATS(STAT_PRSE)/RPT_STATS(STAT_TXN),2);

----- Parses: per transaction select round(((SELECT sum(value)





AND e.STAT_NAME in ('parse count (total)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('parse count (total)'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_HRD_PARSES) :=

ROUND(RPT_STATS(STAT_HARD_PRSE)/RPT_STATS(STAT_ELAPSED),

2);

----- Hard parses: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('parse count (hard)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('parse count (hard)'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_HRD_PARSES) :=

ROUND(RPT_STATS(STAT_HARD_PRSE)/RPT_STATS(STAT_TXN),2);

----- Hard Parses: per transaction

select round(((SELECT sum(value)





AND e.STAT_NAME in ('parse count (hard)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('parse count (hard)'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_SORTS) := ROUND((RPT_STATS(STAT_MEM_SORT)+

RPT_STATS(STAT_DSK_SORT))/RPT_STATS(STAT_ELAPSED), 2);

----- Sorts: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('sorts (disk)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('sorts (disk)'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_SORTS) := ROUND((RPT_STATS(STAT_MEM_SORT)+

RPT_STATS(STAT_DSK_SORT))/RPT_STATS(STAT_TXN), 2);

----- Sorts: per transaction select round(((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('sorts (disk)'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_LOGONS) :=

ROUND(RPT_STATS(STAT_LOGON)/RPT_STATS(STAT_ELAPSED),

2);

----- Logons: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('logons cumulative')) -

(SELECT sum(value)





AND b.STAT_NAME in ('logons cumulative'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_LOGONS) :=

ROUND(RPT_STATS(STAT_LOGON)/RPT_STATS(STAT_TXN),2);

----- Logons: per transaction select round(((SELECT sum(value)





AND e.STAT_NAME in ('logons cumulative')) -

(SELECT sum(value)





AND b.STAT_NAME in ('logons cumulative'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_EXEC) :=

ROUND(RPT_STATS(STAT_EXECUTION)/RPT_STATS(STAT_ELAPSED),

2);

----- Executes: per secondselect round(((SELECT sum(value)





AND e.STAT_NAME in ('execute count')) -

(SELECT sum(value)





AND b.STAT_NAME in ('execute count'))) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual;

SUMM_STATS_2(SM_EXEC) :=

ROUND(RPT_STATS(STAT_EXECUTION)/RPT_STATS(STAT_TXN),2);

----- Executes: per transaction select round(((SELECT sum(value)





AND e.STAT_NAME in ('execute count')) -

(SELECT sum(value)





AND b.STAT_NAME in ('execute count'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_1(SM_TRANS) :=

ROUND(RPT_STATS(STAT_TXN)/RPT_STATS(STAT_ELAPSED), 2);

--------Transactions: per secondselect round(

((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('user rollbacks', 'user

commits')))/(SELECT EXTRACT(DAY FROM




EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM









2)

from dual

SUMM_STATS_3(SM_BLKS_CHG_RD) :=

ROUND(100*RPT_STATS(STAT_DBBLK_CHNG)/RPT_STATS(STAT_LOGC_R

EAD),2);

--------% Blocks changed per Read:select round(((SELECT sum(value)






(SELECT sum(value)






((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('session logical reads'))),

2)

from dual

SUMM_STATS_3(SM_RECUR_PCT) :=

ROUND(100*RPT_STATS(STAT_RECUR_CAL)/RPT_STATS(STAT_CALL),

2);

--------% Blocks changed per Read:select round(((SELECT sum(value)





AND e.STAT_NAME in ('recursive calls')) -

(SELECT sum(value)





AND b.STAT_NAME in ('recursive calls'))) /

((SELECT sum(value)





AND e.STAT_NAME in ('recursive calls')) -

(SELECT sum(value)





AND b.STAT_NAME in ('recursive calls'))+

(SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('user calls'))),

2)

from dual

SUMM_STATS_3(SM_ROLLBACK_PCT):=

ROUND(100*RPT_STATS(STAT_USER_RLBK)/RPT_STATS(STAT_TXN),

2);

----- Rollback per transaction %: select round(((SELECT sum(value)





AND e.STAT_NAME in ('user rollbacks')) -

(SELECT sum(value)





AND b.STAT_NAME in ('user rollbacks'))) /

((SELECT sum(value)






(SELECT sum(value)






2)

from dual

SUMM_STATS_3(SM_ROWS_PER_SORT) :=

ROUND(DIVIDE(RPT_STATS(STAT_ROW_SORT),

RPT_STATS(STAT_MEM_SORT)+ RPT_STATS(STAT_DSK_SORT)),2);

-----Rows per Sort: select round(((SELECT sum(value)





AND e.STAT_NAME in ('sorts (rows)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('sorts (rows)'))) /

((SELECT sum(value)





AND e.STAT_NAME in ('sorts (memory)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('sorts (memory)'))+

(SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('sorts (disk)'))),

2)

from dual


LOAD_PROFILE_HTML(SUMM_STATS_1, SUMM_STATS_2, SUMM_STATS_3);

ELSE

LOAD_PROFILE_TEXT(SUMM_STATS_1, SUMM_STATS_2, SUMM_STATS_3);

END IF;


SUMM_STATS_1(SM_BUF_NOWAIT_PCT) :=

ROUND(100*(1-RPT_STATS(STAT_BUFFER_WAIT)/RPT_STATS(STAT_LOGC_R

EAD)),2);

----- Buffer Nowait %:select round(100 *

(1 - ((SELECT SUM(WAIT_COUNT)

FROM DBA_HIST_WAITSTAT

WHERE SNAP_ID = &end_snap

AND DBID = &L_DBID

AND INSTANCE_NUMBER = &L_INST_NUM) -

(SELECT SUM(WAIT_COUNT)

FROM DBA_HIST_WAITSTAT

WHERE SNAP_ID = &beg_snap

AND DBID = &L_DBID

AND INSTANCE_NUMBER = &L_INST_NUM)) /

((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('session logical reads')))),

2)

from dual

SUMM_STATS_1(SM_REDO_NOWAIT_PCT) :=

ROUND(100*(1-DIVIDE(RPT_STATS(STAT_REDO_REQ),

RPT_STATS(STAT_REDO_ENTR))),2);

----- Redo NoWait %:select round(100 *

(1 - ((SELECT sum(value)





AND e.STAT_NAME in ('redo log space requests')) -

(SELECT sum(value)





AND b.STAT_NAME in ('redo log space requests'))) /

((SELECT sum(value)





AND e.STAT_NAME in ('redo entries')) -

(SELECT sum(value)





AND b.STAT_NAME in ('redo entries')))),

2)

from dual

SUMM_STATS_1(SM_BUF_HIT_PCT) :=

ROUND(100*(1-(RPT_STATS(STAT_PHYR)-RPT_STATS(STAT_PHYR_DIR)-

NVL(RPT_STATS(STAT_PHYR_DIR_LOB),0))/RPT_STATS(STAT_LOGC_READ)

),2);

----- Buffer Hit %:select round(100 *

(1 -

((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('physical reads')) -

((SELECT sum(value)





AND e.STAT_NAME in ('physical reads direct')) -

(SELECT sum(value)





AND b.STAT_NAME in ('physical reads direct'))) -

nvl(((SELECT sum(value)





AND e.STAT_NAME in ('physical reads direct (lob)')) -

(SELECT sum(value)





AND b.STAT_NAME in ('physical reads direct (lob)'))),

0)) /

((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('session logical reads')))),

2)

from dual

SUMM_STATS_1(SM_INMEM_SORT_PCT) :=

ROUND(DIVIDE(100*RPT_STATS(STAT_MEM_SORT),

RPT_STATS(STAT_MEM_SORT) + RPT_STATS(STAT_DSK_SORT)),2);

----- In-memory Sort %:select round(100 * ((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('sorts (memory)'))) /

(((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('sorts (memory)'))) +

((SELECT sum(value)






(SELECT sum(value)





AND b.STAT_NAME in ('sorts (disk)')))),

2)

from dual

SUMM_STATS_1(SM_LIB_HIT_PCT) :=

ROUND(100*RPT_STATS(STAT_LC_HITRATIO),2);

----- Library Hit %SELECT round(100 * (SUM(e.PINHITS) - sum(b.pinhits)) /

(SUM(e.PINS) - sum(b.pins)),

2)

FROM DBA_HIST_LIBRARYCACHE b, DBA_HIST_LIBRARYCACHE e

WHERE e.SNAP_ID = &end_SNAP



and b.SNAP_ID = &beg_SNAP



SUMM_STATS_1(SM_SOFT_PARSE_PCT) := ROUND(100*(1-

RPT_STATS(STAT_HARD_PRSE)/

RPT_STATS(STAT_PRSE)),2);

----- Soft Parse %: select round(100 *






AND e.STAT_NAME = 'parse count (hard)') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse count (hard)')) /

((SELECT sum(value)





AND e.STAT_NAME = 'parse count (total)') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse count (total)'))),

2)

from dual

SUMM_STATS_1(SM_EXEC_TO_PARSE_PCT) := ROUND(100*(1-

RPT_STATS(STAT_PRSE)/

RPT_STATS(STAT_EXECUTION)),2);

-----Execute to Parse %: select round(100 * (1 - ((SELECT sum(value)





AND e.STAT_NAME = 'parse count (total)') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse count (total)')) /

((SELECT sum(value)





AND e.STAT_NAME = 'execute count') -

(SELECT sum(value)





AND b.STAT_NAME = 'execute count'))),

2)

from dual

SUMM_STATS_1(SM_LATCH_HIT_PCT) := ROUND(100*(1-

RPT_STATS(STAT_LT_HITRATIO)),

2);

-----Latch Hit %: SELECT round(100 * (1 - (SUM(e.MISSES) - sum(b.MISSES)) /

(SUM(e.GETS) - sum(b.GETS))),

2)

FROM DBA_HIST_LATCH b, DBA_HIST_LATCH e

WHERE e.SNAP_ID = &end_SNAP



and b.SNAP_ID = &beg_SNAP



SUMM_STATS_1(SM_PARSE_CPU_PARSE_ELA) :=

ROUND(DIVIDE(100*RPT_STATS(STAT_CPU_PRSE),

RPT_STATS(STAT_ELA_PRSE)),2);

-----Parse CPU to Parse Elapsd %: select round(100 * ((SELECT sum(value)





AND e.STAT_NAME = 'parse time cpu') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse time cpu')) /

((SELECT sum(value)





AND e.STAT_NAME = 'parse time elapsed') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse time elapsed')),

2)

from dual

SUMM_STATS_1(SM_PCT_NONPARSE_CPU) := ROUND(100*(1-

DIVIDE(RPT_STATS(STAT_CPU_PRSE),

RPT_STATS(STAT_CPU_TIME)/10000)),2);

-----% Non-Parse CPU:select round(100 *






AND e.STAT_NAME = 'parse time cpu') -

(SELECT sum(value)





AND b.STAT_NAME = 'parse time cpu')) /

(((SELECT sum(value)

FROM DBA_HIST_SYS_TIME_MODEL e




AND e.STAT_NAME = 'DB CPU') -

(SELECT sum(value)

FROM DBA_HIST_SYS_TIME_MODEL b




AND b.STAT_NAME = 'DB CPU')) / 10000)),

2)

from dual


INST_EFF_PCT_HTML(SUMM_STATS_1);

ELSE

INST_EFF_PCT_TEXT(SUMM_STATS_1);

END IF;

BEGIN

SELECT DECODE(B.TOTAL_SQL, 0, 0,

100*(1-B.SINGLE_USE_SQL/B.TOTAL_SQL)),

DECODE(E.TOTAL_SQL, 0, 0,

100*(1-E.SINGLE_USE_SQL/E.TOTAL_SQL)),

DECODE(B.TOTAL_SQL_MEM, 0, 0,

100*(1-B.SINGLE_USE_SQL_MEM/B.TOTAL_SQL_MEM)),

DECODE(E.TOTAL_SQL_MEM, 0, 0,

100*(1-E.SINGLE_USE_SQL_MEM/E.TOTAL_SQL_MEM))

INTO BSUS, ESUS, BSUSM, ESUSM

FROM DBA_HIST_SQL_SUMMARY B,

DBA_HIST_SQL_SUMMARY E

WHERE B.SNAP_ID = L_BID

AND E.SNAP_ID = L_EID

AND B.INSTANCE_NUMBER = L_INST_NUM


AND B.DBID = L_DBID

AND E.DBID = L_DBID;



SUMM_STATS_1(SM_MEM_USE_PCT) := 100*(1-

RPT_STATS(STAT_FREE_MEM_B)/

RPT_STATS(STAT_SHRD_POOL_B));

--------- Memory Usage %:begin select round(100 *

(1 - (SELECT bytes



AND DBID = &L_DBID

AND INSTANCE_NUMBER = &L_INST_NUM

AND NAME = 'free memory'

and pool IN ('shared pool', 'all pools')) /

(SELECT sum(value)

FROM DBA_HIST_PARAMETER


AND DBID = &L_DBID


AND PARAMETER_NAME = '__shared_pool_size')),

2)

from dual

SUMM_STATS_2(SM_MEM_USE_PCT) := 100*(1-

RPT_STATS(STAT_FREE_MEM_E)/

RPT_STATS(STAT_SHRD_POOL_E));

--------- Memory Usage %:end select round(100 *

(1 - (SELECT bytes



AND DBID = &L_DBID


AND NAME = 'free memory'

and pool IN ('shared pool', 'all pools')) /

(SELECT sum(value)



AND DBID = &L_DBID


AND PARAMETER_NAME = '__shared_pool_size')),

2)

from dual

SUMM_STATS_1(SM_SQL_GT_1) := BSUS;

--------- % SQL with executions>1:begin SELECT DECODE(B.TOTAL_SQL,0,0,100 * (1 - B.SINGLE_USE_SQL / B.TOTAL_SQL))

FROM DBA_HIST_SQL_SUMMARY B

Where SNAP_ID = &begin_snap

AND DBID = &L_DBID AND INSTANCE_NUMBER = &L_INST_NUM

SUMM_STATS_2(SM_SQL_GT_1) := ESUS;

--------- % SQL with executions>1:endSELECT DECODE(e.TOTAL_SQL,0,0,100 * (1 - e.SINGLE_USE_SQL / e.TOTAL_SQL))

FROM DBA_HIST_SQL_SUMMARY e

Where SNAP_ID = &end_snap


SUMM_STATS_1(SM_MEM_SQL_GT_1) := BSUSM;

--------- % Memory for SQL w/exec>1:beginSELECT DECODE (B.TOTAL_SQL_MEM, 0, 0,100*(1-B.SINGLE_USE_SQL_MEM/B.TOTAL_SQL_MEM))

FROM DBA_HIST_SQL_SUMMARY B

Where SNAP_ID = &begin_snap


SUMM_STATS_2(SM_MEM_SQL_GT_1) := ESUSM;

--------- % Memory for SQL w/exec>1:endSELECT DECODE (e.TOTAL_SQL_MEM, 0, 0,100*(1-e.SINGLE_USE_SQL_MEM/e.TOTAL_SQL_MEM))

FROM DBA_HIST_SQL_SUMMARY e

Where SNAP_ID = &end_snap



SHARED_POOL_HTML(SUMM_STATS_1, SUMM_STATS_2);

ELSE

SHARED_POOL_TEXT(SUMM_STATS_1, SUMM_STATS_2);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN


APPEND_ROW(PARAGRAPH_TAG);

ELSE


END IF;

APPEND_ROW('Shared Pool Statisitics Not Available');



ELSE



END IF;

END;

EVENT_FMT := '30';

CLASS_FMT := '10';


APPEND_ROW('Top ' || TOP_N_EVENTS || ' Timed Events');

WAITS_FMT := '999,999,999,990';

TIME_FMT := '999,999,999,990';

AVGWT_FMT := '999,999,999,990';

PCTW_FMT := '9,999,999,999.9';


APPEND_ROW(TABLE_BEGIN_FIXEDWID);

GENERIC_TABLE_HEADER(HTML_TABLE_HEADER(

'Event','Waits','Time(s)','Avg Wait(ms)',

'% Total Call Time','Wait Class'

));

ELSE

WAITS_FMT := '999,999,990';

TIME_FMT := '99,999,990';

AVGWT_FMT := '99990';

PCTW_FMT := '990.9';

COLOFFSET := 44;

APPEND_ROW('Top ' || TOP_N_EVENTS || ' Timed Events' ||

LPAD('Avg', COLOFFSET) || SP_CHR ||

LPAD('%Total', LENGTH(AVGWT_FMT) + 1));

APPEND_ROW('~~~~~~~~~~~~~~~~~~' ||

LPAD('wait', COLOFFSET) || SP_CHR ||

LPAD('Call', LENGTH(AVGWT_FMT) + 1));

OUTPUT := RPAD('Event', EVENT_FMT) || SP_CHR ||

LPAD('Waits', LENGTH(WAITS_FMT) + 1) || SP_CHR ||

LPAD('Time (s)', LENGTH(TIME_FMT) + 1) || SP_CHR ||

LPAD('(ms)', LENGTH(AVGWT_FMT) + 1) || SP_CHR ||

LPAD('Time', LENGTH(PCTW_FMT) + 1) || SP_CHR ||

LPAD('Wait Class', CLASS_FMT);

APPEND_ROW(OUTPUT);

OUTPUT := RPAD('-', EVENT_FMT, '-') || SP_CHR ||

LPAD('-', LENGTH(WAITS_FMT) + 1, '-') || SP_CHR ||

LPAD('-', LENGTH(TIME_FMT) + 1, '-') || SP_CHR ||

LPAD('-', LENGTH(AVGWT_FMT) + 1, '-') || SP_CHR ||

LPAD('-', LENGTH(PCTW_FMT) + 1, '-') || SP_CHR ||

LPAD('-', CLASS_FMT, '-');

APPEND_ROW(OUTPUT);

END IF;

OPEN WAIT_EVENT_CUR(TOP_N_EVENTS);

LOOP

FETCH WAIT_EVENT_CUR INTO WAIT_EVENT_OUT;

EXIT WHEN WAIT_EVENT_CUR%NOTFOUND;


ROW_NUM := ROW_NUM + 1;

GENERIC_TABLE_ROW(HTML_TABLE_ROW(

NEW_CELL_TEXT(WAIT_EVENT_OUT.EVENT),

NEW_CELL_NUM(WAIT_EVENT_OUT.WAITS, WAITS_FMT),

NEW_CELL_NUM(WAIT_EVENT_OUT.TIME, TIME_FMT),

NEW_CELL_NUM(WAIT_EVENT_OUT.AVGWT, AVGWT_FMT),

NEW_CELL_NUM(WAIT_EVENT_OUT.PCTWTT, PCTW_FMT),

NEW_CELL_TEXT(WAIT_EVENT_OUT.WAIT_CLASS)

), (MOD(ROW_NUM,2) = 0) );

ELSE

OUTPUT := RPAD(WAIT_EVENT_OUT.EVENT, EVENT_FMT) || SP_CHR ||

NVL(TO_CHAR(WAIT_EVENT_OUT.WAITS, WAITS_FMT),

LPAD(' ', LENGTH(WAITS_FMT) + 1)) || SP_CHR ||

TO_CHAR(WAIT_EVENT_OUT.TIME, TIME_FMT) || SP_CHR ||

NVL(TO_CHAR(WAIT_EVENT_OUT.AVGWT, AVGWT_FMT),

LPAD(' ', LENGTH(AVGWT_FMT) + 1)) || SP_CHR ||

TO_CHAR(WAIT_EVENT_OUT.PCTWTT, PCTW_FMT) || SP_CHR ||

NVL(LPAD(WAIT_EVENT_OUT.WAIT_CLASS, CLASS_FMT),

LPAD(' ', CLASS_FMT));

APPEND_ROW(OUTPUT);

END IF;

END LOOP;

CLOSE WAIT_EVENT_CUR;


APPEND_ROW(TABLE_END);


ELSE

APPEND_ROW(SEC_SEPARATOR);

END IF;

------ Top 5 Timed Events: %total call time数据测试时不准 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_NAME 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 DBA_HIST_SYS_TIME_MODEL e

WHERE e.SNAP_ID = &end_snap AND e.DBID = &DBID

AND e.INSTANCE_NUMBER = &INST_NUM AND e.STAT_NAME = 'DB

time') -

(SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b

WHERE b.SNAP_ID = &beg_snap AND b.DBID = &DBID

AND b.INSTANCE_NUMBER = &INST_NUM AND b.STAT_NAME = 'DB

time')) PCTWTT,

E.WAIT_CLASS WAIT_CLASS

FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E

WHERE B.SNAP_ID(+) = &beg_snap

AND E.SNAP_ID = &end_snap

AND B.DBID(+) = &DBID

AND E.DBID = &DBID

AND B.INSTANCE_NUMBER(+) = &INST_NUM

AND E.INSTANCE_NUMBER = &INST_NUM

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 DBA_HIST_SYS_TIME_MODEL e


AND e.INSTANCE_NUMBER = &INST_NUM AND e.STAT_NAME = 'DB

CPU') -



AND b.INSTANCE_NUMBER = &INST_NUM AND b.STAT_NAME = 'DB

CPU')) / 1000000 TIME,

100 * ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e


AND e.INSTANCE_NUMBER = &INST_NUM AND e.STAT_NAME =

'DB CPU') -



AND b.INSTANCE_NUMBER = &INST_NUM AND b.STAT_NAME =

'DB CPU')) /

((SELECT sum(value)




AND e.INSTANCE_NUMBER = &INST_NUM

AND e.STAT_NAME = 'DB time') -

(SELECT sum(value)



AND b.DBID = &DBID

AND b.INSTANCE_NUMBER = &INST_NUM

AND b.STAT_NAME = 'DB time')) PCTWTT,

NULL WAIT_CLASS

FROM DUAL

WHERE ((SELECT sum(value)



AND e.DBID = &DBID

AND e.INSTANCE_NUMBER = &INST_NUM

AND e.STAT_NAME = 'DB CPU') -

(SELECT sum(value)



AND b.DBID = &DBID

AND b.INSTANCE_NUMBER = &INST_NUM

AND b.STAT_NAME = 'DB CPU')) > 0)

ORDER BY TIME DESC, WAITS DESC)

WHERE ROWNUM <= &TOP_N_EVENTS;

IF (RPT_PARAMS(STAT_PARALLEL) = 'NO') THEN


APPEND_ROW(A_NAME_PREFIX || 'top' || A_SUFFIX || END_A);

DISPLAY_SECTION_LIST(L_OPTIONS,MAIN_REPT,TO_HTML,0,1);

APPEND_ROW(LINE_BREAK || BACK_TO_TOP_LINK || PARAGRAPH_TAG);

END IF;

RETURN;

END IF;


OUTPUT := A_NAME_PREFIX || RAC_SUMMARY || A_SUFFIX || END_A;

OUTPUT := OUTPUT || BEGIN_SECTION_TITLE || 'RAC Statistics' ||

END_SECTION_TITLE;

APPEND_ROW(OUTPUT);

END IF;

SELECT COUNT(B.THREAD#) INTO NUM_INST1

FROM DBA_HIST_THREAD B

WHERE B.SNAP_ID = L_BID

AND B.DBID = L_DBID

AND B.INSTANCE_NUMBER = L_INST_NUM

AND B.STATUS = 'OPEN';

SELECT COUNT(E.THREAD#) INTO NUM_INST2

FROM DBA_HIST_THREAD E

WHERE E.SNAP_ID = L_EID

AND E.DBID = L_DBID


AND E.STATUS = 'OPEN';


RAC_NUM_INSTANCES_HTML(NUM_INST1,NUM_INST2);

ELSE

RAC_NUM_INSTANCES_TEXT(NUM_INST1,NUM_INST2,L_BID,L_EID);

END IF;



SUMM_STATS_1(SM_GLOBAL_BLKS_RCVD) :=

ROUND((RPT_STATS(STAT_GC_CR_RV)+

RPT_STATS(STAT_GC_CU_RV))/RPT_STATS(STAT_ELAPSED),2);

-------- Global Cache blocks received:per secondselect round(((SELECT e.VALUE - b.value

FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e

WHERE B.SNAP_ID = &beg_snap


AND B.DBID = &DBID

AND E.DBID = &DBID

AND B.INSTANCE_NUMBER = &INST_NUM


and e.STAT_NAME = 'gc cr blocks received'

and b.stat_name = 'gc cr blocks received') +

(SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current blocks received'

and b.STAT_NAME = 'gc current blocks received')) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID



AND B.STARTUP_TIME = E.STARTUP_TIME

AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),

2)

from dual

SUMM_STATS_2(SM_GLOBAL_BLKS_RCVD) :=

ROUND((RPT_STATS(STAT_GC_CR_RV)+

RPT_STATS(STAT_GC_CU_RV))/RPT_STATS(STAT_TXN),2);

-------- Global Cache blocks received:per transactionselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc cr blocks received') +





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.STAT_NAME = 'gc current blocks received')) /

(SELECT sum(e.value) - sum(b.value)

FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E



AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in ('user rollbacks','user commits')

and b.STAT_NAME in ('user rollbacks','user commits')),

2)

from dual

SUMM_STATS_1(SM_GLOBAL_BLKS_SRVD) :=

ROUND((RPT_STATS(STAT_GC_CR_SV)+

RPT_STATS(STAT_GC_CU_SV))/RPT_STATS(STAT_ELAPSED), 2);

-------- Global Cache blocks served:per secondselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc cr blocks served'

and b.stat_name = 'gc cr blocks served') +





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current blocks served'

and b.STAT_NAME = 'gc current blocks served')) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_2(SM_GLOBAL_BLKS_SRVD) :=

ROUND((RPT_STATS(STAT_GC_CR_SV)+

RPT_STATS(STAT_GC_CU_SV))/RPT_STATS(STAT_TXN),2);

-------- Global Cache blocks served:per transactionselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc cr blocks served') +





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.STAT_NAME = 'gc current blocks served')) /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_1(SM_GCS_GES_RCVD) :=

ROUND((RPT_STATS(STAT_GCS_MSG_RCVD)+

RPT_STATS(STAT_GES_MSG_RCVD))/RPT_STATS(STAT_ELAPSED), 2);

--------GCS/GES messages received::per secondselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gcs msgs received'

and b.stat_name = 'gcs msgs received') +





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'ges msgs received'

and b.STAT_NAME = 'ges msgs received')) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_2(SM_GCS_GES_RCVD) :=

ROUND((RPT_STATS(STAT_GCS_MSG_RCVD)+

RPT_STATS(STAT_GES_MSG_RCVD))/RPT_STATS(STAT_TXN),2);

--------GCS/GES messages received::per transactionselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gcs msgs received') +





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.STAT_NAME = 'ges msgs received')) /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_1(SM_GCS_GES_SENT) :=

ROUND((RPT_STATS(STAT_GCS_MSG_SNT)+

RPT_STATS(STAT_GES_MSG_SNT))/RPT_STATS(STAT_ELAPSED), 2);

--------GCS/GES messages sent:per secondselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gcs messages sent'

and b.stat_name = 'gcs messages sent') +





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'ges messages sent'

and b.stat_name = 'ges messages sent ')) /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_2(SM_GCS_GES_SENT) :=

ROUND((RPT_STATS(STAT_GCS_MSG_SNT)+

RPT_STATS(STAT_GES_MSG_SNT))/RPT_STATS(STAT_TXN),2);

--------GCS/GES messages sent:per transactionselect round(((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gcs messages sent'

and b.stat_name = 'gcs messages sent') +





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'ges messages sent'

and b.stat_name = 'ges messages sent ')) /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_1(SM_DBWR_FUSION) :=

ROUND(RPT_STATS(STAT_DBWR_FSNW)/

RPT_STATS(STAT_ELAPSED), 2);

--------DBWR Fusion writes:per secondselect round((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'DBWR fusion writes'

and b.stat_name = 'DBWR fusion writes') /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_2(SM_DBWR_FUSION) :=

ROUND(RPT_STATS(STAT_DBWR_FSNW)/

RPT_STATS(STAT_TXN),2);

--------DBWR Fusion writes:per transactionselect round((SELECT e.VALUE - b.value




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'DBWR fusion writes'

and b.stat_name = 'DBWR fusion writes') /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_1(SM_ESTD_INTERCONNECT) :=

ROUND(((RPT_PARAMS(STAT_DBBLK_SIZE) *

(RPT_STATS(STAT_GC_CR_RV) + RPT_STATS(STAT_GC_CU_RV) +

RPT_STATS(STAT_GC_CR_SV) + RPT_STATS(STAT_GC_CU_SV))) +

(200 *

(RPT_STATS(STAT_GCS_MSG_RCVD) +

RPT_STATS(STAT_GES_MSG_RCVD) +

RPT_STATS(STAT_GCS_MSG_SNT) +

RPT_STATS(STAT_GES_MSG_SNT))))

/ 1024 / RPT_STATS(STAT_ELAPSED), 2);

-------- Estd Interconnect traffic (KB)select round(((SELECT VALUE



AND DBID = &DBID

AND INSTANCE_NUMBER = &INST_NUM

AND PARAMETER_NAME = 'db_block_size') *

(SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in

('gc cr blocks received', 'gc current blocks received',

'gc current blocks received',

'gc current blocks served')

and b.stat_name in

('gc cr blocks received', 'gc current blocks received',

'gc current blocks received',

'gc current blocks served')) +

200 *





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in

('gcs msgs received', 'ges msgs received',

'gcs messages sent', 'ges messages sent')

and b.stat_name in

('gcs msgs received', 'ges msgs received',

'gcs messages sent', 'ges messages sent'))) / 1024 /





EXTRACT(MINUTE FROM


EXTRACT(SECOND FROM





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_2(SM_ESTD_INTERCONNECT) := NULL;


RAC_GLOBAL_CACHE_LD_HTML(SUMM_STATS_1, SUMM_STATS_2);

ELSE

RAC_GLOBAL_CACHE_LD_TEXT(SUMM_STATS_1, SUMM_STATS_2);

END IF;


SUMM_STATS_1(SM_BUF_LOCAL_CACHE) :=

ROUND(100*(1-(RPT_STATS(STAT_PHYR)-RPT_STATS(STAT_PHYR_DIR)

-NVL(RPT_STATS(STAT_PHYR_DIR_LOB),0)+

RPT_STATS(STAT_GC_CR_RV)+RPT_STATS(STAT_GC_CU_RV))/

RPT_STATS(STAT_LOGC_READ)),2);

------- Buffer access - local cache %:select round(100 * (1-((SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in

('physical reads', 'gc cr blocks received',

'gc current blocks received')

and b.STAT_NAME in

('physical reads', 'gc cr blocks received',

'gc current blocks received')) -





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in ('physical reads direct',

'physical reads direct (lob)')

and b.stat_name in ('physical reads direct',

'physical reads direct (lob)'))) /





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'session logical reads'

and b.stat_name = 'session logical reads')),

2)

from dual

SUMM_STATS_1(SM_BUF_REMOTE_CACHE) :=

ROUND(100*((RPT_STATS(STAT_GC_CR_RV)

+RPT_STATS(STAT_GC_CU_RV))/


---------- Buffer access - remote cache %:select round(100 * ((SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in ('gc cr blocks received',

'gc current blocks received')

and b.stat_name in ('gc cr blocks received',

'gc current blocks received')) /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual

SUMM_STATS_1(SM_BUF_DISK) :=

ROUND(100*((RPT_STATS(STAT_PHYR)-RPT_STATS(STAT_PHYR_DIR)

-NVL(RPT_STATS(STAT_PHYR_DIR_LOB),0))/


-------Buffer access - disk %:select round(100 * (((SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'physical reads'

and b.STAT_NAME = 'physical reads') -





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in ('physical reads direct',

'physical reads direct (lob)')

and b.stat_name in ('physical reads direct',

'physical reads direct (lob)'))) /





AND B.DBID = &DBID

AND E.DBID = &DBID





2)

from dual


GLOBAL_CACHE_EFF_HTML(SUMM_STATS_1);

ELSE

GLOBAL_CACHE_EFF_TEXT(SUMM_STATS_1);

END IF;


SUMM_STATS_1(SM_AVG_GLOBAL_ENQ) :=

DIVIDE(RPT_STATS(STAT_GLB_ENQ_GT)*10,

RPT_STATS(STAT_GLB_ENQ_AG)+RPT_STATS(STAT_GLB_ENQ_SG));

------- Avg global enqueue get time (ms):select decode( v_2,0,'',v_1/v_2)

from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'global enqueue get time'

and e.STAT_NAME = 'global enqueue get time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in ('global enqueue gets async',

'global enqueue gets sync')

and b.stat_name in ('global enqueue gets async',

'global enqueue gets sync')) v_2

from dual)

SUMM_STATS_1(SM_AVG_CR_RECV_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CR_RT),

RPT_STATS(STAT_GC_CR_RV));

--------- Avg global cache cr block receive time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc cr block receive time'

and e.STAT_NAME = 'gc cr block receive time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc cr blocks received') v_2

from dual)

SUMM_STATS_1(SM_AVG_CUR_RECV_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CU_RT),

RPT_STATS(STAT_GC_CU_RV));

--------- Avg global cache current block receive time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current block receive time'

and e.STAT_NAME = 'gc current block receive time')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc current blocks received') v_2

from dual)

SUMM_STATS_1(SM_AVG_CR_BUILD_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CR_BT),

RPT_STATS(STAT_GC_CR_SV));

---------- Avg global cache cr block build time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc cr block build time'

and e.STAT_NAME = 'gc cr block build time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc cr blocks served') v_2

from dual)

SUMM_STATS_1(SM_AVG_CR_SEND_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CR_ST),


---------- Avg global cache cr block send time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc cr block send time'

and e.STAT_NAME = 'gc cr block send time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID





from dual)

SUMM_STATS_1(SM_GLOB_CR_LOG_FLUSH) := DIVIDE(100 *

RPT_STATS(STAT_CR_FLSH),


--------- Global cache log flushes for cr blocks served %:select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'cr_flushes'

and e.STAT_NAME = 'cr_flushes') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID





from dual)

SUMM_STATS_1(SM_AVG_CR_FLUSH_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CR_FT),

RPT_STATS(STAT_CR_FLSH));

------- Avg global cache cr block flush time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc cr block flush time'

and e.STAT_NAME = 'gc cr block flush time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'cr_flushes'

and b.stat_name = 'cr_flushes') v_2

from dual)

SUMM_STATS_1(SM_AVG_CUR_PIN_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CU_PT),

RPT_STATS(STAT_GC_CU_SV));

--------- Avg global cache current block pin time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current block pin time'

and e.STAT_NAME = 'gc current block pin time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gc current blocks served') v_2

from dual)

SUMM_STATS_1(SM_AVG_CUR_SEND_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CU_ST),


---------- Avg global cache current block send time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current block send time'

and e.STAT_NAME = 'gc current block send time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID





from dual)

SUMM_STATS_1(SM_AVG_CUR_FLUSH_TIME) := DIVIDE(10 *

RPT_STATS(STAT_GC_CU_FT),

RPT_STATS(STAT_CU_FLSH));

-------- Global cache log flushes for current blocks served %:select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gc current block flush time'

and e.STAT_NAME = 'gc current block flush time') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'current_flushes'

and b.stat_name = 'current_flushes') v_2

from dual)

SUMM_STATS_1(SM_GLOB_CUR_LOG_FLUSH):= DIVIDE(100 *

RPT_STATS(STAT_CU_FLSH),


-------- Avg global cache current block flush time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'current_flushes'

and e.STAT_NAME = 'current_flushes') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID





from dual)


GLOBAL_WORKLOAD_HTML(SUMM_STATS_1);

ELSE

GLOBAL_WORKLOAD_TEXT(SUMM_STATS_1);

END IF;


SUMM_STATS_1(SM_AVG_SENT_QTIME) :=

DIVIDE(RPT_STATS(STAT_MSG_SQTIME),

RPT_STATS(STAT_MSG_SQUED));

------- Avg message sent queue time (ms):select decode(v_2, 0, '', v_1 / v_2)

from (select (SELECT sum(e.VALUE) - sum(b.value)




AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs sent queue time (ms)'

and e.STAT_NAME = 'msgs sent queue time (ms)')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs sent queued'

and b.stat_name = 'msgs sent queued') v_2

from dual)

SUMM_STATS_1(SM_AVG_KSXP_QTIME) :=

DIVIDE(RPT_STATS(STAT_MSG_SQTKSXP),

RPT_STATS(STAT_MSG_SQKSXP));

-------- Avg message sent queue time on ksxp (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs sent queue time on ksxp

(ms)'

and e.STAT_NAME = 'msgs sent queue time on ksxp

(ms)') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs sent queued on ksxp'

and b.stat_name = 'msgs sent queued on ksxp') v_2

from dual)

SUMM_STATS_1(SM_AVG_RCVD_QTIME) :=

DIVIDE(RPT_STATS(STAT_MSG_RQTIME),

RPT_STATS(STAT_MSG_RQUED));

-------- Avg message received queue time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs received queue time (ms)'

and e.STAT_NAME = 'msgs received queue time (ms)')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'msgs received queued'

and b.stat_name = 'msgs received queued') v_2

from dual)

SUMM_STATS_1(SM_AVG_GCS_PTIME) :=

DIVIDE(RPT_STATS(STAT_GCS_MSG_TIME),

RPT_STATS(STAT_GCS_MSG_RCVD));

--------- Avg GCS message process time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'gcs msgs process time(ms)'

and e.STAT_NAME = 'gcs msgs process time(ms)')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'gcs msgs received') v_2

from dual)

SUMM_STATS_1(SM_AVG_GES_PTIME) :=

DIVIDE(RPT_STATS(STAT_GES_MSG_TIME),

RPT_STATS(STAT_GES_MSG_RCVD));

--------- Avg GES message process time (ms):select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'ges msgs process time(ms)'

and e.STAT_NAME = 'ges msgs process time(ms)')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID




and b.stat_name = 'ges msgs received') v_2

from dual)

SUMM_STATS_1(SM_PCT_DIRECT_SENT_MSGS):= DIVIDE(100 *

RPT_STATS(STAT_MSG_SNT_DIR), RPT_STATS(STAT_MSG_SNT_DIR) +

RPT_STATS(STAT_MSG_SNT_INDR) + RPT_STATS(STAT_MSG_FLW_CTR));

------- % of direct sent messages:select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'messages sent directly'

and e.STAT_NAME = 'messages sent directly') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in

('messages sent directly', 'messages sent

indirectly',

'messages flow controlled')

and b.stat_name in


indirectly',

'messages flow controlled')) v_2

from dual)

SUMM_STATS_1(SM_PCT_IND_SENT_MSGS) := DIVIDE(100 *

RPT_STATS(STAT_MSG_SNT_INDR), RPT_STATS(STAT_MSG_SNT_DIR) +


-------- % of indirect sent messages:select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'messages sent indirectly'

and e.STAT_NAME = 'messages sent indirectly')

v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in


indirectly',


and b.stat_name in


indirectly',


from dual)

SUMM_STATS_1(SM_PCT_FLOW_CTRL_MSGS) := DIVIDE(100 *

RPT_STATS(STAT_MSG_FLW_CTR), RPT_STATS(STAT_MSG_SNT_DIR) +


-------- % of flow controlled messages:select decode(v_2, 0, '', v_1 / v_2)





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME = 'messages flow controlled'

and e.STAT_NAME = 'messages flow controlled') v_1,





AND B.DBID = &DBID

AND E.DBID = &DBID



and e.STAT_NAME in

('messages sent directly', 'messages sent indirectly',


and b.stat_name in

('messages sent directly', 'messages sent indirectly',


from dual)


GLOBAL_MESSAGING_HTML(SUMM_STATS_1);

APPEND_ROW(HORIZ_LINE);

ELSE

GLOBAL_MESSAGING_TEXT(SUMM_STATS_1);

APPEND_ROW(SEC_SEPARATOR);


END IF;


APPEND_ROW(A_NAME_PREFIX || 'top' || A_SUFFIX || END_A);

DISPLAY_SECTION_LIST(L_OPTIONS,MAIN_REPT,TO_HTML,0,1);

DISPLAY_SECTION_LIST(L_OPTIONS,RAC_REPT,TO_HTML,0,1);

APPEND_ROW(LINE_BREAK || BACK_TO_TOP_LINK || HORIZ_LINE

|| PARAGRAPH_TAG);

END IF;

EXCEPTION

WHEN OTHERS THEN



ELSE


END IF;

APPEND_ROW('Error encountered in Report Summary');



ELSE


END IF;

APPEND_ROW('Continuing to Report Sections');



ELSE



END IF;

END REPORT_SUMMARY;

sql for awr summary

Documents