使用outline稳固sql执行计划

为指定的sql创建outline

USE_STORED_OUTLINES
Syntax:
USE_STORED_OUTLINES = { TRUE | FALSE | category_name }
this parameters are not initialization parameters, so you cannot set them in a pfile or spfile. However, you can set them using an ALTER SYSTEM statement. 重启后需要重新设置。

lau为创建outline的用户,即我们的应用用户。
 
SQL> conn sys/oracle@orcl as sysdba
已连接。

--1.为创建outline用户赋权CREATE ANY OUTLINE
SQL> grant CREATE ANY OUTLINE to lau;

授权成功。

SQL> conn lau/lau@orcl
已连接。

SQL> create table t (id int);

表已创建。

SQL> insert into t select level from dual connect by level <=10000;

已创建10000行。

SQL> commit;

提交完成。

SQL> set autot traceonly
SQL> select * from t where id=1;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         48  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--2.创建两个测试outline
SQL> create or replace outline  test_outline1 for category cate_outline
  2   on select * from t where id=1;

大纲已创建。

SQL> create or replace outline  test_outline2 for category cate_outline
  2   on select * from t where id=2;

大纲已创建。

--3.查看该用户下创建的outline。
SQL> col name for a20
SQL> col sql_text for a50
SQL> col used for a10
SQL> set autot off
SQL> set linesize 200
SQL> select name, sql_text ,used,category
  2    from user_outlines
  3   where category=upper('cate_outline');

NAME                 SQL_TEXT                                           USED       CATEGORY
-------------------- -------------------------------------------------- ---------- ------------------------------
TEST_OUTLINE2        select * from t where id=2                         UNUSED     CATE_OUTLINE
TEST_OUTLINE1        select * from t where id=1                         UNUSED     CATE_OUTLINE

--4.使cate_outline下的outline生效
SQL> alter system set  USE_STORED_OUTLINES =cate_outline;

系统已更改。

SQL> select name, sql_text ,used,category
  2    from user_outlines
  3   where category=upper('cate_outline');

NAME                 SQL_TEXT                                           USED       CATEGORY
-------------------- -------------------------------------------------- ---------- ------------------------------
TEST_OUTLINE2        select * from t where id=2                         UNUSED     CATE_OUTLINE
TEST_OUTLINE1        select * from t where id=1                         UNUSED     CATE_OUTLINE

SQL> set autot explain
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot on explain
SQL> select * from t where id=1;

        ID
----------
         1


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

Note
-----
   - outline "TEST_OUTLINE1" used for this statement  --说明已经使用了我们创建的outline.

SQL> select * from t where id=2;

        ID
----------
         2


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=2)

Note
-----
   - outline "TEST_OUTLINE2" used for this statement

--以下没有使用outline,因为没有绑定变量。
SQL> select * from t where id=3;

        ID
----------
         3


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=3)

Note
-----
   - dynamic sampling used for this statement

--创建索引,验证outline的使用,sql依然使用全表扫描。
SQL> create index ind_t_id on t(id);

索引已创建。

SQL> select * from t where id=1;

        ID
----------
         1


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

Note
-----
   - outline "TEST_OUTLINE1" used for this statement

--禁用outline之后,sql使用了索引
SQL> alter system set  USE_STORED_OUTLINES =false;

系统已更改。

SQL> select * from t where id=1;

        ID
----------
         1


执行计划
----------------------------------------------------------
Plan hash value: 3343177607

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T_ID |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=1)

Note
-----
   - dynamic sampling used for this statement

outline for 绑定变量   
1.获取带绑定变量sql的  child_number,hash_value
select  child_number,hash_value,address,sql_text from v$sql
 where sql_text like 'select * from t where id%';

2.创建outline  
begin
    dbms_outln.create_outline (
       hash_value =>3573770389,
       child_number =>0,
       category =>'CATE_OUTLINE');
end;
/  

SQL> var v_id number;
SQL> exec :v_id :=5;

PL/SQL 过程已成功完成。

提交完成。
SQL> set autot traceonly
SQL> select * from t where id=:v_id;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  1300 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   100 |  1300 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=TO_NUMBER(:V_ID))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> col child_number for 999999999999
SQL> col hash_values for 999999999999
SQL> col hash_value for 999999999999
SQL> col address for a20
SQL> col sql_text for a50
SQL> set linesize 200

SQL> select  child_number,hash_value,address,sql_text from v$sql
  2   where sql_text like 'select * from t where id%';

 CHILD_NUMBER    HASH_VALUE ADDRESS              SQL_TEXT
------------- ------------- -------------------- --------------------------------------------------
            0    3573770389 22E58344             select * from t where id=:v_id

SQL> begin
  2     dbms_outln.create_outline (
  3        hash_value =>3573770389,
  4        child_number =>0,
  5        category =>'CATE_OUTLINE');
  6  end;
  7  /

PL/SQL 过程已成功完成。

提交完成。
SQL> select name, sql_text ,used,category
  2    from user_outlines
  3   where category=upper('cate_outline');

NAME                 SQL_TEXT                                           USED       CATEGORY
-------------------- -------------------------------------------------- ---------- ------------------------------
SYS_OUTLINE_12071817 select * from t where id=:v_id                     UNUSED     CATE_OUTLINE
153216201

SQL>  alter system set  USE_STORED_OUTLINES =cate_outline;

系统已更改。

SQL> set autot traceonly

SQL> exec :v_id :=1;

PL/SQL 过程已成功完成。

提交完成。
SQL> select * from t where id=:v_id;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    16 |   208 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=TO_NUMBER(:V_ID))

Note
-----
   - outline "SYS_OUTLINE_12071817153216201" used for this statement


统计信息
----------------------------------------------------------
         35  recursive calls
        123  db block gets
         44  consistent gets
          0  physical reads
        632  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> exec :v_id :=10;

PL/SQL 过程已成功完成。

提交完成。
SQL> select * from t where id=:v_id;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    16 |   208 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=TO_NUMBER(:V_ID))

Note
-----
   - outline "SYS_OUTLINE_12071817153216201" used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index ind_t_id on t(id);

索引已创建。

SQL> comment on column t.id is 'dddd';

注释已创建。

SQL> select * from t where id=:v_id;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    16 |   208 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=TO_NUMBER(:V_ID))

Note
-----
   - outline "SYS_OUTLINE_12071817153216201" used for this statement


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter system set use_stored_outlines=false;

系统已更改。

SQL> select * from t where id=:v_id;


执行计划
----------------------------------------------------------
Plan hash value: 3343177607

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |   100 |  1300 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T_ID |   100 |  1300 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=TO_NUMBER(:V_ID))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         30  consistent gets
          4  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

USE_STORED_OUTLINES参数在实例重启后需要重新设置,有两种应对方法
 1.使用登录触发器为单独的用户设置会话信息

SQL> conn lau/lau@oralife
已连接。
SQL> create or replace trigger tr_login
  2   after logon on database
  3   declare
  4      v_username varchar2(30);
  5     begin
  6        select SYS_CONTEXT('USERENV','SESSION_USER') into v_username from dual;
  7        if v_username = 'SCOTT' then
  8          execute immediate 'alter session set nls_date_format=''yyyy/mm/dd hh24:mi:ss''';
  9        end if;
 10     exception
 11       when others then
 12        null;
 13  end;
 14  /

触发器已创建

SQL> select sysdate from dual;

SYSDATE
--------------
25-7月 -12

SQL> conn scott/tiger@oralife
已连接。
SQL> select sysdate from dual;

SYSDATE
-------------------
2012/07/25 20:26:10
在登录触发器中为特定的用户添加  
execute immediate 'alter session set use_stored_outlines=cate_outline';
开启类cate_outline。

2.使用启动触发器开启系统级设置
SQL> conn sys/oracle@oralife as sysdba
已连接。
SQL> create or replace trigger tr_login
  2   after STARTUP  on database
  3   declare
  4     begin
  5        execute immediate 'alter system set nls_date_format=''yyyy/mm/dd hh24:mi:ss''';
  6     exception
  7       when others then
  8        null;
  9  end;
 10  /

触发器已创建

SQL> select sysdate from dual;

SYSDATE
--------------
25-7月 -12

--重启实例后,以上的设置并没有生效,但是添加
--execute immediate 'alter system set use_stored_outlines=cate_outline';
--之后的确会开启类cate_outline,使outline生效。
SQL> select sysdate from dual;

SYSDATE
--------------
25-7月 -12    --没有生效
重启实例后,以上的设置并没有生效,但是将其替换为
execute immediate 'alter system set use_stored_outlines=cate_outline';
之后的确会开启类cate_outline,使outline生效。

参考:

Using Plan Stability

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm


  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值