在Editing Stored Outlines in Oracle10g and Oracle11g [ID 726802.1]里提到，当我们想要改变一个sql的执行计划的时候我们可以选择edit Store Outline，而edit Store Outline在10g/11g里的步骤为：
1、connect to a schema with CREATE ANY OUTLINE privilege
2、clone the existing (public) Stored Outline into a private Stored Outline
3、edit the hints in the private Stored Outline
4、resynchronize the private Stored Outline with the edits
5、test the private Stored Outline (optional but recommended)
6、publish the private Stored Outline, thus replacing the public Stored Outline
7、test the new public Stored Outline: (optional but recommended)
并且在上述文章中专门提到：
Note: do not use the procedure DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES in Oracle
这里只提到了让我们不要再调用DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES了，但是并没有讲原因。
我们现在来看一下为什么在10g/11g里就不需要执行上述procedure了？
在9i里执行下述查询：
SQL> select owner,object_name,object_type from dba_objects where object_name='OL$HINTS';
OWNER OBJECT_NAME OBJECT_TYPE
-------------- ----------------------------- ------------------
OUTLN OL$HINTS TABLE
在10g里执行同样的查询：
SQL> select owner,object_name,object_type from dba_objects where object_name='OL$HINTS';
OWNER OBJECT_NAME OBJECT_TYPE
-------------- ----------------------------- ------------------
OUTLN OL$HINTS TABLE
PUBLIC OL$HINTS SYNONYM
SYSTEM OL$HINTS TABLE
可以看到在10g里多了一个public的synonym和system下的一个同名表，我们来看看这多出来的这两个东东是什么：
SQL> set heading off;
SQL> set echo off;
SQL> Set pages 999;
SQL> set long 90000;
SQL> select dbms_metadata.get_ddl('SYNONYM','OL$HINTS','PUBLIC') from dual;
CREATE OR REPLACE PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS"
SQL> select dbms_metadata.get_ddl('TABLE','OL$HINTS','SYSTEM') from dual;
CREATE GLOBAL TEMPORARY TABLE "SYSTEM"."OL$HINTS"
( "OL_NAME" VARCHAR2(30),
"HINT#" NUMBER,
"CATEGORY" VARCHAR2(30),
"HINT_TYPE" NUMBER,
"HINT_TEXT" VARCHAR2(512),
"STAGE#" NUMBER,
"NODE#" NUMBER,
"TABLE_NAME" VARCHAR2(30),
"TABLE_TIN" NUMBER,
"TABLE_POS" NUMBER,
"REF_ID" NUMBER,
"USER_TABLE_NAME" VARCHAR2(64),
"COST" FLOAT(126),
"CARDINALITY" FLOAT(126),
"BYTES" FLOAT(126),
"HINT_TEXTOFF" NUMBER,
"HINT_TEXTLEN" NUMBER,
"JOIN_PRED" VARCHAR2(2000),
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"HINT_STRING" CLOB
) ON COMMIT PRESERVE ROWS
原来是global temporary table。
这里我们来猜一下原因，在10g/11g里oracle把DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES省掉了，因为oracle可以把你对private Stored Outline做的更改存在上述global temporary table里，所以就不需要再在执行上述存储过程的owner下建立OL$HINTS以记录你对private Stored Outline做的更改了。
这个倒不是说9i下的DBMS_OUTLN_EDIT.CREATE_EDIT_TABLE在执行它的owner下建立的OL$HINTS就不是global temporary table，我们从9i的脚本utledtol.sql可以猜到9i的DBMS_OUTLN_EDIT.CREATE_EDIT_TABLE会调用utledtol.ql，而且创建的OL$，OL$HINTS和OL$NODES也都是global temporary table。
如下是9i里utledtol.ql的内容：
Rem
Rem $Header: utledtol.sql 26-feb-2002.07:58:50 sbodagal Exp $
Rem
Rem utledtol.sql
Rem
Rem Copyright (c) 2002, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem utledtol.sql - Outline editing utility file
Rem
Rem DESCRIPTION
Rem This file creates the outline tables OL$, OL$HINTS and OL$NODES
Rem and the associated indices in the user schema. The created tables
Rem will be used to store private outlines generated during an outline
Rem editing session. Users are expected to create the outline tables
Rem in their schemas before starting an outline editing session.
Rem
Rem NOTES
Rem Global temporary tables have been chosen for OL$, OL$HINTS and
Rem OL$NODES in order to provide the appropriate level of isolation
Rem between different editing sessions.
Rem
Rem MODIFIED (MM/DD/YY)
Rem sbodagal
Rem sbodagal
Rem - rename utleditol.sql to utledtol.sql
Rem svivian
Rem svivian
Rem sbodagal
Rem
create global temporary table ol$
(
ol_name varchar2(30), /* name is potentially generated */
sql_text long, /* the SQL stmt being outlined */
textlen number, /* length of SQL stmt */
signature raw(16), /* signature of sql_text */
hash_value number, /* KGL's calculated hash value */
hash_value2 number, /* hash value for stripped text */
category varchar2(30), /* category name */
version varchar2(64), /* db version @ outline creation */
creator varchar2(30), /* user from whom outline created */
timestamp date, /* time of creation */
flags number, /* e.g. everUsed, bindVars, dynSql */
hintcount number /* number of hints on the outline */
)
on commit preserve rows;
create global temporary table ol$hints
(
ol_name varchar2(30), /* outline name */
hint# number, /* which hint for a given outline */
category varchar2(30), /* collection/grouping name */
hint_type number, /* type of hint */
hint_text varchar2(512), /* hint specific information */
stage# number, /* stage of hint generation/applic'n */
node# number, /* QBC node id */
table_name varchar2(30), /* for ORDERED hint */
table_tin number, /* table instance number */
table_pos number, /* for ORDERED hint */
ref_id number, /* node id that this hint is referencing */
user_table_name varchar2(64), /* table name to which this hint applies */
cost double precision, /* optimizer estimated cost of the */
/* hinted operation */
cardinality double precision, /* optimizer estimated cardinality */
/* of the hinted operation */
bytes double precision, /* optimizer estimated byte count */
/* of the hinted operation */
hint_textoff number, /* offset into the SQL statement to */
/* which this hint applies */
hint_textlen number, /* length of SQL to which this hint applies */
join_pred varchar2(2000), /* join predicate (applies only for */
/* join method hints) */
spare1 number, /* spare number for future enhancements */
spare2 number /* spare number for future enhancements */
)
on commit preserve rows;
create global temporary table ol$nodes
(
ol_name varchar2(30), /* outline name */
category varchar2(30), /* outline category */
node_id number, /* qbc node identifier */
parent_id number, /* node id of the parent node for current node */
node_type number, /* qbc node type */
node_textlen number, /* length of SQL to which this node applies */
node_textoff number /* offset into the SQL statement to which this */
/* node applies */
)
on commit preserve rows;
create unique index ol$name on ol$(ol_name);
create unique index ol$signature on ol$(signature,category);
create unique index ol$hnt_num on ol$hints(ol_name, hint#);
Leave a comment