使用outline稳固sql执行计划

来源：互联网发布：linux json 格式化编辑：程序博客网时间：2024/05/16 05:51

为指定的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 OUTLINESQL> 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 traceonlySQL> 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.创建两个测试outlineSQL> 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 a20SQL> col sql_text for a50SQL> col used for a10SQL> set autot offSQL> set linesize 200SQL> 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_OUTLINETEST_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_OUTLINETEST_OUTLINE1        select * from t where id=1                         UNUSED     CATE_OUTLINESQL> set autot explain用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]SQL> set autot on explainSQL> 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 traceonlySQL> 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 processedSQL> col child_number for 999999999999SQL> col hash_values for 999999999999SQL> col hash_value for 999999999999SQL> col address for a20SQL> col sql_text for a50SQL> set linesize 200SQL> 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_idSQL> 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_OUTLINE153216201SQL>  alter system set  USE_STORED_OUTLINES =cate_outline;系统已更改。SQL> set autot traceonlySQL> 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 processedSQL> 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 processedSQL> 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 processedSQL> 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月 -12SQL> 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