跟踪查看oracle 执行计划获取的几种方式

发一下牢骚和主题无关：

    1.set autotrace on 

    

相信种这法方是最简略的，也是最用常的一样法方，种这法方经用常到析分一条SQL，这里贴出语法，很简略

    每日一道理
心是一棵树，爱与希望的根须扎在土里，智慧与情感的枝叶招展在蓝天下。无论是岁月的风雨扑面而来，还是滚滚尘埃遮蔽了翠叶青枝，它总是静默地矗立在那里待等，并接受一切来临，既不倨傲，也不卑微。
　　心是一棵树，一个个故事被年轮携载；一回回驿动与飞鸟相约；一次次碰撞使它绵密柔韧；一幕幕经历造就了它博广的胸怀。心是一棵树，独木不成林。因此，树与树既独立又相联，心与心既相异又相亲。

SQL> set autotrace  

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

举例：

SQL> set autot on 

SQL> select count(*) from plan_table;

  COUNT(*)

----------

        68

Execution Plan

----------------------------------------------------------

Plan hash value: 1751138260

--------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |     1 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |             |     1 |            |          |

|   2 |   TABLE ACCESS FULL| PLAN_TABLE$ |    68 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

         27  recursive calls

          0  db block gets

         15  consistent gets

          0  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        487  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

主要看consistent gets和physical reads，consistent gets是存内耗消，physical reads是磁盘的耗消，位单就是据数块。其他指标为辅。如果不须要列出语句的结果，那么 set autotrace traceonly可即。

2.explain plan for 

 

举例：

SQL> explain plan for select * from book_info;

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3200443156

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |  1363 |   177K|     9   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| BOOK_INFO |  1363 |   177K|     9   (0)| 00:00:01 |

-------------------------------------------------------------------------------

当然以上这个是最简略的应用，explain是DML语句，并非式隐交提。

statment_id指定了须要为那些SQL语句供提执行划计的标识

id指定一个名字，分区执行划计表中的多个执行划计

table指定了划计表的表名，默许是plan_table

这里意注一点，在9i中plan_table是通普表，10g中是同义词，可以通过据数字典查看。

@?/rdbms/admin/utlxplan可以创立划计表

@?/rdbms/admin/utlxpls也可以浏览执行划计表，和面上调用dbms_xplan.display是一样的。

@?/rdbms/admin/utlxplp查看并行处理的息信。

不过这里看不到一致性读和物理读等，看不到IO的统计息信，不过这个在10g前当可以通过display_cursor可以看了。

这里还要意注变量的替换问题：

比如在一个PL/SQL中：select * from tab1 where name=p_value;

那么想要查看这个执行划计的话，千万不要用常量去替换，用select * from tab1 where name=:p_value可即。

通过statsment_id查看执行划计：

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

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1409354130

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     1 |   133 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BOOK_INFO            |     1 |   133 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_BOOK_BOOK_INFO_ID |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

 2 - access("ID"=10270)

SQL>  explain plan set statement_id='t1' for select * from book_info where id=20360;

SQL> select * from table(dbms_xplan.display(null,'t1'));

PLAN_TABLE_OUTPUT

Plan hash value: 1409354130

----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     1 |   133 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BOOK_INFO            |     1 |   133 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_BOOK_BOOK_INFO_ID |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

2 - access("ID"=20360)

关于format参数：

select * from  table(dbms_xplan.display(null,null,' basic'));

 

3.AWR

由于10g前当自动安装AWR集收息信，所以可以通过dbms_xplan包供提的display_awr函数来查看AWR抽取SQL的划计。

这里是通过sql_id参数传入的停止查看。

举例：

select * from table(dbms_xplan.display_awr(' xxxxxxxx'));

这里步初分析下，就不再深刻各个参数。

4.事件跟踪，比如10046,10053等

sql_trace

话会跟踪alter session set sql_trace=true;--跟踪前当话会，false为结束跟踪

当然也可以跟踪其他话会的

select sid,serial#,username from v$session.......根据自己求需查出sid,serial#,停止下一步跟踪

exec dbms_system.set_sql_trace_in_session(sid,serial#,true); --开始

exec dbms_system.set_sql_trace_in_session(sid,serial#,false) ; --结束

种这法方是不显示绑定息信和待等事件的，如果要面全息信，须要设置events品级，也就是10046事件

alter session set events '10046 trace name context  forever,level 12';

level 1：跟踪sql语句，包含析解、执行、提取、交提和回滚等。

level 4：包含变量的详细息信

level 8：包含待等事件

level 12：包含绑定变量与待等事件

alter session set evevts '10046 trace name context off'; 结束跟踪

还有就是dbms_system的set_ev进程也是可以的。另外再10g中有了dbms_monitor可以用来跟踪。

查出跟踪文件：

alter session set tracefile_identifier='xxxxxx';

设定标识，以便查找，默许路径在是udump上面，11G路径当然有些化变是在，$ORACLE_BASE\diag\rdbms\orcl\orcl\trace上面

也可以通过一下SQL直接定位

SELECT d.VALUE

|| '/'

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

|| '_ora_'

|| p.spid

|| '.trc'

AS "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;

或者

SET LINESIZE 100

COLUMN trace_file FORMAT A60

SELECT s.sid,

       s.serial#,

       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    

       '_ora_' || p.spid || '.trc' AS trace_file

FROM   v$session s,

       v$process p,

       v$parameter pa

WHERE  pa.name = 'user_dump_dest'

AND    s.paddr = p.addr

AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

找出文件，那么可以应用TKPROF查看了。TKPROF这里不再讲解

5.通过第三方工具toad PL/SQL Developer

这个相信基本都用过，比较方便PL/SQL Developer的F5，以及toad的database->monitor->session browser选项可以查看正在执行SQL的划计。

6.oradebug10046

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;

SQL> ORADEBUG TRACEFILE_NAME;      -- Display the current trace file. 

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

文章结束给大家分享下程序员的一些笑话语录： 有一天，一个男人穿越森林的时候，听到一个细微的声音叫住他。他低头一看，是一只青蛙。
“如果你亲我一下，我会变成一个美丽的公主哦。”男人一言不发，把青蛙捡起来，放入口袋。
“如果你亲我一下，我会变成一个美丽的公主哦。而且，我会告诉我遇到的每一个人，你是多么聪明和勇敢，你是我的英雄。”男人把青蛙拿出来，对着它微微一笑，又把它放回口袋。
“如果你亲我一下，我会变成一个美丽的公主，然后我愿意成为你的爱人一星期。”男人又把青蛙拿出来，对着它微微一笑，把它放回口袋。
“如果你亲我一下，我会变成一个美丽的公主，然后我愿意成为你的爱人一年，而且你可以对我做任何事。”再一次，男人把青蛙拿出来，对着它微微一笑，又把它放回口袋。
　　最后，青蛙无力地问：“我开出了这么好的条件，为什么你还不肯吻我？”男人说：“我是一个程序员，我可没时间和什么公主鬼混。不过，拥有一个会说话的青蛙，倒是蛮酷的。”