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.