Oracle 通过sql profile为sql语句加hint

sql profile最大的优点是在不修改sql语句和会话执行环境的情况下去优化sql的执行效率,适合无法在应用程序中修改sql时.
sql profile最常用方法大概是:
--创建产生sql tuning advisor任务
DECLARE
  tuning_task varchar2(100);
  l_sql_id    v$session.prev_sql_id%TYPE;
BEGIN
  l_sql_id    := '6w02d3ggsj4xb';
  tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  dbms_sqltune.execute_tuning_task(tuning_task);
  dbms_output.put_line(tuning_task);
END;
--查看任务内容
SELECT dbms_sqltune.report_tuning_task('tuning_task') FROM dual;
--应用任务,产生sql profile
begin
  dbms_sqltune.accept_sql_profile(task_name  => 'tuning_task',
                                  replace    => TRUE,
                                  force_match => true);
end;
但上述方法主要是依赖sql tuning advisor,如果它无法生成你想要的执行计划.你还可以通过手动的方式,
通过sql profile把hint加进去.这里主要用到coe_xfr_sql_profile.sql这个sql,来产生原语句的outline data和
加hint语句的outline data,然后替换对应的SYS.SQLPROF_ATTR,最后执行生成的sql就可以了.
以下是测试(测试环境11.1.0.7):
1.建立测试表和数据
--建表
create table scott.test as select * from dba_objects;
--建索引
create index scott.idx_test_01 on scott.test(object_id);
--收集统计信息
exec dbms_stats.gather_table_stats('scott','test',cascade=>true);
--更新数据,使用数据分布不均匀
update scott.test set object_id=10 where object_id>10;
commit;

2.执行查询语句
--执行原有的查询语句,查看执行计划发现走索引,实现这时表中大部分行的object_id都已经被更新为10,所以走索引是不合理的.
select * from scott.test where object_id=10;
--执行现有加hint的查询语句,使其走全表扫.
select /*+ full(test)*/* from scott.test where object_id=10;

3.查询上面两个语句的sql_id,plan_hash_value
--查询原语句的sql_id,plan_hash_value
select sql_text,sql_fulltext,sql_id,plan_hash_value from v$sql
where sql_text like 'select * from scott.test where object_id=10%';
/*    sql_id          plan_hash_value  */   
/* cpk9jsg2qt52r      2317948335*/

--查询加hint后语句的sql_id,plan_hash_value
select sql_text,sql_fulltext,sql_id,plan_hash_value from v$sql
where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';
/*    sql_id          plan_hash_value  */
/* 06c2mucgn6t5g      1357081020*/

4.把coe_xfr_sql_profile.sql放在$Oracle_HOME/rdbms/admin下
coe_xfr_sql_profile.sql的内容在文档最后

5.对上面的两个sql产生outline data的sql.
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
--原语句
SQL> @coe_xfr_sql_profile.sql cpk9jsg2qt52r 2317948335

----加hint后的语句
SQL>@coe_xfr_sql_profile.sql 06c2mucgn6t5g      1357081020


6.替换coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql中的SYS.SQLPROF_ATTR,把它更改为
coe_xfr_sql_profile_06c2mucgn6t5g_1357081020.sql中产生的SYS.SQLPROF_ATTR

----coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql的SYS.SQLPROF_ATTR
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'[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');

----coe_xfr_sql_profile_06c2mucgn6t5g_1357081020.sql的SYS.SQLPROF_ATTR
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" "TEST"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

7.执行替换过SYS.SQLPROF_ATTR的coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql
sqlplus / as sysdba
SQL> @coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

8.查看产生的sql profile,此时原语句在不加hint的情况下也走全表扫了
select * from dba_sql_profiles;

注意:
1.这个测试只是为了演示通过coe_xfr_sql_profile.sql实现手动加hint的方法,实际上面的语句问题的处理最佳的方法应该是重新
收集scott.test的统计信息才对.
2.当一条sql既有sql profile又有stored outline时,优化器优先选择stored outline.
3.force_match参数,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.通过sql profile手动加hint的方法很简单,而为sql添加最合理的hint才是关键.
5.测试完后,可以通过 exec dbms_sqltune.drop_sql_profile(name =>'coe_6w02d3ggsj4xb_2317948335' );删除这个sql profile.

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/d692a27dc785683b30b328174c7a4800.html