使用sql profle进行偷梁换柱的小例子–outline exchange(续)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
前几天写了一篇sql profile来固定非绑定变量sql的文章，微博上有人提到了outline exchange也可以实现
类似的功能，那我们就再来看看这和sql profile有什么差异，如何去实现：
下面我们再来创建一个测试表，用于测试：
|
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 |
SQL> conn roger/roger Connected. SQL> create table t2 as select * from dba_objects; Table created. SQL> select count(1) from t2; COUNT(1) ---------- 51072 SQL> update t2 set object_id=2000 where object_id >30000; 21619 rows updated. SQL> commit; Commit complete. SQL> create index idx_id_t2 on t2(object_id); Index created. SQL> analyze table t2 compute statistics for table for all indexes for all indexed columns; Table analyzed. 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.5.0 - Prod |
下面来创建outline：
|
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 |
SQL> create outline test_outline_exchange for CATEGORY test_outlines on 2 select owner,object_name from t2 where object_id=1000; Outline created. SQL> select name,category,sql_text from user_outlines where category=upper('test_outlines'); NAME CATEGORY ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------- TEST_OUTLINE_EXCHANGE TEST_OUTLINES select owner,object_name from t2 where object_id=1000 SQL> l 1* select * from user_outline_hints where name=upper('test_outline_exchange') SQL> / NAME NODE STAGE JOIN_POS HINT ------------------------- ---------- ---------- ---------- ------------------------------------------------------- TEST_OUTLINE_EXCHANGE 1 1 1 INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID")) TEST_OUTLINE_EXCHANGE 1 1 0 OUTLINE_LEAF(@"SEL$1") TEST_OUTLINE_EXCHANGE 1 1 0 ALL_ROWS TEST_OUTLINE_EXCHANGE 1 1 0 OPT_PARAM('_optim_peek_user_binds' 'false') TEST_OUTLINE_EXCHANGE 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') TEST_OUTLINE_EXCHANGE 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS 6 rows selected. SQL> set autot traceonly SQL> select owner,object_name from t2 where object_id=1000; Execution Plan ---------------------------------------------------------- Plan hash value: 4034027770 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 86 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID_T2 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) Statistics ---------------------------------------------------------- 55 recursive calls 28 db block gets 10 consistent gets 0 physical reads 8756 redo size 485 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select owner,object_name from t2 where object_id=2000; 21620 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21790 | 1830K| 198 (1)| 00:00:03 | |* 1 | TABLE ACCESS FULL| T2 | 21790 | 1830K| 198 (1)| 00:00:03 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=2000) Statistics ---------------------------------------------------------- 55 recursive calls 28 db block gets 2137 consistent gets 56 physical reads 8656 redo size 816430 bytes sent via SQL*Net to client 16251 bytes received via SQL*Net from client 1443 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21620 rows processed |
我们这里的目的是要想让object_id=2000的sql也走index range scan。
下面也为object_id=2000的sql创建一个outline，然后进行对比:
|
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 |
SQL> create outline test_outline_exchange2 for CATEGORY test_outlines on 2 select owner,object_name from t2 where object_id=2000; Outline created. SQL> select * from user_outline_hints where name=upper('test_outline_exchange2'); NAME NODE STAGE JOIN_POS HINT ----------------------- ----- ---------- ---------- ---------------------------------------------- TEST_OUTLINE_EXCHANGE2 1 1 1 FULL(@"SEL$1" "T2"@"SEL$1") TEST_OUTLINE_EXCHANGE2 1 1 0 OUTLINE_LEAF(@"SEL$1") TEST_OUTLINE_EXCHANGE2 1 1 0 ALL_ROWS TEST_OUTLINE_EXCHANGE2 1 1 0 OPT_PARAM('_optim_peek_user_binds' 'false') TEST_OUTLINE_EXCHANGE2 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') TEST_OUTLINE_EXCHANGE2 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS 6 rows selected. SQL> select * from user_outline_hints where name=upper('test_outline_exchange'); NAME NODE STAGE JOIN_POS HINT ---------------------- ---- ---------- ---------- ------------------------------------------------------- TEST_OUTLINE_EXCHANGE 1 1 1 INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID")) TEST_OUTLINE_EXCHANGE 1 1 0 OUTLINE_LEAF(@"SEL$1") TEST_OUTLINE_EXCHANGE 1 1 0 ALL_ROWS TEST_OUTLINE_EXCHANGE 1 1 0 OPT_PARAM('_optim_peek_user_binds' 'false') TEST_OUTLINE_EXCHANGE 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') TEST_OUTLINE_EXCHANGE 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS 6 rows selected. |
我们对比上面的hint部分，可以发现，一个是full 一个是index_rs_asc，其他完全一致，也就是说，
如果我们想让object_id=2000的sql走index range scan，那么我们只需要把hint修改即可。
那现在的问题的是:我们去哪儿修改呢？
|
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 |
SQL> show user USER is "SYS" SQL> SQL> select dbms_metadata.get_ddl('VIEW','USER_OUTLINE_HINTS') from dual; DBMS_METADATA.GET_DDL('VIEW','USER_OUTLINE_HINTS') -------------------------------------------------------------------------------- CREATE OR REPLACE FORCE VIEW "SYS"."USER_OUTLINE_HINTS" ("NAME", "NODE", "STAG E", "JOIN_POS", "HINT") AS select o.ol_name, h.node#, h.stage#, table_pos, NVL(h.hint_string, h.hint_text) from outln.ol$ o, outln.ol$hints h, sys.user$ u where o.ol_name = h.ol_name and o.creator = u.name and u.user# = USERENV('SCHEMAID') SQL> col HINT_TEXT for a60 SQL> select HINT#,HINT_TEXT from outln.ol$hints where ol_name='TEST_OUTLINE_EXCHANGE2'; HINT# HINT_TEXT ---------- ------------------------------------------------------------ 1 FULL(@"SEL$1" "T2"@"SEL$1") 2 OUTLINE_LEAF(@"SEL$1") 3 ALL_ROWS 4 OPT_PARAM('_optim_peek_user_binds' 'false') 5 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') 6 IGNORE_OPTIM_EMBEDDED_HINTS 6 rows selected. |
我们可以看到，outline信息是存在outln用户下面的ol$hints表中，我们这里来更改hint#为1的 hint_text部分：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> update outln.ol$hints set HINT_TEXT='INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))' 2 where ol_name='TEST_OUTLINE_EXCHANGE2' and hint#=1; 1 row updated. SQL> commit; Commit complete. SQL> |
那下面我们来看看执行计划是否会变成index rang scan？
|
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 |
SQL> alter system flush shared_pool; System altered. SQL> alter system flush buffer_cache; System altered. SQL> set autot traceonly exp SQL> alter session set use_stored_outlines=test_outlines; Session altered. SQL> set autot traceonly exp SQL> select owner,object_name from t2 where object_id=2000; Execution Plan ---------------------------------------------------------- Plan hash value: 4034027770 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21715 | 742K| 513 (0)| 00:00:07 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 21715 | 742K| 513 (0)| 00:00:07 | |* 2 | INDEX RANGE SCAN | IDX_ID_T2 | 21715 | | 46 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=2000) Note ----- - outline "TEST_OUTLINE_EXCHANGE2" used for this statement |
我们可以看到使用了index range scan，成功实现了egale_fan讲的outline exchange。
但是，这仍然有一个很大的问题，既然我应用没有使用绑定变量，那么你要固定其执行计划，也就是说
你必须为每个一个sql创建一个outline，那样太费劲了，而且不现实。
我想这或许是sql profile引入的原因，其中有一点大家应该都看到了，sql profile有一个force_match的功能，
而outline则不具备。
3 Responses to “使用sql profle进行偷梁换柱的小例子–outline exchange(续)”
学习！
如果是绑定变量该如何呢
那更简单啊，以前这里有个例子。http://www.killdb.com/2011/07/12/%e5%85%b3%e4%ba%8eoutline%e7%9a%84%e4%b8%80%e7%82%b9%e6%b5%8b%e8%af%95%e5%92%8c%e6%80%bb%e7%bb%93.html
Leave a Reply
You must be logged in to post a comment.