ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_xplan

dbms_xplan

原创 Linux操作系统 作者:tengrid 时间:2009-05-18 18:54:33 0 删除 编辑

测试表:create table tb1 as select dba_objects;

SQL>delete from plan_table;
SQL>explain plan for select owner,object_type,count(*) from tb1 group by owner,object_type;
SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 649272179

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 16752 |   458K|    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY     |      | 16752 |   458K|    24   (9)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TB1  | 16752 |   458K|    22   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement    <---与9i中不同,这里会抽样统计,dynamic sampling


SQL> set autotrace traceonly
SQL> /

78 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 649272179

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 16752 |   458K|    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY     |      | 16752 |   458K|    24   (9)| 00:00:01 |   <----与dbms_xplan.display的结果一致
|   2 |   TABLE ACCESS FULL| TB1  | 16752 |   458K|    22   (0)| 00:00:01 |
---------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
       3141  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         78  rows processed

SQL> analyze table tb1 compute statistics;
SQL> delete from plan_table;
SQL> explain plan for  select owner,object_type,count(*) from tb1 group by owner,object_type;
SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 649272179

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   217 |  3038 |    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |   217 |  3038 |    24   (9)| 00:00:01 |    <---hash group by?
|   2 |   TABLE ACCESS FULL| TB1  | 14405 |   196K|    22   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autotrace traceonly
SQL> select owner,object_type,count(*) from tb1 group by owner,object_type;

78 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 649272179

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   217 |  3038 |    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |   217 |  3038 |    24   (9)| 00:00:01 |   <---与autotrace的结果一致
|   2 |   TABLE ACCESS FULL| TB1  | 14405 |   196K|    22   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
       3141  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         78  rows processed

9i中的测试见http://www.itpub.net/showthread.php?threadid=304412

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-600265/,如需转载,请注明出处,否则将追究法律责任。

上一篇: datafile on nfs mount
下一篇: dml locks(dml_locks)
请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    378055