Oracle 查看SQL的执行计划

  Oracle 查看SQL的执行计划

1、直接产生执行计划
SQL> set autotrace on explain
SQL> select * from dual;

D
-
X

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autotrace off

这样执行方便，但是当遇到执行时间长的SQL就变得不太现实，它是先产生结果再生成执行计划的。

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN --------- AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS ------ 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY -----------同set autotrace on，但是不显示查询输出

在10G之前的版本中，需要单独创建PLAN_TABLE并授予权限,10g中自动创建PLAN_TABLE$不再需要这一步骤。
10g之前初始化PLAN_TABLE需要创建的步骤：
SQL> @?/rdbms/admin/utlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;

在10G中，用到的是数据字典PLAN_TABLE$而不是PLAN_TABLE表，
SQL> SET AUTOTRACE TRACEONLY;
SQL> select * from plan_table;


Execution Plan
----------------------------------------------------------
Plan hash value: 103984305

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     5 | 55405 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |     5 | 55405 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement 

2、利用explain plan for语句产生执行计划
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>
这样可以直接产生执行计划，没有产生SQL结果，应该相当于SQL SERVER中的预执行计划。 

另一种方式就是利用$ORACLE_HOME/rdbms/admin目录下的utlxplp.sql查看执行计划；

其实u tlxplp.sql文件中就存在一个语句： select * from table(dbms_xplan.display);

例如：

SQL> explain plan for select count(*) from dual;

Explained.

SQL> @?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

Plan hash value: 3910148636

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

9 rows selected.

SQL>

3、启用sql_trace跟踪所有后台进程活动

查看全局SQL_TRACE参数：

SQL> show parameter sql_trace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_trace                            boolean     FALSE

全局参数要求在参数文件中设定数据库重启生效或通过alter system命令设定：
SQL> alter system set sql_trace=true scope=both;

System altered.

SQL> show parameter sql_trace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_trace                            boolean     TRUE
SQL>

启用会话级的SQL_TRACE跟踪

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dual;

 COUNT(*)
----------
        1

SQL> alter session set sql_trace=false;

Session altered. 

对其他用户进行跟踪

SQL> select sid,serial#,username from v$session where username='MYUSER';

      SID    SERIAL# USERNAME
---------- ---------- ------------------------------
      143          5 MYUSER

SQL> exec dbms_system.set_SQL_TRACE_in_session(143,5,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_SQL_TRACE_in_session(143,5,false);

PL/SQL procedure successfully completed. 

最后可以得用ORACLE工具tkprof格式化跟踪文件即可。

4、使用10046事业进行查询

10046事件级别：    
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE    
Lv4 - Level 1 + 绑定值(bind values)    
Lv8 - Level 1 + 等待事件跟踪    
Lv12 - Level 1 + Level 4 + Level 8 

全局设定格式，在参数文件加入：

EVENT='10046 trace name context forever,level 12';

当前session的设定：
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select * from dual;

D
-
X

SQL> alter session set events '10046 trace name context off';

Session altered. 

跟踪其他用户：
SQL> select sid,serial#,username from v$session where username='MYUSER';

      SID    SERIAL# USERNAME
---------- ---------- ------------------------------
      142         71 MYUSER
      143          5 MYUSER

SQL> exec dbms_system.set_ev(143,5,10046,8,'A');

PL/SQL procedure successfully completed.

SQL> select * from dual;

D
-
X

SQL> exec dbms_system.set_ev(143,5,10046,0,'A');

PL/SQL procedure successfully completed. 

5、使用tkprof格式化跟踪文件

查看当前session的跟踪文件：

SELECT      d   .VALUE

      ||   '\'

      ||   LOWER   (RTRIM   (   i   .INSTANCE,   CHR   (   0   )))

      ||   '_ora_'

      ||   p   .   spid

      ||   '.trc'   trace_file_name

    FROM   (SELECT   p   .   spid

            FROM   v$mystat   m,   v$session s   ,   v$process   p

           WHERE   m.   statistic#   =   1   AND   s   .SID   =   m.SID   AND   p   .   addr   =   s   .   paddr   )   p   ,

         (SELECT   t   .INSTANCE

            FROM   v$thread   t   ,   v$parameter v

           WHERE   v   .NAME   =   'thread'

             AND   (   v   .VALUE   =   0   OR   t   .   thread#   =   TO_NUMBER   (   v   .VALUE)))   i   ,

         (SELECT   VALUE

            FROM   v$parameter

           WHERE   NAME   =   'user_dump_dest'   )   d   ;

--当前会话

SQL> SELECT    d.VALUE
 2        || '\'
 3        || LOWER (RTRIM (i.INSTANCE, CHR (0)))
 4        || '_ora_'
 5        || p.spid
 6        || '.trc' trace_file_name
 7   FROM (SELECT p.spid
 8           FROM v$mystat m, v$session s, v$process p
 9          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10        (SELECT t.INSTANCE
11           FROM v$thread t, v$parameter v
12          WHERE v.NAME = 'thread'
13            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14        (SELECT VALUE
15           FROM v$parameter
16          WHERE NAME = 'user_dump_dest') d;

TRACE_FILE_NAME
------------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3868.trc 

查看其他session的跟踪文件

SELECT      d   .VALUE

      ||   '\'

      ||   LOWER   (RTRIM   (   i   .INSTANCE,   CHR   (   0   )))

      ||   '_ora_'

      ||   p   .   spid

      ||   '.trc'   trace_file_name

    FROM   (SELECT   p   .   spid

            FROM   v$session s   ,   v$process   p

           WHERE   s   .SID   =   '143'   AND   s   .   serial#   =   '5'   AND   p   .   addr   =   s   .   paddr   )   p   ,

         (SELECT   t   .INSTANCE

            FROM   v$thread   t   ,   v$parameter v

           WHERE   v   .NAME   =   'thread'

             AND   (   v   .VALUE   =   0   OR   t   .   thread#   =   TO_NUMBER   (   v   .VALUE)))   i   ,

         (SELECT   VALUE

            FROM   v$parameter

           WHERE   NAME   =   'user_dump_dest'   )   d   ;

--其他session

SQL> SELECT    d .VALUE
 2        || '\'
 3        || LOWER (RTRIM ( i .INSTANCE, CHR ( 0 )))
 4        || '_ora_'
 5        || p . spid
 6        || '.trc' trace_file_name
 7    FROM (SELECT p . spid
 8            FROM v$session s , v$process p
 9           WHERE s .SID = '143' AND s . serial# = '5' AND p . addr = s . paddr ) p ,
10         (SELECT t .INSTANCE
11            FROM v$thread t , v$parameter v
12           WHERE v .NAME = 'thread'
13             AND ( v .VALUE = 0 OR t . thread# = TO_NUMBER ( v .VALUE))) i ,
14         (SELECT VALUE
15            FROM v$parameter
16           WHERE NAME = 'user_dump_dest' ) d ;

TRACE_FILE_NAME
---------------------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3812.trc 

5、利用tkprof工具格式化跟踪文件

SQL> host tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3868.trc F:\test\3868.txt

TKPROF: Release 10.2.0.4.0 - Production on Fri Sep 10 11:18:10 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



SQL> host tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3812.trc F:\test\3812.txt

TKPROF: Release 10.2.0.4.0 - Production on Fri Sep 10 11:18:37 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

--The End---

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9932141/viewspace-677386/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9932141/viewspace-677386/