查看SQL执行计划的方法及优劣

作者 | 胡佳伟:云和恩墨技术工程师,有多年数据库优化经验,在一线执行过多个包括通信、保险等行业的优化项目。

在 Oracle 的性能分析中,很多时候需要对 SQL 进行分析,而最重要的就是对执行计划的分析。在本次的分享中,我主要介绍常用的查看 SQL 执行计划的方法。

1

AWR SQL report

AWR 报告是对数据库性能诊断最常用的方式,同时 AWR SQL report 是展示AWR 负载信息中记录的关于 SQL 的相关信息的报告。

AWR SQL report 使用的脚本为 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql、awrsqrpi.sql。通常使用的脚本为 awrsqrpt.sql,该脚本可生成 SQL 在 AWR 信息内保存的执行计划等信息。而 awrsqrpi.sql 可以配合 awr 信息的导出与导入的功能,在被导入的库中生成相关的 AWR 报告。导入与导出的脚本同样在如上的路径里,为 awrextr、awrload,本次不进行演示。

使用方法:

与 AWR 报告生成方法一样,首先登陆数据库:

[oracle@localhost data]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed May 2 09:45:30 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Conneced to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> @?/rdbms/admin/awrsqrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 231940243 H 1 h -----当前的数据库 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: -----选择报告的格式(通常选择默认HTML) Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 231940243 1 H h localhost.lo ----生成报告的数据库 caldomain 4026143893 1 NETDB netdb1 ywdb1 3391174693 1 FWPTDB fwptdb1 fwptdb01 4026143893 2 NETDB netdb2 ywdb2 3391174693 2 FWPTDB fwptdb2 fwptdb02 Using 231940243 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Enter value for num_days: -----列出N天前到现在的SNAP_SHOT Listing the last 2 days of Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- h H 233 02 May 2018 09:45 1 234 02 May 2018 11:00 1 235 02 May 2018 12:00 1 236 02 May 2018 13:00 1 237 02 May 2018 14:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: Enter value for begin_snap: 233 ----填写开始时间点 Begin Snapshot Id specified: 233 Enter value for end_snap: 234 -----填写结束时间点 End Snapshot Id specified: 234 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: -----填写生成报告的SQL ID Enter value for sql_id: 0k8522rmdzg4k SQL ID specified: 0k8522rmdzg4k Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_233_234.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: ----填写报告名称

报告会生成在当前目录中。

与 AWR 报告采集相同,因为性能数据的记录是累计的值,所以两个 snap_id 间如果重启过数据库会报错。如果在选择的 snap_id 间没有该条 SQL 的信息同样会报错。

通常会用于问题诊断时对问题 SQL 的总体概况进行了解,因为可以查看 SQL 的多个执行计划及各执行计划的执行统计信息。

优势:可以在报告中查看执行计划的执行统计信息;

劣势:需要生成报告;缺少执行计划的谓词信息;需要权限较高。

2

Explain plan for

Explain plan for 通常是想要知道某条 SQL 文本在数据库中的执行计划会使什么样。使用该方法的同时需要使用另一个函数 dbms_xplan.display()。

使用方法是,在想要知道执行计划的 SQL 文本前加上 explain plan for ,然后执行。需要注意的是,该条 SQL 不会实际执行,只是生成执行计划,并保存到 PLAN_TABLE$ 中。然后用 dbms_xplan.display() 来查看。

使用方法:

拼写SQL:

explain plan for select * from a where owner='HR' ;

在会话中执行:

SQL> explain plan for select * from a where owner='HR' ; Explained. ----执行计划解析成功

查看执行计划:

SQL> select * from table(dbms_xplan.display)

需要注意的是,该方法是根据当前的 session 的参数来进行解析,如果业务程序与当前会话的设置存在不同,很可能执行计划不同。通常可用于验证自己的优化方法是否会有效。比如在文本中添加了 hint,可以通过该方法验证是否会奏效。

优势:不需实际执行SQL;使用方便。

劣势:可能存在与实际执行计划不符。

3

DBMS_XPLAN

DBMS_XPLAN 是 ORACLE 提供的工具包,如上边的方法中就使用了其中一个过程。接下来介绍其他几种常用方法。

DBMS_XPLAN.display_cursor

使用该函数可以查看在当前会话刚执行完毕的 SQL 的执行计划

使用方法:

SQL> select * from a where owner='HR'; HR A 89290 89290 TABLE 25-APR-17 25-APR-17 2017-04-25:10:02:56 VALID N N N 1 …… SQL>

查看执行计划:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); SQL_ID cncydzc624m60, child number 1 ------------------------------------- select * from a where owner='HR' Plan hash value: 3136622568

Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Outline Data /*+ BEGIN_OUTLINE_DATA …… */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='HR') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128], …… 51 rows selected.

在该方法中存在三个参数第一个参数为 sql_id,第二个为 cursor_child_no,第三个为 format。在如上的部分中前两个参数都为 null,默认情况下,会显示当前会话最后执行的执行计划,而 format 是选择执行计划的显示信息,通常使用显示最全的 ‘advanced’。

优势:执行计划信息全面;

劣势:存在与业务执行计划不同的可能;

DBMS_XPLAN.display_cursor(‘sql_id’,cursor_child_no,'advanced')

该方法可查看在数据库 shared pool 中还存在的执行计划信息。

使用方法:

SQL_ID cncydzc624m60, child number 0 ------------------------------------- select * from a where owner='HR' Plan hash value: 3136622568

Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA …… END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='HR') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128], …….

该方法使用简单,执行计划信息详细,通常用于问题刚刚发生,或正在发生,通过该方法可获取较为详细的执行计划信息。如果执行过后时间较长,执行计划存在被替出的可能。

优势:信息全面;使用方法简便。

劣势:可能存在时间过长已不在 shared pool 中。

dbms_xplan.display_awr('sql_id')

使用该方法可以查看 AWR 信息中的信息,与 AWR 报告查看的执行计划基本一致,但是无法看到执行计划的详细统计信息。

使用方法:

SQL> select * from table(dbms_xplan.display_awr('0k8522rmdzg4k')); SQL_ID 0k8522rmdzg4k -------------------- select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 Plan hash value: 2057665657 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | INLIST ITERATOR | | | | | | | 2 | INDEX RANGE SCAN| I_SYSAUTH1 | 2 | 16 | 2 (0)| 00:00:01 15 rows selected.

使用改方法看到的执行计划与 AWR 报告中的是相同的。但是因为没有时间限制,会显示所有的执行计划。如果存在多个执行计划,不好判断问题时段使用的是哪个执行计划。

优势:保存时间较长。

劣势:信息较少缺少谓词信息等。

4

AUTOTRACE

该方法可以在当前 session 开启,通过该方法可以查看在该 session 执行的所有 SQL 的执行计划及执行的消耗。

语法如下:

Set autotrace (off|on|traceonly) [explain] [statistics]

set autotrace on

这是最简单的打开方法,当打开后,所有执行过的 SQL 在执行结果后,会显示该条 SQL 的执行计划,和资源消耗信息。

使用方法:

SQL> set autotrace on SQL> select * from a where owner='HR'; HR A 89290 89290 TABLE 25-APR-17 25-APR-17 2017-04-25:10:02:56 VALID N N N 1 ……. Execution Plan

Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='HR') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 151 consistent gets 0 physical reads 0 redo size 59783 bytes sent via SQL*Net to client 942 bytes received via SQL*Net from client 40 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 576 rows processed

如上显示,在该条 SQL 执行后,首先返回了查询的数据,然后是该条 SQL 的执行计划,最后是执行的信息。

Set autotrace traceonly

使用该命令开启 autotrace 后,该条 SQL 执行完毕后不会返回具体的结果信息,只会返回结果的数量。同时接下来返回的与上个方法一至,返回执行计划和资源消耗信息。

使用方法:

SQL> set autotrace traceonly SQL> select * from a where owner='HR'; 576 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3136622568 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='HR') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 151 consistent gets 0 physical reads 0 redo size 59783 bytes sent via SQL*Net to client 942 bytes received via SQL*Net from client 40 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 576 rows processed SQL>

如上显示该条 SQL 执行完毕后,未显示所有的结果,而是只有返回的行数。接下来返回的内容与上个方法一至。对于该种打开方法,可以在 SQL 需要返回大量数据的情况下开启。

Set autotrace traceonly explain

使用该方法打开 autotrace,返回的内容里只有该条 SQL 的执行计划。

使用方法:

SQL> SQL> Set autotrace traceonly explain SQL> select * from a where owner='HR';

从如上的返回结果可以看到,只返回了该条 SQL 的执行计划。

Set autotrace traceonly statistics

使用该方法打开 autotrace,只会显示 SQL 的结果数量和资源的消耗量。

使用方法:

SQL> Set autotrace traceonly statistics SQL> select * from a where owner='HR'; 576 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 151 consistent gets 0 physical reads 0 redo size 59783 bytes sent via SQL*Net to client 942 bytes received via SQL*Net from client 40 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 576 rows processed set autotrace off

关闭 autotrace 的功能。

AUTOTACE方式的优劣

优势:使用方法简单开启后自动;信息全面

劣势:存在与业务执行计划不同的可能

10046事件

10046事件是在对复杂 SQL 的问题诊断时会使用的方法。使用 10046 事件可以明确看到执行计划在每一步的资源消耗。

使用方法:

SQL> oradebug setmypid -----设置生成trace的进程 Statement processed. SQL> oradebug event 10046 trace name context forever,level 12 --开启10046trace 12级常用 Statement processed. SQL> select * from a where owner='HR' -----执行SQL语句 …… SQL> oradebug tracefile_name -----查看trace生成路径 /u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121261.trc SQL> SQL> oradebug event 10046 trace name context off -----关闭10046 trace Statement processed.

在 trace 路径上使用 tkprof 格式化内容。

oracle@localhost Desktop]$ tkprof h_ora_121261.trc h_ora_121261_tkprof.trc TKPROF: Release 11.2.0.4.0 - Development on Wed May 2 20:37:14 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

打开格式化的文件即可查看详细的各个步骤的消耗。

在 SQL 优化的时候通常是要对其他正 session 中正在运行的 SQL 进行分析。在这种情况下,就需要对其他session开启10046 trace。

使用方法:

SQL> oradebug setospid 121265 -----设置生成trace的进程 Oracle pid: 44, Unix process pid: 121265, image: oracle@localhost.localdomain SQL> SQL> oradebug event 10046 trace name context forever,level 12 --开启10046trace 12级常用 Statement processed. SQL> oradebug tracefile_name -----查看trace生成路径 /u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121265.trc SQL> oradebug event 10046 trace name context off -----关闭10046 trace Statement processed.

在 trace 路径上使用 tkprof 格式化内容。

[oracle@localhostDesktop]$ttkprof /u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121265.trc /usr/oracle/Desktop/h_ora_121265_tkprof.trc TKPROF: Release 11.2.0.4.0 - Development on Wed May 2 20:55:15 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

如上为在其他 session 开启 10046,与上个不同点在于需要指定 process 进程号。可以通过 v$session 和 v$process 联合查看 session 对应的 process。

SELECT a.spid, b.sid FROM v$process a, v$session b WHERE a.addr= b.paddr AND b.sid = &sid

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-05-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JMCui

SQL优化二(SQL性能调优)

一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知...

4096
来自专栏数据和云

深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

37410
来自专栏码云1024

mysql数据库

4757
来自专栏散尽浮华

Python3出现“No module named 'MySQLdb'“问题-以及使用PyMySQL连接数据库

Python3 与 Django 连接数据库,出现了报错:Error loading MySQLdb module: No module named 'MySQ...

3.8K2
来自专栏逸鹏说道

解决session阻塞的问题

简介 对于数据库运维人员来说创建session或者查询时产生问题是常规情况,下面介绍一种很有效且不借助第三方工具的方式来解决类似问题。 最近开始接触运维...

3556
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

2907
来自专栏技术碎碎念

sql server 事务处理

事物处理 事务是SQL Server中的单个逻辑单元,一个事务内的所有SQL语句作为一个整体执行,要么全部执行,要么都不执行。 事务有4个属性,称为ACID(原...

4148
来自专栏沃趣科技

配置详解 | performance_schema全方位介绍

在上一篇 《初相识 | performance_schema全方位介绍》 中粗略介绍了如何配置与使用performance_schema,相信大家对perfor...

1.7K7
来自专栏智能大石头

XCode v8.11 重量级分表分库(无视海量数据)

XCode天生就有分表分库功能,设计于2005年! 历时9年,这是分表分库功能第一次针对性正式更新。 在XCode里面,分表分库非常简单,在操作数据(查询/更...

23210
来自专栏乐沙弥的世界

Percona XtraDB Cluster 的一些使用限制(PXC 5.7)

Percona XtraDB Cluster有众多的优秀特性,使得mysql集群得以轻松实现。但是不要忽略了它的一些限制。如果你无法接受,或者你的应用程序或数据...

1263

扫码关注云+社区

领取腾讯云代金券