Oracle 10g SQL 調優實戰：索引建立、監控、排序優化、參數調整

問題現場

文章表超過 10 萬筆，SELECT COUNT(1) FROM res WHERE class_id=1 耗時 10 秒以上。在 class_id 建立索引後，查詢時間降至近 0 秒。

步驟 1：鎖定效能瓶頸 SQL

Enterprise Manager Database Home → 管理 → 資料庫監視器 → 會話 → SQL，搜尋 SELECT，關注 CPU 時間 > 1 秒 的查詢，檢視「索引」區域確認是否使用索引。

步驟 2：建立索引最佳實務

• 索引欄位選擇：依 WHERE 條件決定。單欄 WHERE id=1 建單欄索引；多欄 WHERE id=1 AND name=2 建複合索引 (id, name)
• 主鍵必建索引：大表主鍵必須有索引，且設為 UNIQUE
• 非唯一欄位：可嘗試設定 UNIQUE（若業務允許），提升檢索速度
• ORDER BY 欄位：通常無法使用索引，應先在 WHERE 縮小結果集

EM 建立索引操作路徑

物件瀏覽器 → 建立 → 索引 → 輸入表名、選擇「一般」類型 → 輸入索引名與欄位 → 完成

步驟 3：排序區域優化

調大 SORT_AREA_SIZE 減少磁碟排序：

ALTER SYSTEM SET sort_area_size=10240000 SCOPE=SPFILE;  -- 10MB

需重啟實例生效（SPFILE）。

附錄：常用參數查詢與調整

核心參數查詢

SELECT NAME, VALUE FROM v$parameter
WHERE NAME IN (
  'sga_max_size','db_cache_size','shared_pool_size','shared_pool_reserved_size',
  'large_pool_size','java_pool_size','db_block_size','db_block_buffers',
  'log_buffer','sort_area_size','sort_area_retained_size',
  'hash_area_size','sessions','open_cursors'
) ORDER BY NAME;

SGA 相關調整範例

ALTER SYSTEM SET sga_max_size=700M SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=700M SCOPE=SPFILE;

建立主鍵（EM）

物件瀏覽器 → 選擇表 → 約束條件 → 建立 → 類型選「主鍵」