【DB筆試面試598】在Oracle中，如何得到真實的執行計劃？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，如何得到真實的執行計劃？
♣
答案部分
在Oracle數據庫中判斷得到的執行計劃是否準確，就是看目標SQL是否被真正執行過，真正執行過的SQL所對應的執行計劃就是準確的，反之則有可能不準，因此，通過10046事件及如下的幾種方式得到的執行計劃是最準確的，而從其它方式獲取到的執行計劃都有可能不準確。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));
這裡需要注意的是，雖然SQL*Plus的AUTOTRACE功能有部分是真實執行了SQL語句的（例如所有DML語句），但是，由於該命令所顯示的執行計劃來源於調用EXPLAIN PLAN命令，所以，其得到的執行計劃依然可能不準確（特別是在使用了綁定變量的情況下）。那麼，為什麼EXPLAIN PLAN命令里顯示的預估執行計劃與該SQL真實的執行計劃不一樣呢？原因有多個方面，常見的情況包括以下幾個方面：
① 綁定變量窺視（Bind Peeking）：EXPLAIN PLAN里不會進行綁定變量窺視，但是Runtime Plan里會進行綁定變量窺視，所以，如果發生這種情況，那麼會使這兩個執行計劃產生差異。
② 隱式轉換：Explain Plan里不會考慮綁定變量的類型，但是Runtime Plan里會考慮類型，從而有可能會根據綁定變量的類型出現隱式轉換，所以謂詞（Predicate）會發生變化，使得執行計劃也會產生差異。
③ 優化器參數：執行Explain Plan的Session與Runtime Plan的Session不是同一個。如果各個Session之間存在優化器參數差異，那麼執行計劃也會產生差異。
④ 統計信息收集參數：Explain Plan始終是用最新的統計信息產生執行計劃，但是，Runtime Plan不一定會用最新的統計信息。因此也會產生執行計劃差異。在收集統計信息時，一個與緩存的游標是否失效的很重要的參數為NO_INVALIDATE。在重新收集統計信息時，可以指定NO_INVALIDATE選項。該選項有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE這3個值。如果取值為TRUE，那麼表示收集統計信息後不進行游標失效動作，原有的Shared Cursor保持原有狀態。如果取值為FALSE，那麼表示將統計信息對象相關的所有Cursor全部失效，目標SQL語句在下次執行時就會使用硬解析。如果設置為AUTO_INVALIDATE，那麼Oracle自己決定Shared Cursor失效動作，當SQL再次執行時間距離上次收集統計信息的時間超過5小時（隱含參數「_OPTIMIZER_INVALIDATION_PERIOD」決定）則對SQL重新做硬解析。AUTO_INVALIDATE為默認選項。有些DBA在收集統計信息時，沒有使用NO_INVALIDATE=>FALSE選項，所以，即使收集了統計信息，執行計劃也不會立即改變。可以在表級別設置讓所有依賴於該表的游標不失效，設置方法為：
EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的統計信息時，讓所有依賴於該表的游標不失效
實驗一：
CREATE TABLE TEST_EXPLAIN_LHR AS SELECT * FROM DBA_OBJECTS; INSERT INTO TEST_EXPLAIN_LHR SELECT * FROM TEST_EXPLAIN_LHR; COMMIT; SELECT COUNT(*) FROM TEST_EXPLAIN_LHR; CREATE INDEX IDX_OBJ_LHR ON TEST_EXPLAIN_LHR(OBJECT_ID); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'test_explain_lhr',ESTIMATE_PERCENT => 100,CASCADE => TRUE); VAR X NUMBER; VAR Y NUMBER; EXEC :X := 0; EXEC :Y := 100000; EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); SET AUTOT ON SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SET AUTOT OFF SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));
下面實驗驗證了使用EXPLAIN PLAN FOR和SET AUTOT ON方式獲取到的執行計劃都是不準確的：
SYS@PROD1> clear scr SYS@PROD1> CREATE TABLE test_explain_lhr AS SELECT * FROM Dba_Objects; Table created. SYS@PROD1> INSERT INTO test_explain_lhr SELECT * FROM test_explain_lhr; 72503 rows created. SYS@PROD1> COMMIT; Commit complete. SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr; COUNT(*) ---------- 145006 SYS@PROD1> CREATE INDEX idx_obj_lhr ON test_explain_lhr(object_id); Index created. SYS@PROD1> EXEC dbms_stats.gather_table_stats(USER,'test_explain_lhr',estimate_percent => 100,cascade => TRUE); PL/SQL procedure successfully completed. SYS@PROD1> VAR x NUMBER; SYS@PROD1> VAR y NUMBER; SYS@PROD1> EXEC :x := 0; PL/SQL procedure successfully completed. SYS@PROD1> EXEC :y := 100000; PL/SQL procedure successfully completed. SYS@PROD1> EXPLAIN PLAN FOR SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; Explained. SYS@PROD1> set line 9999 SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------- Plan hash value: 3299589416 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 363 | 1815 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND "T"."OBJECT_ID"<=TO_NUMBER(:Y)) 17 rows selected. SYS@PROD1> set autot on SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; COUNT(*) ---------- 145006 Execution Plan ---------------------------------------------------------- Plan hash value: 3299589416 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 363 | 1815 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND "T"."OBJECT_ID"<=TO_NUMBER(:Y)) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SYS@PROD1> SET AUTOT OFF SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; COUNT(*) ---------- 145006 SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID 1r87sg98rdkuf, child number 0 ------------------------------------- SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y Plan hash value: 2428225634 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 90 (100)| | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX FAST FULL SCAN| IDX_OBJ_LHR | 145K| 708K| 90 (2)| 00:00:02 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("TEST_EXPLAIN_LHR"."OBJECT_ID")) END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :X (NUMBER): 0 2 - :Y (NUMBER): 100000 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:X<=:Y) 3 - filter(("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 53 rows selected.
& 說明：
有關真實的執行計劃的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152884/
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
