在oracle 10g中,plan table的基表已经改变,在9i的时候,还是一个table类型,在10g的时候,已经是temporary table类型了。
在10g中,如果通过dblink访问9i的库,那么explain table将无法显示。会报错:
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
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脚本。他的建表结构如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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稍作修改,然后在执行:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
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)); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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> |