记录一下最近在生产上应用的sql profile使用方法：

首先建立测试表：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Solaris: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL>

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index t_idx on t(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('TEST','T',cascade=>true);  

PL/SQL procedure successfully completed.

假设我现在需要调优的sql语句是select object_id,object_name from t where object_id=:a;

先看一下默认情况下，此语句的执行计划和outline：

SQL> set linesize 300 pagesize 300

SQL> explain plan for select object_id,object_name from t where object_id=:a;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 470836197

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

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

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

|   0 | SELECT STATEMENT            |       |     1 |    23 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    23 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=TO_NUMBER(:A))

27 rows selected.

其使用了INDEX RANGE SCAN，现在增加hint，强制sql语句全表扫描。

SQL> explain plan for select /*+ full(t) */ object_id,object_name from t where object_id=:a;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    23 |    34   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    23 |    34   (0)| 00:00:01 |

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      FULL(@"SEL$1" "T"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=TO_NUMBER(:A))

26 rows selected.

好了，对比一下outline data部分。原来的INDEX RANGE SCAN：

  /*+

      BEGIN_OUTLINE_DATA

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

现在的TABLE ACCESS FULL：

  /*+

      BEGIN_OUTLINE_DATA

      FULL(@"SEL$1" "T"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

如果期望调优的sql语句select object_id,object_name from t where object_id=:a;使用全表扫描，那么可以使用sql profile来固化执行计划。

SQL> conn / as sysdba

Connected.

SQL> declare

  2     v_hints sys.sqlprof_attr;

  3  begin

  4     v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "T"@"SEL$1")');

  5     dbms_sqltune.import_sql_profile('select object_id,object_name from t where object_id=:a',

  6                                    v_hints,

  7                                    'SQLPROFILE_TEST',

  8                                    force_match => true);

  9  end;

 10  /

PL/SQL procedure successfully completed.

这里的sys.sqlprof_attr('FULL(@"SEL$1" "T"@"SEL$1")')从对应的outline中提取。

好了，看看sql profile创建后的sql语句执行计划：

SQL> conn test/test

Connected.

SQL> explain plan for select object_id,object_name from t where object_id=:a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    23 |    34   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    23 |    34   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=TO_NUMBER(:A))

Note

-----

   - SQL profile "SQLPROFILE_TEST" used for this statement

17 rows selected.

SQL> explain plan for select /*+ full(t) */ object_id,object_name from t where object_id=:a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    23 |    34   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    23 |    34   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=TO_NUMBER(:A))

13 rows selected.

sql语句按照我们的意愿进行了全表扫描。

如果想删除sql profile，可以使用以下方法：

SQL> conn / as sysdba

Connected.

SQL> exec dbms_sqltune.drop_sql_profile('SQLPROFILE_TEST');

PL/SQL procedure successfully completed.