CURSOR_SHARING时，OUTLINE的使用

CURSOR_SHARING时，OUTLINE的使用

以前一直以为当CURSOR_SHARING <> EXACT的时候，OUTLINE是不可用的
今天查看METALINK时，才发现CURSOR_SHARING=SIMILAR的时候，是可以使用OUTLINE

DOC:132547.1 Using Stored Outlines
*******************************************************************************
Stored outlines are not used when:

o A hint in the stored outline becomes invalid.
o CURSOR_SHARING = FORCE

CURSOR_SHARING was introduced in Oracle8i Release 2. It internally replaces
literals values in queries with bind variables, thus allowing these statements
to be shared.

CURSOR_SHARING = FORCE disables the use of stored outlines.

Setting CURSOR_SHARING to SIMILAR changes the SQL text in the presence of
literals. Hence, it prevents any outlines generated with literals from
being used. Literals in CREATE [OR REPLACE] OUTLINE... statements are not
replaced by bind variables.

To use stored outlines with CURSOR_SHARING=SIMILAR, the outlines must be
generated with CURSOR_SHARING set to FORCE and with the CREATE_STORED_OUTLINES
parameter.
*******************************************************************************
测试：

DROP INDEX IDX_TEST;
ALTER SESSION SET cursor_sharing=FORCE;
ALTER SESSION set create_stored_outlines = ZHANGQIAOC;
SELECT * FROM test WHERE object_id=15;
ALTER SESSION set create_stored_outlines = FALSE;

CREATE INDEX IDX_TEST ON TEST(OBJECT_ID);

*******************************************************************************

SQL> show parameter cursor_sharing

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR

SQL> alter system flush shared_pool;

System altered.

SQL> SELECT * FROM test WHERE object_id=15;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_ STATUS  T G S
------------------------------ ---------- -------------- ------------------ --------- --------- ------- - - -
SYS
UNDO$
                                       15             15 TABLE              19-MAY-09 19-MAY-09 VALID   N N N

SQL> SELECT operation||options||object_name FROM v$sql_plan WHERE object_name = 'TEST';

OPERATION||OPTIONS||OBJECT_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLE ACCESSBY INDEX ROWIDTEST

SQL> alter system set use_stored_outlines=ZHANGQIAOC;

System altered.

SQL> SELECT * FROM test WHERE object_id=15;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_ STATUS  T G S
------------------------------ ---------- -------------- ------------------ --------- --------- ------- - - -
SYS
UNDO$
                                       15             15 TABLE              19-MAY-09 19-MAY-09 VALID   N N N

SQL> SELECT operation||options||object_name FROM v$sql_plan WHERE object_name = 'TEST';

OPERATION||OPTIONS||OBJECT_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLE ACCESSFULLTEST
TABLE ACCESSBY INDEX ROWIDTEST

*******************************************************************************

看来similar是比force要好得多，只要不过度收集直方图就对了

这下调SQL方便多了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-611641/，如需转载，请注明出处，否则将追究法律责任。