【Oracle篇】全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)（第二篇，总共七篇）

💫《博主介绍》：✨又是一天没白过，我是奈斯，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秒)

---

                                                                     

目录

一、优化器

1、优化器三大组件介绍：

组件一：Query Transformer（查询转换器）

组件二：Estimator（估计器）

组件三：Plan Generator（计划生成器）

2、Oracle的优化器分类：

3、优化器的优化模式： 

二、SQL语句的解析步骤

1、步骤一：SQL Parsing（SQL解析）

SQL解析之阶段一：Syntax Check（语法检查）

SQL解析之阶段二：Semantic Check（语义检查）

SQL解析之阶段三：Shared Pool Check（共享池检查，只针对DML语句）

案例一：了解SQL执行过程，模拟软解析和硬解析

2、步骤二：SQL Optimization（SQL优化）

3、步骤三：SQL Row Source Generation（SQL行来源生成）：对SQL语句进行解析(prase)，利用内部算法对SQL进行解析，生成解析树(parse tree)及执行计划(execution plan)

SQL行来源生成之执行计划

3.1 查看SQL执行计划方式一：SQLPLUS AUTOTRACE（推荐，但执行计划不真实，并且需要再执行一遍相关SQL）

案例一：对执行计划的执行顺序进行分析

3.2 查看SQL执行计划方式二：Explain Plan For+SQL（执行计划不真实。需要再执行一遍相关SQL） 

案例一：查看生产用户user查询语句的执行计划

3.3 查看SQL执行计划方式三：DBMS_XPLAN包（执行计划真实）

dbms_xplan.DISPLAY使用案例（默认获取explain plan for最后语句的执行计划，需要再执行一遍相关SQL）：

dbms_xplan.DISPLAY_CURSOR使用案例（通过sql_id号实时分析执行计划，不需要再执行相关SQL，通过V$sql找历史sql_id查看，但有可能SQL已经从shared pool共享池中被替换，就不能使用DISPLAY_CURSOR方式了）：

dbms_xplan.DISPLAY_AWR使用案例（sql语句需要采集到awr才能查看到执行计划，不需要再执行相关SQL）：

3.4 查看SQL执行计划方式四：PL/SQL

4、步骤四：SQL Execution（执行SQL语句，返回结果execute and return）

---

                   

                                

一、优化器

    在Oracle中，优化器（optimizer）是数据库中可谓最核心的部分，主要是因为 优化器用来负责解析SQL语句，因此优化器的性能直接决定SQL语句的执行效率 。想要做好SQL优化，就需要了解优化器，这是基础。optimizer优化器根据统计信息对每个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的执行效率。

                  

官方文档对优化器的详细介绍（12c版本）：

Query Optimizer Concepts

                        

1、优化器三大组件介绍：

    优化器包含三个组件：查询转换器、估计器和计划生成器。如下图所示：

    此图描绘了一个解析后的查询（from the parser）进入到Query Transformer（查询转换器）。
    然后将转换后的查询发送到Estimator（估计器）。从字典中检索统计数据，然后将查询和估计值发送到Plan Generator（计划生成器）。

    Plan Generator（计划生成器）要么将计划返回给Estimator（估计器），要么将执行计划发送给row source generator（行源生成器）。

组件	描述
Query Transformer （查询转换器）	优化器确定更改查询的形式是否有帮助，以便优化器可以生成更好的执行计划。
Estimator （估计器）	优化器根据数据字典中的统计数据估计每个计划的成本。
Plan Generator （计划生成器）	优化器比较计划的成本，并选择成本最低的计划（称为执行计划）传递给行源生成器。

            

组件一：Query Transformer（查询转换器）

    对于某些语句，查询转换器会确定将原始SQL语句重写为语义上等效的SQL语句是否有利，并且成本更低。

    当存在可行的替代方案时，数据库会分别计算替代方案的成本，并选择成本最低的替代方案。下图显示了查询转换器将使用OR的输入查询重写为使用UNION ALL的输出查询。也就是说查询转换器觉得UNION ALL写法成本更低。

                        

                       

组件二：Estimator（估计器）

    估计器是优化器的组成部分，它决定了给定执行计划的总成本。估算师使用三种不同的衡量标准来确定成本：

• 选择性（Selectivity）：查询选择的行集中的行百分比，0表示没有行，1表示所有行。选择性与查询谓词相关联，例如WHERE last_name LIKE“a%”或谓词的组合。当选择性值接近0时，谓词变得更有选择性，当值接近1时，谓词的选择性降低（或更无选择性）。注意：选择性是一种内部计算，在执行计划中不可见。
• 基数（Cardinality）：基数是执行计划中每个操作返回的行数。这种输入对于获得最优计划至关重要，是所有成本函数的共同点。估计器可以从DBMS_STATS收集的表统计信息中推导出基数，或者在考虑谓词（过滤、连接等）、DISTINCT或GROUP by操作等的影响后推导出基数。执行计划中的Rows列显示了估计的基数。
• 成本（Cost）：此度量表示所使用的工作或资源单位。查询优化器使用磁盘I/O、CPU使用率和内存使用率作为工作单位。

    如下图所示，如果统计信息可用，则估计器使用它们来计算度量值。统计信息提高了测量的准确性。该图中左侧的框标有Plan，箭头指向标有Estimator的框，有三个特点：选择性（Selectivity）、基数（Cardinality）和成本（Cost）。箭头指向右侧的一个框，内容为“Total Cost=3（总成本=3）”。Estimator框下方的框标有“Statistics（统计信息）”。该框填充有1和0。

              

           

组件三：Plan Generator（计划生成器）

    计划生成器通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。许多计划都是可能的，因为数据库可以使用各种组合来产生相同的结果。优化器选择成本最低的计划。

    下图显示了优化器测试输入查询的不同计划。此框指向一个标记为“Optimizer（优化器）”的框。此框内有一个标有“Transformer（转换器）”的框，此框包含三个框：Join Method、Join Order、Access Path（连接方法、连接顺序、访问路径）。

• Join Method（连接方法）框包含“Hash、Nested Loop、Sort Merge（哈希、嵌套循环、排序合并）”。
• Join Order（连接顺序）框包含“departments 0 employees 1”和“employees 0 departments 1”。
• Access Path（访问路径）框包含“Index（索引）”和“Full Table Scan（全表扫描）”。

    优化器框指向一个包含“Hash Join”和“departments 0 employees 1”的框。箭头标记为“Lowest Cost Plan（最低成本计划）”。

    优化器跟踪文件中的以下代码片段显示了优化器执行的一些计算：

GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  DEPARTMENTS[D]#0  EMPLOYEES[E]#1***************
Now joining: EMPLOYEES[E]#1
***************
NL JoinOuter table: Card: 27.00  Cost: 2.01  Resp: 2.01  Degree: 1  Bytes: 16
Access path analysis for EMPLOYEES
. . .Best NL cost: 13.17
. . .
SM JoinSM cost: 6.08resc: 6.08 resc_io: 4.00 resc_cpu: 2501688resp: 6.08 resp_io: 4.00 resp_cpu: 2501688
. . .
SM Join (with index on outer)Access Path: index (FullScan)
. . .
HA JoinHA cost: 4.57resc: 4.57 resc_io: 4.00 resc_cpu: 678154resp: 4.57 resp_io: 4.00 resp_cpu: 678154
Best:: JoinMethod: HashCost: 4.57  Degree: 1  Resp: 4.57  Card: 106.00 Bytes: 27
. . .***********************
Join order[2]:  EMPLOYEES[E]#1  DEPARTMENTS[D]#0
. . .***************
Now joining: DEPARTMENTS[D]#0
***************
. . .
HA JoinHA cost: 4.58resc: 4.58 resc_io: 4.00 resc_cpu: 690054resp: 4.58 resp_io: 4.00 resp_cpu: 690054
Join order aborted: cost > best plan cost
***********************

    跟踪文件显示优化器首先尝试将departments表作为联接中的外部表。优化器计算三种不同连接方法的成本：嵌套循环连接（NL）、排序合并（SM）和哈希连接（HA）。优化器选择哈希连接作为最有效的方法：

Best:: JoinMethod: HashCost: 4.57  Degree: 1  Resp: 4.57  Card: 106.00 Bytes: 27

    然后，优化器使用employees作为外部表，尝试不同的连接顺序。此连接顺序的成本高于前一个连接顺序，因此被放弃。

    优化器在找到成本最低的计划时使用内部截止值来减少它尝试的计划数量。截止日期基于当前最佳计划的成本。如果当前的最佳成本较大，则优化器会探索其他计划以找到成本较低的计划。如果当前的最佳成本很小，那么优化器会迅速结束搜索，因为进一步的成本改进并不显著。

                        

2、Oracle的优化器分类：

rbo(rule-based optimization)：基于规则的优化器（9i）

    Oracle 的规则优化器（RBO，Rule-Based Optimizer）是早期版本的Oracle数据库中用于执行 SQL 查询的优化器。 在 RBO 中，查询的优化和执行计划生成是基于一套预定义的规则和优先级，而不是基于成本模型。也就是说RBO是靠规则驱动的，RBO 使用一系列固定的规则来决定查询的执行计划，这些规则包括表的访问顺序、联接方式等 。例如：RBO 可能会选择使用索引扫描而不是全表扫描，或者选择某种特定的联接类型。当一个where子句中的一列有索引时就去走索引，而不是进行全表扫描。又比如在没有合适索引时，RBO 可能会选择全表扫描。

    RBO 在执行计划中应用这些规则时，按照预定义的优先级排序。这些优先级决定了哪些规则被首先应用。与后来的成本优化器（CBO，Cost-Based Optimizer）不同，RBO 不考虑执行计划的成本或性能。它仅依赖于规则和优先级来选择执行路径。

    从 Oracle 10g 版本开始，Oracle 数据库主要使用成本优化器（CBO）。虽然 RBO 在早期版本中被广泛使用，但现代版本的 Oracle 数据库已经完全转向 CBO，因为 CBO 更加灵活且能提供更优化的执行计划。

PS小提示💥：从 10g开始rbo已经被弃用，但可以通过hint方式来使用它。走索引不一定就是优的，比如一个表只有两行数据，一次 I/O 就可以完成全表的检索，而此时走索引时则需要两次io，这时全表扫描(full table scan)是最好。

             

cbo(cost-based optimization)：基于成本的优化器（10g之后）

    Oracle 的成本优化器（CBO，Cost-Based Optimizer）是一种基于成本模型的查询优化器，它通过评估不同执行计划的成本来选择最优的执行路径。 CBO 主要依据统计信息来计算每个可能执行计划的成本，然后选择具有最低成本的计划。也就是说CBO优化器是看语句的成本(Cost)，考虑因素包括 I/O 操作、CPU 消耗、内存和网络传输等。优化器在判断是否用这种方式时主要参照的是表、索引、列和数据分布的统计信息，统计信息给出表的大小、有多少行、每行的长度等信息 。这些统计信息起初在库内是没有的，可以通过 ANALYZE 或 DBMS_STATS 包更新，统计信息越准确，CBO 选择的执行计划通常越高效。并且CBO 会评估多种执行路径，包括不同的联接方法（如嵌套循环、哈希联接等）、访问路径（如全表扫描、索引扫描等）和排序方式，CBO 可以自适应地调整执行计划，以应对数据分布的变化和查询模式的变化。有时过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。

    Oracle 允许通过设置参数（如 OPTIMIZER_MODE）来在不同的兼容模式下运行 CBO，例如选择不同的优化策略或返回与 RBO 相似的行为。

                              

3、优化器的优化模式： 

SQL> show parameter optimizer_mode     ###Oracle使用optimizer_mode参数为实例选择优化方法的默认行为。
first_rows_n     --CBO
first_rows       --CBO
all_rows         --CBO
rule             --RBO
Choose           --RBOSQL> alter session set optimizer_mode=rule | choose | first_rows | all_rows | FIRST_ROWS_[1 | 10 | 100 | 1000];

PS小提示：10g之后默认为all_rows。并且10g之后中不再支持RBO(rule、Choose），10g之后官方文档关于optimizer_mode参数的只有first_rows_n、first_rows和all_rows。但是依然可以设置optimizer_mode为rule或choose，估计是ORACLE为了过渡或向下兼容考虑。

值	描述
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 值来决定是否使用索引扫描。
FIRST_ROWS	CBO模式，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。     在oracle 9i之后这一选项已经过时，出于向后兼容的目的保留了这一选项，该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。这一规则倾向于促使优化器使用索引访问路径，偶尔会出现非常不恰当的访问路径。     设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以first_rows 优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP系统，满足用户能够在较短时间内看到较小查询结果集的要求。
ALL_ROWS	优化器将寻找能够在最短的时间内完成语句的执行计划。不管是不是有统计信息，全部采用基于成本的优化方法CBO。     设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。
RULE	这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则rbo的优化方法。     基于规则的优化器模式，RBO，是早期Oracle版本使用过的一种优化模式。由于RBO不支持自1994年Oracle版本的新特性，如bitmap indexes，table partitions，function-based indexes等，所以在以后Oracle版本中已经不再更新RBO，并且也不推荐用户使用RBO这种优化模式了。
CHOOSE	这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于成本CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。     如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于成本的优化方法CBO。     如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。

---

    关于Oracle优化器的详尽介绍至此已圆满结束，感谢各位大佬耐心阅读这近1万字的深入解析，各位能够坚持阅读完优化器的深度内容，实属不易，已远超众多学习者。现在，是时候稍作休息，让大脑放松一下，以便更好地吸收和应用所学内容😃。

                      

           

二、SQL语句的解析步骤

    在Oracle数据库中执行一条SQL是有一套执行标准的，先干什么后干什么都被安排的明明白白，下图描述了SQL处理的每个阶段。

    此图显示了SQL处理的各个阶段，表示为五个由向下箭头链接的垂直框：SQL Statement（SQL语句）、Parsing（解析）、Optimization（优化）、Row Source Generation（行源生成）和Execution（执行）。

• Parsing（解析）框标有“Syntax check（语法检查），Semantic check（语义检查），Shared Pool Check（共享池检查）”。
• Optimization（优化）框标有“Generation of multiple execution plans（生成多个执行计划）”。
• Row Source Generation（行源生成）框标有“Generation of execution plan（执行计划生成）”。
• Execution（执行）框：标记为“Soft parse（软解析）”的箭头从Parsing（解析）框延伸到Execution（执行）框。

                

官方文档对SQL语句解析步骤的详细介绍（12c版本）：

SQL Processing

                     

1、步骤一：SQL Parsing（SQL解析）

    SQL处理的第一阶段是解析。解析阶段涉及将SQL语句的片段分离为其他例程可以处理的数据结构。当应用程序发出指令时，数据库会解析语句，这意味着只有应用程序而不是数据库本身可以减少解析次数。

    当应用程序发出SQL语句时，应用程序会对数据库进行解析调用，以准备执行该语句。解析调用打开或创建一个游标，游标是会话特定私有SQL区域的句柄，其中包含解析的SQL语句和其他处理信息。游标和私有SQL区域位于程序全局区域（PGA）中。

    在解析调用期间，数据库执行检查，以识别在语句执行之前可以发现的错误。解析无法捕获某些错误。例如，数据库只有在语句执行期间才会在数据转换中遇到死锁或错误。

                            

SQL解析之阶段一：Syntax Check（语法检查）

    Oracle数据库必须检查每个SQL语句的语法有效性。违反格式良好的SQL语法规则的语句未通过检查。例如，以下语句失败，因为关键字FROM拼写错误为FORM：

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语句生成一个哈希值。语句哈希值是SQL ID显示在V$SQL.SQL_ID。此哈希值在Oracle数据库版本中是确定的，因此单个实例或不同实例中的同一语句具有相同的SQL ID。

    当用户提交SQL语句时，数据库会搜索共享SQL区域，查看现有的解析语句是否具有相同的哈希值。SQL语句的哈希值不同于以下值：

• 语句的内存地址：Oracle数据库使用SQL ID在查找表中执行键控读取。通过这种方式，数据库获得语句的可能内存地址。
• 语句执行计划的哈希值：SQL语句在共享池中可以有多个计划。通常，每个计划都有不同的哈希值。如果同一个SQL ID有多个计划哈希值，则数据库知道此SQL ID存在多个计划。

    根据提交的语句类型和哈希检查的结果，解析操作分为以下几类：

• 硬解析：若Oracle数据库不能重用现有代码，则必须构建应用程序代码的新可执行版本。此操作称为硬解析或库缓存未命中。在硬解析过程中，数据库多次访问库缓存和数据字典缓存以检查数据字典。当数据库访问这些区域时，它会在所需对象上使用一种称为锁的序列化设备，这样它们的定义就不会改变。锁存争用增加了语句执行时间并降低了并发性。也就是说在共享池的库缓存中找不到对应的执行计划，则必须继续解析SQL、生成执行计划，这种解析称作硬解析。需要清楚一点创建变量解析树、生成执行计划对于SQL的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析，要在程序中多使用绑定变量，减少系统的资源使用。注意：数据库总是执行DDL的硬解析。
• 软解析：软解析是指任何非硬解析的解析。如果提交的语句与共享池中的可重用SQL语句相同，则Oracle数据库将重用现有代码。这种代码重用也称为库缓存命中。软解析的工作量可能会有所不同。例如，配置会话共享SQL区域有时可以减少软解析中的锁存量，使其“更软”。一般来说，软解析比硬解析更可取，因为数据库跳过优化和行源代码生成步骤，直接执行。 也是就说如果能从共享池的缓存库中找到之前解析过生成的执行计划，则SQL语句则不需要再次解析，便可以直接由库缓存得到之前所产生的执行计划，从而直接跳到绑定或执行阶段，这种解析称作软解析。

    下图是专用服务器架构中UPDATE语句的共享池检查的简化表示。

    此图显示了共享池检查。顶部是三个相互叠放的框，每个框都比后面的框小。最小的框显示哈希值，并标记为Shared SQL Area（共享SQL区域）。第二个框标记为Shared Pool（共享池），最外层标记为SGA。此框下方是另一个标记为PGA的框。PGA框内是一个标记有Private SQL Area的框，其中包含一个哈希值。一个双头箭头连接上下框，并标记为“Comparison of hash values（哈希值的比较）”。PGA框右侧是一个标记为“用户进程”的人图标。图标由双面箭头连接。用户进程图标上方是“Update ....”语句。箭头从下面的用户进程指向，因此下面的服务器进程图标。

    为了开始这个处理Oacle必须在Shared pool中寻找语句，Shared pool是SGA中的一部分用来缓存以前执行过的sql语句、PLSQL、数据字典内容的缓存以及其他许多信息，以供会话重用。为了高效完成此操作，查找Shared pool中是否有相同的语句。检查此SQL是否被当前用户使用过，如果是就是软解析soft parse，如果否那就是硬解析。DDL总是硬解析，语句从不重用。

                                      

例如：修改操作（INSERT、UPDATE、DELETE）

SQL> UPDATE tb_yg SET salary = 1.5 * salary WHERE name = :v_name;

v_name是程序变量，里面员工名，我们要修改该员工的工资。当这个SQL语句执行时，使用该变量的值，那么进行软解析soft parse

SQL> UPDATE tb_yg SET salary = 1.5 * salary WHERE name =’user01’;
SQL> UPDATE tb_yg SET salary = 1.5 * salary WHERE name =’user02’;

这两个虽然SQL前面一样，但值不一样，那么就会进行硬解析hard parse

                          

总结：创建变量解析树、生成执行计划对于SQL的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析，要在程序中多使用绑定变量，减少系统的资源使用。

                  

案例一：了解SQL执行过程，模拟软解析和硬解析

1）创建测试表

SQL> 
create user user1 identified by user1; 
create user user2 identified by user2; 
grant dba to user1; 
grant dba to user2; 

2）启用一个会话

SQL>
conn user1/user1 
create table tb1 as select * from dba_data_files; 
select * from tb1; 

3）启用另一个会话

SQL>
conn user2/user2 
create table tb1 as select * from dba_data_files; 
select * from tb1; 
select * from tb1;

4）启用另一会话，使用 sys 用户连接，进行如下查询

SQL> select address,hash_value, executions, sql_text from v$sql where lower(sql_text) like 'select * from tb1';

可见虽然发出的语句是一样的，但是语义不同，所以v$sql中会有两条记录。不同的用户执行一样的语句，会进行硬解析。user2用户下相同的语句执行了两次，因为语义相同，所以是一条记录两次执行，user2就是软解析

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 tb1')

在视图找出：

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多条执行计划

      

3.1 查看SQL执行计划方式一：SQLPLUS AUTOTRACE（推荐，但执行计划不真实，并且需要再执行一遍相关SQL）

AUTOTRACE语法：

SQL> 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   ---只显示执行计划的统计信息，但是不显示查询输 出。

         

案例一：对执行计划的执行顺序进行分析

1）执行SQL语句

SQL> set line 400;
SQL> set autotrace on;
SQL> 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#"<>11AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND"O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 ORBITAND("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('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$""U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88AND "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('userenv','current_edition_id')))filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','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              ---影响数据的行数。就是结果返回的行数

   

3.2 查看SQL执行计划方式二：Explain Plan For+SQL（执行计划不真实。需要再执行一遍相关SQL） 

explain plan for SQL使用方法：

explain plan for + SQL语句       ###对目标SQL进行explain，展示出该SQL的执行计划

                    

案例一：查看生产用户user查询语句的执行计划

1）执行SQL语句

SQL> explain plan for select * from tb_yg where name='user12345';  ---生产执行计划

     

2）四种方式展示出该SQL的执行计划，不能显示执行计划的信息统计（任意其中一个即可）

SQL> select id,operation,options,object_name,position from plan_table;
SQL> @?/rdbms/admin/utlxpls.sql
SQL> select * from table(dbms_xplan.display);
SQL> select plan_table_output from table(dbms_xplan.display('plan_table'));

默认获取explain plan for最后执行语句的执行计划（如果没有加explain plan for，则不显示出来执行计划）

    

3.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）：

包相关参数与语法：

DBMS_XPLAN.DISPLAY(table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',statement_id  IN  VARCHAR2  DEFAULT  NULL,format        IN  VARCHAR2  DEFAULT  'TYPICAL',filter_preds  IN  VARCHAR2 DEFAULT NULL);

值	描述
table_name	指定存储计划的表名。此参数默认为PLAN_TABLE，这是EXPLAIN PLAN命令的默认计划表。如果指定了NULL，它也默认为PLAN_TABLE。
statement_id	指定要显示的计划的statement_id。此参数默认为NULL，这是在没有set statement_id子句的情况下执行EXPLAIN PLAN命令时的默认值。如果没有指定statement_id，该函数将显示最近解释语句的计划。
format	控制计划的详细程度。它接受以下值：           BASIC：显示计划中的最小信息——操作ID、操作名称及其选项。           TYPICAL：这是默认值。显示计划中最相关的信息（操作id、名称和选项、#行、#字节和优化器成本）。修剪、平行和谓词信息仅在适用时显示。仅排除PROJECTION、ALIAS和REMOTE SQL信息（见下文）。            SERIAL：类似于典型，但即使计划并行执行，也不显示并行信息。             ALL：最高用户级别。包括以典型级别显示的信息以及其他信息（投影、ALIAS和有关远程SQL的信息，如果操作是分布式的）。           为了更好地控制显示输出，可以将以下关键字添加到上述三个标准格式选项中，以自定义其默认行为。每个关键字要么表示计划表列的逻辑组（如PARTITION），要么表示对基本计划表输出的逻辑添加（如PREDICATE）。格式关键字必须用逗号或空格分隔：            ROWS-如果相关，显示优化器估计的行数            BYTES-如果相关，显示优化器估计的字节数            COST-如果相关，显示优化器成本信息            PARTITION-如果相关，显示分区修剪信息            PARALLEL-如果相关，显示PX信息（分发方法和表队列信息）            PREDICATE-如果相关，显示谓词部分            PROJECTION-如果相关，显示投影部分            ALIAS-如果相关，显示“查询块名称/对象别名”部分            REMOTE-如果相关，显示分布式查询的信息（例如，远程串行分发和远程SQL）            NOTE-如果相关，显示解释计划的注释部分 格式关键字可以以符号“-”作为前缀，以排除指定的信息。例如，“-PROJECTION”不包括投影信息。
filter_preds	SQL筛选器谓词，用于限制从存储计划的表中选择的行集。当值为NULL（默认值）时，显示的计划对应于上次执行的解释计划。例如：filter_preds=>'plan_id=10'

1）执行SQL语句

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.cardidAND b.cardid=c.cardidAND 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;SQL> select * from table(dbms_xplan.display);
SQL> 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(sql_id            IN  VARCHAR2  DEFAULT  NULL,cursor_child_no   IN  NUMBER    DEFAULT  0, format            IN  VARCHAR2  DEFAULT  'TYPICAL');

值	描述
sql_id	指定位于库缓存执行计划中 SQL 语句的父游标。默认值为null。当使用默认值时当前会话的最后一条SQL语句的执行计划将被返回。可以通过查询V$SQL或V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID
cursor_child_no	要显示的光标的子编号。如果未提供，则显示与提供的sql_id参数匹配的所有游标的执行计划。仅当指定了sql_id时，才能指定child_number。
format	控制计划的详细程度。它接受五个值：         BASIC：显示计划中的最小信息——操作ID、操作名称及其选项。         TYPICAL：这是默认值。显示计划中最相关的信息（操作id、名称和选项、#行、#字节和优化器成本）。修剪、平行和谓词信息仅在适用时显示。仅排除PROJECTION、ALIAS和REMOTE SQL信息（见下文）。         SERIAL：类似于典型，但即使计划并行执行，也不显示并行信息。         ALL：最高用户级别。包括以典型级别显示的信息以及其他信息（投影、ALIAS和有关远程SQL的信息，如果操作是分布式的）。         ADAPTIVE：显示最终计划，如果执行尚未完成，则显示当前计划。本节包括有关影响计划的运行时优化的说明，例如从嵌套循环连接切换到哈希连接。   为了更好地控制显示输出，可以将以下关键字添加到上述三个标准格式选项中，以自定义其默认行为。每个关键字要么表示计划表列的逻辑组（如PARTITION），要么表示对基本计划表输出的逻辑添加（如PREDICATE）。格式关键字必须用逗号或空格分隔：         ROWS-如果相关，显示优化器估计的行数         BYTES-如果相关，显示优化器估计的字节数         COST-如果相关，显示优化器成本信息         PARTITION-如果相关，显示分区修剪信息         PARALLEL-如果相关，显示PX信息（分发方法和表队列信息）         PREDICATE-如果相关，显示谓词部分         ROJECTION-如果相关，显示投影部分         ALIAS-如果相关，显示“查询块名称/对象别名”部分        ............其他的值可以参考官网文档

1）执行SQL语句：

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.cardidAND b.cardid=c.cardidAND 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;SQL> alter session set statistics_level=all;   ---如果当statistics_level=ALL,系统收集所有的统计信息，也就是说可以收集执行计划和统计信息，默认typical足够诊断99%的性能问题SQL> 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和权限，优化器设置，执行计划等。

注意：v$sql等视图为动态性能视图不是永久存储SQL的信息，就会出现V$SQL或V$SQL_PLAN视图可能已经找不到对应SQL ID的记录，一般来说这些语句已经从 shared pool 共享池中被替换出去了。

               

2）执行dbms_xplan.DISPLAY_CURSOR

SQL>
set linesize 300
set pagesize 300
select * from table(dbms_xplan.display_cursor(sql_id          => 'dd9uw5f7htg9u',    ---也可以是hash_valuscursor_child_no => 0,    ---根据子游标号确定。format          => 'ALL'));   ---默认是typical：足够诊断99%的性能问题；如果设置为all：全面收集，包括OS以及sql执行路径方面的一些统计信息。除非遇见严重的性能问题或在一些特殊的性能诊断方面才会用到。

        

dbms_xplan.DISPLAY_AWR使用案例（sql语句需要采集到awr才能查看到执行计划，不需要再执行相关SQL）：

包相关参数与语法：

DBMS_XPLAN.DISPLAY_AWR( sql_id            IN      VARCHAR2,plan_hash_value   IN      NUMBER DEFAULT NULL,db_id             IN      NUMBER DEFAULT NULL,format            IN      VARCHAR2 DEFAULT TYPICAL);

值	描述
sql_id	指定SQL语句的_RESID。您可以通过查询DBA_HIST_SQLTEXT中的column ID列来检索感兴趣的SQL语句的适当值。
plan_hash_value	指定SQL语句的PLAN_HASH_VALUE。此参数是可选的。如果省略，则表函数将返回给定ViewModel ID的所有存储的执行计划。
db_id	指定database_id，应为其显示SQL语句的计划，该计划由DateTimeID标识。如果没有提供，则使用本地数据库的database_id，如V$database所示。
format	控制计划的详细程度。它接受四个值：        BASIC：显示计划中的最小信息——操作ID、操作名称及其选项。        TYPICAL：这是默认值。显示计划中最相关的信息（操作id、名称和选项、#行、#字节和优化器成本）。修剪、平行和谓词信息仅在适用时显示。仅排除PROJECTION、ALIAS和REMOTE SQL信息（见下文）。        SERIAL：类似于典型，但即使计划并行执行，也不显示并行信息。        ALL：最高用户级别。包括以典型级别显示的信息以及其他信息（投影、ALIAS和有关远程SQL的信息，如果操作是分布式的）。        为了更好地控制显示输出，可以将以下关键字添加到上述四个标准格式选项中，以自定义其默认行为。每个关键字要么表示计划表列的逻辑组（如PARTITION），要么表示对基本计划表输出的逻辑添加（如PREDICATE）。格式关键字必须用逗号或空格分隔：        ROWS-如果相关，显示优化器估计的行数        BYTES-如果相关，显示优化器估计的字节数        COST-如果相关，显示优化器成本信息        PARTITION-如果相关，显示分区修剪信息        PARALLEL-如果相关，显示PX信息（分发方法和表队列信息）        PREDICATE-如果相关，显示谓词部分        PROJECTION-如果相关，显示投影部分        ALIAS-如果相关，显示“查询块名称/对象别名”部分        REMOTE-如果相关，显示分布式查询的信息（例如，远程串行分发和远程SQL）        NOTE-如果相关，显示解释计划的注释部分 格式关键字可以以符号“-”作为前缀，以排除指定的信息。例如，“-PROJECTION”不包括投影信息。

          

    若该SQL的执行计划被采集到awr报告中，则才可以使用dbms_xplan.DISPLAY_AWR查询历史执行计划。只有被采集到awr报告中的SQL才能使用这个包（awr默认通过mmon及mmnl进程来每小自动运行一次），不然只能用dbms_xplan.display_cursor包查看执行计划。

                

1）执行SQL语句：

SQL> select * from itpux_m5 where ID=1233;SQL> select sql_id,hash_value,EXECUTIONS,child_number,SQL_FULLTEXT from v$sql where sql_text like '%itpux_m5%';  

                         

2）执行dbms_xplan.display_awr

SQL>
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

                 

3.4 查看SQL执行计划方式四：PL/SQL

选中SQL语句，右键选择解释计划，或者F5

提供树、html、文本、xml四种方式查看执行计划的方式

                

4、步骤四：SQL Execution（执行SQL语句，返回结果execute and return）

    在讲解之前我们先看一个SQL语句的执行计划。如下示例中列出了SELECT语句的执行计划。该语句为姓氏以字母A开头的所有员工选择姓氏、职务和部门名称。此语句的执行计划是row source generator（行源生成器）的输出。

SELECT e.last_name, j.job_title, d.department_name 
FROM   hr.employees e, hr.departments d, hr.jobs j
WHERE  e.department_id = d.department_id
AND    e.job_id = j.job_id
AND    e.last_name LIKE 'A%';Execution Plan
----------------------------------------------------------
Plan hash value: 975837011---------------------------------------------------------------------------
| Id| Operation                    | Name      |Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT              |             | 3 |189 |7(15)|00:00:01 |
|*1|  HASH JOIN                    |             | 3 |189 |7(15)|00:00:01 |
|*2|   HASH JOIN                   |             | 3 |141 |5(20)|00:00:01 |
| 3|    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   | 3 | 60 |2 (0)|00:00:01 |
|*4|     INDEX RANGE SCAN          | EMP_NAME_IX | 3 |    |1 (0)|00:00:01 |
| 5|    TABLE ACCESS FULL          | JOBS        |19 |513 |2 (0)|00:00:01 |
| 6|   TABLE ACCESS FULL           | DEPARTMENTS |27 |432 |2 (0)|00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")2 - access("E"."JOB_ID"="J"."JOB_ID")4 - access("E"."LAST_NAME" LIKE 'A%')filter("E"."LAST_NAME" LIKE 'A%')

    在执行过程中，SQL引擎执行行源生成器生成的树中的每个行源。此步骤是DML处理中唯一必需的步骤。

    那么我们将上面SQL语句的执行计划画成下面的执行树，也称为解析树，执行树显示了SQL语句的执行计划从一个步骤到另一个步骤的流程。一般来说，执行步骤的顺序与计划中的顺序相反，因此需要从下往上阅读计划。

    执行计划中的每个步骤都有一个ID号。下图中的数字对应于上面SQL语句执行计划中的Id列。SQL语句执行计划中的“Operation（操作）”列，前面的空格表示层次关系。例如，如果一个operation（操作）的名称前面有两个空格，那么这个operation（操作）就是前面有一个空格的操作的子操作。前面有一个空格的操作是SELECT语句本身的子项。

    在上图中，树的每个节点都充当行源，这意味着SQL语句执行计划的每个步骤要么从数据库中检索行，要么接受来自一个或多个行源的行作为输入。SQL引擎按如下方式执行每个行源：

黑框指示的步骤从数据库中的对象中物理检索数据。这些步骤是从数据库检索数据的访问路径或技术：

• 步骤6：使用全表扫描从departments表中检索所有行。
• 步骤5：使用全表扫描从jobs表中检索所有行。
• 步骤4：按顺序扫描emp_name_ix索引，查找以字母A开头的每个键并检索相应的行ID。例如，与Atkinson对应的rowid是AAAPzRAAFAAAABSAAe。
• 步骤3：从employees表中检索其rowid由步骤4返回的行。例如，数据库使用rowid AAAPzRAAFAAAABSAAe检索Atkinson的行。

白框指示的步骤对行源进行操作：

• 步骤2：执行hash join（哈希连接），接受步骤3和5中的行源，在步骤3中将步骤5行源中的每一行连接到其对应的行，并将结果行返回到步骤1。例如，员工Atkinson的行与职位名称“Stock Clerk”相关联。
• 步骤1：执行另一个hash join（哈希连接），接受步骤2和6中的行源，在步骤2中将步骤6源中的每一行连接到其对应的行，并将结果返回给客户端。例如，员工Atkinson的行与名为“Shipping”的部门相关联。

    在某些执行计划中，步骤是迭代的，而在其他执行计划中则是顺序的。在SQL执行计划中显示的哈希连接是顺序的。数据库根据连接顺序完整地完成这些步骤。数据库从emp_name_ix的索引范围扫描开始。使用从索引中检索的行ID，数据库读取employees表中的匹配行，然后扫描jobs表。从jobs表检索行后，数据库执行哈希连接。

    在执行过程中，如果数据不在内存中，数据库会将数据从磁盘读取到内存中。数据库还会取出确保数据完整性所需的任何锁和闩锁，并记录SQL执行过程中所做的任何更改。处理SQL语句的最后阶段是关闭游标。

---

                  

    优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)的介绍到这里就算全部结束啦，对于这两个部分，网上的资源非常有限，并且文章也良莠参半，整理起来非常不容易，可能有些重要的知识点也没有再文章中体现，如果有想深入了解的同学可以去参考官方文档哦，也希望各位小伙伴提出建议，查缺补漏，让这篇文章更加完善。