【原创】Oracle9i和10g中plan_table表的差异

实验环境：WinXP SP2

数据库版本：10.2.0.1

今天在做实验的时候发现，plan_table在不用的Oracle版本间是有一定的差异的。10g相对9i，在plan_table中添加了一些列（下面绿色的部分）

Oracle 9i的utlxplan.sql内容：

create table PLAN_TABLE (
        statement_id    varchar2(30),
        timestamp       date,
        remarks         varchar2(80),
        operation       varchar2(30),
        options         varchar2(255),
        object_node     varchar2(128),
        object_owner    varchar2(30),
        object_name     varchar2(30),
        object_instance numeric,
        object_type     varchar2(30),
        optimizer       varchar2(255),
        search_columns  number,
        id              numeric,
        parent_id       numeric,
        position        numeric,
        cost            numeric,
        cardinality     numeric,
        bytes           numeric,
        other_tag       varchar2(255),
        partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
        other           long,
        distribution    varchar2(30),
        cpu_cost        numeric,
        io_cost         numeric,
        temp_space      numeric,
        access_predicates varchar2(4000),
        filter_predicates  varchar2(4000),
);

Oracle 10g的utlxplan.sql内容：

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

删除原有10g的plan_table表

sys@TEST>drop table plan_table;

Table dropped.

创建9i下的plan_talbe表

sys@TEST>create table PLAN_TABLE (
  2          statement_id    varchar2(30),
  3          timestamp       date,
  4          remarks         varchar2(80),
  5          operation       varchar2(30),
  6          options         varchar2(255),
  7          object_node     varchar2(128),
  8          object_owner    varchar2(30),
  9          object_name     varchar2(30),
 10          object_instance numeric,
 11          object_type     varchar2(30),
 12          optimizer       varchar2(255),
 13          search_columns  number,
 14          id              numeric,
 15          parent_id       numeric,
 16          position        numeric,
 17          cost            numeric,
 18          cardinality     numeric,
 19          bytes           numeric,
 20          other_tag       varchar2(255),
 21          partition_start varchar2(255),
 22          partition_stop  varchar2(255),
 23          partition_id    numeric,
 24          other           long,
 25          distribution    varchar2(30),
 26          cpu_cost        numeric,
 27          io_cost         numeric,
 28          temp_space      numeric,
 29          access_predicates varchar2(4000),
 30          filter_predicates varchar2(4000));

Table created.

可以看出执行计划的显示没有问题，只是在Note中会提示使用的是旧版的plan_table（绿色的部分）

sys@TEST>select * from dual;

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)|
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)|
---------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

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

重新使用10g的plan_table表

sys@TEST>drop table plan_table;

Table dropped.

sys@TEST>@?/rdbms/admin/utlxplan.sql

Table created.

会发现比使用9i的plan_table多显示了每步操作的执行时间

sys@TEST>select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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