系統化整理Oracle效能診斷核心指標，提供即時可執行的SQL與判讀標準，支援DBA快速健檢。
| # | 指標 | 計算公式 | 健康閾值 | 調優方向 |
|---|---|---|---|---|
| 1 | Library Cache Hit Ratio | SUM(PINHITS)/SUM(PINS) | > 98% | 增大Shared Pool、使用綁定變數、調整CURSOR_SHARING |
| 2 | Shared Pool 記憶體使用率 | (1 - FREE_MEM/TOTAL_SGA)*100 | 75%-90% | 過低浪費、過高隱患 ORA-04031 |
| 3 | Buffer Cache Hit Ratio | 1 - PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS) | > 90% | 增大DB_CACHE_SIZE、優化高耗SQL、索引調優 |
| 4 | Data Buffer Hit Ratio(官方精確版) | 1 - (PHY_READS - DIRECT_READS - LOB_READS)/(LOGICAL_READS - DIRECT_READS - LOB_READS) | > 90% | 排除Direct Path/Lob讀取干擾，更精確 |
| 5 | Shared Pool Hit Ratio(含Reload) | SUM(PINHITS-RELOADS)/SUM(PINS)*100 | > 95% | 低於閾值檢查綁定變數使用率、增大Shared Pool |
| 6 | 記憶體排序比率 | SORTS(MEMORY)/(SORTS(MEMORY)+SORTS(DISK))*100 | 越高越好 | 磁盤排序過多 → 增大PGA/SORT_AREA_SIZE |
| 7 | PGA Cache Hit Percentage | Bytes Processed / (Bytes Processed + Extra Bytes RW) * 100 | > 50%(建議>90%) | 參考V$PGA_TARGET_ADVICE建議值調整PGA_AGGREGATE_TARGET |
-- 1. Library Cache
SELECT SUM(pinhits)/SUM(pins) "Lib Cache Hit Ratio" FROM v$librarycache;
-- 2. Shared Pool 使用率
SELECT (1-ROUND(bytes/(&tsp_m*1024*1024),2))*100 || '%' "Usage%"
FROM v$sgastat WHERE name='free memory' AND pool='shared pool';
-- 3. Buffer Cache (簡易版)
SELECT 1 - (physical_reads/(db_block_gets+consistent_gets)) "Hit Ratio"
FROM v$buffer_pool_statistics WHERE name='DEFAULT';
-- 4. 精確版命中率所需統計值
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');
-- 5. Shared Pool (含Reload)
SELECT SUM(pinhits-reloads)/SUM(pins)*100 "Hit Ratio" FROM v$librarycache;
-- 6. 排序統計
SELECT name, value FROM v$sysstat WHERE name LIKE '%sort%';
-- 7. PGA 建議值曲線
SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
estd_pga_cache_hit_percentage cache_hit_pct
FROM v$pga_target_advice;
-- 8. 工作區執行模式分布
SELECT name, value, ROUND(value*100/SUM(value) OVER (),2) pct
FROM v$sysstat WHERE name LIKE 'workarea exec%';
MEMORY_TARGET或SGA_TARGET/PGA_AGGREGATE_TARGET，減少手動調整V$PGA_TARGET_ADVICE曲線拐點：PGA分配至曲線平緩處即為投資報酬最佳點
