Oracle 效能調優實戰筆記：從執行計畫到索引優化

核心診斷與調優技巧彙總

1. 查詢執行中 SQL 的實際執行計畫

SELECT * FROM v$sql_plan 
WHERE hash_value = (SELECT sql_hash_value FROM v$session WHERE sid = 1111);

以 id 與 parent_id 解讀執行樹狀結構，數值最大者最先執行。

2. 自動追蹤 (AUTOTRACE) 設定

• 以 SYSTEM 執行 utlxplan.sql 建立計畫表
• 執行 plustrce.sql 建立角色
• 建立公用同義詞與授權：CREATE PUBLIC SYNONYM plan_table FOR plan_table; GRANT ALL ON plan_table TO PUBLIC; GRANT plustrace TO PUBLIC;
• 控制指令：SET AUTOTRACE ON | OFF | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

3. Session 追蹤技術

• 自身 Session：ALTER SESSION SET SQL_TRACE TRUE 或 EXEC dbms_session.set_sql_trace(TRUE)
• 他人 Session：EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE)
• 追蹤檔位置查詢與 TKPROF 分析：TKPROF 原始檔 目標檔 SYS=NO

4. 系統級追蹤與事件設定

-- 開啟
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-- 關閉
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Level 說明：1=SQL追蹤、4=綁定變數、8=等待事件、12=綁定變數+等待事件

5. OS 程序關聯 DB 程序快速查詢

編寫 Shell 腳本對應 OS PID 與 DB Session，快速定位問題 SQL。

6. 統計資訊收集策略 (DBMS_STATS)

• 分區表優先使用 DBMS_STATS，支援平行、多層級、匯出、自動收集
• 缺點：無法 VALIDATE STRUCTURE、收集鏈列/叢集表資訊、預設不分析索引 (需 CASCADE=TRUE)
• External Table 僅支援 DBMS_STATS

7. 索引重建與合併

-- 重建並移動表空間
ALTER INDEX idx_name REBUILD TABLESPACE ts_name STORAGE(...);
-- 批次重建使用者下所有索引
SET HEADING OFF FEEDBACK OFF
SPOOL rebuild.sql
SELECT 'ALTER INDEX ' || index_name || ' REBUILD TABLESPACE INDEXES STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0);'
FROM all_indexes WHERE owner = USER;
SPOOL OFF
-- 僅合併 leaf block（低消耗）
ALTER INDEX idx_name COALESCE;

8. Hint 使用技巧

SELECT /*+ INDEX(table_name index_name) */ ...  -- 指定索引
SELECT /*+ NO_INDEX(t, idx_b) */ * FROM test t ...  -- 避免使用特定索引

注意：/*+ 中間無空格；使用表別名時 Hint 也須用別名；索引名錯誤會被忽略。

9. 高效資料複製與插入

-- 快速建表（無日誌）
CREATE TABLE t1 NOLOGGING AS SELECT * FROM t2;
-- 快速插入
INSERT /*+ APPEND */ INTO t1 SELECT * FROM t2;
-- 注意 FORCE LOGGING 會失效，可關閉：ALTER DATABASE NO FORCE LOGGING;

10. 避免錯誤索引使用

技巧：WHERE a=? AND b=? AND c=? GROUP BY b||'' (字串) 或 b+0 (數字) 強制使用複合索引；或使用 NO_INDEX Hint。

11. Index Skip Scan (9i 新特性)

複合索引 (a,b,c) 於 WHERE b=? 可能觸發，條件：優化器判斷適合、前導欄位重複值多、有統計資訊、SQL 適合。

12. 虛擬索引測試

CREATE INDEX virtual_idx ON table_name(col_name) NOSEGMENT;
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
EXPLAIN PLAN FOR SELECT ...;
@$ORACLE_HOME/rdbms/admin/utlxpls
DROP INDEX virtual_idx;

僅供 EXPLAIN PLAN 測試，實際執行不生效。

13. 監控未使用索引

ALTER INDEX idx_name MONITORING USAGE;
SELECT * FROM v$object_usage;
ALTER INDEX idx_name NOMONITORING USAGE;

14. 固定執行計畫 (OUTLINE)

CREATE OR REPLACE OUTLINE outline_name ON SELECT ...;
ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

15. v$sysstat 統計類別對照表

1=例項活動、2=Redo緩衝、4=鎖、8=資料緩衝、16=OS活動、32=平行、64=表存取、128=除錯

16. 強制結束 Session

ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
-- Windows: orakill.exe; Linux/Unix: kill -9 OS_PID

17. 鎖與等待查詢

-- 所有 DML 鎖
SELECT /*+ RULE */ s.username, DECODE(l.type,'TM','TABLE LOCK','TX','ROW LOCK') lock_level, 
       o.owner, o.object_name, s.sid, s.serial#, s.machine, s.program
FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username IS NOT NULL;

-- 鎖等待關係樹
SELECT /*+ RULE */ LPAD(' ', DECODE(l.xidusn,0,3,0)) || l.oracle_username user_name,
       o.owner, o.object_name, s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;

18. 大表刪除策略

TRUNCATE TABLE big_table REUSE STORAGE;
ALTER TABLE big_table DEALLOCATE UNUSED KEEP 2000M;
ALTER TABLE big_table DEALLOCATE UNUSED KEEP 1500M;
...
DROP TABLE big_table;

19. 暫存資料檔縮減與暫存區段清理

• 9i 以下：ALTER DATABASE DATAFILE 'file' RESIZE 100M
• 9i 以上：ALTER DATABASE TEMPFILE 'file' RESIZE 100M（需離線）
• 清理步驟：殺掉使用暫存區段的 Session → ALTER TABLESPACE TEMP COALESCE → 診斷事件 ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL TS#+1'

20. 內部結構 Dump 診斷指令

ALTER SYSTEM DUMP DATAFILE n BLOCK m;           -- 資料檔區塊
ALTER SYSTEM DUMP LOGFILE 'logfile';            -- 日誌檔
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 10';  -- 控制檔
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME FILE_HDRS LEVEL 10'; -- 所有資料檔頭
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME REDOHDR LEVEL 10';   -- 日誌檔頭
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'; -- 系統狀態
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME PROCESSSTATE LEVEL 10'; -- 程序狀態
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 10'; -- Library Cache

21. 事件代碼查詢

-- PL/SQL 列出 10000-10999 事件
SET SERVEROUTPUT ON
DECLARE err_msg VARCHAR2(120);
BEGIN
  FOR err_num IN 10000..10999 LOOP
    err_msg := SQLERRM(-err_num);
    IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
      DBMS_OUTPUT.PUT_LINE(err_msg);
    END IF;
  END LOOP;
END;

-- Unix: $ORACLE_HOME/rdbms/mesg/oraus.msg
-- 已設定事件查詢
DECLARE l_level NUMBER;
BEGIN
  FOR l_event IN 10000..10999 LOOP
    DBMS_SYSTEM.READ_EV(l_event, l_level);
    IF l_level > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Event '||l_event||' is set at level '||l_level);
    END IF;
  END LOOP;
END;

22. Statspack 效能監控工具

-- 安裝
@spdrop.sql; @spcreate.sql
-- 使用
EXEC statspack.snap;  -- 產生快照
SELECT snap_id, snap_time FROM stats$snapshot;
@spreport.sql  -- 輸入起迄快照號產生報表
-- 相關腳本：spauto.sql(自動)、sppurge.sql(清除範圍)、sptrunc.sql(清除所有)

23. 常用 SQL 優化 Hint 速查表 (31 種)

涵蓋：ALL_ROWS、FIRST_ROWS、CHOOSE、RULE、FULL、ROWID、CLUSTER、INDEX、INDEX_ASC/DESC、INDEX_COMBINE、INDEX_JOIN、INDEX_FFS、AND_EQUAL、USE_CONCAT/NO_EXPAND、NOREWRITE/REWRITE、MERGE/NO_MERGE、ORDERED、USE_NL/MERGE/HASH、DRIVING_SITE、LEADING、CACHE/NOCACHE、APPEND/NOAPPEND、PARALLEL 等，各附語法範例。