关于outline的一点测试和总结
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 关于outline的一点测试和总结
我们知道 outlines 特性在oracle 8i就引入了，不过我用的很少，今天同事问到了，
我也就再回顾温习一下，如下是做的简单测试。
首先来看看2个跟outline相关的参数：
create_stored_outlines — 控制是否自动创建outline
use_stored_outlines — 控制是否启用outline
使用outline的方式有很多种，列出如下几种方式：
1. 针对sql语句或sqlid
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 |
SQL> conn roger/roger Connected. SQL> alter session set create_stored_outlines = true; Session altered. SQL> select * from v$version where rownum <2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod SQL> CREATE OUTLINE test_id FOR CATEGORY test_outlines ON 2 SELECT owner,object_id 3 FROM test_ht 4 WHERE object_id=:p; Outline created. SQL> select name,category,sql_text from user_outlines where category=upper('test_outlines'); NAME CATEGORY SQL_TEXT --------- ----------------- ------------------------------------------------------- TEST_ID TEST_OUTLINES select owner,object_id from test_ht where object_id=:p SQL> select * from user_outline_hints where name=upper('test_id'); NAME NODE STAGE JOIN_POS HINT ------- ---- ------ -------- ---------------------------------------------------------------- TEST_ID 1 1 1 INDEX_RS_ASC(@"SEL$1" "TEST_HT"@"SEL$1" ("TEST_HT"."OBJECT_ID")) TEST_ID 1 1 0 OUTLINE_LEAF(@"SEL$1") TEST_ID 1 1 0 ALL_ROWS TEST_ID 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4') TEST_ID 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS SQL> select name,category,used from user_outlines where category=upper('test_outlines'); NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED -- 该outline未使用 SQL> var p number; SQL> exec :p :=1000; PL/SQL procedure successfully completed. SQL> select name,category,used from user_outlines where category=upper('test_outlines'); NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED SQL> -- 设置参数use_stored_outlines SQL> var p number; SQL> exec :p :=10000; PL/SQL procedure successfully completed. SQL> select owner,object_id from test_ht where object_id=:p; OWNER OBJECT_ID ------------------------------ ---------- WMSYS 10000 SQL> select name,category,used from user_outlines where category=upper('test_outlines'); NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED SQL> alter session set use_stored_outlines=TEST_OUTLINES; Session altered. SQL> var p number; SQL> exec :p :=20000; PL/SQL procedure successfully completed. SQL> select owner,object_id from test_ht where object_id=:p; OWNER OBJECT_ID ------------------------------ ---------- SYS 20000 SQL> select name,category,used from user_outlines where category=upper('test_outlines'); NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES USED SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,object_id%'; SQL_ID SQL_TEXT ------------- --------------------------------------------------------------------------- 3v6z2kwca0ttm select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%' 45s1gxyr1y5k3 select owner,object_id from test_ht where object_id=:p 6cc34dzmkg686 create table test_ht as select owner,object_id,object_name from dba_objects SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id',null)); Enter value for sql_id: 45s1gxyr1y5k3 old 1: select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id',null)) new 1: select * from table(dbms_xplan.DISPLAY_CURSOR('45s1gxyr1y5k3',null)) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 45s1gxyr1y5k3, child number 0 ------------------------------------- select owner,object_id from test_ht where object_id=:p Plan hash value: 793292976 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 9 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=:P) SQL_ID 45s1gxyr1y5k3, child number 1 ------------------------------------- select owner,object_id from test_ht where object_id=:p PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 793292976 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 9 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=:P) Note ----- - outline "TEST_ID" used for this statement 42 rows selected. -- 对于存在的cursor创建outline SQL> select owner,object_id,object_name from test_ht where object_id=3000; OWNER OBJECT_ID OBJECT_NAME -------------- ---------- -------------------------- SYS 3000 EXU8SYNU SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,object_id%'; SQL_ID SQL_TEXT ------------- --------------------------------------------------------------------------- 3v6z2kwca0ttm select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%' 8gz444rhg594f select owner,object_id,object_name from test_ht where object_id=3000 SQL> select hash_value, child_number, sql_text from v$sql where sql_text like '%select owner,object_id%'; HASH_VALUE CHILD_NUMBER SQL_TEXT ---------- ------------ --------------------------------------------------------------------------- 413165363 0 select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%' 47485093 0 select hash_value, child_number, sql_text from v$sql where sql_text like '% select owner,object_id%' 3773998222 0 select owner,object_id,object_name from test_ht where object_id=3000 SQL> exec dbms_outln.create_outline(3773998222,0); PL/SQL procedure successfully completed. SQL> select owner,object_id,object_name from test_ht where object_id=3000; OWNER OBJECT_ID OBJECT_NAME ------------------------------ ---------- -------------------- SYS 3000 EXU8SYNU SQL> select hash_value, child_number, sql_text 2 from v$sql 3 where sql_text like '%select owner,object_id%'; HASH_VALUE CHILD_NUMBER SQL_TEXT ---------- ------------ --------------------------------------------------------------------------- 413165363 0 select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%' 47485093 0 select hash_value, child_number, sql_text from v$sql where sql_text like '% select owner,object_id%' 3773998222 0 select owner,object_id,object_name from test_ht where object_id=3000 SQL> select * from table(dbms_xplan.display_cursor('8gz444rhg594f')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- SQL_ID 8gz444rhg594f, child number 0 ------------------------------------- select owner,object_id,object_name from test_ht where object_id=3000 Plan hash value: 793292976 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 33 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=3000) Note ----- - outline "SYS_OUTLINE_11071210544304523" used for this statement PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- 23 rows selected. SQL> select name,CATEGORY,USED,SQL_TEXT from user_outlines 2 where name='SYS_OUTLINE_11071210544304523'; NAME CATEGORY USED SQL_TEXT ------------------------------- ----------- ------ --------------------------------------------------------------------------- SYS_OUTLINE_11071210544304523 DEFAULT USED select owner,object_id,object_name from test_ht where object_id=3000 |
这里需要说明一下的是alter session set create_stored_outlines = true;这是由于bug5454975的缘故（10204已经修复）
虽然我这里是10204，不过我还是设置了一下，列出来说明，以提醒大家。详见metalink ID 445126.1
最后再补充一下，如果不用outline，那么可以将其删除，可以通过如下的几种方式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
execute DBMS_OUTLN.drop_by_cat('TEST_OUTLINES'); execute DBMS_OUTLN.CLEAR_USED('TEST_OUTLINES'); execute DBMS_OUTLN.drop_unused; -- 这是删除所有状态为unused的outline，要慎重。 SQL> show parameter outline NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _outline_bitmap_tree boolean TRUE _plan_outline_data boolean TRUE create_stored_outlines string |
关于outlines的使用，有几个需要注意的地方：
1. 参数cursor_sharing设置为force时，outlines将无效；
2. literial sql的共享程度不高的情况下，使用outline会生产很多个执行计划，可能会有一些问题；
3. 一般情况我们在使用outline的时候，也是发现某个sql的执行计划不稳定的时候，由于执行计划是基于
统计信息的，那么由于生产系统中统计信息可能是在不断的变化，那么使用outline固定的执行计划不见得
一定就是最好的，这一点需要考虑。
4. 由于outlines信息的存放在用户outln下，那么该用户就显得尤为重要，维护的时候需要注意，不能随便给drop了。
5. outline创建以后，不是说就一层不变了，可以进行编辑，至于什么时候编辑，怎么编辑，大家可以参考
metalink文档 730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline。
Leave a Reply
You must be logged in to post a comment.