查看执行计划

1：语句执行前查看预估执行计划

sqlplus 方式：

使用explain plan for 和table(dbms_xplan.display)来显示

 

SQL> explain plan for select * from GMS_IVC.IVC_STOCK_COST b where b.enterprise_group_code='CN' and b.company_code='BJ' and b.unit_price=1.17 and b.stock_date=to_date('20091208','yyyymmdd');

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    66 | 16498
|   1 |  TABLE ACCESS BY INDEX ROWID| IVC_STOCK_COST    |     1 |    66 | 16498
|   2 |   INDEX SKIP SCAN           | IVC_STOCK_COST_PK |  8143 |       |  9219
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

12 rows selected

 

plsql developer

直接F5就ok了

 

2：语句执行结束后查看实际执行计划状况

使用sqltrace

http://blog.youkuaiyun.com/yuzhenhuan01/archive/2010/05/21/5614706.aspx

 

使用10046

http://blog.youkuaiyun.com/yuzhenhuan01/archive/2010/08/24/5834838.aspx

 

 

3：查询正在执行的语句实际的执行计划

实际的执行计划主要与以下几个视图相关

v$sql_plan

这个是查看library cache的真实执行计划

v$sql_plan_statistics

这个是查看执行计划每一步运行时的统计信息，包括时间，数据量

默认是不开启统计的，需设置参数statistics_level=all,或者在sql语句添加gather_plan_statistics提示

v$sql_workarea

这个提供了sql运行时sql workarea内存的消耗

 

v$sql_plan_statistics_all

前三个视图的汇总视图

v$session

这个就是所有会话的视图了，基本查sql都从此处入手的

 

使用dbms_xplan.display_cursor查看执行计划

dbms_xplan.display_cursor(

sql_id in varchar2 default null,

child_number in number default null,

format in varchar2 default 'TYPICAL'

)

 

sql_id             就是以上几个视图里的sql_id

child_number 语句的子游标号，一般默认null输出所有

format            控制输出的内容，默认为TYPICAL

     basic         基本执行计划，没有度量值

     TYPICAL    默认

     serial        和typical比没有并行信息

     all             和TYPICAL比增加了语句过滤信息

 

测试实例

 

basic         

 

 SQL> select * from table(dbms_xplan.display_cursor('aza7nxb4hd5cn',null,'BASIC'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc
where companycode=:1  and empno=:2  and objectcode=:3  order by
entrytime desc
Plan hash value: 444965878
------------------------------------------
| Id  | Operation          | Name        |
------------------------------------------
|   0 | SELECT STATEMENT   |             |
|   1 |  SORT ORDER BY     |             |
|   2 |   TABLE ACCESS FULL| LOGUSERFUNC |
------------------------------------------

16 rows selected

 

TYPICAL    

 

SQL> select * from table(dbms_xplan.display_cursor('aza7nxb4hd5cn',null,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  aza7nxb4hd5cn, child number 1
-------------------------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc where
companycode=:1  and empno=:2  and objectcode=:3  order by entrytime desc
Plan hash value: 444965878
--------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       | 19029 (100)|
|   1 |  SORT ORDER BY     |             |     3 |    72 | 19029   (2)| 00:03:49
|*  2 |   TABLE ACCESS FULL| LOGUSERFUNC |     3 |    72 | 19028   (2)| 00:03:49
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EMPNO"=:2 AND "COMPANYCODE"=:1 AND "OBJECTCODE"=:3))

20 rows selected

 

serial       

 

SQL> select * from table(dbms_xplan.display_cursor('aza7nxb4hd5cn',null,'SERIAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  aza7nxb4hd5cn, child number 1
-------------------------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc where
companycode=:1  and empno=:2  and objectcode=:3  order by entrytime desc
Plan hash value: 444965878
--------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       | 19029 (100)|
|   1 |  SORT ORDER BY     |             |     3 |    72 | 19029   (2)| 00:03:49
|*  2 |   TABLE ACCESS FULL| LOGUSERFUNC |     3 |    72 | 19028   (2)| 00:03:49
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EMPNO"=:2 AND "COMPANYCODE"=:1 AND "OBJECTCODE"=:3))

20 rows selected

 

all             

 

SQL> select * from table(dbms_xplan.display_cursor('aza7nxb4hd5cn',null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  aza7nxb4hd5cn, child number 1
-------------------------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc where
companycode=:1  and empno=:2  and objectcode=:3  order by entrytime desc
Plan hash value: 444965878
--------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       | 19029 (100)|
|   1 |  SORT ORDER BY     |             |     3 |    72 | 19029   (2)| 00:03:49
|*  2 |   TABLE ACCESS FULL| LOGUSERFUNC |     3 |    72 | 19028   (2)| 00:03:49
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / LOGUSERFUNC@SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EMPNO"=:2 AND "COMPANYCODE"=:1 AND "OBJECTCODE"=:3))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) INTERNAL_FUNCTION("ENTRYTIME")[7],
       TO_CHAR(INTERNAL_FUNCTION("ENTRYTIME"),:SYS_B_0)[75]
   2 - "ENTRYTIME"[DATE,7]

33 rows selected

 

 

 对与format项来说，可以进一步控制显示的内容，通过一些特定参数

ROWS             开启或关闭这列－－行

BYTES                      字节

COST                消耗

PARTITION      分区信息

PARALLEL        并行信息

PREDICATE      条件信息

PROJECTION    列投影信息

ALIAS               alias信息

REMOTE           远程sql信息

NOTE               note信息

OUTLINE          outline信息

/*以下需/* + gather_plan_statistics*/的提示执行sql才能统计出*/

IOSTATS         io状况

MEMSTATS      如果内存pga手工管理，则会出内容

ALLSTATS       显示上面俩内容

 

测试实例：

SQL> select * from table(dbms_xplan.display_cursor('aza7nxb4hd5cn',null,'ALL,-ROWS,+NOTE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  aza7nxb4hd5cn, child number 1
-------------------------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc
where companycode=:1  and empno=:2  and objectcode=:3  order by
entrytime desc
Plan hash value: 444965878
--------------------------------------------------------------------------
| Id  | Operation          | Name        | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       | 19029 (100)|          |
|   1 |  SORT ORDER BY     |             |    72 | 19029   (2)| 00:03:49 |
|*  2 |   TABLE ACCESS FULL| LOGUSERFUNC |    72 | 19028   (2)| 00:03:49 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - SEL$1 / LOGUSERFUNC@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EMPNO"=:2 AND "COMPANYCODE"=:1 AND "OBJECTCODE"=:3))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) INTERNAL_FUNCTION("ENTRYTIME")[7],
       TO_CHAR(INTERNAL_FUNCTION("ENTRYTIME"),:SYS_B_0)[75]
   2 - "ENTRYTIME"[DATE,7]

34 rows selected

 

 

使用awr的方式得到计划

dbms_xplan.display_awr(

sql_id in varchar2,

plan_hash_value in number default null,

db_id in number default null,

format in varchar2 default 'TYPICAL'

)

 

测试用例：

 SQL> select * from table(dbms_xplan.display_awr('aza7nxb4hd5cn'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID aza7nxb4hd5cn
--------------------
select to_char(entrytime,:"SYS_B_0") as lastlogin from loguserfunc where
companycode=:1  and empno=:2  and objectcode=:3  order by entrytime desc
Plan hash value: 444965878
--------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       | 17451 (100)|
|   1 |  SORT ORDER BY     |             |     6 |   150 | 17451   (2)| 00:03:30
|   2 |   TABLE ACCESS FULL| LOGUSERFUNC |     6 |   150 | 17450   (2)| 00:03:30
--------------------------------------------------------------------------------

15 rows selected