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

1、24.oracle深度学习笔记一一使用存储提纲oracle存储提纲(stored outline )用来提供稳定的执行计划，以消除执行环境或者对象统计信息的改变造成的影响。因此，这个特性也被称作计划稳定性。具体的讲，存储提纲是一个提示的集合，更精确地说，所有这些提示强制查询优化器为一个给定的sql语句，稳定地产生一个特殊的执行计划。但实践中，即使使用存储提纲， 还是可能观察到执行计划的改变。不是总能提供一个稳定的执行计划，oracle 11g版本起，不再赞成使用存储提纲，而是推荐sql计划基线。在cbo之后不久被引入，最早基于提示的机制。9i对大纲进行了增强，10gr1后就没有对该特性做过任何

2、事情了。在11g官方文档中称该特性已经被弃用不再维护了。我们先来看看如何使用的如下：1.测试一在12.1.0.2.0版本上进行的测试tpcctoaddb create table oln_test as select * from dba_tables;table created.tpcctoaddb create index idex_oln on oln_test (table_name); index created.tpcctoaddb set autotrace on;tpcctoaddb select owner from oln_test where table_name = o

3、ln_test; no rows selected execution planplan 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 |predi

4、cate information (identified by operation id):2 - access(table_name=oln_test) statistics98 recursive calls 0 db block gets73 consistent gets6 physical reads0 redo size341 bytes sent via sql*net to client540 bytes received via sql*net from client1 sql*net roundtrips to/from client0 sorts (memory)0 so

5、rts (disk)0 rows processed然后加入hint如下：tpcctoaddb select /*+full(oln_test)*/ owner from oln_test where table_name = oln_test;no rows selectedexecution planplan hash value: 1307524366| id | operation | name | rows | bytes | cost (%cpu)| time |0 | select statement|1 |26 |30(0)| 00:00:01 |*1 | table acce

6、ss full| oln_test | 1 |26 |30(0)| 00:00:01 |predicate information (identified by operation id):1 - filter(table_name=oln_test) statistics1 recursive calls0 db block gets98 consistent gets95 physical reads 0 redo size341 bytes sent via sql*net to client540 bytes received via sql*net from client1 sql*

7、net roundtrips to/from client0 sorts (memory)0 sorts (disk) 0 rows processed1.1 创建 outline生成存储提纲如下：tpcctoaddb set autotrace off;tpcctoaddb createor replaceoutline oln_to on select owner from oln_test where table_name = oln_test;outline created.tpcctoaddb createor replaceoutline oln hint on select /*

8、+full(oln test)*/ owner from oln_test where table_name = oln_test;outline created.1.2 交换 outline直接更新dba_outline除 查看 dba_outline及口下： systoaddb desc dba_outlines;namenull? typenamevarchar2(128)ownervarchar2(128)categoryvarchar2(128)usedvarchar2(6)timestampdateversionvarchar2(64)sql_textlongsignaturera

9、w(16)compatiblevarchar2(12)enabledvarchar2(8)formatvarchar2(6)migratedvarchar2(12)systoaddb select count(*) from dba_outlines;count(*)2tpcctoaddb select name,sql_text from dba_outlines;name sql_textoln_to select owner from oln_test where table_name = oln_test oln_hint select /*+full(oln_test)*/ owne

10、r from oln_test where table_name = oln_test当前共两行，就是我们之前创建的两项。交换可以使用如下命令进行：systoaddbupdatedba_outlinessetname=decode(name,oln_hint,oln_to,oln_to,oln_hint) where name in (oln_to,oln_hint); 2 rows updated. systoaddb commit; commit complete. tpcctoaddb select name,sql_text from dba_outlines;namesql text

11、oln_hint select owner from oln_test where table_name = oln_testoln_to select /*+full(oln_test)*/ owner from oln_test where table_name = oln_test 检验如下：tpcctoaddb alter system flush shared_pool;tpcctoaddb set autotrace on;12c 需要设置：systoaddb alter system set use stored outlines=true;tpcctoaddb select o

12、wner from oln_test where table_name = oln_test;no rows selectedexecution planplan 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 (identifie

13、d by operation id):1 - filter(table_name=oln_test) note-outline oln_hint used for this statement statistics0 recursive calls0 db block gets98 consistent gets0 physical reads0 redo size341 bytes sent via sql*net to client540 bytes received via sql*net from client0 sql*net roundtrips to/from client0 s

14、orts (memory)0 sorts (disk)0 rows processed删除 outlinetpcctoaddb drop outline oln_to;outline dropped.tpcctoaddb drop outline oln_hint;outline dropped.1.3 通过 shared pool 中 sql 仓|建 outline需要10g以上，蛤蟆在12c上进行的操作tpcctoaddb select count(*) from oln_test;count(*)2476tpcctoaddb col sql_id format a20;tpcctoadd

15、b col sql_text format a40;tpcctoaddb select sql_id,hash_value, child_number, sql_text from v$sql where sql_text like select count(*) from oln_test%;sql_idhash_value child_number sql_text24z60pxhd61h916244423770 select count(*) from oln_test使能会话创建存储提纲tpcctoaddb alter session set create_stored_outline

16、s = true;session altered.通过sql的hash值来创建存储提纲如下：tpcctoaddb exec dbms_outln.create_outline(hash_value = 1624442377,child_number =0);pl/sql procedure successfully completed.执行检查测试如下：tpcctoaddb select name,sql_text from dba_outlines;name sql_textsys_outline_160 select count(*) from oln_test 3051713221981

17、9sys_outline_160 select sql_id,hash_value, child_number, 30517132615720 sql_text from v$sql where sql_text like select count(*) from oln_test%sys_outline_160 select count(*) from dba_outlines 30517140764322sys_outline_160 select name,sql_text from dba_outlines 30517143173123tpcctoaddbset autotrace o

18、n;tpcctoaddb select count(*) from oln_test;count(*)2476execution planplan hash value: 2860217201| id | operation| name | rows | cost (%cpu)| time |0 | select statement |1 |5(0)| 00:00:01 |1 | sort aggregate |1 |5(0)| 00:00:01 |2 | index fast full scan| idex_oln |2476 |note-outline sys_outline_16030516461948302 used for this statementstatistics0 recursive calls0 db block gets0