ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CURSOR_SHARING时,OUTLINE的使用

CURSOR_SHARING时,OUTLINE的使用

原创 Linux操作系统 作者:westzq1984 时间:2009-08-07 19:58:18 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: SQLRepository
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    948576