rondon
rondon I'm a Oracle DBA(Database Administrator).

oracle获取SQL执行计划常用方法

前言

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跟踪事件。

comments powered by Disqus