获取和解读Oracle中SQL的执行计划(译文)

简介: 生成和显示 SQL 语句的执行计划是大多数 DBA、SQL 开发人员和性能专家的常见任务,因为它提供了 SQL 语句执行性能相关的信息。执行计划显示执行 SQL 语句的详细步骤,这些步骤表示为一组使用和生成行的数据库运算符。运算符的顺序和实现由查询优化器根据查询转换和物理优化技术来决定。

生成和显示 SQL 语句的执行计划是大多数 DBA、SQL 开发人员和性能专家的常见任务,因为它提供了 SQL 语句执行性能相关的信息。执行计划显示执行 SQL 语句的详细步骤,这些步骤表示为一组使用和生成行的数据库运算符。运算符的顺序和实现由查询优化器根据查询转换和物理优化技术来决定。

虽然显示通常以表格格式显示,但计划实际上是树形的。例如,以下是基于 SH 架构(销售历史记录)的查询:


select prod_category, avg(amount_sold)from sales s, products pwhere p.prod_id = s.prod_idgroup by prod_category;

此查询计划的表格表示形式为:



------------------------------------------ Id   Operation              Name   ------------------------------------------   0  SELECT STATEMENT                 1   HASH GROUP BY                   2    HASH JOIN                      3     TABLE ACCESS FULL   PRODUCTS   4     PARTITION RANGE ALL           5      TABLE ACCESS FULL  SALES  ------------------------------------------

而计划的树形表示是:



GROUP BY      |     JOIN _____|_______ |            |ACCESS     ACCESS(PRODUCTS) (SALES)

当你阅读计划树时,你应该从下到上开始。在上面的示例中,首先查看ACCESS运算符(或树叶)。在这种情况下,ACESS运算符是使用全表扫描实现的。这些表扫描生成的行将交给JOIN运算符使用。这里的JOIN运算符是哈希(HASH)连接(其他替代方法包括嵌套循环 nested-loop或排序合并 sort-merge连接)。最后,使用哈希实现的GROUP BY运算符(替代方法是排序)处理JOIN运算符生成的行。SQL 语句执行时采用的执行计划只是查询优化器考虑的众多执行计划中的一个。查询优化器根据执行过程的 IO、CPU 和网络使用情况计算成本,然后选择开销最低的执行计划。

有两种不同的方法可用于查看 SQL 语句的执行计划:

  1. EXPLAIN PLAN 命令:显示 SQL 语句的执行计划,而不实际执行该语句。
  2. V$SQL_PLAN:一个字典视图,显示已编译为保存在游标缓存中的游标的 SQL 语句的执行计划。

在某些情况下,使用 EXPLAIN PLAN 命令时显示的计划可能与使用 V$SQL_PLAN显示的计划不同,例如前者在计划生成过程中不会考虑绑定变量值,而后者会。使用DBMS_XPLAN包可以很容易显示执行计划,这个软件包提供了几个PL / SQL存储过程来显示不同来源的计划:

  • EXPLAIN PLAN 命令
  • V$SQL_PLAN
  • 自动工作负载存储库 (AWR)
  • SQL 优化集 (STS)
  • SQL 计划基线 (SPM)

以下示例说明了如何使用 dbms_xplan 包中提供的不同函数生成和显示原始 SQL 语句的执行计划。

示例 1:使用EXPLAIN PLAN命令和DBMS_XPLAN.DISPLAY函数。



SQL> EXPLAIN PLAN FOR 2   select prod_category, avg(amount_sold) 3   from sales s, products p 4   where p.prod_id = s.prod_id 5   group by prod_category;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
------------------------------------------ Id   Operation              Name   ------------------------------------------   0  SELECT STATEMENT                 1   HASH GROUP BY                   2    HASH JOIN                      3     TABLE ACCESS FULL   PRODUCTS   4     PARTITION RANGE ALL           5      TABLE ACCESS FULL  SALES  ------------------------------------------

DBMS_XPLAN.DISPLAY的参数:

  • Plan table name(默认为“PLAN_TABLE”)
  • Statement_id(默认空)
  • format(默认为“TYPICAL”)

更多详细信息可以在 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql 中找到。

示例 2:生成并显示会话中执行的最后一个 SQL 语句的执行计划:



SQL> select prod_category, avg(amount_sold) 2   from sales s, products p 3   where p.prod_id = s.prod_id 4   group by prod_category;
no rows selected
SQL> select plan_table_output 2    from table(dbms_xplan.display_cursor(null,null,'basic'));------------------------------------------ Id   Operation              Name   ------------------------------------------   0  SELECT STATEMENT                 1   HASH GROUP BY                   2    HASH JOIN                      3     TABLE ACCESS FULL   PRODUCTS   4     PARTITION RANGE ALL           5      TABLE ACCESS FULL  SALES  ------------------------------------------

DBMS_XPLAN.DISPLAY_CURSOR的参数:

  • SQL_ID(默认 NULL,表示在此会话中执行的最后一个 SQL 语句)
  • Child Number(默认为 0)
  • format(默认为“TYPICAL”)

详细信息位于 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql。

示例 3:显示任何其他语句的执行计划需要直接或间接提供 SQL_ID:

直接:

SQL> select plan_table_output from 2   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

间接:


SQL> select plan_table_output 2   from v$sql s, 3   table(dbms_xplan.display_cursor(s.sql_id, 4                                  s.child_number, 'basic')) t 5   where s.sql_text like 'select PROD_CATEGORY%';

例4:显示与 SQL 计划基线对应的执行计划。




SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SQL> select prod_category, avg(amount_sold) 2   from sales s, products p 3   where p.prod_id = s.prod_id 4   group by prod_category;
no rows selected

如果上述语句已执行多次,则将为其创建 SQL 计划基线,您可以使用以下查询进行验证:



SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED 2   from dba_sql_plan_baselines 3   where sql_text like 'select prod_category%';
SQL_HANDLE                     PLAN_NAME                      ACC------------------------------ ------------------------------ ---SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

上面创建的 SQL 计划基线的执行计划可以直接或间接显示:

直接:


select t.* fromtable(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',                                           format => 'basic')) t

间接:

select t.*

from (select distinct sql_handle          from dba_sql_plan_baselines          where sql_text like 'select prod_category%') pb,         table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,                                                    null,'basic')) t;

这两个语句中的任何一个的输出是:



----------------------------------------------------------------------------SQL handle: SYS_SQL_1899bb9331ed7772SQL text: select prod_category, avg(amount_sold) from sales s, products p          where p.prod_id = s.prod_id group by prod_category--------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE----------------------------------------------------------------------------Plan hash value: 4073170114--------------------------------------------------------- Id   Operation                 Name               ---------------------------------------------------------   0  SELECT STATEMENT                                1   HASH GROUP BY                                  2    HASH JOIN                                     3     VIEW                   index$_join$_002      4      HASH JOIN                                   5       INDEX FAST FULL SCAN PRODUCTS_PK           6       INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX   7     PARTITION RANGE ALL                          8      TABLE ACCESS FULL     SALES              ---------------------------------------------------------

格式(Formatting)

格式参数是高度可定制的,允许您在计划输出中看到您想要的尽可能少的(高级)或尽可能多的(低级)详细信息。高级选项包括:

1.BASIC

计划包括操作、选项和对象名称(表、索引、物理视图等)

2.TYPICAL

它包括 BASIC 中显示的信息以及其他与优化器相关的内部信息,如成本、大小、基数等。显示每步的操作成本、生成的行数等。它还显示对操作的谓词估算,有两种类型的谓词:ACCESS 和 FILTER。索引的 ACCESS 谓词用于搜索的相关块,FILTER 谓词用于获取的块。

3.ALL

它包括 TYPICAL 中显示的信息以及每个操作生成的表达式(列)列表、hint别名和操作所属的查询块名称。最后两条信息可以用作向语句添加提示的参数。

低级选项指定包含或排除的细节,例如谓词和成本。


select plan_table_outputfrom table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));
------------------------------------------------------- Id   Operation              Name      Cost (%CPU)-------------------------------------------------------   0  SELECT STATEMENT                    17  (18)   1   HASH GROUP BY                      17  (18)*  2    HASH JOIN                         15   (7)   3     TABLE ACCESS FULL   PRODUCTS      9   (0)   4     PARTITION RANGE ALL               5   (0)   5      TABLE ACCESS FULL  SALES         5   (0)-------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("P"."PROD_ID"="S"."PROD_ID")




select plan_table_output fromtable(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));
---------------------------------------------------------------------------- Id   Operation              Name      Rows  Time      Pstart Pstop----------------------------------------------------------------------------   0  SELECT STATEMENT                    4  00:00:01                1   HASH GROUP BY                      4  00:00:01             *  2    HASH JOIN                       960  00:00:01                3     TABLE ACCESS FULL   PRODUCTS   766  00:00:01                4     PARTITION RANGE ALL            960  00:00:01      1     16   5      TABLE ACCESS FULL  SALES      960  00:00:01      1     16----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("P"."PROD_ID"="S"."PROD_ID")

Note部分

除了计划之外,包还会在“Note”部分中显示说明,例如在查询优化期间使用了动态采样或对查询应用了星形转换。

例如,如果表 SALES 没有统计信息,则优化程序将使用动态抽样,计划显示将按如下方式报告(请参阅查询中的 '+note' 详细信息):



select plan_table_outputfrom table(dbms_xplan.display('plan_table',null,'basic +note'));
------------------------------------------ Id   Operation              Name   ------------------------------------------   0  SELECT STATEMENT                 1   HASH GROUP BY                   2    HASH JOIN                      3     TABLE ACCESS FULL   PRODUCTS   4     PARTITION RANGE ALL           5      TABLE ACCESS FULL  SALES  ------------------------------------------Note------ dynamic sampling used for this statement

绑定偷窥

查询优化器在生成执行计划时会考虑绑定变量值的值。它执行通常称为绑定偷窥的操作。请参阅关于绑定窥视及其对计划和 SQL 语句性能的影响的文章(https://blogs.oracle.com/optimizer/why-are-there-more-cursors-in-11g-for-my-query-containing-bind-variables)。如前所述,V$SQL_PLAN 中显示的计划考虑了绑定变量的值,而使用 EXPLAIN PLAN 显示的计划则没有。DBMS_XPLAN包可显示用于生成计划的绑定变量值,方法是给display_cursor()的format参数添加“+peeked_binds”。以下示例对此进行了说明:




variable pcat varchar2(50)exec :pcat := 'Women'
select PROD_CATEGORY, avg(amount_sold)from sales s, products pwhere p.PROD_ID = s.PROD_IDand prod_category != :pcatgroup by PROD_CATEGORY;select plan_table_outputfrom table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));
------------------------------------------ Id   Operation              Name   ------------------------------------------   0  SELECT STATEMENT                 1   HASH GROUP BY                   2    HASH JOIN                      3     TABLE ACCESS FULL   PRODUCTS   4     PARTITION RANGE ALL           5      TABLE ACCESS FULL  SALES  ------------------------------------------Peeked Binds (identified by position):--------------------------------------1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'
相关文章
|
29天前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构,并且通过合理使用可以显著改善查询速度及整体性能。需要注意,在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
96 8
|
6月前
|
SQL Oracle 关系型数据库
解决大小写、保留字与特殊字符问题!Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中,双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景,包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议,帮助开发者规避常见错误,提高代码可维护性和效率。
219 6
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
|
7月前
|
SQL 存储 关系型数据库
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
本文来自YashanDB官网,介绍如何处理Oracle客户端sql*plus中使用@@调用同级目录SQL脚本的场景。崖山数据库23.2.x.100已支持@@用法,但旧版本可通过Python脚本批量重写SQL文件,将@@替换为绝对路径。文章通过Oracle示例展示了具体用法,并提供Python脚本实现自动化处理,最后调整批处理脚本以适配YashanDB运行环境。
|
17天前
|
Oracle 关系型数据库 Linux
【赵渝强老师】Oracle数据库配置助手:DBCA
Oracle数据库配置助手(DBCA)是用于创建和配置Oracle数据库的工具,支持图形界面和静默执行模式。本文介绍了使用DBCA在Linux环境下创建数据库的完整步骤,包括选择数据库操作类型、配置存储与网络选项、设置管理密码等,并提供了界面截图与视频讲解,帮助用户快速掌握数据库创建流程。
197 93
|
3月前
|
存储 Oracle 关系型数据库
服务器数据恢复—光纤存储上oracle数据库数据恢复案例
一台光纤服务器存储上有16块FC硬盘,上层部署了Oracle数据库。服务器存储前面板2个硬盘指示灯显示异常,存储映射到linux操作系统上的卷挂载不上,业务中断。 通过storage manager查看存储状态,发现逻辑卷状态失败。再查看物理磁盘状态,发现其中一块盘报告“警告”,硬盘指示灯显示异常的2块盘报告“失败”。 将当前存储的完整日志状态备份下来,解析备份出来的存储日志并获得了关于逻辑卷结构的部分信息。
|
3月前
|
SQL Oracle 关系型数据库
比较MySQL和Oracle数据库系统,特别是在进行分页查询的方法上的不同
两者的性能差异将取决于数据量大小、索引优化、查询设计以及具体版本的数据库服务器。考虑硬件资源、数据库设计和具体需求对于实现优化的分页查询至关重要。开发者和数据库管理员需要根据自身使用的具体数据库系统版本和环境,选择最合适的分页机制,并进行必要的性能调优来满足应用需求。
127 11
|
3月前
|
Oracle 关系型数据库 数据库
数据库数据恢复—服务器异常断电导致Oracle数据库报错的数据恢复案例
Oracle数据库故障: 某公司一台服务器上部署Oracle数据库。服务器意外断电导致数据库报错,报错内容为“system01.dbf需要更多的恢复来保持一致性”。该Oracle数据库没有备份,仅有一些断断续续的归档日志。 Oracle数据库恢复流程: 1、检测数据库故障情况; 2、尝试挂起并修复数据库; 3、解析数据库文件; 4、导出并验证恢复的数据库文件。
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle RMAN的目录数据库
Oracle RMAN默认将备份元信息存储在控制文件中,但控制文件损坏或丢失会导致恢复失败,且备份增多会使控制文件无限增长。为解决这些问题,Oracle引入了RMAN目录数据库(Catalog Database),专门用于存储RMAN备份的元信息。使用目录数据库可提升备份管理效率,支持多数据库共享、长期备份历史记录存储,并可保存RMAN脚本。本文详细介绍了如何创建目录数据库、注册目标数据库及其操作步骤。

热门文章

最新文章

推荐镜像

更多