24.Oracle深度学习笔记——使用存储提纲

oracle存储提纲（stored outline）用来提供稳定的执行计划，以消除执行环境或者对象统计信息的改变造成的影响。因此，这个特性也被称作计划稳定性。具体的讲，存储提纲是一个提示的集合，更精确地说，所有这些提示强制查询优化器为一个给定的sql语句，稳定地产生一个特殊的执行计划。但实践中，即使使用存储提纲，还是可能观察到执行计划的改变。不是总能提供一个稳定的执行计划，Oracle 11g版本起，不再赞成使用存储提纲，而是推荐sql计划基线。

在CBO之后不久被引入，最早基于提示的机制。

9i对大纲进行了增强，10gR1后就没有对该特性做过任何事情了。在11g官方文档中称该特性已经被弃用不再维护了。

我们先来看看如何使用的如下：

1. 测试一

在12.1.0.2.0版本上进行的测试

tpcc@TOADDB> create table oln_test as select *from dba_tables;

Table created.

tpcc@TOADDB>create index idex_oln on oln_test (TABLE_NAME);

Index created.

tpcc@TOADDB> set autotrace on;

tpcc@TOADDB> select OWNER from oln_test wheretable_name = 'OLN_TEST';

no rows selected

Execution Plan

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

Plan hash value: 2108416138

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

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

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

| 0| SELECT STATEMENT | |1 | 26 | 2 (0)| 00:00:01 |

| 1| TABLE ACCESS BY INDEX ROWID BATCHED|OLN_TEST | 1 | 26 |2 (0)| 00:00:01 |

|* 2| INDEX RANGE SCAN | IDEX_OLN | 1 || 1 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

2- access("TABLE_NAME"='OLN_TEST')

Statistics

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

98recursive calls

0 dbblock gets

73consistent gets

6physical reads

0 redosize

341 bytes sent via sql*Net to client

540 bytes received via sql*Net from client

1sql*Net roundtrips to/from client

6sorts (memory)

0sorts (disk)

0 rowsprocessed

然后加入HINT如下：

tpcc@TOADDB> select /*+FULL(oln_test)*/ OWNER fromoln_test where table_name = 'OLN_TEST';

no rows selected

Execution Plan

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

Plan hash value: 1307524366

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

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

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

| 0| SELECT STATEMENT | | 1 | 26 | 30(0)| 00:00:01 |

|* 1| TABLE ACCESS FULL| OLN_TEST | 1 | 26| 30 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- filter("TABLE_NAME"='OLN_TEST')

Statistics

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

1 recursivecalls

0 dbblock gets

98consistent gets

95physical reads

0 redosize

341 bytes sent via sql*Net to client

540 bytes received via sql*Net from client

1sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

0 rowsprocessed

1.1创建OUTLINE

生成存储提纲如下：

tpcc@TOADDB> set autotrace off;

tpcc@TOADDB> CREATE OR REPLACE OUTLINE oln_to ONselect OWNER from oln_test where table_name = 'OLN_TEST';

Outline created.

tpcc@TOADDB> CREATE OR REPLACE OUTLINE oln_hint ONselect /*+FULL(oln_test)*/ OWNER from oln_test where table_name = 'OLN_TEST';

Outline created.

1.2交换OUTLINE

直接更新DBA_OUTLINES表

查看DBA_OUTLINES如下：

sys@TOADDB> desc dba_outlines;

Name Null? Type

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

NAME VARCHAR2(128)

OWNER VARCHAR2(128)

CATEGORY VARCHAR2(128)

USED VARCHAR2(6)

TIMESTAMP DATE

VERSION VARCHAR2(64)

sql_TEXT LONG

SIGNATURE RAW(16)

COMPATIBLE VARCHAR2(12)

ENABLED VARCHAR2(8)

FORMAT VARCHAR2(6)

MIGRATED VARCHAR2(12)

sys@TOADDB> select count(*) from dba_outlines;

COUNT(*)

----------

2

tpcc@TOADDB> select name,sql_text fromdba_outlines;

NAME sql_TEXT

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

OLN_TO selectOWNER from oln_test where table_name = 'OLN_TEST'

OLN_HINT select/*+FULL(oln_test)*/ OWNER from oln_test where table_name = 'OLN_TEST'

当前共两行，就是我们之前创建的两项。

交换可以使用如下命令进行：

sys@TOADDB> UPDATE DBA_OUTLINES SETNAME=DECODE(NAME,'OLN_HINT','OLN_TO','OLN_HINT') WHERE NAME IN('OLN_TO','OLN_HINT');

2 rows updated.

sys@TOADDB> commit;

Commit complete.

tpcc@TOADDB> select name,sql_text fromdba_outlines;

NAME sql_TEXT

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

OLN_HINT selectOWNER from oln_test where table_name = 'OLN_TEST'

OLN_TO select/*+FULL(oln_test)*/ OWNER from oln_test where table_name = 'OLN_TEST'

检验如下：

tpcc@TOADDB> alter system flush shared_pool;

tpcc@TOADDB> set autotrace on;

12C需要设置：sys@TOADDB>alter system set use_stored_outlines=true;

tpcc@TOADDB> select OWNER from oln_test wheretable_name = 'OLN_TEST';

no rows selected

Execution Plan

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

Plan hash value: 1307524366

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

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

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

| 0| SELECT STATEMENT | | 1 | 26 | 30(0)| 00:00:01 |

|* 1| TABLE ACCESS FULL| OLN_TEST | 1 | 26| 30 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- filter("TABLE_NAME"='OLN_TEST')

Note

-----

-outline "OLN_HINT" used for this statement

Statistics

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

0recursive calls

0 dbblock gets

98consistent gets

0physical reads

0 redosize

341 bytes sent via sql*Net to client

540 bytes received via sql*Net from client

1sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

0rows processed

删除OUTLINE：

tpcc@TOADDB> drop outline oln_to;

Outline dropped.

tpcc@TOADDB> drop outline oln_hint;

Outline dropped.

1.3通过SHARED POOL中sql创建OUTLINE

需要10G 以上，蛤蟆在12C上进行的操作

tpcc@TOADDB> select count(*) from oln_test;

COUNT(*)

----------

2476

tpcc@TOADDB> col sql_id format a20;

tpcc@TOADDB> col sql_text format a40;

tpcc@TOADDB> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like 'select count(*) fromoln_test%';

sql_ID HASH_VALUE CHILD_NUMBER sql_TEXT

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

24z60pxhd61h9 1624442377 0 select count(*) from oln_test

使能会话创建存储提纲

tpcc@TOADDB> alter session setcreate_stored_outlines = true;

Session altered.

通过sql的HASH值来创建存储提纲如下：

tpcc@TOADDB> execdbms_outln.create_outline(hash_value => 1624442377,child_number =>0);

PL/sql procedure successfully completed.

执行检查测试如下：

tpcc@TOADDB> select name,sql_text fromdba_outlines;

NAME sql_TEXT

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

SYS_OUTLINE_160 select count(*) from oln_test

30517132219819

SYS_OUTLINE_160 select sql_id,

30517132615720 sql_text from v$sql where sql_text like

'selectcount(*) from oln_test%'

SYS_OUTLINE_160 select count(*) fromdba_outlines

30517140764322

SYS_OUTLINE_160 select name,sql_text fromdba_outlines

30517143173123

tpcc@TOADDB>set autotrace on;

tpcc@TOADDB> select count(*) from oln_test;

COUNT(*)

----------

2476

Execution Plan

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

Plan hash value: 2860217201

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

| Id| Operation | Name | Rows | Cost (%cpu)| Time |

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

| 0| SELECT STATEMENT | | 1| 5(0)| 00:00:01 |

| 1| SORT AGGREGATE | | 1| | |

| 2| INDEX FAST FULL SCAN| IDEX_OLN | 2476 |5 (0)| 00:00:01 |

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

Note

-----

- outline"SYS_OUTLINE_16030516461948302" used for this statement

Statistics

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

0recursive calls

0 dbblock gets

17consistent gets

0physical reads

0 redosize

543 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1 rowsprocessed

2. 存储提供维护命令

停止db使用outline功能：

alter system set use_stored_outlines=false;

disable/enable具体outline：

alter outline ol_name disable;

alter outline ol_name enable;

删除：outlinecategory：

outline相关视图：

dba_outlines

dba_outline_hints 该视图列出outline的hints内容

检查outline是否存在：

select name,category,owner from dba_outlines;

可以用如下命令分别删除指定的outline或者某个类别下的所有outline：

drop outline dh_test1;

dbms_outln.drop_by_cat(cat=>'Default');

原文链接：https://www.f2er.com/oracle/207440.html