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

640?wx_fmt=gif&wxfrom=5&wx_lazy=1&retryload=1


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


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


1AWR 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 的多个执行计划及各执行计划的执行统计信息。


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

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


2Explain 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)


640?wx_fmt=png


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


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

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


3DBMS_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


640?wx_fmt=png


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


640?wx_fmt=png


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 报告中的是相同的。但是因为没有时间限制,会显示所有的执行计划。如果存在多个执行计划,不好判断问题时段使用的是哪个执行计划。


优势:保存时间较长。

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


4AUTOTRACE


该方法可以在当前 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


640?wx_fmt=png


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.


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

640?wx_fmt=png


在 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.


640?wx_fmt=png


如上为在其他 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)回复关键字获取

‘2017DTC’,2017 DTC 大会 PPT

‘DBALIFE’,“DBA 的一天”海报

‘DBA04’,DBA 手记4 经典篇章电子书

‘RACV1’, RAC 系列课程视频及 PPT

‘122ARCH’,Oracle 12.2 体系结构图

‘2017OOW’,Oracle OpenWorld 资料

‘PRELECTION’,大讲堂讲师课程资料

640?wx_fmt=png


原文地址:https://www.cnblogs.com/hzcya1995/p/13312380.html