空间管理您的位置: 51Testing软件测试网 » 江湖吉祥物的个人空间 » 日志

ORACLE优化工具包DBMS_SQLTUNE

上一篇 / 下一篇 2016-11-02 14:10:18 / 个人分类：oracle数据库优化工具包

查看( 2022 ) / 评论( 0 ) / 评分( 0 / 0 )

DBMS_SQLTUNE是10g引入的一个新特性，它可以通过自动优化性能较差SQL，并给出合理的优化建议，可以通过OEM进行管理。

使用DBMS_SQLTUNE包进行调优的一般步骤：

1、赋予用户advisor权限；

grant ADVISOR to user

2、创建sql tuning任务；

在实际工作中，我们常遇到三种情况下的SQL类型的SQL优化

1.完整的SQL语句，无绑定变量（最简单，但不常见）

2.通过AWR获得的SQL语句，这种SQL有绑定变量

3.在V$SQL不存在的SQL语句

针对以上三种情况，使用DBMS_SQLTUNE包进行优化：

1.无绑定变量型sql，通过sqltext进行优化

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  => 'TEST_sql_tuning_task',
                                                 description => 'tune the bad sql');
dbms_sqltune.Execute_tuning_task(task_name => 'TEST_sql_tuning_task');
END;

2.通过sqlID进行优化

declare
l_tuning_task varchar2(30);
begin
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => 'XXXXXXXX');
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);--需要跟据此处输出值进行查询
end;

SELECT dbms_sqltune.report_tuning_task('TASK_XXX') from dual;

3.通过指定AWR中的Snap_id进行优化

declare
    my_task_name varchar2(30);
begin
    dbms_sqltune.drop_tuning_task(task_name => 'TEST_sql_tuning_task');
    my_task_name := dbms_sqltune.create_tuning_task(
         begin_snap     => 12345,
         end_snap       => 12346,
         sql_id         => 'XXXXXXXXX',
         plan_hash_value => null,
         scope          => 'comprehensive',
         time_limit     => 60,
         task_name      => 'TEST_sql_tuning_task',
         description    => 'tune the bad sql'
    );
    dbms_sqltune.execute_tuning_task (task_name => TEST_sql_tuning_task');
end;

3、查看任务名；

SELECT TASK_NAME
FROM DBA_ADVISOR_LOG
WHERE OWNER = 'USER';

4、执行sql tuning任务；

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TEST_sql_tuning_task' );
END;

5、查看sql tunning任务状态；

SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = 'TEST_sql_tuning_task' ;

6、展示sql tunning结果；

SET LONG 10000 --SQL*PLUS中不要忘记执行，否则看不全优化结果
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_sql_tuning_task' )
FROM DUAL;

7、执行优化策略；

查看建议报告，选择性执行sql_profile

sql_profile文件它是一个存储在数据字典中的信息集合，在SQL执行过程中自动发现并修改较差的估值，进而影响优化器选择最优的执行计划，因此sql_profile文件可以达到不修改应用程序的情况下修改执行计划。

sql_profile的控制范围可以通过dba_sql_profiles中的category属性进行控制，如果数据库sqltune_category参数值为DEFAULT，那么此时对所有用户会话session有效，可以通过以下语句查询

show parameter sqltune_category;

select category,name from dba_sql_profiles;

8、完成后删除sql tunning任务

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('TEST_sql_tuning_task' )

9、其他说明

修改sql tunning任务的参数

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'TEST_sql_tuning_task',
parameter => 'TIME_LIMIT', value => 300);
END;

ORACLE优化工具包DBMS_SQLTUNE

相关阅读:

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅