前言
oracle执行计划其实就是oracle内部的机器级代码，决定如何访问存储器来得到想要的结果，主要内容有：访问方式与访问顺序。
基于Centos6.7下的单机Oracle11.2.0.4数据库环境
示例SQL为SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800;
使用explain plan for
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> SET LINESIZE 999 PAGESIZE 999 LONG 999
SQL> EXPLAIN PLAN FOR SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 54 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 54 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7800)
优点
不需要真实执行业务SQL，可快速获取SQL执行计划。
缺点
无法判断表被访问多少次、处理多少行、逻辑读、物理读情况
使用set autotrace on
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> SET LINESIZE 999 PAGESIZE 999 LONG 999
SQL> SET AUTOTRACE ON
SQL> SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800;
EMPNO ENAME JOB
---------- ------------------------------ ---------------------------
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
执行计划
----------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 54 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 54 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7800)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
829 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)
6 rows processed
优点
可以体现大致的逻辑读、物理读、递归调用情况
缺点
必须等业务SQL执行完毕后才能获取到执行计划；无法判断表被访问多少次
使用statistics_level
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> alter session set statistics_level=all;
SQL> SET LINESIZE 999 PAGESIZE 999 LONG 999
SQL> SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800;
EMPNO ENAME JOB
---------- ------------------------------ ---------------------------
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 7zhmdhvvn4wu2, child number 1
-------------------------------------
SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800
Plan hash value: 169057108
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 | 6 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | 3 | 6 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7800)
优点
从Starts分析可以明确知道表被访问多少次；从E-ROWS和A-ROWS对比获取到预估的行数和实际的行数，某些特定业务可以通过此种方式数据总数；
缺点
必须等业务SQL执行完毕后才能获取到执行计划；无法获取递归调用的次数与物理读的数值；
使用10046事件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='dba';
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever ,level 12';
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE='UNLIMITED';
SQL> SELECT EMPNO, ENAME, JOB FROM SCOTT.EMP WHERE EMPNO > 7800;
EMPNO ENAME JOB
---------- ------------------------------ ---------------------------
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
SQL> SELECT DISTINCT(M.SID) ,P.PID,P.TRACEFILE FROM V$MYSTAT M,V$SESSION S ,V$PROCESS P WHERE M.SID=S.SID AND S.PADDR = P.ADDR;
SID PID TRACEFILE
---------- ---------- ---------------------------------------------------------
1012 28 /u01/app/diag/rdbms/orcl/orcl/trace/orcl_ora_8820_dba.trc
[oracle@dba1 ~]$ tkprof /u01/app/diag/rdbms/orcl/orcl/trace/orcl_ora_8820_dba.trc output=/home/oracle/dba_10046
[oracle@dba1 ~]$ ls -l /home/oracle/dba_10046.prf
优点
可以获取到SQL解析事件、执行时间、等待事件、产生行数、物理读；可以跟踪整个SQL执行
缺点
操作步骤多;无法获取表被访问次数;执行计划中的谓语部分不能清晰显示
使用awrsqrpt.sql
1
2
SQL> @?/rdbms/admin/awrsqrpt.sql
依次输入report_type、num_days、begin_snap、end_snap、sql_id就会生成一份html报告。
优点
可以获取到总的消耗CPU事件、执行次数、逻辑读、物理读等.
缺点
操作步骤多，必须知道SQL的SQL_ID与两个快点间隔是否存在此SQL运行.
总结
不同的应用场景下选择不同的方法，可快速获取到SQL执行计划。当然也可以使用PL/SQL工具选中SQL语句后按下F5就可以大致观察执行计划了。这种方式是最为简单的。
如果SQL执行耗时很长才会返回结果，这时候看执行计划考虑用方法explain plan for。如果想看SQL多条执行计划情况，可以考虑使用awrsqrpt.sql的方式。如果想获取到表的访问次数，只能使用statistics_level，如果SQL中含有多函数，函数中套有SQL等多层递归调用，想获得准确结果，只能使用方法10046跟踪事件。