重庆手机网站推广,手机建站免费,wordpress 写文章页面,热门国际新闻#x1f4ab;《博主介绍》#xff1a;✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ #x1f4ab;《擅长领域》#xff1a;✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux#xff0c;也在扩展大数据方向的知识面✌️… 《博主介绍》✨又是一天没白过我是奈斯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 ParsingSQL解析
SQL解析之阶段一Syntax Check语法检查
SQL解析之阶段二Semantic Check语义检查
SQL解析之阶段三Shared Pool Check共享池检查只针对DML语句
案例一了解SQL执行过程模拟软解析和硬解析
2、步骤二SQL OptimizationSQL优化
3、步骤三SQL Row Source GenerationSQL行来源生成对SQL语句进行解析(prase)利用内部算法对SQL进行解析生成解析树(parse tree)及执行计划(execution plan)
SQL行来源生成之执行计划
3.1 查看SQL执行计划方式一SQLPLUS AUTOTRACE推荐但执行计划不真实并且需要再执行一遍相关SQL
案例一对执行计划的执行顺序进行分析
3.2 查看SQL执行计划方式二Explain Plan ForSQL执行计划不真实。需要再执行一遍相关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 Cost3总成本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 的规则优化器RBORule-Based Optimizer是早期版本的Oracle数据库中用于执行 SQL 查询的优化器。 在 RBO 中查询的优化和执行计划生成是基于一套预定义的规则和优先级而不是基于成本模型。也就是说RBO是靠规则驱动的RBO 使用一系列固定的规则来决定查询的执行计划这些规则包括表的访问顺序、联接方式等 。例如RBO 可能会选择使用索引扫描而不是全表扫描或者选择某种特定的联接类型。当一个where子句中的一列有索引时就去走索引而不是进行全表扫描。又比如在没有合适索引时RBO 可能会选择全表扫描。 RBO 在执行计划中应用这些规则时按照预定义的优先级排序。这些优先级决定了哪些规则被首先应用。与后来的成本优化器CBOCost-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 的成本优化器CBOCost-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_moderule | choose | first_rows | all_rows | FIRST_ROWS_[1 | 10 | 100 | 1000]; PS小提示10g之后默认为all_rows。并且10g之后中不再支持RBO(rule、Choose10g之后官方文档关于optimizer_mode参数的只有first_rows_n、first_rows和all_rows。但是依然可以设置optimizer_mode为rule或choose估计是ORACLE为了过渡或向下兼容考虑。 值描述 FIRST_ROWS_N 不管是不是有统计信息全部采用基于成本的优化方法CBO并以最快的速度返回前N行记录。 N的值可以为1101001000优化器首先通过彻底分析第一个连接顺序来估计返回行的总数目。这样就可以知道查询可能获得的整个数据集的片段并重新启动整个优化过程其目标在于找到能够以最小的资源消耗返回整个数据片段的执行计划。 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 indexestable partitionsfunction-based indexes等所以在以后Oracle版本中已经不再更新RBO并且也不推荐用户使用RBO这种优化模式了。 CHOOSE 这个是Oracle的默认值。采用这个值时Oracle即可以采用基于规则RBO也可以采用基于成本CBO到底使用那个值取决于当前SQL的被访问的表中是不是有可以使用的统计信息。 如果有多个被访问的表其中有一个或多个有统计信息那么Oralce会对没有统计信息的表进行采样统计即不全部采样统计完成后使用基于成本的优化方法CBO。 如果所有被访问的表都没有统计信息Oracle就会采用基于规则的优化方法RBO。 关于Oracle优化器的详尽介绍至此已圆满结束感谢各位大佬耐心阅读这近1万字的深入解析各位能够坚持阅读完优化器的深度内容实属不易已远超众多学习者。现在是时候稍作休息让大脑放松一下以便更好地吸收和应用所学内容。 二、SQL语句的解析步骤 在Oracle数据库中执行一条SQL是有一套执行标准的先干什么后干什么都被安排的明明白白下图描述了SQL处理的每个阶段。 此图显示了SQL处理的各个阶段表示为五个由向下箭头链接的垂直框SQL StatementSQL语句、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 ParsingSQL解析 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 OptimizationSQL优化 在优化过程中Oracle数据库必须对每个唯一的DML语句至少执行一次硬解析并在此解析过程中执行优化。数据库未优化DDL。唯一的例外是当DDL包含需要优化的DML组件如子查询时。这里主要就是跟优化器相关了可以参考上面的优化器部分。 3、步骤三SQL Row Source GenerationSQL行来源生成对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多条执行计划计划执行真实
statisticslevelall; ###计划执行真实可以看到表被访问的次数
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 rownum2; ---tab类似于user_tablestabs 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(ROWNUM2) 2 - filter(O.TYPE#4 AND O.TYPE#5 AND O.TYPE#7 ANDO.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 ANDO.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 ANDU.TYPE#2 OR U.TYPE#2 AND U.SPARE2TO_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 ANDU2.SPARE2TO_NUMBER(SYS_CONTEXT(userenv,current_edition_id))))) 4 - access(O.OWNER#U.USER#) 6 - access(O.SPARE3USERENV(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.SPARE2TO_NUMBER(SYS_CONTEXT(userenv,current_edition_id)))filter(U2.TYPE#2 AND U2.SPARE2TO_NUMBER(SYS_CONTEXT(userenv,current_edition_id)))11 - access(O2.DATAOBJ#:B1 AND O2.TYPE#88 ANDO2.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 ForSQL执行计划不真实。需要再执行一遍相关SQL explain plan for SQL使用方法 explain plan for SQL语句 ###对目标SQL进行explain展示出该SQL的执行计划 案例一查看生产用户user查询语句的执行计划 1执行SQL语句 SQL explain plan for select * from tb_yg where nameuser12345; ---生产执行计划 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_predsSQL筛选器谓词用于限制从存储计划的表中选择的行集。当值为NULL默认值时显示的计划对应于上次执行的解释计划。例如filter_predsplan_id10 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.cardidc.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_IDcursor_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.cardidc.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_levelall; ---如果当statistics_levelALL,系统收集所有的统计信息也就是说可以收集执行计划和统计信息默认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 ID1233;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_IDD.DEPARTMENT_ID)2 - access(E.JOB_IDJ.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语句的解析步骤(含执行计划的详细分析和四种查看方式)的介绍到这里就算全部结束啦对于这两个部分网上的资源非常有限并且文章也良莠参半整理起来非常不容易可能有些重要的知识点也没有再文章中体现如果有想深入了解的同学可以去参考官方文档哦也希望各位小伙伴提出建议查缺补漏让这篇文章更加完善。
