» 9i和10g的plan table OracleBlog.cn -天堂向左 DBA向右

9i和10g的plan table

· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/explan-table-between-9i-and-10g/

在oracle 10g中,plan table的基表已经改变，在9i的时候，还是一个table类型，在10g的时候，已经是temporary table类型了。

在10g中，如果通过dblink访问9i的库，那么explain table将无法显示。会报错：

SQL> explain plan for
2 select count(*) from tab1@dblink_test;

Explained.

Elapsed: 00:00:00.79
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

我们去找9i下的explan table：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select object_name,object_type,owner from dba_objects where object_name='PLAN_TABLE'
2 /

OBJECT_NAME OBJECT_TYPE OWNER
------------------------------ ------------------ ------------------------------
PLAN_TABLE TABLE SYS
PLAN_TABLE SYNONYM PUBLIC
PLAN_TABLE TABLE MISC
PLAN_TABLE SYNONYM MISCTOOLS

SQL> select dbms_metadata.get_ddl('SYNONYM','PLAN_TABLE','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('SYNONYM','PLAN_TABLE','PUBLIC')
--------------------------------------------------------------------------------

CREATE PUBLIC SYNONYM "PLAN_TABLE" FOR "SYS"."PLAN_TABLE"

SQL> select dbms_metadata.get_ddl('TABLE','PLAN_TABLE','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLE','SYS')
--------------------------------------------------------------------------------

CREATE TABLE "SYS"."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" NUMBER(*,0),
   "OBJECT_TYPE" VARCHAR2(30),
   "OPTIMIZER" VARCHAR2(255),
   "SEARCH_COLUMNS" NUMBER,
   "ID" NUMBER(*,0),
   "PARENT_ID" NUMBER(*,0),
   "POSITION" NUMBER(*,0),
   "COST" NUMBER(*,0),
   "CARDINALITY" NUMBER(*,0),
   "BYTES" NUMBER(*,0),
   "OTHER_TAG" VARCHAR2(255),
   "PARTITION_START" VARCHAR2(255),
   "PARTITION_STOP" VARCHAR2(255),
   "PARTITION_ID" NUMBER(*,0),
   "OTHER" LONG,
   "DISTRIBUTION" VARCHAR2(30),
   "CPU_COST" NUMBER(*,0),
   "IO_COST" NUMBER(*,0),
   "TEMP_SPACE" NUMBER(*,0),
   "ACCESS_PREDICATES" VARCHAR2(4000),
   "FILTER_PREDICATES" VARCHAR2(4000)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

SQL>

而在10g中：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

SQL> l
1* select object_name,object_type,owner from dba_objects where object_name='PLAN_TABLE'
SQL> /

OBJECT_NAME OBJECT_TYPE OWNER
------------------------------ ------------------- ------------------------------
PLAN_TABLE SYNONYM PUBLIC

SQL>
SQL> select dbms_metadata.get_ddl('SYNONYM','PLAN_TABLE','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('SYNONYM','PLAN_TABLE','PUBLIC')
--------------------------------------------------------------------------------

CREATE OR REPLACE PUBLIC SYNONYM "PLAN_TABLE" FOR "SYS"."PLAN_TABLE$"

SQL> select dbms_metadata.get_ddl('TABLE','PLAN_TABLE$','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLE$','SYS')
--------------------------------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE "SYS"."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" NUMBER(*,0),
   "OBJECT_TYPE" VARCHAR2(30),
   "OPTIMIZER" VARCHAR2(255),
   "SEARCH_COLUMNS" NUMBER,
   "ID" NUMBER(*,0),
   "PARENT_ID" NUMBER(*,0),
   "DEPTH" NUMBER(*,0),
   "POSITION" NUMBER(*,0),
   "COST" NUMBER(*,0),
   "CARDINALITY" NUMBER(*,0),
   "BYTES" NUMBER(*,0),
   "OTHER_TAG" VARCHAR2(255),
   "PARTITION_START" VARCHAR2(255),
   "PARTITION_STOP" VARCHAR2(255),
   "PARTITION_ID" NUMBER(*,0),
   "OTHER" LONG,
   "OTHER_XML" CLOB,
   "DISTRIBUTION" VARCHAR2(30),
   "CPU_COST" NUMBER(*,0),
   "IO_COST" NUMBER(*,0),
   "TEMP_SPACE" NUMBER(*,0),
   "ACCESS_PREDICATES" VARCHAR2(4000),
   "FILTER_PREDICATES" VARCHAR2(4000),
   "PROJECTION" VARCHAR2(4000),
   "TIME" NUMBER(*,0),
   "QBLOCK_NAME" VARCHAR2(30)
   ) ON COMMIT PRESERVE ROWS

SQL>

我们看到2个表的定义已经发生了差别，字段也发生了变化，且9i中还是使用table，10g中是用temporary table了。而在9i中，我们还是需要手工创建plan_table，在10g中plan_table在数据库创建的时候，已经包含在catproc脚本中。他是通过catplan脚本在实现的。而在9i中，手工创建，是通过utlxplan脚本来实现。

在10g中，也有utlxplan脚本，也是创建table，这个在9i中一样，但是创建的plan_table的结构已然不同了。上面我们说的2个已经显示了9i的plan_table的定义和10g的plan_table的定义，前者通过utlxplan脚本创建，后者在db创建时，通过catplan脚本创建。而在10g中，还保留了utlxplan脚本。他的建表结构如下：

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
);

也是创建了table类型的plan table。但是这个explain table无法解析9i的通过dblink的执行计划，如果需要，我们还是手工将9i的utlxplan稍作修改，然后在执行：

D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>cat utlxplan_for_9i.sql
rem
rem $Header: utlxplan.sql 29-oct-2001.20:28:58 mzait Exp $ xplainpl.sql
rem
Rem Copyright (c) 1988, 2001, Oracle Corporation. All rights reserved.
Rem NAME
REM UTLXPLAN.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem mzait 10/26/01 - add keys and filter predicates to the plan table
Rem ddas 05/05/00 - increase length of options column
Rem ddas 04/17/00 - add CPU, I/O cost, temp_space columns
Rem mzait 02/19/98 - add distribution method column
Rem ddas 05/17/96 - change search_columns to number
Rem achaudhr 07/23/95 - PTI: Add columns partition_{start, stop, id}
Rem glumpkin 08/25/94 - new optimizer fields
Rem jcohen 11/05/93 - merge changes from branch 1.1.710.1 - 9/24
Rem jcohen 09/24/93 - #163783 add optimizer column
Rem glumpkin 10/25/92 - Renamed from XPLAINPL.SQL
Rem jcohen 05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)
Rem rlim 04/29/91 - change char to varchar2
Rem Peeler 10/19/88 - Creation
Rem
Rem This is the format for the table that is used by the EXPLAIN PLAN
Rem statement. The explain statement requires the presence of this
Rem table in order to store the descriptions of the row sources.

create table PLAN_TABLE_FOR_9I (
   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));

SQL> @?/rdbms/admin/utlxplan_for_9i.sql

Table created.

Elapsed: 00:00:01.23
SQL>
SQL> create view plan_table as select * from plan_table_for_9i;

View created.

Elapsed: 00:00:01.07
SQL>
SQL>
SQL> explain plan for
2 select count(*) from tab1@dblink_test;

Explained.

Elapsed: 00:00:00.39
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

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

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Inst |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 1 | 103 (8)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | TAB1 | 120K| 103 (8)| MISC |
---------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version
- fully remote statement

13 rows selected.

Elapsed: 00:00:00.28
SQL>

· 【文章发布信息】发表于： 2009-02-12 @ 11:21:29 · ||分类： Study note

留条评论