作为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讲和大家分享！
DBMS_SQLTUNE是10g引入的一个新特性，它可以通过自动优化性能较差SQL，并给出合理的优化建议，可以通过OEM进行管理，其中优化建议中 的sql_profile文件它是一个存储在数据字典中的信息集合，在SQL执行过程中自动发现并修改较差的估值，进而影响优化器选择最优的执行计划，因 此sql_profile文件可以达到不修改应用程序的情况下修改执行计划。
一、执行DBMS_SQLTUNE所需要的权限
执行dbms_sqltune需要advisor权限
grant advisor to user;
grant select_catalog_role to user; --通过OEM管理必不可少
grant execute on dbms_sqltune to user;
二、sql_profile文件的作用
sql_profile不包含单独的执行计划，但是，提供以下信息供优化器选择执行计划
A.数据库配置、绑定变量、优化统计信息、数据集等信息
B.追加的统计信息
值得一提的是，sql_profile并不会以outline方式存储冻结执行计划，当表中数据增长或索引被删除或重建时，在sql_profile不变 的情况下执行计划也可以发生变化，信息的存储和与数据的分布或者访问路径有关，但是，经过长时间运行以后，存储的内容就会过时，可以通过先前同样的SQL 优化语句进行优化，重新产生sql_profile文件。
三、sql_profile文件相关设置
sql_profile的控制范围可以通过dba_sql_profiles中的category属性进行控制，如果数据库sqltune_category参数值为DEFAULT，那么此时对所有用户会话session有效，可以通过以下语句查询
show parameter sqltune_category;
select category,name from dba_sql_profiles;
通过修改dba_sql_profile中的category属性来控制sql_profile对那些会话进行控制，举个例子来说，如果将 dba_sql_profile中的category属性改为DEV，数据库参数sqltune_category也改为DEV，那么只有这个DEV用户 的会话信息受sql_profile的影响，其它用户不受影响，这种设置常用于开发过程中的测试，生产库中不常见
四、sql_profile文件的管理
sql_profile文件可以通过两种方式进行管理，一种是DBMS_SQLTUNE包进行管理，另一种是通过OEM控制台进行管理
Using Enterprise Manager
- On the Performance page, click Top Activity.
The Top Activity page appears. - Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
The SQL Details page appears. - Click the Plan Control tab.
A list of SQL profiles is displayed under SQL Profiles and Outlines. - Select the SQL profile you want to manage.
Do one of the following:
- To enable a SQL profile that is disabled, click Disable/Enable.
- To disable a SQL profile that is enabled, click Disable/Enable.
- To remove a SQL profile, click Delete.
- A confirmation page appears.
Click Yes to continue, or No to cancel the action
Using DBMS_SQLTUNE package.
To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.
Accepting a SQL ProfileUse the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task',
name => 'my_sql_profile');
END;
my_sql_tuning_task is the name of the SQL tuning task.
You can view information about a SQL Profile in the DBA_SQL_PROFILES view.
Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure.
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
In this example, my_sql_profile is the name of the SQL Profile that you want to alter.
The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.
A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
end;
/
需要注意的是，sql_profile文件可以使SQL中的hint失效，因此当我们在管理数据库中研究执行计划时，如果发现hint没有启作用，有可能是sql_profile文件影响的结果！
四、查看所有优化任务集
SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1816630/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29337971/viewspace-1816630/
扫一扫
422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
