蓝枫的个人空间

暂无签名

  • 博客访问: 178413
  • 博文数量: 87
  • 用 户 组: 普通用户
  • 注册时间: 2008-07-05 17:48
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(87)

文章存档

2011年(1)

2010年(58)

2009年(17)

2008年(11)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
Oracle 查看SQL的执行计划 2010-11-04 17:44:23

分类: Linux

 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查看执行计划;
其实utlxplp.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---

阅读(2224) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册