【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程序员面试笔试宝典》，作者：李华荣。
