查看: 28210|回复: 22

[Tips] 使用dbms_xplan工具查看执行计划

[复制链接]
论坛徽章:
8
授权会员
日期:2005-10-30 17:05:33会员2006贡献徽章
日期:2006-04-17 13:46:34会员2007贡献徽章
日期:2007-09-26 18:42:102011新春纪念徽章
日期:2011-02-18 11:42:49ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32兰博基尼
日期:2014-01-31 14:56:26优秀写手
日期:2015-01-08 06:00:14优秀写手
日期:2015-02-12 06:00:15
发表于 2004-12-23 15:05 | 显示全部楼层 |阅读模式
使用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》
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
发表于 2004-12-23 16:55 | 显示全部楼层
这是经常做的一件事情了,只做分析

set  pagesize 0
set  linesize 150
SQL> explain  plan  for  select *
  2  from
  3   nirvana.sfa_repeat_log  where account_id = :1   order by  gmt_created desc
  4  ;

Explained.

SQL> @?/rdbms/admin/utlxplp;

----------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |     2 |   200 |    15 |
|   1 |  SORT ORDER BY               |                         |     2 |   200 |    15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SFA_REPEAT_LOG          |     2 |   200 |     6 |
|*  3 |    INDEX RANGE SCAN          | SFA_REPEAT_LOG_AID_IND  |     2 |       |     3 |
----------------------------------------------------------------------------------------

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

   3 - access("SFA_REPEAT_LOG"."ACCOUNT_ID"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.


[oracle@ocn1 admin]$ pwd
/opt/oracle/products/9.2.0/rdbms/admin
[oracle@ocn1 admin]$ more  utlxplp.sql
Rem
Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
Rem
Rem utlxplp.sql
Rem
Rem Copyright (c) 1998, 2002, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      utlxplp.sql - UTiLity eXPLain Parallel plans
Rem
Rem    DESCRIPTION
Rem      script utility to display the explain plan of the last explain plan
Rem      command. Display also Parallel Query information if the plan happens to
Rem      run parallel
Rem
Rem    NOTES
Rem      Assume that the table PLAN_TABLE has been created. The script
Rem      utlxplan.sql should be used to create that table
Rem
Rem      With SQL*plus, it is recomended to set linesize and pagesize before
Rem      running this script. For example:
Rem         set linesize 130
Rem         set pagesize 0
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bdagevil    01/23/02 - rewrite with new dbms_xplan package
Rem    bdagevil    04/05/01 - include CPU cost
Rem    bdagevil    02/27/01 - increase Name column
Rem    jihuang     06/14/00 - change order by to order siblings by.
Rem    jihuang     05/10/00 - include plan info for recursive SQL in LE row source
Rem    bdagevil    01/05/00 - make deterministic with order-by
Rem    bdagevil    05/07/98 - Explain plan script for parallel plans           
Rem    bdagevil    05/07/98 - Created
Rem

set markup html preformat on

Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Use default mode which will display only relevant information
Rem
select * from table(dbms_xplan.display());
[oracle@ocn1 admin]$

使用道具 举报

回复
论坛徽章:
2
授权会员
日期:2005-10-30 17:05:33会员2006贡献徽章
日期:2006-04-17 13:46:34
发表于 2004-12-23 20:13 | 显示全部楼层
要是楼主能把10G中的也整合进来就好了

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
66
ITPUB元老
日期:2005-07-16 18:49:11授权会员
日期:2005-10-30 17:05:33ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44现任管理团队成员
日期:2011-05-07 01:45:08版主3段
日期:2012-05-15 15:24:11
发表于 2004-12-24 10:57 | 显示全部楼层
explain plan for ....
select * from table(dbms_xplan.display())
在调试bind var的SQL是特合适的选择.从来没有注意到还可以显示排序需要的内存。

使用道具 举报

回复
论坛徽章:
3
授权会员
日期:2006-04-18 13:25:09生肖徽章2007版:猴
日期:2009-02-04 17:50:05ITPUB学员
日期:2011-08-03 10:55:36
发表于 2004-12-24 16:17 | 显示全部楼层
SQL> explain plan for
  2  select * from tb1;

已解释。

SQL> @E:\oracle\ora92\rdbms\admin\utlxplp;

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------


--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | TB1         |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization

已选择9行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'




Statistics
----------------------------------------------------------
       2170  recursive calls
         23  db block gets
        678  consistent gets
         37  physical reads
       4308  redo size
        897  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         63  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> analyze table tb1 compute statistics;

表已分析。
SQL> explain plan for
  2  select * from tb1;

已解释。

SQL> @E:\oracle\ora92\rdbms\admin\utlxplp;

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------


--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1000 | 11000 |     2 |
|   1 |  TABLE ACCESS FULL   | TB1         |  1000 | 11000 |     2 |
--------------------------------------------------------------------

Note: cpu costing is off

已选择9行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'




Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        892  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

请问:
Note: cpu costing is off是什么意思?

使用道具 举报

回复
论坛徽章:
4
授权会员
日期:2005-10-30 17:05:33会员2006贡献徽章
日期:2006-04-17 13:46:34ITPUB元老
日期:2007-05-15 19:49:36秀才
日期:2016-01-13 12:14:26
发表于 2004-12-27 11:50 | 显示全部楼层
对于cpu的cost统计没有给出。

使用道具 举报

回复
论坛徽章:
0
发表于 2004-12-29 10:51 | 显示全部楼层
o

使用道具 举报

回复
论坛徽章:
62
马上加薪
日期:2014-02-19 11:55:142011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:田径
日期:2011-02-17 18:03:352011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:012010广州亚运会纪念徽章:三项全能
日期:2010-11-15 13:36:51ITPUB9周年纪念徽章
日期:2010-10-08 09:34:02
发表于 2004-12-31 10:25 | 显示全部楼层
这个东西很好勇,我经常用。

使用道具 举报

回复
论坛徽章:
47
蒙奇·D·路飞
日期:2017-03-27 08:04:23马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11一汽
日期:2013-09-01 20:46:27复活蛋
日期:2013-03-13 07:55:232013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:322012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20
发表于 2005-1-2 11:39 | 显示全部楼层
I want to add some comments that may or may not be relevant.

(1) One issue of Oracle Magazine, the 1st issue of 2004 I think, AskTom column talks about TempSpc in xplan output. But at least one time I couldn't get numbers for that, even if the query is sure to use temp space. I didn't spend much time finding out why.

(2) In 10g, you don't need to create plan_table in your schema to explain plan. It uses the table plan_table$ belonging to SYS through a public synonym. The table is a session duration-based global temporary table. Makes perfect sense!

(3) Recently somebody on news:comp.databases.oracle.server asked how to check syntax of long SQL code without running it (which would take many hours). I suggested using explain plan. I.e., prepend EXPLAIN PLAN FOR to each SQL statement. If running that .sql script shows no errors, all syntax is good.

Any comments and corrections are welcome.

Yong Huang

使用道具 举报

回复
论坛徽章:
31
授权会员
日期:2005-10-30 17:05:332012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:23马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:09:23
发表于 2005-1-4 21:12 | 显示全部楼层
比较好用的东西
我也常用

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表