oracle 执行计划获取的几种方式

5g6932g9iw

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 * fromtable(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;