Oracle 大小表判斷標準與全表掃描效能分析：從 _small_table_threshold 到多緩衝池策略

全表掃描統計指標

透過 V$SYSSTAT 或 Statspack 報告可觀察兩個關鍵計數器：

• table scans (short tables)：小表全表掃描次數
• table scans (long tables)：大表全表掃描次數

若 long tables 計數偏高，通常伴隨 db file scattered read 等待事件顯著，意味著大量大表全掃導致 I/O 壓力。

大小表分界線：_small_table_threshold

Oracle 內部參數 _small_table_threshold 決定大小表門檻。預設值 = Buffer Cache 區塊數的 2%。

驗證範例（Oracle 9iR2）：

SQL> SHOW PARAMETER db_cache_size
db_cache_size = 83886080  (80MB)
SQL> SELECT (83886080/8192)*2/100 FROM DUAL;  -- 區塊大小 8K
結果：204.8 ≒ 200 (參數值)

表區塊數 < 200 視為小表，≥ 200 視為大表。

大小表不同處理機制

多緩衝池架構：更精細的控制

Oracle 8i 引入三大緩衝池，從建表即可指定，突破單一 LRU 限制：

• DEFAULT 池：預設通用池，適用多數物件。
• KEEP 池：將熱門、小型、頻繁存取物件（如查詢代碼表、參考資料）強制保留，不被老化。
• RECYCLE 池：用於大型、低頻存取物件（如歷史日誌、批次處理表），掃描後立即釋放，不佔用 DEFAULT 池空間。

語法範例：CREATE TABLE t (...) STORAGE (BUFFER_POOL KEEP);

實務調優建議

1. 監控指標：定期檢視 table scans (long tables) 趨勢，異常上升需檢視 SQL 執行計劃。
2. 物件分類：依存取頻率、大小將表/索引分配至 KEEP/RECYCLE/DEFAULT 池。
3. 參數調整：極少數情況下可調整 _small_table_threshold（需 Oracle 支援建議），通常不建議動態修改隱含參數。
4. SQL 調優優先：大表全掃根因多為缺索引、統計資訊過期、綁定變數窺探失效，應優先從 SQL 層面解決。