dbms_xplan

测试表：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   


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 |  
|   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 |   
|   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 |   |   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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-600265/