Focus On Oracle,MySQL,SQL Server and PostgreSQL..

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

—

由

dbsupport
于

前几天写了一篇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 (%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，然后进行对比:

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 (%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则不具备。

评论

《 “使用sql profle进行偷梁换柱的小例子–outline exchange(续)” 》 有 3 条评论

  1. Lixora 的头像
    Lixora

    学习！

    回复
  2. Lisf4142 的头像
    Lisf4142

    如果是绑定变量该如何呢

    回复
    1. lizhenxu 的头像
      lizhenxu

      那更简单啊，以前这里有个例子。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

      回复

回复 Lixora 取消回复

您的邮箱地址不会被公开。 必填项已用 * 标注