20161101_sqlprofile

10g oracle 执行计划绑定

访问表方式 有全表扫描、索引扫描

本实验目的强制走全表扫描

 

1  获取某一sql_id的OUTLINE

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8dpvzbf4v3g8c', 1, 'outline'));

以下outline 内容 注意/*+  */

SQL_ID  8dpvzbf4v3g8c, child number 1

-------------------------------------

select * from t1  where id=99

Plan hash value: 838529891

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |       |       |    68 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   |     3 |   237 |    68   (2)| 00:00:01 |

--------------------------------------------------------------------------

Outline Data

-------------

  /*+

BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=99)

Note

-----

   - dynamic sampling used for this statement

 

2  创建sql_profile

DECLARE
     V_HINTS SYS.SQLPROF_ATTR;
     CURSOR C1 IS
     SELECT *
     FROM V$SQL
     WHERE SQL_ID = '8dpvzbf4v3g8c'  --需要改变
     AND CHILD_NUMBER = 1;           --需要改变
     L_C1 C1%ROWTYPE;
BEGIN
     V_HINTS := SYS.SQLPROF_ATTR(                 
     'BEGIN_OUTLINE_DATA',               --需要改变 注意'' 知道括号结束
     'IGNORE_OPTIM_EMBEDDED_HINTS',
     'OPTIMIZER_FEATURES_ENABLE(''10.2.0.5'')',--"10.2.0.5"
     'ALL_ROWS',
     'OUTLINE_LEAF(@"SEL$1")',
     'FULL(@"SEL$1" "T1"@"SEL$1")',
     'END_OUTLINE_DATA'                 --直到这里
     );                             
     OPEN C1;
     FETCH C1
     INTO L_C1;
     DBMS_SQLTUNE.IMPORT_SQL_PROFILE(L_C1.SQL_FULLTEXT,
     V_HINTS,
     'sqlprofile_8dpvzbf4v3g8c',     --sql profile名字
     FORCE_MATCH => TRUE,
     REPLACE => TRUE);
     CLOSE C1;
END;
/

3  相关视图

DBA_SQL_PROFILES

SYS.SQLPROF$ATTR

4  删除sqlprofile

exec dbms_sqltune.drop_sql_profile('sqlprofile_2cy6t5y4auwan')