在实际的工作当中，可能会碰到在开发环境或测试环境中系统运行正常，但是移植到真实生产环境中却发现响应速度很慢，当然原因可能有很多种，如果在调试之后效果仍然不佳的情况下，可以尝试使用outlines来稳定个别SQL语句的执行计划。

另外还有两种主要的情形会用到outlines，1、优化器从RBO到CBO；2、从低版本升级到高版本。

Outlines存储在表OL$,OL$HINTS和OL$NODES,可通过视图[USER|ALL|DBA]_OUTLINES 和 [USER|ALL|DBA]_OUTLINE_HINTS查询已存在的outlines的信息。

一、创建outlines

Outlines创建可分为oracle自动和手动，通过参数create_stored_outlines来控制，create_stored_outlines的值可以是true/flase/category_name，可在实例级和会话级别修改。

——开启自动创建 outlines.

ALTER SYSTEM SET create_stored_outlines=TRUE;

ALTER SESSION SET create_stored_outlines=TRUE;

——关闭自动创建 outlines.

ALTER SYSTEM SET create_stored_outlines=FALSE;

ALTER SESSION SET create_stored_outlines=FALSE;

——授权

CONN sys/password AS SYSDBA

GRANT CREATE ANY OUTLINE TO SCOTT;

GRANT EXECUTE_CATALOG_ROLE TO SCOTT;

——创建outlines

把条件写成变量，主要是因为在实际的运用过程中会绑定变量。

CREATE OUTLINE dave_id FOR CATEGORY dave_outlines ON select * from dave where id=:a;

——检查outlines是否创建成功

SQL> select name,category,sql_text from user_outlines where category='DAVE_

OUTLINES';

NAME CATEGORY

------------------------------ ------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

DAVE_ID DAVE_OUTLINES

select * from dave where id=:a

——列出和outlines有关的hints

SQL> select * from user_outline_hints where name='DAVE_ID';

NAME NODE STAGE JOIN_POS

------------------------------ ---------- ---------- ----------

HINT

--------------------------------------------------------------------------------

DAVE_ID 1 1 1

FULL(@"SEL$1" "DAVE"@"SEL$1")

DAVE_ID 1 1 0

OUTLINE_LEAF(@"SEL$1")

DAVE_ID 1 1 0

ALL_ROWS

NAME NODE STAGE JOIN_POS

------------------------------ ---------- ---------- ----------

HINT

--------------------------------------------------------------------------------

DAVE_ID 1 1 0

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

DAVE_ID 1 1 0

IGNORE_OPTIM_EMBEDDED_HINTS

SQL>

二、使用outlines

现在我们已经创建好了outlines，但是从下面查询可以看出outlines并没有被使用。

——检查outlines是否使用

SQL> select name,category,used from user_outlines where category='DAVE_OUTLINES'

;

NAME CATEGORY USED

------------------------------ ------------------------------ ------

DAVE_ID DAVE_OUTLINES UNUSED

——执行下面的SQL语句

sql>var a int

sql>exec :a=1

select * from dave where id=:a;

——再一次检查

SQL> select name,category,used from user_outlines where category='DAVE_OUTLINES'

;

NAME CATEGORY USED

------------------------------ ------------------------------ ------

DAVE_ID DAVE_OUTLINES UNUSED

我们执行一遍SQL之后，outlines还是没使用，这是因为还没有启用。我们可以通过ALTER SYSTEM和ALTER SESSION命令去开启。下面将演示一下在会话级别启用outlines.

——开启outlines

alter session set use_stored_outlines=dave_outlines;

alter session set query_rewrite_enable=true;

——执行下面的SQL语句

sql>var a int

sql>exec :a=1

select * from dave where id=:a;

——检查

SQL> select name,category,used from user_outlines where category='DAVE_OUTLINES'

;

NAME CATEGORY USED

------------------------------ ------------------------------ ------

DAVE_ID DAVE_OUTLINES USED

例子演示

——起初执行计划

sql>var a int

sql>exec :a=1

select * from dave where id=:a;

执行计划

----------------------------------------------------------

Plan hash value: 3458767806

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DAVE | 4 | 52 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=:a)

Note

-----

- outline "DAVE2_ID" used for this statement

——关闭outlines

SQL> ALTER session create_stored_outlines=false;

——创建索引

SQL> create index dave_id on dave(id);

——查看执行计划

sql>var a int

sql>exec :a=1

select * from dave where id=:a;

执行计划

----------------------------------------------------------

Plan hash value: 4246230437

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 52 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| DAVE_ID | 4 | 52 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------

——开启使用outlines

SQL> alter session set use_stored_outlines=dave2_outlines;

——查看执行计划

sql>var a int

sql>exec :a=1

select * from dave where id=:a;

执行计划

----------------------------------------------------------

Plan hash value: 3458767806

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DAVE | 4 | 52 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=:a)

Note

-----

- outline "DAVE2_ID" used for this statement

三、删除和清除outlines

删除outlines

SQL>execute DBMS_OUTLN.drop_by_cat('DAVE_OUTLINES');

清除制定outlines

SQL> execute DBMS_OUTLN.CLEAR_USED(' DAVE_OUTLINES ');

删除所有状态为unused（dba(all,user)_outlines中可查）的outline

SQL> execute DBMS_OUTLN.drop_unused;

ITPub博客

Oracle OutLines的使用

例子演示

删除outlines

清除制定outlines

删除所有状态为unused（dba(all,user)_outlines中可查）的outline

最新文章