| 本文所涉及实验内容均来自版本11.2.0.4，以下内容为个人整理，仅用于记录与交流，切记任何实验内容请勿在生产库上进行实验！请珍惜DBA职业生涯！转载注明出处。 |
在SQL优化的工作中，大部分的情况就是对SQL的执行计划进行调整，本编文章是我在回顾获取SQL执行计划时所整理，如有不足之处，欢迎指正。
●SQL_TRACE
●10046事件
●explain plan
●DBMS_XPLAN
●AUTOTRACE开关
1、SQL_TRACE
SQL_TRACE工具可以在会话级别和全局级别进行使用，在全局级别使用时会造成系统资源的过度消耗，请谨慎使用，会话级别的跟踪足以满足获取目标SQL执行计划的需求。
下面的实验是在当前会话级别设置跟踪。
SYS@tank> alter session set sql_trace=true;
Session altered.
SYS@tank> select * from scott.emp where mgr='7902';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
SYS@tank> alter session set events '10046 trace name context off';
Session altered.
SYS@tank> select * from v$diag_info where NAME='Default Trace File';
INST_ID NAME VALUE
---------- --------- --------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/tank/tank/trace/tank_ora_46538.trc
*** 2017-02-13 06:14:26.988
CLOSE #140260733475704:c=0,e=36,dep=0,type=0,tim=1486937666988561
=====================
PARSING IN CURSOR #140260733475704 len=40 dep=0 uid=0 oct=3 lid=0 tim=1486937666989008 hv=1742497264 ad='b8f3a058' sqlid='8rz
uygtmxstgh'
select * from scott.emp where mgr='7902'
END OF STMT
PARSE #140260733475704:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1491239009,tim=1486937666988985
EXEC #140260733475704:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1491239009,tim=1486937666989267
FETCH #140260733475704:c=3000,e=2080,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1491239009,tim=1486937666991546
FETCH #140260733475704:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1491239009,tim=1486937666993344
STAT #140260733475704 id=1 cnt=1 pid=0 pos=1 obj=87108 op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=199 us cost=2
size=87 card=1)'
STAT #140260733475704 id=2 cnt=1 pid=1 pos=1 obj=88613 op='INDEX RANGE SCAN INDX_MGR (cr=1 pr=0 pw=0 time=100 us cost=1 size=
0 card=1)'
可以发现以上信息阅读起来不是很方便，在这里使用tkprof命令对trc文件进行格式化再进行查看。
[oracle@tank ~]$ tkprof /u01/app/oracle/diag/rdbms/tank/tank/trace/tank_ora_46538.trc output.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Feb 13 06:17:29 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
查看格式化后的output.txt文件末端的信息即可查看目标SQL的执行计划及相关详细信息。
********************************************************************************
SQL ID: 8rzuygtmxstgh Plan Hash: 1491239009
select *
from
scott.emp where mgr='7902'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.04 0.04 18 108 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.05 0.05 18 114 0 3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=294 us cost=2 size=87 card=1)
1 1 1 INDEX RANGE SCAN INDX_MGR (cr=1 pr=0 pw=0 time=97 us cost=1 size=0 card=1)(object id 88613
)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.00 0.00
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 7.55 13.79
********************************************************************************
如果想跟踪其他用户进程，需用到dbms_system包来完成，此处不做演示。
2、10046事件
10046事件相对比其他的执行计划获取方式，这种方法所得到执行计划的内容更为详细，针对性较强，尤其是在SQL性能分析中帮助很大，所得到的执行计划中明确显示了目标SQL实际执行中每一个执行步骤所消耗的逻辑读、物理读以及所花费的时间。
在以下调试级别中Level 12使用频率较高，11g在此基础上增加了几个调试级别，各级别向下兼容，可自行学习。
●Level 1：跟踪SQL语句，包括解析、执行、提取、提交和回滚等。等价于启用标准的SQL_TRACE功能。
●Level 4：Level 1+绑定变量的详细信息。
●Level 8：Level 1+等待时间跟踪。
●Level 12：Level 4+ Level 8。
10046时间可以在全局进级别（修改参数文件）行设置也可以在会话级别进行设置，使用全局设置的情况不多，下面将演示的是在会话级别获取SQL的执行计划。
SYS@tank> alter session set events '10046 trace name context forever,level 12';
Session altered.
SYS@tank> select * from scott.emp where mgr='7902';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
SYS@tank> alter session set events '10046 trace name context off';
Session altered.
SYS@tank> select * from v$diag_info where NAME='Default Trace File';
INST_ID NAME VALUE
------------- --------- -------------------------------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/tank/tank/trace/tank_ora_43934.trc
同样使用tkprof命令对trc文件进行格式化再进行查看。
[oracle@tank ~]$ tkprof /u01/app/oracle/diag/rdbms/tank/tank/trace/tank_ora_43934.trc output.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Feb 13 05:45:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
查看output.txt格式化文件末端的信息即可查看目标SQL的执行计划及相关详细信息。
********************************************************************************
SQL ID: 8rzuygtmxstgh Plan Hash: 1491239009
select *
from
scott.emp where mgr='7902'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.04 0.04 18 108 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.05 0.04 18 112 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=342 us cost=2 size=87 card=1)
1 1 1 INDEX RANGE SCAN INDX_MGR (cr=1 pr=0 pw=0 time=96 us cost=1 size=0 card=1)(object id 88613
)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.00 0.00
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 7.55 13.79
********************************************************************************
3、explain plan
在执行explain plan命令时，Oracle 10g以上版本会将目标SQL产生的执行计划的具体执行步骤写入到PLAN_TABLE$中，然后通过查询语句“select * from table(dbms_xplan.display);”从PLAN_TABLE$中将执行步骤以格式化的方式显示出来，PLAN_TABLE$是会话级临时表，各个会话互不干扰，只能查看自己的执行SQL所产生的执行计划。
PL/SQL developer中F5快捷键（获取SQL执行计划）封装的就是explain plan。
SYS@tank> explain plan for select * from scott.emp where mgr='7902';
Explained.
SYS@tank> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1491239009
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_MGR | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGR"=7902)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
4、DBMS_XPLAN包
SYS@tank> select * from scott.emp where mgr='7902';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
执行目标SQL后紧跟着以下语句进行查询执行计划。
SYS@tank> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 8rzuygtmxstgh, child number 1
-------------------------------------
select * from scott.emp where mgr='7902'
Plan hash value: 1491239009
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_MGR | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."MGR"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGR"=7902)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "MGR"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
51 rows selected.
如果将第三个传入值“ADVANCED”替换成“ALL”，输出结果中将不显示Outline Data内容。
5、AUTOTRACE开关
使用AUTOTRACE开关出了获取目标SQL执行计划外，还能额外观察到目标SQL执行时所耗费的物理读、逻辑读、产生REDO的数量以及排序的数量等。
SYS@tank> set autotrace on
SYS@tank> select * from scott.emp where mgr='7902';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
Execution Plan
----------------------------------------------------------
Plan hash value: 1491239009
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX_MGR | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGR"=7902)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1025 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
AUTOTRACE开关有如下几种参数可以设置：
set autotrace on --三个部分：执行结果、执行计划、统计信息
set autotrace traceonly --两个部分：执行计划和统计信息
set autotrace traceonly explain --只看执行计划
set autotrace traceonly statistics --只看统计信息
以上5项内容是学习查看SQL的执行计划简单整理，后续会根据实际使用情况做进一步补充与修改，如有描述不妥，欢迎指正。
参考资料：《基于Oracle的SQL优化》-崔华 P85-P203
《深入解析Oracle：DBA入门、进阶与诊断案例》-盖国强 P479-P488
-The end-
Tank
20170220