24.Oracle深度学习笔记——使用存储提纲
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'); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |