甜橘子，专注成都网站制作网站设计与营销型网站建设与优化
QQ :532337155
电话：028-86922220
select t1.v1, t2.v1 From t1, t2 where t1.n2= :b1 And t2.id= t1.id And t2.n2 between :b2 and :b3;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 832 | 46 (3)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 32 | 832 | 46 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 32 | 416 | 24 (5)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 500 | 6500 | 22 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_N2 | 45 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
2 - access("T2"."ID"="T1"."ID")
3 - filter("T1"."N2"=TO_NUMBER(:B1))
5 - access("T2"."N2">=TO_NUMBER(:B2) AND "T2"."N2"<=TO_NUMBER(:B3))
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 33 (100)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 17 | 442 | 33 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 17 | 442 | 33 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 221 | 16 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N2 | 17 | | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| |
|* 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B3>=:B2)
5 - access("T1"."N2"=:B1)
6 - access("T2"."ID"="T1"."ID")
7 - filter(("T2"."N2">=:B2 AND "T2"."N2"<=:B3))
这两个执行计划明显不同 ——即使我是在相同的会话里分别产生的。 主要的不同体现在两方面； 第一，计划主体行数不同，一个 6行，另一个有 8行； 另一个就是 Predicate Information(谓词信息 )，一个明确显示了所有绑定变量的强制转换 (e.g. to_number(:B3))，另一个并没有这种信息。 如果我们要根据执行计划来帮助我们提高系统的效率，我们必须知道为什么会有这种自相矛盾现象发生，并且需要确定可以多大程度上相信 Oracle给我们的执行计划。 接下来一起看看常见的获取执行计划的方法以及每种方法的局限性。
成都创新互联是一家集网站建设,徐汇企业网站建设,徐汇品牌网站建设,网站定制,徐汇网站建设报价,网络营销,网络优化,徐汇网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
获取执行计划
即使有许多可以通过图形化界面生成执行计划的工具，我更偏向于在 SQL命令行使用 Oracle的 dbms_xplan包。 不论你使用什么工具，得到的信息都是几乎一样的，但是如果你需要在不同的论坛或者会议上分享你的执行计划的话，那么通过 dbms_xplan生成的执行计划的格式是大部分人所熟悉和接受的。
解释计划
SQL*Plus 会话 :
explain plan for (your select statement) select * from table(dbms_xplan.display);
这是最简单也是最基本的方法获取到当你执行语句时， Oracle"预测 "的可能会采用的执行计划； 上述第一种执行计划就是通过这种方式得到的。 这种获取的方法存在一些问题，一些明显的问题上面已经阐述了，下面说些更深层的原因。
第一点，如果你的查询包含绑定变量时 (类似上面的例子 )， "explain plan"的特点是不知道绑定变量的数据类型。 它假设它们是 char类型的，所以我第一个执行计划中的 Predicate Information会显示强制转换 to_number()，这会使优化器不去考虑本来可以使用的索引，从而导致执行计划的巨大差距。
第二点， Oracle采用绑定变量窥探许多年了，当一条语句第一次被优化时，会获取到绑定变量真实的值。 但是 "explain plan"并不会去尝试； 它不会去获取真实的值。 它只会使用几种基本的规则去预估谓词中涉及到的绑定变量的选择性。 有一些规则是比较正确的，但是有些就是纯粹的猜测 ——通常用 1%或 5%作为选择性。 对选择性的错误预估会导致对于基数的错误语句最终导致错误的执行计划。
附注 :第一个执行计划就是猜测产生自相矛盾的例子 :在第 4行和第 5行， index range scan预估会有 45个 rowid会被获取到，但是 tableaccess预估返回 500行数据， 45个 rowid不可能对应 500行数据。 这是索引基于 range_based的预测，并使用了最小的选择性 0.45%，但是同等情况下表的最小选择性为 5%，所以导致了这种情况的发生。
还有更多的关于" explain plan "的细节你可能会碰到。调用时有一些额外的选项，在官方手册中记录如下 :
explain plan
set statement_id = ‘{string}’
into {schema}.{table}@{db_link}
for {statement};
statement_id默认为空，目标 table就是 plan_table(在新的版本中是全局临时表 sys.plan_table$的同义词 )。 可以使用 table和 statement_id参数来指定想要的语句的执行计划的输出，函数的声明如下：
dbms_xplan.display({plan_table},{statement_id},{formatoptions},{filter option})如果无参数调用 dbms_xplan或者将前两个参数赋值为 NULL，则输出最近解释的语句。 就输出格式选项而言， plan table里有许多信息可供选择显示，我们会在后续的章节里介绍。 filter选项允许你限制 plan_table返回的行 —这几乎用不到。
Autotrace
这是嵌入到 SQL*PLUS 里的 "explain plan" 的特殊变体，可以使用 set 命令使 autotrace 选项生效：
set autotrace on set autotrace traceonly set autotrace traceonly explain set autotrace traceonly statistics set autotrace off
在 SQL*PLUS中开启 autotrace后，可以输出执行的任意 SQL语句的执行计划和执行统计信息。 你可以限制只输出执行计划，只输出执行统计信息，或者全部输出，你还可以不输出语句的执行结果 (使用 traceonly选项 )。 下面是一个我使用 set autotrace to traceonly statistics的输出例子：
1 row selected. Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 36 consistent gets 0 physical reads 0 redo size 471 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
这对于只想知道执行一条语句 Oracle 做了多少工作量来说十分方便，因为我不需要获取结果集，或者将结果集存储在客户端。
对于获取真实的执行计划来说， autotrace依然做不到，它只是简单的在后台执行 "explain plan"并且调用 "dbms_xplan.display"； 此外，如果你设置 "setautotrace traceonly explain"并且执行一条查询语句，该选项因为并不返回真实的结果，所以 Oracle根本不会执行这条语句。 但是如果是 insert、 update、 delete或者 merge语句，会真实执行，并输出影响的行数，提醒你是否需要回滚。
Dbms_xplan.display_cursor()
这是文章中唯一提及的，可以在语句执行后从内存中获取真实执行计划的函数的选项。 该函数的定义如下：
dbms_xplan.display_cursor({sql_id},{child_number},{formatoptions})如果不带参数调用，则会返回最近一次执行的 SQL 执行计划。有许多原因会导致获取不到执行计划，有可能是游标不可用了，不过最常见的原因是没有设置 "set serveroutput off", 这个获取失败的执行计划是针对跟在执行的语句后的对 dbms_output(BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;) 的调用，可以看到如下信息 :
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- SQL_ID b3s1x9zqrvzvc, child number 0 BEGIN DBMS_OUTPUT.ENABLE(1000000); END; NOTE: cannot fetch plan for SQL_ID: b3s1x9zqrvzvc, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 8 rows selected.
我调用 dbms_xplan.display_cursor()来获得上述的第二个真实的执行计划。 这是 Oracle根据我绑定变量的值、涉及对象的统计信息以及会话的环境所真正执行的路径。 但是它依然只是返回优化器每一步预估的返回的行数，并不是在执行时真正获取的行数。 我们会在接下来的话题中继续讨论。
虽然还有许多关于 dbms_xplan.display_cursor要说的，也有很多使用它的方式。 但是介绍先到此，并且有个小提醒。 虽然它会根据你提供的绑定变量输出真正的执行计划 (大部分情况 )，但是不同环境下不能保证是一致的，或者同样的环境下始终都是一样的执行计划。
如果不了解最后的用户做了什么，就会有很多原因导致你被执行计划欺骗。 在生产系统中，最常见的包括 :
• 真实的绑定变量值
• 优化器环境和对象统计信息
• 名称解析
虽然 "dbms_xplan.display_cursor()" 的调用结果相对于调用 "explain plan" 以及 "dbms_xplan.display()" 的结果来说会真实很多，但是如果你想确保没有获取到错误的执行计划，仍然需要一些明智的判断。
结论
通过这篇文章我们了解了获取执行计划其实非常简单，但是计划会有两种类别 —预测的和真实的。 也了解到如果语句中存在绑定变量的话，预测的执行计划更倾向于是一个错误的执行计划。
一种普遍且相当准确的观点认为，在生产库上的执行计划会与在自己环境执行语句后获取的真正的执行计划一致，但这只能取决于你的环境是否与生产库最后用户执行该语句时的环境十分相似。
我们从真实的执行计划中获取到的关于 " 体积 "(rows ， bytes) 的信息仍然是通过预估得出的，下一章节我们会获取到真实的 " 体积 " 数据，这也会帮助我们判断为什么优化器的选择与我们预期的不符。
原文链接： https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/
原文作者： Jonathan Lewis
| 译者简介