查看SQL执行计划的方法及优劣
jaywcjlove 2018-11-10
在 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 H1 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 InstanceHost
------------ -------- ------------ ------------ ------------
* 231940243 1 H hlocalhost.lo ----生成报告的数据库
caldomain
4026143893 1 NETDB netdb1ywdb1
3391174693 1 FWPTDB fwptdb1fwptdb01
4026143893 2 NETDB netdb2ywdb2
3391174693 2 FWPTDB fwptdb2fwptdb02
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 H233 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)
需要注意的是，该方法是根据当前的 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_IDcncydzc624m60, 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_IDcncydzc624m60, 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 报告中的是相同的。但是因为没有时间限制，会显示所有的执行计划。如果存在多个执行计划，不好判断问题时段使用的是哪个执行计划。
优势：保存时间较长。
劣势：信息较少缺少谓词信息等。
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
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: [email protected]
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
