执行计划-1：获取执行计划
selectt1.v1,t2.v1Fromt1,t2wheret1.n2=:b1Andt2.id=t1.idAndt2.n2between:b2and:b3;这是简单的两表关联；单列关联条件，有两个单表谓词过滤数据。当我们查看这个语句时，我们希望能回答以下的问题-这些问题不一定互相独立：• Oracle会先访问哪张表?• Oracle怎么访问那张表，通过索引或者表扫描?•它能获取多少数据？•接下来会以何种方式访问哪张表？• Oracle会用哪种方式连接两张表？•通过连接列谓词条件会生成多少数据？•在连接后应用谓词会有多少数据被过滤？• Oracle的预测以及真正执行结果会有显著的区别吗？•我们能不能快速确定为什么会发生(上一条内容)，以及有何影响？• Oracle的执行策略符合我们的预期吗？•我们可以找出为什么Oracle不执行我们认为更好的策略的原因吗？这个例子中，我们假设Oracle利用n2上面的索引找到t1中的一小部分的行，接下来使用nested loop跟t2关联，通过t2上的主键索引与t1中的过滤后的每行数据进行匹配，获取匹配成功的行，最后丢弃大部分不符合(t2.n2between :b2 and :b3)条件的行。这里有两个关于这个查询的执行计划，在我们开始解释执行计划前需要了解一些我们必须考虑的问题。这些计划来自11.2.0.4的数据库环境。预测的执行计划
---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||32|832|46(3)|00:00:01||*1|FILTER|||||||*2|HASHJOIN||32|832|46(3)|00:00:01||*3|TABLEACCESSFULL|T1|32|416|24(5)|00:00:01||4|TABLEACCESSBYINDEXROWID|T2|500|6500|22(0)|00:00:01||*5|INDEXRANGESCAN|T2_N2|45||2(0)|00:00:01|---------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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|SELECTSTATEMENT||||33(100)|||*1|FILTER|||||||2|NESTEDLOOPS||17|442|33(0)|00:00:01||3|NESTEDLOOPS||17|442|33(0)|00:00:01||4|TABLEACCESSBYINDEXROWID|T1|17|221|16(0)|00:00:01||*5|INDEXRANGESCAN|T1_N2|17||1(0)|00:00:01||*6|INDEXUNIQUESCAN|T2_PK|1||0(0)|||*7|TABLEACCESSBYINDEXROWID|T2|1|13|1(0)|00:00:01|----------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter(:B3>=:B2)5-access("T1"."N2"=:B1)6-access("T2"."ID"="T1"."ID")7-filter(("T2"."N2">=:B2AND"T2"."N2"<=:B3))
这两个执行计划明显不同——即使我是在相同的会话里分别产生的。主要的不同体现在两方面；第一，计划主体行数不同，一个6行，另一个有8行；另一个就是Predicate Information(谓词信息)，一个明确显示了所有绑定变量的强制转换(e.g. to_number(:B3))，另一个并没有这种信息。如果我们要根据执行计划来帮助我们提高系统的效率，我们必须知道为什么会有这种自相矛盾现象发生，并且需要确定可以多大程度上相信Oracle给我们的执行计划。接下来一起看看常见的获取执行计划的方法以及每种方法的局限性。
获取执行计划
即使有许多可以通过图形化界面生成执行计划的工具，我更偏向于在SQL命令行使用Oracle的dbms_xplan包。不论你使用什么工具，得到的信息都是几乎一样的，但是如果你需要在不同的论坛或者会议上分享你的执行计划的话，那么通过dbms_xplan生成的执行计划的格式是大部分人所熟悉和接受的。
解释计划
SQL*Plus会话:
explainplanfor(yourselectstatement)select*fromtable(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"的细节你可能会碰到。调用时有一些额外的选项，在官方手册中记录如下:
explainplansetstatement_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},{filteroption})
如果无参数调用dbms_xplan或者将前两个参数赋值为NULL，则输出最近解释的语句。就输出格式选项而言，plan table里有许多信息可供选择显示，我们会在后续的章节里介绍。filter选项允许你限制plan_table返回的行—这几乎用不到。
Autotrace
这是嵌入到SQL*PLUS里的"explain plan"的特殊变体，可以使用set命令使autotrace选项生效：
setautotraceonsetautotracetraceonlysetautotracetraceonlyexplainsetautotracetraceonlystatisticssetautotraceoff
在SQL*PLUS中开启autotrace后，可以输出执行的任意SQL语句的执行计划和执行统计信息。你可以限制只输出执行计划，只输出执行统计信息，或者全部输出，你还可以不输出语句的执行结果(使用traceonly选项)。下面是一个我使用set autotrace to traceonly statistics的输出例子：
1rowselected.Statistics----------------------------------------------------------1recursivecalls0dbblockgets36consistentgets0physicalreads0redosize471bytessentviaSQL*Nettoclient415bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed
这对于只想知道执行一条语句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*fromtable(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_IDb3s1x9zqrvzvc,childnumber0BEGINDBMS_OUTPUT.ENABLE(1000000);END;NOTE:cannotfetchplanforSQL_ID:b3s1x9zqrvzvc,CHILD_NUMBER:0PleaseverifyvalueofSQL_IDandCHILD_NUMBER;Itcouldalsobethattheplanisnolongerincursorcache(checkv$sql_plan)8rowsselected.
我调用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
| 译者简介
林锦森·沃趣科技数据库技术专家沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
