[转帖]使用dbms_xplan工具查看执行计划_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2721 | 回复: 0   主题: [转帖]使用dbms_xplan工具查看执行计划        下一篇 
kim
注册用户
等级:中校
经验:1729
发帖:222
精华:0
注册:2011-7-21
状态:离线
发送短消息息给kim 加好友    发送短消息息给kim 发消息
发表于: IP:您无权察看 2011-8-15 11:05:38 | [全部帖] [楼主帖] 楼主

使用dbms_xplan工具查看执行计划

9i有一个新的包 dbms_xplan,对查询plan_table表是一个很有用的工具,相对于以前写一个复杂的SQL语句,然后从plan_table看执行计划,不如调用 dbms_xplan 包,还可以显示格式,这个工具的使用也非常方便。

调用的语法类似

select * from table(dbms_xplan.display(format=>'BASIC'))


使用 TABLE() 操作符,或者 CAST 操作。

DISPLAY 函数有三个参数

TABLE_NAME        指出优化计划放在哪个表里面,默认是 PLAN_TABLE.

STATEMENT_ID      指的是plan table中的statement_id字段,默认是last ID 或者 NULL.

FORMAT            指的是显示的格式

FORMAT参数有三个可选值,原文如下

BASIC           It provides only the minimum amount of information, as in
case of the example above, similar to a query from
PLAN_TABLE directly.
TYPICAL           This is the default value. It provides a variety of the
information useful for understanding how the optimizer
works for this statement. For instance, in case of partitioned
table operation, the columns PARTITION_START,
PARTITION_STOP, PARTITION_ID, and
FILTER_PREDICATES are displayed in addition to COST
for that step, the number of rows expected to be retrieved,
and number of bytes those rows may have. This provides
the information to understand statements involving
partitioned objects.
ALL                This setting displays all the information displayed for the
BASIC and TYPICAL values, and also displays parallel
query operations and the related SQL statements, if those
are involved.
SERIAL             This setting gets results similar to those retrieved by the
TYPICAL setting, but the queries are explained serially even
if a parallel query will be used.


一般推荐使用typical 参数,把SQLPLUS的linesize 参数调整到至少 120

下面是测试步骤



用sys用户建立

PLUSTRACE 角色

$ORACLE_HOME\E:\oracle\ora92\sqlplus\admin\Plustrce.sql


二:把权限授予某个人

grant plustrace to mjs;


三:建立表

建表SQL脚本为在${ORACLE_HOME}/rdbms/admin/下的utlxplan.sql。

四:使用说明

1:我们用一个大表来举例说明如何使用

dw_for_bo@MJS.SENSKY.COM> select count(*) from tbl_fact_sublog;
COUNT(*)
----------
1757960


2:一个很平常的SQL语句

用常规方法如下分析执行计划

analyze table tbl_fact_sublog compute statistics;
set autotrace traceonly
select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;


结果如下:

1757960 rows selected.
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30264 Card=1757960 B
ytes=24611440)
1    0   SORT (GROUP BY) (Cost=30264 Card=1757960 Bytes=24611440)
2    1     TABLE ACCESS (FULL) OF 'TBL_FACT_SUBLOG' (Cost=2284 Card
=1757960 Bytes=24611440)
Statistics
----------------------------------------------------------
0  recursive calls
88  db block gets
23749  consistent gets
35593  physical reads
0  redo size
47677309  bytes sent via SQL*Net to client
1289670  bytes received via SQL*Net from client
117199  SQL*Net roundtrips to/from client
0  sorts (memory)
1  sorts (disk)
1757960  rows processed


用 dbms_xplan 方法分析

delete from plan_table;
explain plan for select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;
dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Id  | Operation            |  Name            | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |  1757K|    23M|       | 30264 |
|   1 |  SORT GROUP BY       |                  |  1757K|    23M|    80M| 30264 |
|   2 |   TABLE ACCESS FULL  | TBL_FACT_SUBLOG  |  1757K|    23M|       |  2284 |
---------------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.


可见,用dbms_xplan这个包可以发现排序的时候需要大概 80M的临时空间

dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display('PLAN_TABLE',null,'BASIC'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------
-------------------------------------------------
| Id  | Operation            |  Name            |
-------------------------------------------------
|   0 | SELECT STATEMENT     |                  |
|   1 |  SORT GROUP BY       |                  |
|   2 |   TABLE ACCESS FULL  | TBL_FACT_SUBLOG  |
-------------------------------------------------
8 rows selected.


用BASIC参数得到的信息就少多了。

另:一个有趣的现象,如果我删除统计信息,结果是什么样的?

analyze table tbl_fact_sublog delete statistics;
dw_for_bo@MJS.SENSKY.COM> set autotrace traceonly
dw_for_bo@MJS.SENSKY.COM> select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;
1757960 rows selected.
Execution Plan
---------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   SORT (GROUP BY)
2    1     TABLE ACCESS (FULL) OF 'TBL_FACT_SUBLOG'
Statistics
---------------------------------------------------------
0  recursive calls
88  db block gets
23749  consistent gets
35641  physical reads
0  redo size
47677309  bytes sent via SQL*Net to client
1289670  bytes received via SQL*Net from client
117199  SQL*Net roundtrips to/from client
0  sorts (memory)
1  sorts (disk)
1757960  rows processed


用 dbms_xplan 方法分析

delete from plan_table;
explain plan for select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;
dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation            |  Name            | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |       |       |       |
|   1 |  SORT GROUP BY       |                  |       |       |       |
|   2 |   TABLE ACCESS FULL  | TBL_FACT_SUBLOG  |       |       |       |
-------------------------------------------------------------------------
Note: rule based optimization
10 rows selected.


从提示看出,用的是基于规则的优化器,而且没有显示排序大概需要多少空间,看来还是经过分析后用CBO比较好

参考文章:otn相关例子

                   Donald K. Burleson等人所著的 《Oracle Space Management Handbook》




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论