Oracle調優相關的各種命中率、使用率彙總

Library Cache的命中率

計算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)

SELECT SUM(pinhits) / sum(pins) FROM V$LIBRARYCACHE;

通常在98%以上，否則，需要要考慮加大共享池，繫結變數，修改cursor_sharing等引數。

共享池記憶體使用率

SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'

FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool';

其中: &TSP_IN_M是你的總的共享池的SIZE(M)

共享池記憶體使用率，應該穩定在75%-90%間，太小浪費記憶體，太大則記憶體不足。

查詢空閒的共享池記憶體:

SELECT * FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool';

db buffer cache命中率

計算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]

SELECT NAME,

PHYSICAL_READS,

DB_BLOCK_GETS,

CONSISTENT_GETS,

1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS

WHERE NAME = 'DEFAULT';

通常應在90%以上，否則，需要調整,加大DB_CACHE_SIZE。

另外一種計算命中率的方法：

命中率的計算公式為:

Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets+ consistent gets - physical reads direct - physical reads direct (lob))

分別代入上一查詢中的結果值，就得出了Buffer cache的命中率

SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE NAME IN ('session logical reads',

'physical reads',

'physical reads direct',

'physical reads direct (lob)',

'db block gets',

'consistent gets');

資料緩衝區命中率

select value from v$sysstat where name ='physical reads';

select value from v$sysstat where name ='physical reads direct';

select value from v$sysstat where name ='physical reads direct (lob)';

select value from v$sysstat where name ='consistent gets';

select value from v$sysstat where name = 'db block gets';

這裡命中率的計算應該是令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果發現命中率低於90%,則應該調整應用可可以考慮是否增大資料緩衝區

共享池的命中率

select sum(pinhits - reloads) / sum(pins) * 100 "hit radio" from v$librarycache;

假如共享池的命中率低於95%,就要考慮調整應用（通常是沒使用bind var ）或者增加記憶體

CPU消耗

計算CPU消耗首先要藉助Linux的TOP命令尋找CPU使用量高的session，找到高佔用的PID。然後藉助v$process的addr欄位跟v$session的paddr找到sql_id、hash_value，然後結合v$sqltext然後找相應的SQL語句，找到之後分析執行計劃，進行相應調優。