本文由恩墨大讲堂154期线上分享整理而成。
一
问题概要
对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行计划,偶尔会发现两边有不一致的情况,为什么呢?为什么预估执行计划会不准确?怎样才能避免这种情况的发生?
二
问题解答
这是执行计划相关中会被经常问道的问题,也是困扰自己很长时间的问题。希望通过下面的分析能解释一部分原因。
对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的真实执行计划不一致的情况,其原因要比想象的更多种多样。
绑定变量窥视(Bind Peeking):Explain Plan 里不会进行绑定变量窥视,但是 Runtime Plan 里会进行绑定变量窥视,所以,如果这种情况发生会使两个执行计划会产生差异。
隐式转换:Explain Plan 里不会考虑绑定变量的类型,但是 Runtime Plan 里会考虑类型,从而有可能会根据绑定变量的类型出现隐式转换,所以谓词(Predicate)会发生变化,使得执行计划也会产生差异。
优化器参数:执行 Explain Plan 的 Session 与 Runtime Plan 的 Session 不是同一个。如果各个 Session 之间存在优化器参数差异,执行计划也会产生差异。
统计信息收集参数:Explain Plan 始终是用最新的统计信息产生执行计划,但是,Runtime Plan 不一定会用最新的统计信息。因此也会产生执行计划差异。
预估执行计划与实际执行计划产生差异的原因总结为上面几种情况,当然也有因 Oracle Bug 的原因也会有产生执行计划的差异情况。
下面通过几个测试,加深对上面的问题的理解。
三
测试环境
Oracle 版本是 11.2.0.1的情况。
SQL> SELECT * FROMV$VERSION WHERE ROWNUM <= 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 –Production
生成表 T1,T1 表有如下特点:
表名 | 列名 | 列类型 | 说明 |
T1 | C1 | Number | “1”值有10,000个,“1~10000”的值各一个,总共有10,000种值 |
C2 | Varchar2 | 同上 |
之后,对列 C1、C2 分别生成单列索引 IDX_T1_C1 和IDX_T1_C2。
SQL> CREATE TABLET1 ( C1 INT , C2 VARCHAR2(10));
表已创建。
SQL> INSERT INTOT1 SELECT 1, '1' FROM DUAL CONNECT BY LEVEL <= 10000;
已创建10000行。
SQL> INSERT INTOT1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
已创建10000行。
SQL> CREATE INDEXIDX_T1_C1 ON T1(C1);
索引已创建。
SQL> CREATE INDEXIDX_T1_C2 ON T1(C2);
索引已创建。
对表T1进行统计信息收集。METHOD_OPT 的参数设为 ALLCOLUMNS SIZE 5 ,即,直方图的 BUCKETS 个数指定为5。但是列 C1 和 C2 有 10,000个不同的值,BUCKETS 个数为5的话,会生成等高直方图(HEIGHT BALANCED)。
SQL> EXECDBMS_STATS.gather_table_stats(user,'T1', method_opt =>'FOR ALL COLUMNS SIZE5');
PL/SQL 过程已成功完成。
收集统计信息以后如下:
--table stats
SELECT t1.TABLE_NAME,
t1.num_rows,
t1.SAMPLE_SIZE
FROM dba_tables t1
WHERE table_name = 'T1'
AND t1.OWNER = user;
TABLE_NAME NUM_ROWS SAMPLE_SIZE
---------- ---------------------
T1 20000 20000
--column stats
SELECT t2.TABLE_NAME,
t2.COLUMN_NAME,
t2.NUM_DISTINCT,
t2.NUM_NULLS,
t2.DENSITY,
t2.LOW_VALUE,
t2.HIGH_VALUE,
t2.HISTOGRAM
FROM dba_tab_columns t2
WHERE t2.table_name = 'T1'
AND t2.OWNER = user';
TABLE COLUMNUM_DISTINCT NUM_NULLS DENSITY LOW_V HIGH_VALUE HISTOGRAM
----- ----------------- --------- ------- ----- ---------- --------------------
T1 C1 10000 0 0.00005 C102 C302 HEIGHT BALANCED
T1 C2 10000 0 0.00005 31 39393939 HEIGHT BALANCED
--histogram stats
select t3.TABLE_NAME
,t3.COLUMN_NAME
,t3.ENDPOINT_NUMBER
,t3.ENDPOINT_VALUE
from dba_tab_histograms t3
WHERE t3.table_name = 'T1'
AND t3.OWNER = user;
TABLE COLUM ENDPOINTENDPOINT_VALUE
----- ----- ----------------------
T1 C1 2 1
T1 C1 3 2000
T1 C1 4 6000
T1 C1 5 10000
T1 C2 2 2.544225460682
T1 C2 3 2.607349087913
T1 C2 4 2.814229665870
T1 C2 5 2.971215519298
四
案列1:绑定变量窥视(Bind Peeking)
下面我们看下,因绑定变量窥视,而引起的预估执行计划与实际执行计划不一致的情况。首先,激活绑定变量窥视功能,默认值就是TRUE。
SQL> alter sessionset "_optim_peek_user_binds" = true;
会话已更改。
首先,我们输出预估执行计划。从下面可以看到,执行计划选择的是索引范围扫描(Index Range Scan)的方式。
SQL> var b1number;
SQL> exec :b1 :=1;
PL/SQL 过程已成功完成。
SQL> explain planfor
2 select count(c2)
3 fromt1
4 where c1 = :b1;
已解释。
SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 12 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_T1_C1 | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("C1"=TO_NUMBER(:B1))
绑定变量B1的实际值是“1”。T1表里值为1的记录数将近占50%,这种情况与其选择索引范围扫,不如选择全表扫(Table Full Scan)会有效率一些。但是 ExplainPlan命令不进行绑定变量的窥视,即,在创建预估执行计划的过程中,会把绑定变量的值设为未知(Uknown)来处理,不会考虑实际的绑定变量的值到底是什么。所以,ExplainPlan 不关心其值是不是“1”,而只考虑 Distinct Count 来建立执行计划。
等高直方图(HEIGHT BALANCED)存在的时候,预估行数会通过 DistinctCount 列进行计算。计算公式如下:
预估行数 = 全部行数 / Distinct Count = 20,000 /10,000 = 2
但是,实际执行计划与上面的结果完全不一样,如下。
SQL> select /*+gather_plan_statistics */ count(c2)
2 fromt1
3 where c1 = :b1;
COUNT(C2)
----------
10001
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'typical'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T1 | 8000 | 48000 | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter("C1"=:B1)
可以看到,使用了绑定变量窥视,即,优化器在创建执行计划前读取了绑定变量的实际的值(进行是窥视)。之后,参考绑定变量的值来创建执行计划。这个例子,使用了值“1”来创建了执行计划。所以,预估行数从 ExplainPlan 里的2 变成了8000。其原因如下:
预估行数 = 值“1”的 buckets 数 * buckets 的高度
= 2 *(20000 / 5 ) = 8000
实际行数为10,001,预估值与实际值相当接近了。
使用 DBMS_XPLAN.DISPLAY_CURSOR函数的时候,参数里如果加上 +PEEKED_BBINDS 的话,执行计划里可以看到绑定变量窥视的值。
SQL> select /*+gather_plan_statistics */ count(c2)
2 fromt1
3 where c1 = :b1;
COUNT(C2)
----------
10001
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'all +peeked_binds'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T1 | 8000 | 48000 | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name /Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Peeked Binds(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter("C1"=:B1)
Column ProjectionInformation (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C2")[22]
2 - "C2"[VARCHAR2,10]
绑定变量窥视(Binding Peeking)与绑定变量捕获(Bind Capture)经常弄混。绑定变量捕获(Bind Capture)是对特定 SQL 里使用的绑定变量值按照固定周期放到 SGA 里保存的情况。最初的绑定变量窥视与绑定变量捕获的时间是一样,约15分钟(900秒)后,绑定变量捕获会再次发生,周期性反复发生。下面可以查看绑定变量捕获的信息。
SELECT t4.NAME,
t4.POSITION,
t4.VALUE_STRING,
t4.WAS_CAPTURED,
t4.LAST_CAPTURED
FROM V$sql_bind_capture t4
WHERE sql_id = 'bqqp887001jj8';
NAME POSITION VALUE WAS_C LAST_CAPTU
----- -------- ---------- ----------
:B1 1 1 YES 11-4月 -18
五
案列2:绑定变量类型问题
首先,为了证明这个测试不是因为上面的绑定变量窥视而引起的不一致,所以把绑定变量窥视功能关掉了。
SQL> alter sessionset "_optim_peek_user_binds" = false;
会话已更改。
对 C2 列使用绑定变量,进行观察。从下面可以看到,预估执行计划里使用了索引。
SQL> var b2number;
SQL> exec :b2 :=1;
PL/SQL 过程已成功完成。
SQL> explain planfor
2 selectcount(c2)
3 fromt1
4 where c2 = :b2;
已解释。
SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1_C2 | 2 | 8 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - access("C2"=:B2)
ExplainPlan 命令只会查看是否存在绑定变量,而不会考虑绑定变量的类型是什么,其值是什么,始终会把绑定变量的类型设为 VARCHAR2 类型进行考虑。所以,上面的例子里不管对绑定变量B2如何定义,ExplainPlan 里预估执行计划始终是一样。
但是,真实执行计划里没有选择 INDEX RANGE SCAN,而是选择了 TABLE FULL SCAN。
SQL> select /*+gather_plan_statistics */ count(c2)
2 fromt1
3 where c2 = :b2;
COUNT(C2)
----------
10001
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows| A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 39 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 39 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 10001 |00:00:00.01 | 39 |
-------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C2")=:B2)
为什么会发生这种情况?绑定变量窥视功能已经关闭了,所以肯定不是绑定变量窥视的问题。这里需要注意的是,C2 列是 VARCHAR2 类型,绑定变量 B2 是 NUMBER类型。这时,Oracle 会进行隐式转换,VARCHAR2 类型会被转换成 NUMBER 类型,即,NUMBER 类型的优先级更高。所以,会对C2列进行隐式转换(VARCHAR2 →NUMBER),从而不能使用C2列的索引。可以在谓词信息(Predicate Information)中确认。
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C2")=:B2)
为了再次证明这个是因为隐式转换的问题,我们使用 VARCHAR2 类型的绑定变量 B3 进行测试。
SQL> var b3varchar2(10);
SQL> exec :b3 :='1';
PL/SQL 过程已成功完成。
SQL> select /*+gather_plan_statistics */ count(c2)
2 fromt1
3 where c2 = :b3;
COUNT(C2)
----------
10001
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 20 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 20 |
|* 2 | INDEX RANGE SCAN| IDX_T1_C2 | 1 | 2 | 10001 |00:00:00.01 | 20 |
-----------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - access("C2"=:B3)
从上面可以看到,绑定变量类型是 VARCHAR2 的时候,没有进行隐式转换,产生了与预估执行计划相同的执行计划,使用了索引的范围扫描。
这个例子也说明,不能完全相信预估的执行计划。内部的一些转换(比如列的隐式转换)会使执行计划改变,甚至有时候会出现不希望的执行计划。
六
案列3:统计信息收集的参数问题
下面 SQL 的预估执行计划与实际执行计划完全一致。
SQL> explain planfor
2 select count(c2)
3 fromt1
4 where c1 = 2;
已解释。
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_T1_C1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("C1"=2)
SQL> select /*+ gather_plan_statistics */ count(c2)
2 fromt1
3 where c1 = 2;
COUNT(C2)
----------
1
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN |IDX_T1_C1 | 1 | 1 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("C1"=2)
对 T1 表的 C1 = 2,C2=1 的值增加20,000个,之后重新收集统计信息,但是 NO_INVALIDATE 参数设为 NULL,NULL 的意思是让 Oracle 自动处理的意思。NO_INVALIDATE 其他参数情况参考如下:
NO_INVALIDATE=TRUE:更新统计信息,但对有从属(Dependency)关系的 SQL 不进行Invalidation。为了避免一次性大量的硬解析(Hard Parse)现象的发生。SQL 如果在 SGA 里 Age Out 后,再次执行的时候,才会用到更新后的统计信息。
NO_INVALIDATE=FALSE:更新统计信息,并对有从属(Dependency)关系的 SQL 马上进行 Invalidation。
NO_INVALIDATE=AUTO(NULL):更新统计信息,但对有从属关系的 SQL 不会一次性的进行 Invalidation,而是在最大5小时(18,000秒)内随机进行 Invalidation 的方式进行。可以说是 TRUE 与 FALSE 的中间形式。18,000秒是可以通过 _OPTIMIZER_INVALIDATION_PERIOD 参数进行设定。
现在对表T1增加数据,并收集统计信息,但是 NO_INVALIDATE 参数设为 NULL(默认值是 NULL)。
SQL> insert intot1 select 2,'1' from dual connect by level <= 20000;
已创建20000行。
SQL> execdbms_stats.gather_table_stats(user,'T1',method_opt => 'for all columns size5',no_invalidate => null);
PL/SQL 过程已成功完成。
Explain Plan 命令始终是在用最新的统计信息,所以从下面可以看到,ExplainPlan 命令对 C1=2 的条件使用了最新的统计信息,执行计划选择了 Table Full Scan。预估行数为 16,000 行,与实际行数 20,001 行数相当接近。因为存在列的直方图,这种预估是可行的。
SQL> explain planfor
2 select count(t1.c2)
3 fromsys.t1
4 where t1.c1 = 2 ;
已解释。
SQL> select * fromtable(dbms_xplan.display());
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 20 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T1 | 16000 |96000 | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
2 - filter("T1"."C1"=2)
但是,在真实执行计划中仍然选择了 Index Range Scan,因为虽然统计信息更新了,但是相关的 SQL 还没有被 Invalidation。
SQL> select /*+ gather_plan_statistics */ count(c2)
2 fromt1
3 where c1 = 2;
COUNT(C2)
----------
20001
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 102 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 102 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 2 | 20001 |00:00:00.02 | 102 |
|* 3 | INDEX RANGE SCAN | IDX_T1_C1 | 1 | 2 | 20001 |00:00:00.01 | 70 |
----------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("C1"=2)
如果只看 Explain Plan 后就判断“执行计划的效率不错”是不可取的,会根据不同的情况产生很大的性能差异。这时可以通过 DBMS_SHARED_POOL.PURGE 存储过程,或使用 清理共享池(Shared Pool Flush)等方法强制反应最新的统计信息。
七
总结
预估执行计划与真实执行计划产生差异的原因,其实是多种多样的,在分析其原因的过程中发现需要相当多的知识点。
产生差异的原因,其中最普遍的有因绑定变量的窥视,也有因绑定变量的隐式转换,也有因参数差异,也有因统计信息收集参数等问题。
不能对预估执行计划100%信任,一定要实际执行以后验证其结果。如果这个过程中想解释执行计划异常的现象,需要了解 DBMS_XPLAN 包的使用方法与对其结果的正确理解。