Oracle 通过sql profile为sql语句加hint

sql profile最大的优点是在不修改sql语句和会话执行环境的情况下去优化sql的执行效率,适合无法在应用程序中修改sql时.

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.

-----coe_xfr_sql_profile.sql
SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.3.5 2011/08/10 carlos.sierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM [email protected]
REM
REM SCRIPT
REM coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM This script generates another that contains the commands to
REM create a manual custom SQL Profile out of a known plan from
REM memory or AWR. The manual custom profile can be implemented
REM into the same SOURCE system where the plan was retrieved,
REM or into another similar TARGET system that has same schema
REM objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM 2. Plan Hash Value for which a manual custom SQL Profile is
REM needed (required). A list of known plans is presented.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as SYSDBA or user with access to
REM data dictionary.
REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM plan hash value (parameters can be passed inline or until
REM requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_xfr_sql_profile.log
REM 2. If SQLT is installed in SOURCE, you can use instead:
REM sqlt/utl/sqltprofile.sql
REM 3. Be aware that using DBMS_SQLTUNE requires a license for
REM Oracle Tuning Pack.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;