💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨
💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️
💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖
SQL优化续新篇，第二篇章启幕时。
优化器内藏奥秘，解析SQL步步细。
执行计划透玄机，性能调优必知悉。
性能瓶颈何处觅？且看此文析微理。（博主没有这么好的才华，感谢文心一言的生成😁）
最近爆火的3A游戏《黑神话：悟空》不知道各位大佬们有没有玩，博主是一个3A游戏爱好者，所以也是再第一时间购入了这款游戏，不得不说游戏的画质、剧情、特效可以算的上顶尖了，没想到“全村第一个大学生”这么惊艳，但是也有点小瑕疵，感觉用手柄连接电脑玩的时候有点小小的延迟，希望官网在后期能给优化下。悟空不仅在国内市场好评如潮，也得到了海外市场的认可，在3A游戏领域也算是扬眉吐气一次了（在《黑神话：悟空》出来之前中国没有3A题材的游戏，在3A单机游戏市场中都被日本、欧美长期垄断），也希望国内可以产出越来越多优秀的3A游戏，不过游戏是给人放松的，千万不要沉迷进去哦！
那么回归正题，今天下午难得清闲，有空给大家分享一下SQL优化相关的系列第二篇——全面理解优化器和SQL语句的解析步骤(含执行计划分析)，对于这篇文章而言也是非常非常非常的（重要的事情说三篇），因为这篇文章介绍了对SQL执行计划的分析，所以各位愿意深入研究性能优化的小伙伴们可以打起120分的精神。
还是老规矩为了让大家更容易消化和逐个理解，我将分成七篇文章来进行介绍，以便大家劳逸结合而不至于感觉到阅读枯燥，七篇的内容分别如下：
- 第一篇：统计信息和动态采样的深度剖析
- 第二篇：全面理解优化器和SQL语句的解析步骤(含执行计划分析)（当前篇）
- 第三篇：SQL执行计划之访问路径
- 第四篇：SQL执行计划之多表连接
- 第五篇：精细化查询优化：如何有效使用Hint干预执行计划
- 第六篇：掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南
- 第七篇：SQL性能优化实战案例(从15秒优化到0.08秒)
目录
SQL解析之阶段二：Semantic Check（语义检查）
4.1.1 查看SQL执行计划方式一：SQLPLUS AUTOTRACE（推荐，但执行计划不真实，并且需要再执行一遍相关SQL）
4.1.2 查看SQL执行计划方式二：Explain Plan For+SQL（执行计划不真实。需要再执行一遍相关SQL）
4.1.3 查看SQL执行计划方式三：DBMS_XPLAN包（执行计划真实）
dbms_xplan.DISPLAY使用案例（默认获取explain plan for最后语句的执行计划，需要再执行一遍相关SQL）：
dbms_xplan.DISPLAY_AWR使用案例（sql语句需要采集到awr才能查看到执行计划，不需要再执行相关SQL）：
4、步骤四：SQL Execution（执行SQL语句，返回结果execute and return）
一、优化器
在Oracle中，优化器（optimizer）是数据库中可谓最核心的部分，主要是因为 优化器负责解析SQL，因此优化器的性能直接决定数据库的强弱 。想要做好SQL优化，就需要了解优化器，这是基础。
优化器是内置的数据库软件，它确定SQL语句访问请求数据的最有效方法。优化器试图为SQL语句生成最佳执行计划。优化器在所有考虑的候选计划中选择成本最低的计划。优化器使用可用的统计数据来计算成本。对于给定环境中的特定查询，成本计算考虑了查询执行的因素，如I/O、CPU和通信。
例如，查询可能会请求有关担任经理的员工的信息。如果优化器的统计数据表明80%的员工是经理，那么优化器可能会决定全表扫描是最有效的。然而，如果统计数据表明很少有员工是经理，那么读取索引并按rowid访问表可能比全表扫描更有效。
由于数据库有许多内部统计数据和工具可供使用，优化器通常比用户更能确定语句执行的最佳方法。因此，所有SQL语句都使用优化器。
优化器是按照一定的判断原则来得到它认为的目标SQL在当前情形下最高效的执行路径（Access Path）,优化器的目的就是为了得到目标SQL的执行计划 。优化器决定oracle以什么样的方式来访问数据，是全表扫描（full table scan），索引范围扫描（index range scan）还是全索引快速扫描（index fast full scan : indexffs）。
对于表关联查询，它负责确定表之间以一种什么方式来关联，比如hash_john还是嵌套关联(nested loops)或者合并关联(merge join)，这些因素直接决定sql的执行效率，所以优化器是sql执行的核心，它做出的执行计划好坏，直接决定着sql的执行效率。优化器组件：
oracle的优化器分为两类：
rbo(rule-based optimization)：基于规则的优化器（9i）
Rbo优化器在分析SQL语句时，所遵循的是Oracle内部预定的一些规则。例如：当一个where子句中的一列有索引时去走索引
cbo(cost-based optimization)：基于代价的优化器（10g之后）
CBO优化器是看语句的代价(Cost)，主要指Cpu和内存，优化器在判断是否用这种方式时主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现，有时过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。
注：从 10g开始rbo已经被弃用，但可以通过hint方式来使用它。走索引不一定就是优的，比如一个表只有两行数据，一次io就可以完成全表的检索，而此时走索引时则需要两次io，这时全表扫描(full table scan)是最好。
优化器的优化模式：
sql> show parameter optimizer_mode ###oracle使用optimizer_mode参数来控制优化器 first_rows --cbo first_rows_n --cbo all_rows --cbo rule --rbo Choose --rbo一、CHOOSE：这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。
如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。
如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。
二、ALL_ROWS：优化器将寻找能够在最短的时间内完成语句的执行计划。不管是不是有统计信息，全部采用基于成本的优化方法CBO。
设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。
三、FIRST_ROWS：CBO模式，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。
在oracle 9i之后这一选项已经过时，出于向后兼容的目的保留了这一选项，该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。这一规则倾向于促使优化器使用索引访问路径，偶尔会出现非常不恰当的访问路径。
设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以first_rows 优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP系统，满足用户能够在较短时间内看到较小查询结果集的要求。
四、FIRST_ROWS_N：不管是不是有统计信息，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录。
N的值可以为1，10，100，1000，优化器首先通过彻底分析第一个连接顺序来估计返回行的总数目。这样就可以知道查询可能获得的整个数据集的片段，并重新启动整个优化过程，其目标在于找到能够以最小的资源消耗返回整个数据片段的执行计划。
Oracle 9i 对一些预期返回结果集的数据量小的SQL语句优化模式进行了加强，增加了四个参数值：first_rows_1、first_rows_10、first_rows_100、first_rows_1000。CBO通过first_rows_n中的 n 值，决定了返回结果集数量的基数，我们可能仅仅需要查询结果集中的一部分，CBO就根据这样的n 值来决定是否使用索引扫描。
五、RULE：这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则rbo的优化方法。
基于规则的优化器模式，RBO，是早期Oracle版本使用过的一种优化模式。由于RBO不支持自1994年Oracle版本的新特性，如bitmap indexes，table partitions，function-based indexes等，所以在以后Oracle版本中已经不再更新RBO，并且也不推荐用户使用RBO这种优化模式了。
二、SQL语句的解析步骤
在数据库中执行一条SQL是有一套执行标准的，先干什么后干什么都被安排的明明白白，下图描述了SQL处理的阶段。
此图显示了SQL处理的各个阶段，表示为五个由向下箭头链接的垂直框：SQL语句、解析、优化、行源生成和执行。解析框标有“语法检查，语义检查”。优化框标有“生成多个执行计划”。行源生成框标有“执行计划生成”。标记为“软解析”的箭头从解析框延伸到执行框。
1、步骤一：SQL Parsing（SQL解析）
SQL处理的第一阶段是解析。解析阶段涉及将SQL语句的片段分离为其他例程可以处理的数据结构。当应用程序发出指令时，数据库会解析语句，这意味着只有应用程序而不是数据库本身可以减少解析次数。
当应用程序发出SQL语句时，应用程序会对数据库进行解析调用，以准备执行该语句。解析调用打开或创建一个游标，游标是会话特定私有SQL区域的句柄，其中包含解析的SQL语句和其他处理信息。游标和私有SQL区域位于程序全局区域（PGA）中。
在解析调用期间，数据库执行检查，以识别在语句执行之前可以发现的错误。解析无法捕获某些错误。例如，数据库只有在语句执行期间才会在数据转换中遇到死锁或错误。
SQL解析之阶段一：Syntax Check（语法检查）
Oracle数据库必须检查每个SQL语句的语法有效性。违反格式良好的SQL语法规则的语句未通过检查。例如，以下语句失败，因为关键字FROM拼写错误为FOR
SQL> SELECT * FORM employees; SELECT * FORM employees * ERROR at line 1: ORA-00923: FROM keyword not found where expected
SQL解析之阶段二：Semantic Check（语义检查）
语句的语义就是它的意义。语义检查确定语句是否有意义，例如，语句中的对象和列是否存在。语法正确的语句可能无法通过语义检查，如下面查询不存在的表的示例所示：
SQL> SELECT * FROM nonexistent_table; SELECT * FROM nonexistent_table * ERROR at line 1: ORA-00942: table or view does not exist
SQL解析之阶段三：Shared Pool Check（共享池检查，只针对DML语句）
此图显示了共享池检查。顶部是三个相互叠放的盒子，每个盒子都比后面的盒子小。最小的盒子显示哈希值，并标记为共享SQL区域。第二个框标记为共享池。外箱标有SGA。此框下方是另一个标记为PGA的框。PGA框内是一个标记有Private SQL Area的框，其中包含一个哈希值。一个双头箭头连接上下框，并标记为“哈希值比较”。PGA框右侧是一个标记为“用户进程”的人图标。图标由双面箭头连接。用户进程图标上方是“更新…”语句。箭头从下面的用户进程指向，因此下面的服务器进程图标。
检查此sql是否被当前用户使用过，如果是就是软解析soft parse，如果否那就是硬解析。DDL总是硬解析，语句从不重用。
为了开始这个处理Oacle必须在Shared pool中寻找语句，Shared pool是SGA中的一部分用来缓存以前执行过的sql语句、PLSQL、数据字典内容的缓存以及其他许多信息，以供会话重用。为了高效完成此操作，查找Shared pool中是否有相同的语句。有进行软解析跳过4和5；没有进行硬解析开始4和5。
根据提交的语句类型和哈希检查的结果，解析操作分为以下几类：
1. 软解析：检查shared pool中是否有之前解析相同的SQL语句后所存储的SQL文本、解析树和执行计划。
如果能从共享池的缓存库中找到之前解析过生成的执行计划，则SQL语句则不需要再次解析，便可以直接由库缓存得到之前所产生的执行计划，从而直接跳到绑定或执行阶段，这种解析称作软解析。
1：语法分析
2：权限与对象检查
3：在共享池中检查是否有完全相同的之前完全解析好的.
如果存在，直接跳过4和5，运行sql，此时算soft parse
2. 硬解析：但是如果在shared pool的库缓存中找不到对应的执行计划，则必须继续解析SQL、生成执行计划，这种解析称作硬解析。就是上面提到的对提交的sql完全重新从头进行解析（当在shared pool中找不到时候将会进行此操作），总共有一下5个执行步骤：
1：语法分析
2：权限与对象检查
3：在共享池中检查是否有完全相同的之前完全解析好的，不存在进行4和5
4：选择执行计划，生成解析树(parse tree)
5：产生执行计划
注：创建变量解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。要在程序中多使用绑定的原因。
例如：修改操作（INSERT、UPDATE、DELETE）
SQL> UPDATE itpux_yg SET salary = 1.5 * salary WHERE name = :v_name;v_name是程序变量，里面员工名，我们要修改该员工的工资。当这个SQL语句执行时，使用该变量的值，那么进行软解析soft parse
SQL> UPDATE itpux_yg SET salary = 1.5 * salary WHERE name =’itpux01’; SQL> UPDATE itpux_yg SET salary = 1.5 * salary WHERE name =’itpux02’;这两个虽然SQL前面一样值不一样，那么进行硬解析hard parse
总结：在优化中应用程序尽量绑定变量，会进行软解析，就会减少系统的资源使用
案例一：了解SQL执行过程，模拟软解析和硬解析
1）创建测试表
SQL> create user itpux1 identified by itpux1; create user itpux2 identified by itpux2; grant dba to itpux1; grant dba to itpux2;2）启用一个会话
SQL> conn itpux1/itpux1 create table itpux1 as select * from dba_data_files; select * from itpux1;3）启用另一个会话
SQL> conn itpux2/itpux2 create table itpux1 as select * from dba_data_files; select * from itpux1; select * from itpux1;4）启用另一会话，使用 sys 用户连接，进行如下查询
SQL> select address,hash_value, executions, sql_text from v$sql where lower(sql_text) like 'select * from itpux1';可见虽然发出的语句是一样的，但是语义不同，所以v$sql中会有两条记录。不同的用户执行一样的语句，会进行硬解析。tpux2用户下相同的语句执行了两次，因为语义相同，所以是一条记录两次执行，itpux2就是软解析
5）查询 v$sql_shared_cursor视图得知为何同语句不同的对象执行时不能共享，走的是硬解析hard soft
SQL> select * from v$sql_shared_cursor where address in ( select address from v$sql where lower(sql_text) like 'select * from itpux1')在视图找出：
auth check mismatch（身份验证检查不匹配）：语句相同但执行对象不一样
auth check mismatch（翻译不匹配）：
同一条语句不同用户执行，在v$sql_shared_cursor视图中auth check mismatch和auth check mismatch的值不一样，所以进行的是硬解析hard soft
2、步骤二：SQL Optimization（SQL优化）
在优化过程中，Oracle数据库必须对每个唯一的DML语句至少执行一次硬解析，并在此解析过程中执行优化。数据库未优化DDL。唯一的例外是当DDL包含需要优化的DML组件（如子查询）时。
3、步骤三：SQL Row Source Generation（SQL行来源生成）：对sql语句进行解析(prase)，利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)
行源生成器是从优化器接收最佳执行计划并生成可供数据库其余部分使用的迭代执行计划的软件。
迭代计划是一个二进制程序，当由SQL引擎执行时，会产生结果集。该计划采取了一系列步骤的组合形式。每一步返回一个行集。下一步使用此集中的行，或者最后一步将行返回给发出SQL语句的应用程序。
行源是由执行计划中的步骤返回的行集，以及可以迭代处理行的控制结构。行源可以是表、视图或联接或分组操作的结果。
行源生成器生成一个行源树，它是行源的集合。行源树显示以下信息：
- 语句引用的表的排序
- 语句中提到的每个表的访问方法
- 语句中受联接操作影响的表的联接方法
- 数据操作，如筛选、排序或聚合
SQL行来源生成之执行计划
SQL的执行计划实际代表了目标SQL在数据库内部的具体执行步骤。执行计划贯穿Oracle调优始终，进行调优只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向，了解执行计划的真实执行过程将有助于SQL优化。
什么是SQL Execution Plan执行计划：
SQL是声明型语言，她只说我要去哪里，但很少告诉你到底如何去
SQL语句的执行最终会落实为Oracle执行步骤的组合=>【SQL执行计划】
为什么要分析执行计划：
如果一条sql平时执行很快，突然有一天性能很差，排除了系统资源和阻塞的原因，那么基本可以断定是sql执行计划出了问题，那么就要对sql进行性能分析
查看执行计划的方法：
SQLPLUS AUTOTRACE ---自动跟踪某条SQL最简单的方法，但计划执行不真实
Explain Plan For SQL(VSSQL和VSSQL_PLAN) ---对于很长时间不能返回结果的sql使用这种方法，但计划执行不真实使用
DBMS_XPLAN包 ---查看某条SQL多条执行计划，计划执行真实
statisticslevel=all; ---计划执行真实，可以看到表被访问的次数
sql trace与10046 ---计划执行真实
PL/SQLDev,Toad ---调用的就是Explain Plan For SQL
Enterprise Manager ---可以图形化显示执行计划
AWR执行计划报告 ---查看某条SQL多条执行计划
4.1.1 查看SQL执行计划方式一：SQLPLUS AUTOTRACE（推荐，但执行计划不真实，并且需要再执行一遍相关SQL）
AUTOTRACE语法：
SYS@orcl1> set autotrace; set autotrace off ---默认为off，不生成atuotrace报告（执行计划和统计信息） set autotrace on ---包括执行计划和统计信息 set autotrace traceonly ---同set autotrace on，但是不显示查询输出。 set autotrace on explain ---只显示执行路径 set autotrace statistics ---只显示执行计划的统计信息 set autotrace traceonly explain ---只显示执行路径，但是不显示查询输出。 set autotrace traceonly statistics ---只显示执行计划的统计信息，但是不显示查询输 出。
案例一：对执行计划的执行顺序进行分析
ITPUX@orcl1> set line 400; ITPUX@orcl1> set autotrace on; ITPUX@orcl1> select * from tab where rownum<=2; ---tab类似于user_tables（tabs）SQL语句的执行计划和统计信息分析
一条sql中的执行计划中有多条操作，他的操作并不是从0一直到11按顺序执行的。
从开头看到最右边，如果操作并行从上向看下看，然后执行父系操作。同父系下还有操作时先执行下面的操作再执行父系操作。对于不并列的，靠右的先执行（类似于目录）
那么这条执行计划的执行顺序为：5、6、4、8、7、3、10、11、9、2、1、0
Id：内部标识符，这里并不是表示的执行计划的执行顺序，只是用于表示执行计划中的节点顺序。特别注意：有*的标识符表示有谓词信息，也就是说这步操作有where过滤条件或者join连接条件，where过滤条件或者join连接条件对于查询性能最重要，通过谓词信息部分考虑是否可以通过添加或修改索引来提高过滤操作的效率。
Operation：描述了数据库执行的具体操作（访问路径），如“SELECT STATEMENT”，“HASH JOIN”等。
Name： 通常表示正在执行操作的对象的名称，例如表名或索引名。如果是索引名那么表示使用了索引，如果是表名那么就是用到了全表扫描，也可以通过Operation是不是TABLE ACCESS FULL来确定是不是进行了全表扫描，如果是全备扫描，需要重点关注。
Rows/card：估计的行数。这通常是查询优化器对查询结果的估计。
Bytes：估计的字节数。这通常是查询优化器对查询结果的估计。
TempSpc：估计的临时空间需求（以字节为单位）。这通常用于排序或分组操作。
Cost (%CPU)：开销。估计的成本和CPU使用百分比。成本是查询优化器用来决定执行顺序的一个度量，它考虑了多种因素，如I/O、CPU使用等。
Time：估计的执行时间（HH:MM:SS），返回的结果仅供参考，计划执行不真实。特别注意：虽然估计的执行时间不真实，但是如果这里的时间明显长，那么就需要重点关注。
Predicate Information (identified by operation id)为谓词信息部分：谓词信息部分列出了执行计划部分where过滤条件或者join连接条件的信息，其实也就是where过滤条件或者join连接条件都会在谓词信息中，where过滤条件或者join连接条件对于查询性能最重要，通过谓词信息部分考虑是否可以通过添加或修改索引来提高过滤操作的效率。
列出了执行计划部分where过滤条件或者join连接条件的信息，一共有两种：access表示使用索引作为过滤条件，那么filter表示没有使用索引作为过滤条件
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=2) 2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('use renv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) 4 - access("O"."OWNER#"="U"."USER#") 6 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL AND "O"."TYPE#"<=5) filter("O"."TYPE#"<=5 AND "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL) 8 - access("O"."OBJ#"="T"."OBJ#"(+)) 10 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere nv','current_edition_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere nv','current_edition_id'))) 11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
Statistics为执行计划统计信息部分：
Statistics ---------------------------------------------------------- 0 recursive calls ---递归调用。对其它SQL语句的调用的次数，越少越好 0 db block gets ---从buffer cache中读取的block的数据 13 consistent gets ---计算sql占用的运行内存。从buffer cache中读取undo数据的block的数量，13 *8192/1024/=104KB。 0 physical reads ---计算sql从磁盘读取的IO。从磁盘读取block的数量，如果是全盘扫描那么就是表的实际大小。不到一个块 0 redo size ---生成的redo大小。Select不生成redo数据，所以为0 731 bytes sent via SQL*Net to client ---服务器到客户端的字节总数 523 bytes received via SQL*Net from client ---客户机接受的字节数 2 SQL*Net roundtrips to/from client ---客户机到服务器之间发生的sql*net 次数 0 sorts (memory) ---在内存执行的排序量 0 sorts (disk) ---在磁盘上执行的排序量 2 rows processed ---影响数据的行数。就是结果返回的行数
4.1.2 查看SQL执行计划方式二：Explain Plan For+SQL（执行计划不真实。需要再执行一遍相关SQL）
explain plan for SQL使用方法：
1）执行explain plan for+SQL ----对目标SQL进行explain
2）执行展示出该 SQL 的执行计划。
案例一：查看生产用户itpux查询语句的执行计划
ITPUX@orcl1> explain plan for select * from itpux_yg where name='itpux12345'; ---生产执行计划
四种方式展示出该SQL的执行计划，不能显示执行计划的信息统计（任意其中一个即可）
ITPUX@orcl1> select id,operation,options,object_name,position from plan_table; ITPUX@orcl1> @?/rdbms/admin/utlxpls.sql ITPUX@orcl1> select * from table(dbms_xplan.display); ITPUX@orcl1> select plan_table_output from table(dbms_xplan.display('plan_table'));默认获取explain plan for最后执行语句的执行计划（如果没有加explain plan for，则不显示出来执行计划）
4.1.3 查看SQL执行计划方式三：DBMS_XPLAN包（执行计划真实）
DBMS_XPLAN包在9i引入，用于通过sql_id查看sql的执行计划。
DBMS_XPLAN提供的功能：
dbms_xplan.DISPLAY ---格式化和显示plan table中的内容
dbms_xplan.DISPLAY_AWR ---格式化和显示存储在AWR中的sql语句的执行计划。
dbms_xplan.DISPLAY_CURSOR ---格式化和显示任意shared pool中加载的sql语句的执行计划
dbms_xplan.DISPLAY_SQLSET ---格式化和显示存储在SQL tuning set中的sql语句的执行计划
11G增加：
dbms_xplan.DISPLAY_SQL_PLAN_BASELINE ---显示被SQL handle标识的SQL语句的一个或者多个执行计划
dbms_xplan.DISPLAY使用案例（默认获取explain plan for最后语句的执行计划，需要再执行一遍相关SQL）：
explain plan for SELECT a.id, a.name, a.sex, a.region, b.logtime, b.cardid, b.score, c.tel, c.joindate FROM itpux_member a, itpux_sales b, itpux_m10 c WHERE a.cardid = b.cardid AND b.cardid=c.cardid AND a.name in ('itpux1175189','itpux3077390','itpux7132935') AND b.cardid like '10%' AND c.joindate > to_date('2010-12-15', 'yyyy-mm-dd') ORDER BY c.joindate; select * from table(dbms_xplan.display); select plan_table_output from table(dbms_xplan.display('plan_table'));默认获取explain plan for最后执行语句的执行计划（如果没有加explain plan for，则不显示出来执行计划）
dbms_xplan.DISPLAY_CURSOR使用案例（通过sql_id号实时分析执行计划，不需要再执行相关SQL，通过V$sql找历史sql_id查看，但有可能SQL已经从shared pool共享池中被替换，就不能使用DISPLAY_CURSOR方式了）：
DBMS_XPLAN.DISPLAY_CURSOR相关参数与语法：
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL, ---指定sql_id或者hash_value
child_number IN NUMBER DEFAULT NULL, ---子游标的编号。子游标：SQL文本相同，但是因执行环境等不同，会生成多个执行计划，sql_id一样就通过指定子游标编号区分
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id：指定位于库缓存执行计划中 SQL 语句的父游标。默认值为null。当使用默认值时当前会话的最后一条SQL语句的执行计划将被返回。可以通过查询V$SQL或V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
child_number：指定父游标下子游标的序号。即指定被返回执行计划的 SQL 语句的子游标。默认值为 0。如果为 null，则 sql_id 所指父游标下所有子游标的执行计划都将被返回。
format：控制 SQL 语句执行计划的输出部分，即哪些可以显示哪些不显示。使用与 display 函数的 format 参数与修饰符在这里同样适用。除此之外当在开启statistics_level=all时或使用gather_plan_statistics 提示可以获得执行计划中实时的统计信息
下面给出启用统计信息时 format 新增的修饰符，官档的参数如下：
1.BASIC: 显示最少的信息，只包括操作类型，ID 名称和选项。
2.TYPICAL: 默认值，显示相关信息以及某些附加的显示选项，如分区和并发使用等。
3.SERIAL: 与 TYPICAL 类型相似，区别是它不包括并发的信息，即使是并行执行的计划。
4.ALL: 显示最多的信息，包含了 TYPICAL 的全部以及更多的附加信息,如别名和远程调用等。
除了以上的基本的四种输出格式外，format 还有一些附加的选项可用于定制化输出行为，使用中可以通过逗号和空格分隔来声明多个关键字，同时可以使用”+”和”-”符号来包含或排除相应的显示元素，这些附加的选项在官档中也有记载：
1.ROWS – 显示被优化器估算的记录的行号
2.BYTES – 显示优化器估算的字节数
3.COST – 显示优化器计算的成本信息
4.PARTITION – 显示分区的分割信息
5.PARALLEL – 显示并行执行信息
6.PREDICATE – 显示谓语
7.PROJECTION – 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
8.ALIAS – 显示查询块名称已经对象别名
9.REMOTE – 显示分布式查询信息
10.NOTE – 显示注释
11.IOSTATS – 显示游标执行的 IO 统计信息
12.MEMSTATS – 为内存密集运算如散列联结，排序，或一些类型的位图运算显示内存管理统计信息
13.ALLSTATS – 与'IOSTATS或MEMSTATS'等价
14.LAST – 显示最后执行的执行计划统计信息，默认显示为 ALL 类型，并且可以累积。
1）执行SQL语句：
SELECT a.id, a.name, a.sex, a.region, b.logtime, b.cardid, b.score, c.tel, c.joindate FROM itpux_member a, itpux_sales b, itpux_m10 c WHERE a.cardid = b.cardid AND b.cardid=c.cardid AND a.name in ('itpux1175189','itpux3077390','itpux7132935') AND b.cardid like '10%' AND c.joindate > to_date('2010-12-15', 'yyyy-mm-dd') ORDER BY c.joindate; alter session set statistics_level=all; ---如果当statistics_level=ALL,系统收集所有的统计信息，也就是说可以收集执行计划和统计信息，默认typical足够诊断99%的性能问题 select sql_id,hash_value,EXECUTIONS,child_number,SQL_FULLTEXT from v$sql where sql_text like '%itpux1175189%'; ---根据sql ID查看sql语句 child_number：子游标的编号。子游标：SQL文本相同，但是因执行环境等不同，会生成多个执行计划。包含了SQL的metadata，这个SQL可以执行的所有相关信息，如OBJECT和权限，优化器设置，执行计划等，具体参考“PLSQL工具使用详解（2）、PLSQL常用图形操作”文档。注意：v$sql等视图为动态性能视图不是永久存储SQL的信息，就会出现V$SQL或V$SQL_PLAN视图可能已经找不到对应SQL ID的记录，一般来说这些语句已经从 shared pool 共享池中被替换出去了。
set linesize 300 set pagesize 300 select * from table(dbms_xplan.display_cursor(sql_id => 'dd9uw5f7htg9u', ---也可以是hash_valus cursor_child_no => 0, ---根据子游标号确定。 format => 'ALL')); ---默认是typical：足够诊断99%的性能问题；如果设置为all：全面收集，包括OS以及sql执行路径方面的一些统计信息。除非遇见严重的性能问题或在一些特殊的性能诊断方面才会用到。
dbms_xplan.DISPLAY_AWR使用案例（sql语句需要采集到awr才能查看到执行计划，不需要再执行相关SQL）：
若该sql的执行计划被采集到awr库中，则可以用来查询历史执行计划。只有被采集到awr报告中的sql才能使用这个函数（awr默认通过mmon及mmnl进程来每小自动运行一次，参考“rac日志、statspack、awr、addm、ash、awrdd、awrsql”文档），不然只能用dbms_xplan.display_cursor包。
select * from itpux_m5 where ID=1233; select sql_id,hash_value,EXECUTIONS,child_number,SQL_FULLTEXT from v$sql where sql_text like '%itpux_m5%';
dbms_xplan.display_awr：
set linesize 300 set pagesize 300 select * from table(dbms_xplan.display_awr(sql_id => '1n0nx7rbdg4sp', format => 'ALL'));刚刚执行的sql还没有记录到awr报告中，所以使用dbms_xplan.display_awr函数是不能查出执行计划的，只能等到自动收集到awr报告，或者使用dbms_xplan.display_cursor函数
等到awr报告自动收集，查看SQL的执行计划。需要注意：dbms_xplan.display_awr是看不到谓词信息部分的，所以还是需要通过dbms_xplan.display_cursor去查看谓词信息进行分析，如果打AWRSQL报告也是只能看到执行计划部分，谓词信息部分看不到，所以最好还是在执行一遍SQL
4.1.4 查看SQL执行计划方式四：PL/SQL
选中SQL语句，右键选择解释计划，或者F5
提供树、html、文本、xml四种方式查看执行计划的方式
4、步骤四：SQL Execution（执行SQL语句，返回结果execute and return）
在执行过程中，SQL引擎执行行源生成器生成的树中的每个行源。此步骤是DML处理中唯一必需的步骤。
下图是一个执行树，也称为解析树，显示了示例上图中计划中行源从一个步骤到另一个步骤的流程。一般来说，执行步骤的顺序与计划中的顺序相反，因此您从下往上阅读计划。
执行计划中的每个步骤都有一个ID号。下图中的数字对应于示例上图所示平面图中的Id列。平面图“操作”列中的初始空格表示层次关系。例如，如果一个操作的名称前面有两个空格，那么这个操作就是前面有一个空格的操作的子操作。前面有一个空格的操作是SELECT语句本身的子项。
在图3-3中，树的每个节点都充当行源，这意味着示例3-1中执行计划的每个步骤要么从数据库中检索行，要么接受来自一个或多个行源的行作为输入。SQL引擎按如下方式执行每个行源：
黑框指示的步骤从数据库中的对象中物理检索数据。这些步骤是从数据库检索数据的访问路径或技术。
- 步骤6使用全表扫描从departments表中检索所有行。
- 步骤5使用全表扫描从作业表中检索所有行。
- 步骤4按顺序扫描emp_name_ix索引，查找以字母A开头的每个键并检索相应的行ID。例如，与阿特金森对应的rowid是AAAPzRAAFAAAABSAAe。
- 步骤3从employees表中检索其rowid由步骤4返回的行。例如，数据库使用rowid AAAPzRAAFAAAABSAAe检索Atkinson的行。
白框指示的步骤对行源进行操作。
- 步骤2执行哈希连接，接受步骤3和5中的行源，在步骤3中将步骤5行源中的每一行连接到其对应的行，并将结果行返回到步骤1。例如，员工Atkinson的行与职位名称“库存职员”相关联。
- 步骤1执行另一个哈希连接，接受步骤2和6中的行源，在步骤2中将步骤6源中的每一行连接到其对应的行，并将结果返回给客户端。例如，员工Atkinson的行与名为Shipping的部门相关联。
在某些执行计划中，步骤是迭代的，而在其他执行计划中则是顺序的。示例3-1中显示的哈希连接是顺序的。数据库根据连接顺序完整地完成这些步骤。数据库从emp_name_ix的索引范围扫描开始。使用从索引中检索的行ID，数据库读取employees表中的匹配行，然后扫描jobs表。从jobs表检索行后，数据库执行哈希连接。
在执行过程中，如果数据不在内存中，数据库会将数据从磁盘读取到内存中。数据库还会取出确保数据完整性所需的任何锁和闩锁，并记录SQL执行过程中所做的任何更改。处理SQL语句的最后阶段是关闭游标。
文章到这里就结束啦，优化器和SQL的执行计划介绍到这里就算全部结束啦，对于这两个部分，网上的资源非常有限，并且文章也良莠参半，整理起来非常不容易，可能有些重要的知识点也没有再文章中体现，如果有想深入了解的同学可以去参考官方文档哦，也希望各位小伙伴提出建议，查缺补漏，让这篇文章更加完善。
1325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
