sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle sql profile和outline exchange固定非绑定变量sql

时间:2016-12-11 19:01   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库固定非绑定变量sql的方法,详细对比Oracle sql profile和outline exchange区别。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 使用sql profle进行偷梁换柱的小例子–outline exchange(续)

一篇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 Oracleoracleplus.net- 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则不具备。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle sql profile和outline exchange固定非绑定变量sql

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1390.html

Oracle研究中心

关键词:

Oracle sql profile

Oracle outline exchange

Oracle如何固定非绑定变量sql