暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 使用sql profle进行偷梁换柱的小例子--outline exchange(续)

使用sql profle进行偷梁换柱的小例子--outline exchange(续)

原创 Roger 2012-09-16
456
前几天写了一篇sql profile来固定非绑定变量sql的文章，微博上有人提到了outline exchange也可以实现
类似的功能，那我们就再来看看这和sql profile有什么差异，如何去实现：

下面我们再来创建一个测试表，用于测试：

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：

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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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，然后进行对比:

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修改即可。

那现在的问题的是:我们去哪儿修改呢？

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部分：

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？

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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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则不具备。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论