oracle sql_tunne ORACLE提供的sql优化

她人

作为ORACLE DBA对SQL优化是必备的技能，常用的方法就是看执行计划，加hint或者修改SQL写法，但现实的工作中，往往修改应用的SQL是非常困难的，特别是对电信、银行这个行业，修改应用程序过程十分繁杂，有没有办法不修改SQL就可以达到优化SQL执行计划的方法呢？答案是肯定的，从ORACLE 10g开始ORACLE提供了自动优化功能，经过一段时间的总结运用，和大家分享一下如何运用DBMS_SQLTUNE对SQL进行优化。
在实际工作中，我们常遇到三种情况下的SQL类型的SQL优化
1.完整的SQL语句，无绑定变更（最简单的一种，也是不常见的一种）
2.通过AWR获得的SQL语句，这种SQL有绑定变量
3.在V$SQL不存在的SQL语句
针对以上三种情况的优化，和大家分享一下我的优化语句，既然要用这个包，必须要有相应的权限
一、执行DBMS_SQLTUNE所需要的权限
grant advisor to scott
因此要想普通用户执行DBMS_SQLTUNE包进行优化，需要赋相应的权限
二、无绑定变量SQL的优化
这种是最简单的一种，通常开发的同事用的比较多，详细SQL如下：
执行优化任务：
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from orabpel.cube_scope';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'orabpel',
scope => 'COMPREHENSIVE',
time_limit => 60, --优化限时60s
task_name => 'wxw_sql_tuning_task',
description => 'tune the bad sql');
dbms_sqltune.Execute_tuning_task(task_name => 'TEST_sql_tuning_task');
END;
--查看优化结果
select task_name,status from user_advisor_tasks where task_name=''-----查看是否创建成功
set long 10000 --SQL*PLUS中不要忘记执行，否则看不全优化结果（切记）
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('wxw_sql_tuning_task') FROM DUAL;
dbms_sqltune.drop_tuning_task('task_name');----删除执行计划
三、通过SQL_ID进行优化
这种情况通常常用于v$sql中的异常SQL优化
declare
l_tuning_task varchar2(30);
begin
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '9qjctmkwvny7j');
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);--A需要跟据此处输出值进行查询
end;
--查看执行计划
select dbms_sqltune.report_tuning_task('task_27888') from dual; --此处标红值是--A此的输出
注意，在实际执行过程中，如果sql_id已经不在v$sql中会报错，此时这种方法已经无用武之地了，怎么办呢，下面我们看第三种方法，指定AWR报告中的snap_id进行优化
四、通过指定AWR中的Snap_id进行优化
declare
my_task_name varchar2(30);
begin
dbms_sqltune.drop_tuning_task(task_name => 'wxw_sql_tuning_task');
my_task_name := dbms_sqltune.create_tuning_task(
begin_snap => 24365,
end_snap => 24366,
sql_id => 'd40kghyfbg8sj',
plan_hash_value => null,
scope => 'comprehensive',
time_limit => 60,
task_name => 'wxw_sql_tuning_task',
description => 'tune the bad sql'
);
dbms_sqltune.execute_tuning_task (task_name => 'wxw_sql_tuning_task');
end;
--查看sql优化结果
select dbms_sqltune.report_tuning_task('wxw_sql_tuning_task') from dual;
注意，此方法仍有一个弊端，前提需要AWR报告中的Snap_id中在数据库中存在，我们知道，在10g中默认保留七天，11g中默认保留八天，一旦过了这个时间，这种方法也无用武之地，既不存v$sql中，AWR也过期，此时我们还有一种方法进行优化，那就是通过无绑定变量SQL进行优化，但此时会带来一个问题，我们AWR报告中的SQL是带有绑定变量的都是：1 ，：2这种形式，如何转换呢？继续往下看
四、绑定变量还原
此时分两种情况，一种是在v$sql中存在的SQL，一种是在v$sql中不存在的SQL
1> 转化v$sql中对应的绑定变量值
跟据SQL_ID取v$sql中bind_data的值
select position, value_string
from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));
2>如果v$sql中不存在，可以用此方法获取绑定变量值
select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 6).value_string
from sys.wrh$_sqlstat
where sql_id = '1nnrufx6sw8sd'