Oracle OutLines的使用

在实际的工作当中，可能会碰到在开发环境或测试环境中系统运行正常，但是移植到真实生产环境中却发现响应速度很慢，当然原因可能有很多种，如果在调试之后效果仍然不佳的情况下，可以尝试使用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

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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博客 ” ，链接：http://blog.itpub.net/12751636/viewspace-683651/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12751636/viewspace-683651/