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

今天药忘吃喽~ 2022-03-27 14:56 432阅读 0赞

发一下牢骚和主题无关：

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(\*)

\----------

Execution Plan

\----------------------------------------------------------

Plan hash value: 1751138260

\--------------------------------------------------------------------------

\--------------------------------------------------------------------------

| 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

![跟踪和查看][6597996654958250887.jpg]

举例：

SQL> explain plan for select \* from book\_info;

SQL> select \* from table(dbms\_xplan.display);

PLAN\_TABLE\_OUTPUT

\--------------------------------------------------------------------------------

Plan hash value: 3200443156

\-------------------------------------------------------------------------------

\-------------------------------------------------------------------------------

| 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

\----------------------------------------------------------------------------------------------------

\----------------------------------------------------------------------------------------------------

| 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

\----------------------------------------------------------------------------------------------------

\----------------------------------------------------------------------------------------------------

| 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'));

![跟踪和查看][2795046518754515207.jpg]

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;

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

[6597996654958250887.jpg]: http://img7.ph.126.net/YLgWoNOHL24T2GyNnNnFeg==/6597996654958250887.jpg
[2795046518754515207.jpg]: http://img7.ph.126.net/swQCKcIcp_LqdCvejgAQ1g==/2795046518754515207.jpg