oracle 优化(sqltrune篇)

SQL优化----dbms_sqltune详解(1)

(2013-09-14 22:47:46)

转载▼

标签：

dbms_sqltune详解

sql优化

王显伟

sql_profile

自动优化sql

分类： ORACLE新特性实践

作为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所需要的权限

CREATE ANY SQL_PROFILE,

DROP ANY SQL_PROFILE

ALTER ANY SQL_PROFILE

因此要想普通用户执行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;

--查看优化结果

set long 10000 --SQL*PLUS中不要忘记执行，否则看不全优化结果（切记）
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('wxw_sql_tuning_task') FROM DUAL;

三、通过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'

基本上通过以上几种方法足可以应对我们工作需要优化SQL的情况，如何做到不修改SQL优化执行计划，就是我们优化结果中sql_profile，关于优化结果中建议接受sql_profile文件怎么处理？他是怎么影响执行计划的？怎么管理？这个放在第2讲和大家分享！