执行计划

查看执行计划有几种，下面我小结了一下

1、执行explain plan，然后查询plan_table

语法

>explain plan [set statement_id=id] [into plan_table]for sql_statement

>select * from table (dbms_xplan.display)

其中id是用来区分同一个sql的多个执行计划的，plan_table就是指定存放执行计划信息的表，默认就是plan_table（sys的表，做了一个公共同义词，utlxplan.sql可以创建）

display函数对应的参数

table_name

statement_id

format，主要是这个要注意，有如下

      basic，基本信息

      typical，默认值

      all，除了outline以外的所有

      advaced，所有

SQL>  select * from table (dbms_xplan.display_cursor('fjhbyrfs7gygq',null,'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */ * from emp

Plan hash value: 3956160932

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

13 rows selected.

 

SQL>  select * from table (dbms_xplan.display_cursor('fjhbyrfs7gygq'));
SQL_ID  fjhbyrfs7gygq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

17 rows selected.

 

SQL>  select * from table (dbms_xplan.display_cursor('fjhbyrfs7gygq',null,'typical -bytes'));
SQL_ID  fjhbyrfs7gygq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp

Plan hash value: 3956160932

------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
------------------------------------------------------------------

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

17 rows selected.

 

2、动态视图查看

v$sql_plan，执行计划的信息就是在这里了

v$sql_plan_statistics_all，这个更具体的分析到每一个操作，但由于这个是要在运行时收集的统计信息，会有额外的开销，需要使用statistics_level=all或/*+ gather_plan_statistics */

使用v$session,v$sql查找出sql_id，child_number，再select * from table (dbms_xplan.display_cursor('sql_id',child_number))

display_cursor

format：allstats，iostats，memstats..

SQL>  select * from table (dbms_xplan.display_cursor('39xyqn2q9x3d5',null,'allstats'));
SQL_ID  39xyqn2q9x3d5, child number 0
-------------------------------------
 select /*+ gather_plan_statistics */ * from scott.emp where empno=7839

Plan hash value: 2949544139

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7839)

18 rows selected.

 

3、awr和statspack

>select * from table (dbms_xplan.display_awr('sql_id'))，事实上是查询dba_hist_sql_plan表

而sp需要快照>=6，会在stats$sql_plan中存储，不过没有对应的dbms_xplan，需要导入到plan_table，再查询

 

4、10053 event

>alter session set events '10053 trace name context forever'

>alter session set events '10053 trace name context off'