在上一篇《第86期 Oracle执行计划，彻底搞懂Oracle的执行计划》中，我们探究了多种生成执行计划的方法，具体来说，我们学习了如何完成以下操作：
1. 展示 SQL 语句**实际（TYPICAL 模式）**执行计划，包含基数预估与成本值。
2. 通过 GATHER_PLAN_STATISTICS Hint，展示 SQL 语句**实际（ALLSTATS LAST 模式）**执行计划，用来对比预估基数和计划里每一步操作返回的真实行数，同时获取语句最终实际执行时间（中间步骤的耗时不一定贴近真实耗时）。
3. 在会话级别将 STATISTICS_LEVEL 参数设置为 ALL，展示 SQL 语句**实际（ALLSTATS LAST 模式）**执行计划，可以拿到最终执行时间，并且中间步骤的耗时估算也更加贴近真实值（部分场景下，SQL 整体执行耗时会明显上升）。
4. 在 ALL_ROWS、FIRST_ROWS_1、CHOOSE 之间切换 OPTIMIZER_MODE 参数，对比实际执行计划的变化。注意：将优化器模式设置为 FIRST_ROWS_1 时，优化器可能低估返回行数，执行计划中就能观察到这个现象。
5. 使用 SQL*Plus 的 AUTOTRACE 和 EXPLAIN PLAN 展示一份看似正确的执行计划，并探究依赖这类计划时可能遇到的各类问题。
6. 观察绑定变量窥视（bind peeking）对执行计划带来的影响。
7. 通过设置 10132 事件来获取执行计划。
8. 直接从 10046 跟踪文件中读取执行计划。
9. 使用 TKPROF 工具处理跟踪文件，同时了解让 TKPROF 生成 Explain 计划时会引发的一个典型隐患。
本系列第一篇博客使用的是OPTIMIZER_FEATURES_ENABLE设置为 10.2.0.1。将 64 位 Oracle 11.2.0.1 的输出结果与 Oracle 10.2.0.2 做对比会很有参考价值。通过对比输出结果，或许可以解答一个疑问：Oracle 10.2.0.2 是否存在某个 bug？比如当OPTIMIZER_MODE设置为FIRST_ROWS_1时，执行计划中展示的基数估算是否异常。如果 11.2.0.1 中也出现同样输出，那大概率说明优化器就是按照该设计逻辑运行的。
当然，本文会沿用第一篇博客中定义的 T1、T2、T3、T4 四张表。
首先，我们将OPTIMIZER_MODE设置为ALL_ROWS，为一条 SQL 语句生成执行计划：
#SET LINESIZE 150 #ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS'; # #SELECT # T3.C1, # SUBSTR(T1.C2,1,10) C2 #FROM # T3, # T1 #WHERE # T1.C1=T3.C1 # AND T1.C1 BETWEEN 1 AND 10; C1 C2 --- ---------- 1 1AAAAAAAAA 2 2AAAAAAAAA 3 3AAAAAAAAA 4 4AAAAAAAAA 5 5AAAAAAAAA 6 6AAAAAAAAA 7 7AAAAAAAAA 8 8AAAAAAAAA 9 9AAAAAAAAA 10 10AAAAAAAA
查看刚执行完的 SQL 语句执行计划的常规方法如下（注意：第三个参数，也就是 FORMAT 参数，也可以设置为 NULL，效果相同）。
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
#
#SQL_ID 9dq71tc7vasgu, child number 0
#-------------------------------------
#SELECT T3.C1, SUBSTR(T1.C2,1,10) C2 FROM T3, T1 WHERE
#T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 985299768
#
#---------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#---------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | | | 10219 (100)| |
#|* 1 | HASH JOIN | | 9 | 2394 | 10219 (1)| 00:02:03 |
#|* 2 | TABLE ACCESS FULL | T3 | 10 | 50 | 10214 (1)| 00:02:03 |
#| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 2610 | 4 (0)| 00:00:01 |
#|* 4 | INDEX RANGE SCAN | SYS_C0017307 | 10 | | 3 (0)| 00:00:01 |
#---------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - access("T1"."C1"="T3"."C1")
# 2 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1">=1 AND "T1"."C1"<=10)
请注意上面输出中，该执行计划的计算成本为 10219（无负载 CPU 统计模式）。
接下来我们再做一组测试，将 OPTIMIZER_MODE 设置为 FIRST_ROWS（Oracle 会假定只需要取回第一行数据，其余行大概率会被丢弃）。
#ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
#
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
#
#SQL_ID 9dq71tc7vasgu, child number 1
#-------------------------------------
#SELECT T3.C1, SUBSTR(T1.C2,1,10) C2 FROM T3, T1 WHERE
#T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 2322422896
#
#---------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#---------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | | | 3073 (100)| |
#| 1 | NESTED LOOPS | | | | | |
#| 2 | NESTED LOOPS | | 2 | 532 | 3073 (1)| 00:00:37 |
#|* 3 | TABLE ACCESS FULL | T3 | 7 | 35 | 3066 (1)| 00:00:37 |
#|* 4 | INDEX UNIQUE SCAN | SYS_C0017307 | 1 | | 1 (0)| 00:00:01 |
#| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 261 | 2 (0)| 00:00:01 |
#---------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 3 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=10 AND "T1"."C1">=1))
从上面的结果可以看到，将优化器模式从ALL_ROWS修改为FIRST_ROWS_1后，生成了一份新的执行计划（子游标号加 1，计划哈希值也发生了变化）。该新计划采用两次嵌套循环连接（先将表 T1 的索引与表 T3 做连接），而不再使用哈希连接，计算出的成本下降到了 3073。你可能会有疑问：既然这个计划的估算成本更低，为什么当OPTIMIZER_MODE设置为ALL_ROWS时，Oracle 没有选择这个嵌套循环连接的计划，而是选择哈希连接？
这里注意，Oracle 11.2.0.1 估算的返回行数和 Oracle 10.2.0.2 是一样的。但仅仅通过上面这些计划，我们其实无法判断真实性能好坏。因此我们重新做测试，修改 SQL 语句，这样就可以在调用DBMS_XPLAN时传入格式参数ALLSTATS LAST：
#ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID ddnbt67ftu9ds, child number 0
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 985299768
#
#----------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
#----------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.07 | 37170 | | | |
#|* 1 | HASH JOIN | | 1 | 9 | 10 |00:00:00.07 | 37170 | 1452K| 1452K| 1365K (0)|
#|* 2 | TABLE ACCESS FULL | T3 | 1 | 10 | 10 |00:00:00.01 | 37164 | | | |
#| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
#|* 4 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10 | 10 |00:00:00.01 | 4 | | | |
#----------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - access("T1"."C1"="T3"."C1")
# 2 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1">=1 AND "T1"."C1"<=10)
这条查询耗时 0.07 秒执行完毕，返回 10 行数据；在内存哈希连接阶段，占用了 1365×1024 字节的内存。
没有从磁盘读取任何数据块（该查询之前已经执行过，表和索引的数据块保留在缓冲区缓存中）。
#ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID ddnbt67ftu9ds, child number 1
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 2322422896
#
#-------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
#-------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 37189 |
#| 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 37189 |
#| 2 | NESTED LOOPS | | 1 | 2 | 10 |00:00:00.01 | 37179 |
#|* 3 | TABLE ACCESS FULL | T3 | 1 | 7 | 10 |00:00:00.01 | 37165 |
#|* 4 | INDEX UNIQUE SCAN | SYS_C0017307 | 10 | 1 | 10 |00:00:00.01 | 14 |
#| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 10 |00:00:00.01 | 10 |
#-------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
#
# 3 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=10 AND "T1"."C1">=1))
如上所示，在FIRST_ROWS_1优化器模式下，这条查询仅用时 0.01 秒就执行完成。
GATHER_PLAN_STATISTICS提示非常有用，它可以让DBMS_XPLAN输出上一条 SQL 语句的真实执行统计信息。
如果不想使用该提示，也可以在会话级别将STATISTICS_LEVEL修改为ALL，示例如下：
#ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
#ALTER SESSION SET STATISTICS_LEVEL='ALL';
#
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID 9dq71tc7vasgu, child number 2
#-------------------------------------
#SELECT T3.C1, SUBSTR(T1.C2,1,10) C2 FROM T3, T1 WHERE
#T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 985299768
#
#----------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
#----------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.07 | 37170 | | | |
#|* 1 | HASH JOIN | | 1 | 9 | 10 |00:00:00.07 | 37170 | 1452K| 1452K| 1424K (0)|
#|* 2 | TABLE ACCESS FULL | T3 | 1 | 10 | 10 |00:00:00.07 | 37164 | | | |
#| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
#|* 4 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10 | 10 |00:00:00.01 | 4 | | | |
#----------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
#
# 1 - access("T1"."C1"="T3"."C1")
# 2 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1">=1 AND "T1"."C1"<=10)
注意，修改STATISTICS_LEVEL参数触发了这条 SQL 语句的又一次硬解析，子游标号现在变为 2。
不知何种原因，哈希连接所需的内存量出现了小幅上升。仔细观察还能发现，修改STATISTICS_LEVEL参数前后，执行耗时保持不变。
我们再次测试，将 OPTIMIZER_MODE设置为FIRST_ROWS_1。
#ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
#
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID 9dq71tc7vasgu, child number 3
#-------------------------------------
#SELECT T3.C1, SUBSTR(T1.C2,1,10) C2 FROM T3, T1 WHERE
#T1.C1=T3.C1 AND T1.C1 BETWEEN 1 AND 10
#
#Plan hash value: 2322422896
#
#-------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
#-------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.07 | 37189 |
#| 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.07 | 37189 |
#| 2 | NESTED LOOPS | | 1 | 2 | 10 |00:00:00.07 | 37179 |
#|* 3 | TABLE ACCESS FULL | T3 | 1 | 7 | 10 |00:00:00.07 | 37165 |
#|* 4 | INDEX UNIQUE SCAN | SYS_C0017307 | 10 | 1 | 10 |00:00:00.01 | 14 |
#| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 10 |00:00:00.01 | 10 |
#-------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 3 - filter(("T3"."C1"<=10 AND "T3"."C1">=1))
# 4 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=10 AND "T1"."C1">=1))
再次留意，这里发生了一次硬解析，子游标号加 1。这一次，查询不再是 0.01 秒完成，而是耗时 0.07 秒。
我们再试一次，这次改用绑定变量，而不是常量（字面值）：
#ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
#ALTER SESSION SET STATISTICS_LEVEL='TYPICAL';
#
#VARIABLE N1 NUMBER
#VARIABLE N2 NUMBER
#EXEC :N1:=1
#EXEC :N2:=10
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID cvq22z77c8fww, child number 0
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 744086632
#
#-----------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
#-----------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.07 | 37170 | | | |
#|* 1 | FILTER | | 1 | | 10 |00:00:00.07 | 37170 | | | |
#|* 2 | HASH JOIN | | 1 | 9 | 10 |00:00:00.07 | 37170 | 1452K| 1452K| 1424K (0)|
#|* 3 | TABLE ACCESS FULL | T3 | 1 | 10 | 10 |00:00:00.01 | 37164 | | | |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10 | 10 |00:00:00.01 | 4 | | | |
#-----------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
执行计划和之前看到的一样，由于绑定变量窥视，基数估算结果也保持不变；唯一不同的是，ID 为1的位置新增了一个FILTER过滤操作，对应的谓词条件要求绑定变量 N1 小于等于绑定变量 N2—— 这是 SQL 语句中的 BETWEEN 语法自动生成的谓词。现在我们把第二个绑定变量的值改成一个大得多的值：
#EXEC :N2:=500000
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID cvq22z77c8fww, child number 0
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 744086632
#
#--------------------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
#--------------------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 500K|00:00:08.01 | 123K| 12985 | | | |
#|* 1 | FILTER | | 1 | | 500K|00:00:08.01 | 123K| 12985 | | | |
#|* 2 | HASH JOIN | | 1 | 9 | 500K|00:00:07.72 | 123K| 12985 | 15M| 3723K| 23M (0)|
#|* 3 | TABLE ACCESS FULL | T3 | 1 | 10 | 500K|00:00:00.11 | 37164 | 0 | | | |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 500K|00:00:06.72 | 86064 | 12985 | | | |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10 | 500K|00:00:00.43 | 34211 | 634 | | | |
#--------------------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
#
# 1 - filter(:N1<=:N2)
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
从上面的结果可以看到，本次没有发生硬解析（SQL_ID 和子游标号与之前一致），两份DBMS_XPLAN输出里的估算行数（E-Rows）列完全相同。这里没有展示 Used-Tmp 列（该 11.2.0.1 环境配置了更大的 PGA_AGGREGATE_TARGET）。我们修改OPTIMIZER_MODE，重新进行测试：
#EXEC :N2:=10
#ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID cvq22z77c8fww, child number 1
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 1340904442
#
#--------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
#--------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 37189 |
#|* 1 | FILTER | | 1 | | 10 |00:00:00.01 | 37189 |
#| 2 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 37189 |
#| 3 | NESTED LOOPS | | 1 | 2 | 10 |00:00:00.01 | 37179 |
#|* 4 | TABLE ACCESS FULL | T3 | 1 | 7 | 10 |00:00:00.01 | 37165 |
#|* 5 | INDEX UNIQUE SCAN | SYS_C0017307 | 10 | 1 | 10 |00:00:00.01 | 14 |
#| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 1 | 10 |00:00:00.01 | 10 |
#--------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 4 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
#
#EXEC :N2:=500000
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID cvq22z77c8fww, child number 1
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 1340904442
#
#-----------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
#-----------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 500K|00:00:03.77 | 782K| 1 |
#|* 1 | FILTER | | 1 | | 500K|00:00:03.77 | 782K| 1 |
#| 2 | NESTED LOOPS | | 1 | | 500K|00:00:03.52 | 782K| 1 |
#| 3 | NESTED LOOPS | | 1 | 2 | 500K|00:00:02.00 | 282K| 1 |
#|* 4 | TABLE ACCESS FULL | T3 | 1 | 7 | 500K|00:00:00.39 | 70498 | 0 |
#|* 5 | INDEX UNIQUE SCAN | SYS_C0017307 | 500K| 1 | 500K|00:00:00.95 | 212K| 1 |
#| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 500K| 1 | 500K|00:00:00.86 | 500K| 0 |
#-----------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 4 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
当OPTIMIZER_MODE设置为FIRST_ROWS_1时，这条 SQL 执行耗时 3.77 秒；
设置为ALL_ROWS时耗时 8.01 秒（对比之前只返回 10 行数据时的 0.01 秒和 0.07 秒）。很明显，这是不是就意味着：如果要优化性能，我们就应该把OPTIMIZER_MODE设成FIRST_ROWS_1？
简短回答：不是。这个问题或许会在后续博客文章中深入研究。
接下来，我们来看未收集统计信息的表。我们修改那条使用绑定变量的原始 SQL，使其访问两张表 —— 这两张表以及它们的索引都没有最新的统计信息：
#EXEC :N2:=10
#ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T4.C1,
# SUBSTR(T2.C2,1,10) C2
#FROM
# T4,
# T2
#WHERE
# T2.C1=T4.C1
# AND T2.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID 94bv1jwkzcc38, child number 0
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T4.C1, SUBSTR(T2.C2,1,10) C2
#FROM T4, T2 WHERE T2.C1=T4.C1 AND T2.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 2134503202
#
#--------------------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
#--------------------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 37169 | 37128 | | | |
#|* 1 | FILTER | | 1 | | 10 |00:00:00.01 | 37169 | 37128 | | | |
#|* 2 | HASH JOIN | | 1 | 10 | 10 |00:00:00.01 | 37169 | 37128 | 711K| 711K| 1116K (0)|
#| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 10 |00:00:00.01 | 4 | 1 | | | |
#|* 4 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | | | |
#|* 5 | TABLE ACCESS FULL | T4 | 1 | 1855 | 10 |00:00:00.01 | 37165 | 37127 | | | |
#--------------------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 2 - access("T2"."C1"="T4"."C1")
# 4 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
# 5 - filter(("T4"."C1">=:N1 AND "T4"."C1"<=:N2))
#
#Note
#-----
# - dynamic sampling used for this statement (level=2)
这里唯一的变化是，Oracle 现在估算会返回 10 行，而不是之前看到的 9 行；
并且在谓词信息标题下方有一条备注，说明使用了2 级动态采样。另外，计划中 HASH JOIN（哈希连接）下方直接相连的行源顺序发生了变化。
#ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T4.C1,
# SUBSTR(T2.C2,1,10) C2
#FROM
# T4,
# T2
#WHERE
# T2.C1=T4.C1
# AND T2.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID 94bv1jwkzcc38, child number 1
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T4.C1, SUBSTR(T2.C2,1,10) C2
#FROM T4, T2 WHERE T2.C1=T4.C1 AND T2.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 2134503202
#
#--------------------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
#--------------------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 37169 | 37129 | | | |
#|* 1 | FILTER | | 1 | | 10 |00:00:00.01 | 37169 | 37129 | | | |
#|* 2 | HASH JOIN | | 1 | 10 | 10 |00:00:00.01 | 37169 | 37129 | 711K| 711K| 1147K (0)|
#| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 10 |00:00:00.01 | 4 | 1 | | | |
#|* 4 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | | | |
#|* 5 | TABLE ACCESS FULL | T4 | 1 | 1855 | 10 |00:00:00.01 | 37165 | 37128 | | | |
#--------------------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 2 - access("T2"."C1"="T4"."C1")
# 4 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
# 5 - filter(("T4"."C1">=:N1 AND "T4"."C1"<=:N2))
#
#Note
#-----
# - dynamic sampling used for this statement (level=2)
好的，相比这条 SQL 访问 T1、T3 表时的计划，本次计划发生了一点变化。执行计划不再采用嵌套循环连接；
实际上，它选用的计划和OPTIMIZER_MODE设置为ALL_ROWS时完全一样。
Oracle 9i 以及更早版本的默认优化器模式是CHOOSE，那我们看看在这两张表上使用该优化器模式会是什么效果：
#ALTER SESSION SET OPTIMIZER_MODE='CHOOSE';
#
#SELECT /*+ GATHER_PLAN_STATISTICS */
# T4.C1,
# SUBSTR(T2.C2,1,10) C2
#FROM
# T4,
# T2
#WHERE
# T2.C1=T4.C1
# AND T2.C1 BETWEEN :N1 AND :N2;
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
#
#SQL_ID 94bv1jwkzcc38, child number 2
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T4.C1, SUBSTR(T2.C2,1,10) C2
#FROM T4, T2 WHERE T2.C1=T4.C1 AND T2.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 1136565161
#
#-------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads |
#-------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.03 | 386K| 39012 |
#| 1 | NESTED LOOPS | | 1 | 10 |00:00:00.03 | 386K| 39012 |
#| 2 | NESTED LOOPS | | 1 | 10 |00:00:00.03 | 386K| 39011 |
#| 3 | TABLE ACCESS FULL | T4 | 1 | 1000K|00:00:00.38 | 37165 | 37133 |
#|* 4 | INDEX UNIQUE SCAN | SYS_C0017308 | 1000K| 10 |00:00:02.30 | 348K| 1878 |
#| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 |00:00:00.01 | 10 | 1 |
#-------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 4 - access("T2"."C1"="T4"."C1")
# filter(("T2"."C1"<=:N2 AND "T2"."C1">=:N1))
#
#Note
#-----
# - rule based optimizer used (consider using cbo)
注意，输出的备注部分显示使用了基于规则的优化器RBO。尽管 Oracle 官方文档http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/compat.htm写明：自 Oracle 10.1 版本起，已经不再支持 RBO 基于规则的优化器。同时还可以看到，执行计划现在采用嵌套循环连接，不再出现 FILTER 过滤算子；全表扫描再次先和另一张表的索引做连接，
这和将OPTIMIZER_MODE设置为FIRST_ROWS_1查询访问 T1 与 T3 表时的表现一模一样。
假设我们闲得无聊，我们假定 C1 列存储的是信用卡号，接下来做一个实验：提取SQL_ID为cvq22z77c8fww的全部子游标，
同时查看语句初次硬解析时传入的绑定变量。生产环境务必管控该功能的访问权限，因为它有可能泄露敏感数据。
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cvq22z77c8fww',NULL,'ALLSTATS LAST +PEEKED_BINDS'));
#
#SQL_ID cvq22z77c8fww, child number 0
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 744086632
#
#--------------------------------------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
#--------------------------------------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 500K|00:00:08.01 | 123K| 12985 | | | |
#|* 1 | FILTER | | 1 | | 500K|00:00:08.01 | 123K| 12985 | | | |
#|* 2 | HASH JOIN | | 1 | 9 | 500K|00:00:07.72 | 123K| 12985 | 15M| 3723K| 23M (0)|
#|* 3 | TABLE ACCESS FULL | T3 | 1 | 10 | 500K|00:00:00.11 | 37164 | 0 | | | |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 500K|00:00:06.72 | 86064 | 12985 | | | |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10 | 500K|00:00:00.43 | 34211 | 634 | | | |
#--------------------------------------------------------------------------------------------------------------------------------------------
#
#Peeked Binds (identified by position):
#--------------------------------------
#
# 1 - (NUMBER): 1
# 2 - (NUMBER): 10
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
#
#-
#
#SQL_ID cvq22z77c8fww, child number 1
#-------------------------------------
#SELECT /*+ GATHER_PLAN_STATISTICS */ T3.C1, SUBSTR(T1.C2,1,10) C2
#FROM T3, T1 WHERE T1.C1=T3.C1 AND T1.C1 BETWEEN :N1 AND :N2
#
#Plan hash value: 1340904442
#
#-----------------------------------------------------------------------------------------------------------------
#| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
#-----------------------------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 1 | | 500K|00:00:03.77 | 782K| 1 |
#|* 1 | FILTER | | 1 | | 500K|00:00:03.77 | 782K| 1 |
#| 2 | NESTED LOOPS | | 1 | | 500K|00:00:03.52 | 782K| 1 |
#| 3 | NESTED LOOPS | | 1 | 2 | 500K|00:00:02.00 | 282K| 1 |
#|* 4 | TABLE ACCESS FULL | T3 | 1 | 7 | 500K|00:00:00.39 | 70498 | 0 |
#|* 5 | INDEX UNIQUE SCAN | SYS_C0017307 | 500K| 1 | 500K|00:00:00.95 | 212K| 1 |
#| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 500K| 1 | 500K|00:00:00.86 | 500K| 0 |
#-----------------------------------------------------------------------------------------------------------------
#
#Peeked Binds (identified by position):
#--------------------------------------
#
# 1 - (NUMBER): 1
# 2 - (NUMBER): 10
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(:N1<=:N2)
# 4 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
# 5 - access("T1"."C1"="T3"."C1")
# filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
那么，如果我们想弄清楚子游标产生的原因该怎么做？可以执行类似下面的操作：
#DESC V$SQL_SHARED_CURSOR # #SET LINESIZE 200 #SET HEADING ON #BREAK ON SQL_ID SKIP 1 # #SELECT # * #FROM # V$SQL_SHARED_CURSOR #WHERE # SQL_ID='94bv1jwkzcc38'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER USOOSLFEBPISTABDLTBIIRLIOEMUTNFAITDLDBPCSCPTMBMROPMFLPLAFLRLHPB ------------- ---------------- ---------------- ------------ --------------------------------------------------------------- 94bv1jwkzcc38 0000000257BD5498 0000000257E2E5B0 0 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 0000000257BD5498 0000000257DC8F18 1 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNN 0000000257BD5498 0000000257C8C3F8 2 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNN
上面的结果表明，子游标是因为优化器模式不匹配而创建的（没错，我们修改过 `OPTIMIZER_MODE`）。
我们也可以查看绑定变量的定义（本例中并不需要，因为我们已经使用了DBMS_XPLAN那个未公开的格式参数PEEKED_BINDS，看到了大部分相关信息）：
#SELECT # S.CHILD_NUMBER CN, # SBM.* #FROM # V$SQL_BIND_METADATA SBM, # V$SQL S #WHERE # S.SQL_ID='94bv1jwkzcc38' # AND S.CHILD_ADDRESS=SBM.ADDRESS #ORDER BY # S.CHILD_NUMBER, # SBM.POSITION; CN ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME -- ---------------- ---------- ---------- ---------- ---------- --------- 0 0000000257E2E5B0 1 2 22 0 N1 0 0000000257E2E5B0 2 2 22 0 N2 1 0000000257DC8F18 1 2 22 0 N1 1 0000000257DC8F18 2 2 22 0 N2 2 0000000257C8C3F8 1 2 22 0 N1 2 0000000257C8C3F8 2 2 22 0 N2
上面的示例展示的是实际使用过的执行计划，它有可能和 explain plan生成的计划不一样。
那我们来看看EXPLAIN PLAN FOR语法。
#ALTER SYSTEM FLUSH SHARED_POOL;
#
#ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
#
#EXPLAIN PLAN FOR
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN 1 AND 10;
#
#Explained.
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY());
#
#Plan hash value: 985299768
#
#---------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#---------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 9 | 2394 | 10219 (1)| 00:02:03 |
#|* 1 | HASH JOIN | | 9 | 2394 | 10219 (1)| 00:02:03 |
#|* 2 | TABLE ACCESS FULL | T3 | 10 | 50 | 10214 (1)| 00:02:03 |
#| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 2610 | 4 (0)| 00:00:01 |
#|* 4 | INDEX RANGE SCAN | SYS_C0017307 | 10 | | 3 (0)| 00:00:01 |
#---------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - access("T1"."C1"="T3"."C1")
# 2 - filter("T3"."C1"<=10 AND "T3"."C1">=1)
# 4 - access("T1"."C1">=1 AND "T1"."C1"<=10)
上面这份计划看起来和第一个实际执行计划完全相同。接下来我们使用绑定变量做一次测试：
#VARIABLE N1 NUMBER
#VARIABLE N2 NUMBER
#EXEC :N1:=1
#EXEC :N2:=10
#
#EXPLAIN PLAN FOR
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#Explained.
#
#SELECT
# *
#FROM
# TABLE(DBMS_XPLAN.DISPLAY());
#
#Plan hash value: 744086632
#
#----------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#----------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 1 | FILTER | | | | | |
#|* 2 | HASH JOIN | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 3 | TABLE ACCESS FULL | T3 | 2500 | 12500 | 10219 (1)| 00:02:03 |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 637K| 178 (0)| 00:00:03 |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 4500 | | 11 (0)| 00:00:01 |
#----------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(TO_NUMBER(:N1)<=TO_NUMBER(:N2))
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter("T3"."C1">=TO_NUMBER(:N1) AND "T3"."C1"<=TO_NUMBER(:N2))
# 5 - access("T1"."C1">=TO_NUMBER(:N1) AND "T1"."C1"<=TO_NUMBER(:N2))
可以注意到，在上面输出的谓词信息部分，每个绑定变量外侧都包裹了TO_NUMBER转换，
即便这些绑定变量声明的本身就是 NUMBER 数据类型。成本值也有所上升，估算行数从 9 一下子涨到了 2499（没有发生绑定变量窥视）。
我们再用 AUTOTRACE 查看执行计划（从 Oracle 10.1.0.1 版本开始，AUTOTRACE 底层会调用 DBMS_XPLAN 输出格式化后的执行计划）。
#SET AUTOTRACE TRACEONLY EXPLAIN
#
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#Plan hash value: 744086632
#
#----------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#----------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 1 | FILTER | | | | | |
#|* 2 | HASH JOIN | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 3 | TABLE ACCESS FULL | T3 | 2500 | 12500 | 10219 (1)| 00:02:03 |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 637K| 178 (0)| 00:00:03 |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 4500 | | 11 (0)| 00:00:01 |
#----------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(TO_NUMBER(:N1)<=TO_NUMBER(:N2))
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter("T3"."C1">=TO_NUMBER(:N1) AND "T3"."C1"<=TO_NUMBER(:N2))
# 5 - access("T1"."C1">=TO_NUMBER(:N1) AND "T1"."C1"<=TO_NUMBER(:N2))
好的，和 Oracle 10.1.0.1 及更高版本里前面的示例一样，谓词信息部分依旧存在错误。我们再次展示运行时统计信息以及解释计划：
#SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN
#
#SELECT
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#10 rows selected.
#
#Execution Plan
#----------------------------------------------------------
#Plan hash value: 744086632
#
#----------------------------------------------------------------------------------------------
#| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
#----------------------------------------------------------------------------------------------
#| 0 | SELECT STATEMENT | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 1 | FILTER | | | | | |
#|* 2 | HASH JOIN | | 2499 | 649K| 10397 (1)| 00:02:05 |
#|* 3 | TABLE ACCESS FULL | T3 | 2500 | 12500 | 10219 (1)| 00:02:03 |
#| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 637K| 178 (0)| 00:00:03 |
#|* 5 | INDEX RANGE SCAN | SYS_C0017307 | 4500 | | 11 (0)| 00:00:01 |
#----------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
# 1 - filter(TO_NUMBER(:N1)<=TO_NUMBER(:N2))
# 2 - access("T1"."C1"="T3"."C1")
# 3 - filter("T3"."C1">=TO_NUMBER(:N1) AND "T3"."C1"<=TO_NUMBER(:N2))
# 5 - access("T1"."C1">=TO_NUMBER(:N1) AND "T1"."C1"<=TO_NUMBER(:N2))
#
#Statistics
#----------------------------------------------------------
# 0 recursive calls
# 0 db block gets
# 37170 consistent gets
# 7760 physical reads
# 0 redo size
# 812 bytes sent via SQL*Net to client
# 523 bytes received via SQL*Net from client
# 2 SQL*Net roundtrips to/from client
# 0 sorts (memory)
# 0 sorts (disk)
# 10 rows processed
请注意，即便我们看到了真实的运行时统计信息，上面这份计划也未必是实际执行计划。这种情况容易让人困惑：我们拿到的是真实的执行统计，但配套的执行计划却只是一个粗略的预估。
接下来做另一项测试。本次我们配置 Oracle，让它每次发生硬解析时，都把实际执行计划写入跟踪文件。我们会给 SQL 语句增加注释，以此强制触发一次硬解析：
#ALTER SESSION SET TRACEFILE_IDENTIFIER = '10132_HARD_PARSE';
#ALTER SESSION SET EVENTS '10132 TRACE NAME CONTEXT FOREVER, LEVEL 1';
#
#SELECT /* TEST */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2;
#
#ALTER SESSION SET EVENTS '10132 TRACE NAME CONTEXT OFF';
如果我们查看跟踪文件里的输出内容，可能会看到类似下面这样的信息：
#user_id=68 user_name=TESTUSER module=SQL*Plus action=
#sql_id=3g3fc5qyju0j3 plan_hash_value=744086632 problem_type=3
#----- Current SQL Statement for this session (sql_id=3g3fc5qyju0j3) -----
#SELECT /* TEST */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2
#sql_text_length=119
#sql=SELECT /* TEST */
# T3.C1,
# SUBSTR(T1.C2,1,10) C2
#FROM
# T3,
# T1
#WHERE
# T1.C1=T3.C1
# AND T1.C1 BETWEEN :N1 AND :N2
#----- Explain Plan Dump -----
#----- Plan Table -----
#
#============
#Plan Table
#============
#-----------------------------------------------------+-----------------------------------+
#| Id | Operation | Name | Rows | Bytes | Cost | Time |
#-----------------------------------------------------+-----------------------------------+
#| 0 | SELECT STATEMENT | | | | 10K | |
#| 1 | FILTER | | | | | |
#| 2 | HASH JOIN | | 9 | 2394 | 10K | 00:02:03 |
#| 3 | TABLE ACCESS FULL | T3 | 10 | 50 | 10K | 00:02:03 |
#| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 2610 | 4 | 00:00:01 |
#| 5 | INDEX RANGE SCAN | SYS_C0017307| 10 | | 3 | 00:00:01 |
#-----------------------------------------------------+-----------------------------------+
#Predicate Information:
#----------------------
#1 - filter(:N1<=:N2)
#2 - access("T1"."C1"="T3"."C1")
#3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
#5 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
#
#Content of other_xml column
#===========================
# db_version : 11.2.0.1
# parse_schema : TESTUSER
#
# plan_hash : 744086632
# plan_hash_2 : 3461135235
#Peeked Binds
#============
# Bind variable information
# position=1
# datatype(code)=2
# datatype(string)=NUMBER
# precision=0
# scale=0
# max length=22
# value=1
# Bind variable information
# position=2
# datatype(code)=2
# datatype(string)=NUMBER
# precision=0
# scale=0
# max length=22
# value=10
# 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")
# FULL(@"SEL$1" "T3"@"SEL$1")
# INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
# LEADING(@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1")
# USE_HASH(@"SEL$1" "T1"@"SEL$1")
# END_OUTLINE_DATA
# */
#
#Optimizer state dump:
#Compilation Environment Dump
#optimizer_mode_hinted = false
#optimizer_features_hinted = 0.0.0
#parallel_execution_enabled = true
#parallel_query_forced_dop = 0
#parallel_dml_forced_dop = 0
#parallel_ddl_forced_degree = 0
#parallel_ddl_forced_instances = 0
#_query_rewrite_fudge = 90
#optimizer_features_enable = 11.2.0.1
#_optimizer_search_limit = 5
#cpu_count = 8
#active_instance_count = 1
#hash_area_size = 131072
#bitmap_merge_area_size = 1048576
#sort_area_size = 65536
#sort_area_retained_size = 0
#_sort_elimination_cost_ratio = 0
#_optimizer_block_size = 8192
#_sort_multiblock_read_count = 2
#_hash_multiblock_io_count = 0
#_db_file_optimizer_read_count = 8
#_optimizer_max_permutations = 2000
#pga_aggregate_target = 1843200 KB
#_pga_max_size = 368640 KB
#_query_rewrite_maxdisjunct = 257
#_smm_auto_min_io_size = 56 KB
#_smm_auto_max_io_size = 248 KB
#_smm_min_size = 1024 KB
#_smm_max_size = 184320 KB
#_smm_px_max_size = 921600 KB
#_cpu_to_io = 0
#_optimizer_undo_cost_change = 11.2.0.1
#parallel_query_mode = enabled
#parallel_dml_mode = disabled
#parallel_ddl_mode = enabled
#optimizer_mode = all_rows
#sqlstat_enabled = false
#_optimizer_percent_parallel = 101
#_always_anti_join = choose
#_always_semi_join = choose
#_optimizer_mode_force = true
#_partition_view_enabled = true
#_always_star_transformation = false
#_query_rewrite_or_error = false
#_hash_join_enabled = true
#cursor_sharing = exact
#_b_tree_bitmap_plans = true
#star_transformation_enabled = false
#_optimizer_cost_model = choose
#_new_sort_cost_estimate = true
#_complex_view_merging = true
#_unnest_subquery = true
#_eliminate_common_subexpr = true
#_pred_move_around = true
#_convert_set_to_join = false
#_push_join_predicate = true
#_push_join_union_view = true
#_fast_full_scan_enabled = true
#_optim_enhance_nnull_detection = true
#_parallel_broadcast_enabled = true
#_px_broadcast_fudge_factor = 100
#_ordered_nested_loop = true
#_no_or_expansion = false
#optimizer_index_cost_adj = 100
#optimizer_index_caching = 0
#_system_index_caching = 0
#_disable_datalayer_sampling = false
#query_rewrite_enabled = true
#query_rewrite_integrity = enforced
#_query_cost_rewrite = true
#_query_rewrite_2 = true
#_query_rewrite_1 = true
#_query_rewrite_expression = true
#_query_rewrite_jgmigrate = true
#_query_rewrite_fpc = true
#_query_rewrite_drj = true
#_full_pwise_join_enabled = true
#_partial_pwise_join_enabled = true
#_left_nested_loops_random = true
#_improved_row_length_enabled = true
#_index_join_enabled = true
#_enable_type_dep_selectivity = true
#_improved_outerjoin_card = true
#_optimizer_adjust_for_nulls = true
#_optimizer_degree = 0
#_use_column_stats_for_function = true
#_subquery_pruning_enabled = true
#_subquery_pruning_mv_enabled = false
#_or_expand_nvl_predicate = true
#_like_with_bind_as_equality = false
#_table_scan_cost_plus_one = true
#_cost_equality_semi_join = true
#_default_non_equality_sel_check = true
#_new_initial_join_orders = true
#_oneside_colstat_for_equijoins = true
#_optim_peek_user_binds = true
#_minimal_stats_aggregation = true
#_force_temptables_for_gsets = false
#workarea_size_policy = auto
#_smm_auto_cost_enabled = true
#_gs_anti_semi_join_allowed = true
#_optim_new_default_join_sel = true
#optimizer_dynamic_sampling = 2
#_pre_rewrite_push_pred = true
#_optimizer_new_join_card_computation = true
#_union_rewrite_for_gs = yes_gset_mvs
#_generalized_pruning_enabled = true
#_optim_adjust_for_part_skews = true
#_force_datefold_trunc = false
#statistics_level = typical
#_optimizer_system_stats_usage = true
#skip_unusable_indexes = true
#_remove_aggr_subquery = true
#_optimizer_push_down_distinct = 0
#_dml_monitoring_enabled = true
#_optimizer_undo_changes = false
#_predicate_elimination_enabled = true
#_nested_loop_fudge = 100
#_project_view_columns = true
#_local_communication_costing_enabled = true
#_local_communication_ratio = 50
#_query_rewrite_vop_cleanup = true
#_slave_mapping_enabled = true
#_optimizer_cost_based_transformation = linear
#_optimizer_mjc_enabled = true
#_right_outer_hash_enable = true
#_spr_push_pred_refspr = true
#_optimizer_cache_stats = false
#_optimizer_cbqt_factor = 50
#_optimizer_squ_bottomup = true
#_fic_area_size = 131072
#_optimizer_skip_scan_enabled = true
#_optimizer_cost_filter_pred = false
#_optimizer_sortmerge_join_enabled = true
#_optimizer_join_sel_sanity_check = true
#_mmv_query_rewrite_enabled = true
#_bt_mmv_query_rewrite_enabled = true
#_add_stale_mv_to_dependency_list = true
#_distinct_view_unnesting = false
#_optimizer_dim_subq_join_sel = true
#_optimizer_disable_strans_sanity_checks = 0
#_optimizer_compute_index_stats = true
#_push_join_union_view2 = true
#_optimizer_ignore_hints = false
#_optimizer_random_plan = 0
#_query_rewrite_setopgrw_enable = true
#_optimizer_correct_sq_selectivity = true
#_disable_function_based_index = false
#_optimizer_join_order_control = 3
#_optimizer_cartesian_enabled = true
#_optimizer_starplan_enabled = true
#_extended_pruning_enabled = true
#_optimizer_push_pred_cost_based = true
#_optimizer_null_aware_antijoin = true
#_optimizer_extend_jppd_view_types = true
#_sql_model_unfold_forloops = run_time
#_enable_dml_lock_escalation = false
#_bloom_filter_enabled = true
#_update_bji_ipdml_enabled = 0
#_optimizer_extended_cursor_sharing = udo
#_dm_max_shared_pool_pct = 1
#_optimizer_cost_hjsmj_multimatch = true
#_optimizer_transitivity_retain = true
#_px_pwg_enabled = true
#optimizer_secure_view_merging = true
#_optimizer_join_elimination_enabled = true
#flashback_table_rpi = non_fbt
#_optimizer_cbqt_no_size_restriction = true
#_optimizer_enhanced_filter_push = true
#_optimizer_filter_pred_pullup = true
#_rowsrc_trace_level = 0
#_simple_view_merging = true
#_optimizer_rownum_pred_based_fkr = true
#_optimizer_better_inlist_costing = all
#_optimizer_self_induced_cache_cost = false
#_optimizer_min_cache_blocks = 10
#_optimizer_or_expansion = depth
#_optimizer_order_by_elimination_enabled = true
#_optimizer_outer_to_anti_enabled = true
#_selfjoin_mv_duplicates = true
#_dimension_skip_null = true
#_force_rewrite_enable = false
#_optimizer_star_tran_in_with_clause = true
#_optimizer_complex_pred_selectivity = true
#_optimizer_connect_by_cost_based = true
#_gby_hash_aggregation_enabled = true
#_globalindex_pnum_filter_enabled = true
#_px_minus_intersect = true
#_fix_control_key = 0
#_force_slave_mapping_intra_part_loads = false
#_force_tmp_segment_loads = false
#_query_mmvrewrite_maxpreds = 10
#_query_mmvrewrite_maxintervals = 5
#_query_mmvrewrite_maxinlists = 5
#_query_mmvrewrite_maxdmaps = 10
#_query_mmvrewrite_maxcmaps = 20
#_query_mmvrewrite_maxregperm = 512
#_query_mmvrewrite_maxmergedcmaps = 50
#_query_mmvrewrite_maxqryinlistvals = 500
#_disable_parallel_conventional_load = false
#_trace_virtual_columns = false
#_replace_virtual_columns = true
#_virtual_column_overload_allowed = true
#_kdt_buffering = true
#_first_k_rows_dynamic_proration = true
#_optimizer_sortmerge_join_inequality = true
#_optimizer_aw_stats_enabled = true
#_bloom_pruning_enabled = true
#result_cache_mode = MANUAL
#_px_ual_serial_input = true
#_optimizer_skip_scan_guess = false
#_enable_row_shipping = true
#_row_shipping_threshold = 80
#_row_shipping_explain = false
#transaction_isolation_level = read_commited
#_optimizer_distinct_elimination = true
#_optimizer_multi_level_push_pred = true
#_optimizer_group_by_placement = true
#_optimizer_rownum_bind_default = 10
#_enable_query_rewrite_on_remote_objs = true
#_optimizer_extended_cursor_sharing_rel = simple
#_optimizer_adaptive_cursor_sharing = true
#_direct_path_insert_features = 0
#_optimizer_improve_selectivity = true
#optimizer_use_pending_statistics = false
#_optimizer_enable_density_improvements = true
#_optimizer_aw_join_push_enabled = true
#_optimizer_connect_by_combine_sw = true
#_enable_pmo_ctas = 0
#_optimizer_native_full_outer_join = force
#_bloom_predicate_enabled = true
#_optimizer_enable_extended_stats = true
#_is_lock_table_for_ddl_wait_lock = 0
#_pivot_implementation_method = choose
#optimizer_capture_sql_plan_baselines = false
#optimizer_use_sql_plan_baselines = true
#_optimizer_star_trans_min_cost = 0
#_optimizer_star_trans_min_ratio = 0
#_with_subquery = OPTIMIZER
#_optimizer_fkr_index_cost_bias = 10
#_optimizer_use_subheap = true
#parallel_degree_policy = manual
#parallel_degree = 0
#parallel_min_time_threshold = 10
#_parallel_time_unit = 10
#_optimizer_or_expansion_subheap = true
#_optimizer_free_transformation_heap = true
#_optimizer_reuse_cost_annotations = true
#_result_cache_auto_size_threshold = 100
#_result_cache_auto_time_threshold = 1000
#_optimizer_nested_rollup_for_gset = 100
#_nlj_batching_enabled = 1
#parallel_query_default_dop = 0
#is_recur_flags = 0
#optimizer_use_invisible_indexes = false
#flashback_data_archive_internal_cursor = 0
#_optimizer_extended_stats_usage_control = 224
#_parallel_syspls_obey_force = true
#cell_offload_processing = true
#_rdbms_internal_fplib_enabled = false
#db_file_multiblock_read_count = 128
#_bloom_folding_enabled = true
#_mv_generalized_oj_refresh_opt = true
#cell_offload_compaction = ADAPTIVE
#parallel_degree_limit = 65535
#parallel_force_local = false
#parallel_max_degree = 16
#total_cpu_count = 8
#cell_offload_plan_display = AUTO
#_optimizer_coalesce_subqueries = true
#_optimizer_fast_pred_transitivity = true
#_optimizer_fast_access_pred_analysis = true
#_optimizer_unnest_disjunctive_subq = true
#_optimizer_unnest_corr_set_subq = true
#_optimizer_distinct_agg_transform = true
#_aggregation_optimization_settings = 0
#_optimizer_connect_by_elim_dups = true
#_optimizer_eliminate_filtering_join = true
#_connect_by_use_union_all = true
#dst_upgrade_insert_conv = true
#advanced_queuing_internal_cursor = 0
#_optimizer_unnest_all_subqueries = true
#_bloom_predicate_pushdown_to_storage = true
#_bloom_vector_elements = 0
#_bloom_pushing_max = 524288
#parallel_autodop = 0
#parallel_ddldml = 0
#_parallel_cluster_cache_policy = adaptive
#_parallel_scalability = 50
#iot_internal_cursor = 0
#_optimizer_instance_count = 0
#_optimizer_connect_by_cb_whr_only = false
#_suppress_scn_ch1k1_for_cqn = nosuppress_1466
#_optimizer_join_factorization = true
#_optimizer_use_cbqt_star_transformation = true
#_optimizer_table_expansion = true
#_and_pruning_enabled = true
#_deferred_constant_folding_mode = DEFAULT
#_optimizer_distinct_placement = true
#partition_pruning_internal_cursor = 0
#parallel_hinted = none
#_sql_compatibility = 0
#_optimizer_use_feedback = true
#_optimizer_try_st_before_jppd = true
#Bug Fix Control Environment
# fix 3834770 = 1
# fix 3746511 = enabled
# fix 4519016 = enabled
# fix 3118776 = enabled
# fix 4488689 = enabled
# fix 2194204 = disabled
# fix 2660592 = enabled
# fix 2320291 = enabled
# fix 2324795 = enabled
# fix 4308414 = enabled
# fix 3499674 = disabled
# fix 4569940 = enabled
# fix 4631959 = enabled
# fix 4519340 = enabled
# fix 4550003 = enabled
# fix 1403283 = enabled
# fix 4554846 = enabled
# fix 4602374 = enabled
# fix 4584065 = enabled
# fix 4545833 = enabled
# fix 4611850 = enabled
# fix 4663698 = enabled
# fix 4663804 = enabled
# fix 4666174 = enabled
# fix 4567767 = enabled
# fix 4556762 = 15
# fix 4728348 = enabled
# fix 4708389 = enabled
# fix 4175830 = enabled
# fix 4752814 = enabled
# fix 4583239 = enabled
# fix 4386734 = enabled
# fix 4887636 = enabled
# fix 4483240 = enabled
# fix 4872602 = disabled
# fix 4711525 = enabled
# fix 4545802 = enabled
# fix 4605810 = enabled
# fix 4704779 = enabled
# fix 4900129 = enabled
# fix 4924149 = enabled
# fix 4663702 = enabled
# fix 4878299 = enabled
# fix 4658342 = enabled
# fix 4881533 = enabled
# fix 4676955 = enabled
# fix 4273361 = enabled
# fix 4967068 = enabled
# fix 4969880 = disabled
# fix 5005866 = enabled
# fix 5015557 = enabled
# fix 4705343 = enabled
# fix 4904838 = enabled
# fix 4716096 = enabled
# fix 4483286 = disabled
# fix 4722900 = enabled
# fix 4615392 = enabled
# fix 5096560 = enabled
# fix 5029464 = enabled
# fix 4134994 = enabled
# fix 4904890 = enabled
# fix 5104624 = enabled
# fix 5014836 = enabled
# fix 4768040 = enabled
# fix 4600710 = enabled
# fix 5129233 = enabled
# fix 4595987 = enabled
# fix 4908162 = enabled
# fix 5139520 = enabled
# fix 5084239 = enabled
# fix 5143477 = disabled
# fix 2663857 = enabled
# fix 4717546 = enabled
# fix 5240264 = disabled
# fix 5099909 = enabled
# fix 5240607 = enabled
# fix 5195882 = enabled
# fix 5220356 = enabled
# fix 5263572 = enabled
# fix 5385629 = enabled
# fix 5302124 = enabled
# fix 5391942 = enabled
# fix 5384335 = enabled
# fix 5482831 = enabled
# fix 4158812 = enabled
# fix 5387148 = enabled
# fix 5383891 = enabled
# fix 5466973 = enabled
# fix 5396162 = enabled
# fix 5394888 = enabled
# fix 5395291 = enabled
# fix 5236908 = enabled
# fix 5509293 = enabled
# fix 5449488 = enabled
# fix 5567933 = enabled
# fix 5570494 = enabled
# fix 5288623 = enabled
# fix 5505995 = enabled
# fix 5505157 = enabled
# fix 5112460 = enabled
# fix 5554865 = enabled
# fix 5112260 = enabled
# fix 5112352 = enabled
# fix 5547058 = enabled
# fix 5618040 = enabled
# fix 5585313 = enabled
# fix 5547895 = enabled
# fix 5634346 = enabled
# fix 5620485 = enabled
# fix 5483301 = enabled
# fix 5657044 = enabled
# fix 5694984 = enabled
# fix 5868490 = enabled
# fix 5650477 = enabled
# fix 5611962 = enabled
# fix 4279274 = enabled
# fix 5741121 = enabled
# fix 5714944 = enabled
# fix 5391505 = enabled
# fix 5762598 = enabled
# fix 5578791 = enabled
# fix 5259048 = enabled
# fix 5882954 = enabled
# fix 2492766 = enabled
# fix 5707608 = enabled
# fix 5891471 = enabled
# fix 5884780 = enabled
# fix 5680702 = enabled
# fix 5371452 = enabled
# fix 5838613 = enabled
# fix 5949981 = enabled
# fix 5624216 = enabled
# fix 5741044 = enabled
# fix 5976822 = enabled
# fix 6006457 = enabled
# fix 5872956 = enabled
# fix 5923644 = enabled
# fix 5943234 = enabled
# fix 5844495 = enabled
# fix 4168080 = enabled
# fix 6020579 = enabled
# fix 5842686 = disabled
# fix 5996801 = enabled
# fix 5593639 = enabled
# fix 6133948 = enabled
# fix 3151991 = enabled
# fix 6146906 = enabled
# fix 6239909 = enabled
# fix 6267621 = enabled
# fix 5909305 = enabled
# fix 6279918 = enabled
# fix 6141818 = enabled
# fix 6151963 = enabled
# fix 6251917 = enabled
# fix 6282093 = enabled
# fix 6119510 = enabled
# fix 6119382 = enabled
# fix 3801750 = enabled
# fix 5705630 = disabled
# fix 5944076 = enabled
# fix 5406763 = enabled
# fix 6070954 = enabled
# fix 6282944 = enabled
# fix 6138746 = enabled
# fix 6082745 = enabled
# fix 3426050 = enabled
# fix 599680 = enabled
# fix 6062266 = enabled
# fix 6087237 = enabled
# fix 6122894 = enabled
# fix 6377505 = enabled
# fix 5893768 = enabled
# fix 6163564 = enabled
# fix 6073325 = enabled
# fix 6188881 = enabled
# fix 6007259 = enabled
# fix 6239971 = enabled
# fix 5284200 = disabled
# fix 6042205 = enabled
# fix 6051211 = enabled
# fix 6434668 = enabled
# fix 6438752 = enabled
# fix 5936366 = enabled
# fix 6439032 = enabled
# fix 6438892 = enabled
# fix 6006300 = enabled
# fix 5947231 = enabled
# fix 5416118 = 1
# fix 6365442 = 1
# fix 6239039 = enabled
# fix 6502845 = enabled
# fix 6913094 = enabled
# fix 6029469 = enabled
# fix 5919513 = enabled
# fix 6057611 = enabled
# fix 6469667 = enabled
# fix 6608941 = disabled
# fix 6368066 = enabled
# fix 6329318 = enabled
# fix 6656356 = enabled
# fix 4507997 = enabled
# fix 6671155 = enabled
# fix 6694548 = enabled
# fix 6688200 = enabled
# fix 6612471 = enabled
# fix 6708183 = disabled
# fix 6326934 = enabled
# fix 6520717 = disabled
# fix 6714199 = enabled
# fix 6681545 = enabled
# fix 6748058 = enabled
# fix 6167716 = enabled
# fix 6674254 = enabled
# fix 6468287 = enabled
# fix 6503543 = enabled
# fix 6808773 = disabled
# fix 6766962 = enabled
# fix 6120483 = enabled
# fix 6670551 = enabled
# fix 6771838 = enabled
# fix 6626018 = disabled
# fix 6530596 = enabled
# fix 6778642 = enabled
# fix 6699059 = enabled
# fix 6376551 = enabled
# fix 6429113 = enabled
# fix 6782437 = enabled
# fix 6776808 = enabled
# fix 6765823 = enabled
# fix 6768660 = enabled
# fix 6782665 = enabled
# fix 6610822 = enabled
# fix 6514189 = enabled
# fix 6818410 = enabled
# fix 6827696 = enabled
# fix 6773613 = enabled
# fix 5902962 = enabled
# fix 6956212 = enabled
# fix 3056297 = enabled
# fix 6440977 = disabled
# fix 6972291 = disabled
# fix 6904146 = enabled
# fix 6221403 = enabled
# fix 5475051 = enabled
# fix 6845871 = enabled
# fix 5468809 = enabled
# fix 6917633 = enabled
# fix 4444536 = disabled
# fix 6955210 = enabled
# fix 6994194 = enabled
# fix 6399597 = disabled
# fix 6951776 = enabled
# fix 5648287 = 3
# fix 6987082 = disabled
# fix 7132036 = enabled
# fix 6980350 = enabled
# fix 5199213 = enabled
# fix 7138405 = enabled
# fix 7148689 = enabled
# fix 6820988 = enabled
# fix 7032684 = enabled
# fix 6617866 = enabled
# fix 7155968 = enabled
# fix 7127980 = enabled
# fix 6982954 = enabled
# fix 7241819 = enabled
# fix 6897034 = enabled
# fix 7236148 = enabled
# fix 7298570 = enabled
# fix 7249095 = enabled
# fix 7314499 = enabled
# fix 7324224 = enabled
# fix 7289023 = enabled
# fix 7237571 = enabled
# fix 7116357 = enabled
# fix 7345484 = enabled
# fix 7375179 = enabled
# fix 6430500 = disabled
# fix 5897486 = enabled
# fix 6774209 = enabled
# fix 7306637 = enabled
# fix 6451322 = enabled
# fix 7208131 = enabled
# fix 7388652 = enabled
# fix 7127530 = enabled
# fix 6751206 = enabled
# fix 6669103 = enabled
# fix 7430474 = enabled
# fix 6990305 = enabled
# fix 7043307 = enabled
# fix 6921505 = enabled
# fix 7388457 = enabled
# fix 3120429 = enabled
# fix 7452823 = disabled
# fix 6838105 = enabled
# fix 6769711 = enabled
# fix 7170213 = enabled
# fix 6528872 = enabled
# fix 7295298 = enabled
# fix 5922070 = enabled
# fix 7259468 = enabled
# fix 6418552 = enabled
# fix 4619997 = enabled
# fix 7524366 = enabled
# fix 6942476 = enabled
# fix 6418771 = enabled
# fix 7375077 = enabled
# fix 5400639 = enabled
# fix 4570921 = enabled
# fix 7426911 = enabled
# fix 5099019 = disabled
# fix 7528216 = enabled
# fix 7521266 = enabled
# fix 7385140 = enabled
# fix 7576516 = enabled
# fix 7573526 = enabled
# fix 7576476 = enabled
# fix 7165898 = enabled
# fix 7263214 = enabled
# fix 3320140 = enabled
# fix 7555510 = enabled
# fix 7613118 = enabled
# fix 7597059 = enabled
# fix 7558911 = enabled
# fix 5520732 = enabled
# fix 7679490 = disabled
# fix 7449971 = enabled
# fix 3628118 = enabled
# fix 4370840 = enabled
# fix 7281191 = enabled
# fix 7519687 = enabled
# fix 5029592 = 3
# fix 6012093 = 1
# fix 6053861 = disabled
# fix 6941515 = disabled
# fix 7696414 = enabled
# fix 7272039 = enabled
# fix 7834811 = enabled
# fix 7640597 = enabled
# fix 7341616 = enabled
# fix 7168184 = enabled
# fix 399198 = enabled
# fix 7831070 = enabled
# fix 7676897 = disabled
# fix 7414637 = enabled
# fix 7585456 = enabled
# fix 8202421 = enabled
# fix 7658097 = disabled
# fix 8251486 = enabled
# fix 7132684 = enabled
# fix 7512227 = enabled
# fix 6972987 = enabled
# fix 7199035 = enabled
# fix 8243446 = enabled
# fix 7650462 = enabled
# fix 6720701 = enabled
# fix 7592673 = enabled
# fix 7718694 = enabled
# fix 7534027 = enabled
# fix 7708267 = enabled
# fix 5716785 = enabled
# fix 7356191 = enabled
# fix 7679161 = enabled
# fix 7597159 = enabled
# fix 7499258 = enabled
# fix 8328363 = enabled
# fix 7452863 = enabled
# fix 8284930 = enabled
# fix 7298626 = enabled
# fix 7657126 = enabled
# fix 8371884 = enabled
# fix 8318020 = enabled
# fix 8255423 = enabled
# fix 7135745 = enabled
# fix 8356253 = enabled
# fix 7534257 = enabled
# fix 8323407 = enabled
# fix 7539815 = enabled
# fix 8289316 = enabled
# fix 8447850 = enabled
# fix 7675944 = enabled
# fix 8355120 = enabled
# fix 7176746 = enabled
# fix 8442891 = enabled
# fix 8373261 = enabled
# fix 7679164 = enabled
# fix 7670533 = enabled
# fix 8408665 = enabled
# fix 8491399 = enabled
# fix 8348392 = enabled
# fix 8348585 = enabled
# fix 8508056 = enabled
# fix 8335178 = enabled
# fix 8515269 = enabled
# fix 8247017 = enabled
# fix 7325597 = enabled
# fix 8531490 = enabled
# fix 6163600 = enabled
# fix 8589278 = disabled
# fix 8557992 = enabled
# fix 7556098 = enabled
# fix 8580883 = enabled
# fix 5892599 = disabled
# fix 8609714 = enabled
# fix 8514561 = enabled
# fix 8619631 = disabled
#
#Query Block Registry:
#SEL$1 0x6e975eb0 (PARSER) [FINAL]
#
#:
# call(in-use=25024, alloc=82024), compile(in-use=102048, alloc=164272), execution(in-use=12872, alloc=16200)
#
#End of Optimizer State Dump
#Dumping Hints
#=============
注意，从上面的内容里我们可以看到实际执行计划、被窥视的绑定变量、能够复现该执行计划的 Hint 集合，
还有大量常规以及隐藏的优化器参数。和 Oracle Database 10.2.0.2 的输出对比后你会发现，这份输出里列出了更多服务器参数。下面是4 级 10046 跟踪的示例（在 Oracle 11.1.0.6 及更高版本上默认行为已经改变：不再需要在目标 SQL 之后额外再执行一条 SQL，才能把目标 SQL 的 STAT 行写入跟踪文件）：
#ALTER SESSION SET TRACEFILE_IDENTIFIER = '10046_EXECUTION_PLAN'; #ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; # #SELECT /* TEST */ # T3.C1, # SUBSTR(T1.C2,1,10) C2 #FROM # T3, # T1 #WHERE # T1.C1=T3.C1 # AND T1.C1 BETWEEN :N1 AND :N2; # #SELECT SYSDATE FROM DUAL;
最终生成的跟踪文件内容大致如下：
#===================== #PARSING IN CURSOR #6 len=118 dep=0 uid=68 oct=3 lid=68 tim=1264989049019258 hv=3172794915 ad='25b889600' sqlid='3g3fc5qyju0j3' #SELECT /* TEST */ # T3.C1, # SUBSTR(T1.C2,1,10) C2 #FROM # T3, # T1 #WHERE # T1.C1=T3.C1 # AND T1.C1 BETWEEN :N1 AND :N2 #END OF STMT #PARSE #6:c=0,e=165,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1264989049019257 #BINDS #6: # Bind#0 # oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 # oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0 # kxsbbbfp=2b7f6eb34ea8 bln=22 avl=02 flg=05 # value=1 # Bind#1 # oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 # oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24 # kxsbbbfp=2b7f6eb34ec0 bln=22 avl=02 flg=01 # value=10 #EXEC #6:c=999,e=1078,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=744086632,tim=1264989049020428 #FETCH #6:c=74988,e=75221,p=0,cr=37168,cu=0,mis=0,r=1,dep=0,og=1,plh=744086632,tim=1264989049095700 #FETCH #6:c=0,e=50,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,plh=744086632,tim=1264989049095954 #STAT #6 id=1 cnt=10 pid=0 pos=1 obj=0 op='FILTER (cr=37170 pr=0 pw=0 time=0 us)' #STAT #6 id=2 cnt=10 pid=1 pos=1 obj=0 op='HASH JOIN (cr=37170 pr=0 pw=0 time=0 us cost=10219 size=2394 card=9)' #STAT #6 id=3 cnt=10 pid=2 pos=1 obj=70288 op='TABLE ACCESS FULL T3 (cr=37164 pr=0 pw=0 time=0 us cost=10214 size=50 card=10)' #STAT #6 id=4 cnt=10 pid=2 pos=2 obj=70284 op='TABLE ACCESS BY INDEX ROWID T1 (cr=6 pr=0 pw=0 time=0 us cost=4 size=2610 card=10)' #STAT #6 id=5 cnt=10 pid=4 pos=1 obj=70285 op='INDEX RANGE SCAN SYS_C0017307 (cr=4 pr=0 pw=0 time=36 us cost=3 size=0 card=10)' #CLOSE #6:c=0,e=10,dep=0,type=0,tim=1264989049096413 #=====================
如果你把上面的输出和 Oracle 10.2.0.2 的对比，会发现跟踪文件中会写入这条 SQL 的SQL_ID，STAT 行里包含基数估算信息，并且游标关闭时会在跟踪文件中新增一行记录。
当然，我们可以直接从 10046 跟踪文件里读取真实执行计划、绑定变量的值以及数据类型。不过这里我们改用 TKPROF 工具处理（使用 TKPROF 时将看不到绑定变量的值）。
> tkprof or112_ora_5062_10046_EXECUTION_PLAN.trc or112_ora_5062_10046_EXECUTION_PLAN.txt
输出的结果如下：
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.07 0.07 0 37170 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.07 0.07 0 37170 0 10 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 68 Rows Row Source Operation ------- --------------------------------------------------- 10 FILTER (cr=37170 pr=0 pw=0 time=0 us) 10 HASH JOIN (cr=37170 pr=0 pw=0 time=0 us cost=10219 size=2394 card=9) 10 TABLE ACCESS FULL T3 (cr=37164 pr=0 pw=0 time=0 us cost=10214 size=50 card=10) 10 TABLE ACCESS BY INDEX ROWID T1 (cr=6 pr=0 pw=0 time=0 us cost=4 size=2610 card=10) 10 INDEX RANGE SCAN SYS_C0017307 (cr=4 pr=0 pw=0 time=36 us cost=3 size=0 card=10)(object id 70285)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3187583/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
没有了~
请登录后发表评论
登录
全部评论
最新文章
- 第87期 Oracle执行计划，彻底搞懂Oracle的执行计划 II
- 第86期 Oracle执行计划，彻底搞懂Oracle的执行计划
- 第85期 为什么直方图和绑定变量一起使用时，有时会引发性能问题
- 第84期 查询RAC之间私网的流量信息，监控 RAC 私网流量变化
- 第83期 MySQL JSON Duality View + HeatWave Vector Search for RAG
- 第82期 Oracle 19c RAC集群：如何在线迁移/替换 OCR 磁盘组
- 第81期 批量抓取数据库中耗时最高的SQL自动生成【Active HTML格式的SQL Monitor报告】
- 第80期 追踪 SQL 性能随时间变化、执行计划是否漂移、资源消耗变化
- 第79期 Oracle Buffer Busy Waits 等待事件 P3 参数详解
- 第78期 Oracle Buffer Busy Waits等待事件深入解读
