通过SQL Profile使用HINT

                                                  通过SQL Profile使用HINT


       在oracle 11g的后续版本中，use_stored_outlines这个参数已经不存在了。意味着我们不能像以前的版本
中使用create outline的方式来为一个sql创建hint，然后使用store outline来固定执行计划这种方式了.在11g中
我们可以通过dbms_sqltune来为一个sql创建sql profile.这种sql profile是由系统来生成的，我们只有选择和不
选择。其实我们可以通过dbms_spm的非公开过程import_sql_profile来达到此目的.

     首先我们得了解什么是sql profile。sql profile是除了标准的统计信息，参数等一系列的额外信息，比如
对额外的统计，手动的hint.通过sql profile中的信息，optimizer能够更加快速和准确的生成执行计划.而正常的
optimzer运行期间，由于受限于时间的限制，可能产生的计划并不是最佳的。后续引进了sql tune advisor，是一种
让optimzer有足够时间来产生各种执行计划的顾问。

   比如我现在创建一个表，并且在在object_id的栏位上创建了一个索引

   
SQL> create table frank.t1  as select * from dba_objects;

Table created.

SQL> create index ix_object_id on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'FRANK',tabname=>'T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

 

SQL> select object_name from t1 where object_id=10;

OBJECT_NAME
--------------------------------------------------------------------------------
C_USER#

Plan hash value: 575481074

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |

 语句正常执行，计划也是最好的，选择的是走索引，如果运行了dbms_sqltune,也会选择是索引.sql profile
可能都不会生成，现在已经是最好的执行计划了。可是我现在并不想这样，我想使用全表扫描的方式.
通过sql profile很方便就可以实现.

 通过import_sql_profile手动增加import_sql_profile
SQL> begin 
  2  dbms_sqltune.import_sql_profile
  3  (name=>'sp_01',
  4   sql_text=>'select object_name from t1 where object_id=10',
  5   replace=>true,
  6   profile=>sqlprof_attr('FULL()) ;
  7   end;
  8  /

PL/SQL procedure successfully completed.

SQL> select object_name from t1 where object_id=10;

OBJECT_NAME
------------------------------------------------------------------------
C_USER#

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  31mm3rsbs12s4, child number 0
-------------------------------------
select object_name from t1 where object_id=10

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   285 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    30 |   285   (1)| 00:00:04 |

..............

Note
-----
   - SQL profile sp_01 used for this statement

现在sql语句的执行计划是全表扫描了。在最后note处显示了使用了sql profile sp_01.表
明我们创建的sql profile被使用了.

  在这里最麻烦的sqlprof_attr('FULL()是这里的格式如何写.在mos上的文章
note 215187.1中的sqlt.zip的目录utl中提供了脚本coe_xfr_sql_profile.sql可以生成
这些信息.

先加hint运行我们要改的sql，再通过coe_xfr_sql_profile.sql取出sqlprof_attr数据

SQL> select /*+ FULL(t1) */ object_name from t1 where object_id=10;

SQL> @coe_xfr_sql_profile.sql;

Parameter 1:
SQL_ID (required)

Enter value for 1: 83zu6pbuujtxs

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3617692013        .615

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3617692013

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "83zu6pbuujtxs"
PLAN_HASH_VALUE: "3617692013"

..................................

在当前目录下生成了一个sql脚本.coe_xfr_sql_profile_83zu6pbuujtxs_3617692013.sql,用vi打开.
发现里面存在一段这样的定义,这就是我们需要的数据.

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.1.0.7')]',
q'[DB_VERSION('11.1.0.7')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" ,
q'[END_OUTLINE_DATA]');

  再通过import_sql_profile,而sql_text则定义原始的sql,或是一些不能修改源代码应用的sql语句.
通过这种方式,我们实现了人为通过hint改变执行计划.

  sql profile中的hint信息都是存放在sys.sqlobj$data中的comp_data的栏位中.以xml格式存储的
也可以通过从这个栏位来获取sqlprof_attr信息

  在11.2的版本中，dbms_spm也提供了一个过程MIGRATE_STORED_OUTLINE，可以把原来的stored outline
迁移到spm中，通过先建立一个stored outline,然后再迁移到spm中，也可以实现在类似的目的.