--------------
-- perform.sql
--------------
set lines 1000
set heading OFF
col AAA for a23
col DB_BLOCK_BUFFERS for a23
col BUFFER_CACHE_FREE for a23
col LIBLARY_CACHE_HIT for a23
col DICTIONALY_CACHE for a23
col REQUEST_MISSES for a23
col REQUEST_FAILURES for a23
col FREE_BUFFER_INSPECTED for a23
col BUFFER_BUSY_WAITS for a23
col REDO_SPACE_WAIT for a23
col ALLOCATION_RETRY for a23
col MEMORY_SORT_SIZE for a23
col TABLE_FULL_SCAN for a23
pro+----------------------+-------------+----------+;
-- バッファキャッシュのヒット率(1)
SELECT 'DB_BLOCK_BUFFERS ' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.9),1,'OK', '(NG)'), 6, ' ') as DB_BLOCK_BUFFERS
, '|'||' OK:90%以上 NG:DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZEを増'
FROM (SELECT 1 - P.VALUE / (L1.VALUE + L2.VALUE) as RATIO
FROM V$SYSSTAT P
,V$SYSSTAT L1
,V$SYSSTAT L2
WHERE P.NAME = 'physical reads'
AND L1.NAME = 'db block gets'
AND L2.NAME = 'consistent gets'
) A
;
-- キャッシュのヒット率の測定(2)
-- select 'CACHE_HIT_RATIO' as AAA
-- , 1-(phy.value - lob.value - dir.value) / ses.value as "CACHE HIT RATIO"
-- FROM v$sysstat ses , v$sysstat lob,
-- v$sysstat dir , v$sysstat phy
-- WHERE ses.name = 'session logical reads'
-- AND dir.name = 'physical reads direct'
-- AND lob.name = 'physical reads direct (lob)'
-- AND phy.name = 'physical reads'
--;
-- バッファキャッシュの最適化
SELECT 'BUFFER_CACHE_FREE ' as AAA
, '|'||lpad(to_char(ROUND(A.RATIO, 3) * 100,'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(ROUND(A.RATIO, 3) * 100),1,'OK', '(NG)'), 6,' ') as BUFFER_CACHE_FREE
, '|'||' OK:バッファキャッシュ余裕あり NG:バッファキャッシュ余裕なし'
FROM (SELECT B.FREE / C.TOTAL RATIO
FROM (SELECT COUNT(*) as FREE
FROM X$BH
WHERE STATE = 0
) B
,
(SELECT COUNT(*) as TOTAL
FROM X$BH
) C
) A
;
-- ライブラリキャッシュのヒット率
SELECT 'LIBLARY_CACHE_HIT' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.99),1,'OK', '(NG)'), 6, ' ') as LIBLARY_CACHE_HIT
, '|'||' OK:99%以上 NG:SHARED_POOL_SIZEの値を大きくする'
FROM (SELECT SUM(PINS - RELOADS) /SUM(PINS) RATIO
FROM V$LIBRARYCACHE) A
;
-- ディクショナリキャッシュのヒット率
SELECT 'DICTIONALY_CACHE ' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.95),1,'OK','(NG)'), 6, ' ') as DICTIONALY_CACHE
, '|'||' OK:95%以上 NG:SHARED_POOL_SIZEの値を大きくする'
FROM (SELECT SUM(GETS - GETMISSES) /SUM(GETS) RATIO
FROM V$ROWCACHE) A
;
-- LRUリストからオブジェクトのフラッシュが開始された回数
SELECT 'REQUEST__MISSES ' as AAA
, '|'||lpad(to_char(REQUEST_MISSES, '9,999,990'),12,' ')||
'|'||lpad(DECODE(REQUEST_MISSES,0,'OK','(NG)'), 6, ' ') as REQUEST_MISSES
, '|'||' 0が望ましい'
FROM V$SHARED_POOL_RESERVED
;
-- 要求を満たすためのメモリーが見つからなかった回数
SELECT 'REQUEST_FAILURES ' as AAA
, '|'||lpad(to_char(REQUEST_FAILURES, '9,999,990'),12,' ')||
'|'||' -- ' as REQUEST_FAILURES
, '|'||' 要求を満たすためのメモリーが見つからなかった回数'
FROM V$SHARED_POOL_RESERVED
;
-- 待機統計情報の確認
SELECT 'FREE_BUFFER_INSPECTED ' as AAA
, '|'||lpad(to_char(value, '9,999,990'),12,' ')||
'|'||' -- ' as FREE_BUFFER_INSPECTED
, '|'||' inspectedの値が高い場合はバッファキャッシュのサイズを増やす'
FROM v$sysstat
WHERE name = 'free buffer inspected'
;
-- 待機イベントのチェック
SELECT 'BUFFER_BUSY_WAITS' as AAA
, '|'||lpad(to_char(total_waits, '99999990'), 12, ' ')||
'|'||' --' as BUFFER_BUSY_WAITS
, '|'||' バッファ・キャッシュが使用可能になるまでの待機'
FROM v$system_event
WHERE event IN ('buffer busy waits')
-- WHERE event IN ('free buffer waits','buffer busy waits')
;
-- REDOログバッファの待機割合
SELECT 'REDO_SPACE_WAIT ' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.01),1,'(NG)','OK'), 6, ' ') as REDO_SPACE_WAIT
, '|'||' OK:1%未満 NG:LOG_BUFFERの値を大きくする'
FROM (SELECT R.VALUE / W.VALUE RATIO
FROM V$SYSSTAT R
, V$SYSSTAT W
WHERE R.NAME = 'redo log space requests'
AND W.NAME = 'redo writes') A
;
-- redo buffer allocation retries を0に近づけるのが目標
SELECT 'ALLOCATION_RETRY' as AAA
, '|'||lpad(to_char(value, '999990'),12,' ')||
'|'||lpad(' --', 6, ' ') as ALLOCATION_RETRY
, '|'||' 0に近づけるのが目標(REDOログ・バッファ無いの領域の待機があるかどうか)'
from v$sysstat
where name='redo buffer allocation retries'
;
-- メモリソート割合
SELECT 'MEMORY_SORT_SIZE ' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.95),1,'OK','(NG)'), 6, ' ') as MEMORY_SORT_SIZE
, '|'||' OK:95%以上 NG:SORT_AREA_SIZEの値を大きくする'
FROM (SELECT M.VALUE / (M.VALUE + D.VALUE) RATIO
FROM V$SYSSTAT M, V$SYSSTAT D
WHERE M.NAME = 'sorts (memory)'
AND D.NAME = 'sorts (disk)') A
;
-- 全走査
SELECT 'TABLE_FULL_SCAN ' as AAA
, '|'||lpad(to_char((A.RATIO * 100),'990.99'),12,' ')||
'|'||lpad(DECODE(SIGN(A.RATIO - 0.1),1,'(NG)','OK'), 6, ' ') as TABLE_FULL_SCAN
, '|'||' OK:10%未満 NG:インデックスを見直す'
FROM (SELECT L.VALUE / (L.VALUE + S.VALUE) RATIO
FROM V$SYSSTAT L, V$SYSSTAT S
WHERE L.NAME = 'table scans (long tables)'
AND S.NAME = 'table scans (short tables)') A
;
pro+----------------------+-------------+----------+;
-- 実行計画が共有されているかどうかの確認
SELECT namespace as AAA
, lpad(to_char((gethitratio * 100), '990.99'),12, ' ')
FROM v$librarycache
;
-- 大きな無名PL/SQLブロック
SELECT ADDRESS, HASH_VALUE, substr(sql_text,1,128)
FROM V$SQLAREA
WHERE COMMAND_TYPE = 47
;
＜実行結果＞
+----------------------+-------------+----------+
DB_BLOCK_BUFFERS | 99.92| OK | OK:90%以上 NG:DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZEを増
BUFFER_CACHE_FREE | 28.30| OK | OK:バッファキャッシュ余裕あり NG:バッファキャッシュ余裕なし
LIBLARY_CACHE_HIT | 99.92| OK | OK:99%以上 NG:SHARED_POOL_SIZEの値を大きくする
DICTIONALY_CACHE | 99.83| OK | OK:95%以上 NG:SHARED_POOL_SIZEの値を大きくする
REQUEST__MISSES | 0| OK | 0が望ましい
REQUEST_FAILURES | 0| -- | 要求を満たすためのメモリーが見つからなかった回数
FREE_BUFFER_INSPECTED | 0| -- | inspectedの値が高い場合はバッファキャッシュのサイズを増やす
BUFFER_BUSY_WAITS | 42| -- | バッファ・キャッシュが使用可能になるまでの待機
REDO_SPACE_WAIT | 0.10| OK | OK:1%未満 NG:LOG_BUFFERの値を大きくする
ALLOCATION_RETRY | 8| -- | 0に近づけるのが目標(REDOログ・バッファ無いの領域の待機があるかどうか)
MEMORY_SORT_SIZE | 99.90| OK | OK:95%以上 NG:SORT_AREA_SIZEの値を大きくする
TABLE_FULL_SCAN | 10.16| (NG) | OK:10%未満 NG:インデックスを見直す
+----------------------+-------------+----------+
＊ REQUEST_FAILURESの値が1よりも大きく増加している場合、SHARED_POOL_RESERVED_SIZEの値を増やす。