性能调优工具01–Sql Tuning Advisor工具的使用

6月 1st, 2014
STA(Sql Tuning Advisor)工具的使用

  1. STA工具其实就是DBMS_SQLTUNE包,要使用STA的话,必须是在CBO模式下使用;
  2. 执行DBMS_SQLTUNE包,需要有advisor角色权限;
  3. DBMS_SQLTUNE包介绍:
    1. DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text, bind_list, user_name, scope, time_limit, task_name, DESCRIPTION) RETURN VARCHAR2;
      1. sql_text:需要优化的sql语句;
      2. bind_list:绑定的变量列表,默认为NULL;
      3. user_name:要优化的sql语句通过哪个用户执行,默认为NULL;
      4. scope:优化范围,有两个取值,limited-分析时不使用推荐的SQL Profile,大约每个语句1s时间;comprehensive-分析时使用推荐的SQL Profile,可能花费的时间较长,默认值;
      5. time_limit:优化过程的时间限制,默认为TIME_LIMIT_DEFAULT;
      6. task_name:优化任务的名称,默认为NULL;
      7. decription:描述信息,默认为NULL;
    2. DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
      1. task_name:要执行的优化任务的名称;
    3. DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name, type, level, section, object_id, result_limit);
      1. task_name:要查看的优化任务的名称;
      2. type:优化报告的类型,TEXT,HTML或者XML,默认是TEXT;
      3. level:格式化的级别,TYPICAL, BASIC, ALL.默认是TYPICAL;
      4. section:优化报告的部分,FINDING,PLAN,INFORMATION,ERROR或者ALL,默认是ALL;
      5. 调用方法:SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) from dual;
    4. DBMS_SQLTUNE.DROP_TUNING_TASK(task_name);
      1. task_name:要删除的优化任务的名称;
    5. DBMS_SQLTUNE.RESET_TUNING_TASK(task_name):重置优化任务的结果;
      1. task_name:优化任务的名称;
      2. 执行过之后就可以重新执行EXECUTE_TUNING_TASK过程了;
  4. 一般的步骤:
    1. 创建优化任务;
    2. 执行优化任务;
    3. 显示优化结果;
    4. 根据建议来做相应的优化;
    5. 删除优化任务;
  5. 创建测试用户:SQLTUNE;                                                            
  6. 创建测试表;                                                                     
  7. 执行查询语句:SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name;
    1. 需要的时间;                                                          
    2. 执行计划;                                                         
    3. 统计信息;                                                          
  8. 创建优化任务;                                                            
  9. 执行优化任务,并查看优化任务的执行进度;                                          
  10. 查询优化结果;
    1. 关于这次优化任务的基本信息:如任务名称,执行时间,范围,涉及到的语句,发现问题的类型及个数的信息等等;      
    2. 发现的问题:两个表没有收集统计信息;                                               
    3. 发现的问题:两个表没有索引;                                                    
    4. 按照优化建议修改前后的执行计划的对比;                                             
  11. 按照建议做相应的修改,然后执行语句验证优化的结果;                                 
  12. 删除优化任务:                                                           
  13. 如果需要对多条语句进行优化时,应该使用STS(Sql Tuning Set);
    1. STS可以从多种数据源(Cursor Cache, AWR, STS)获取SQL;
    2. 创建一个STS:DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ‘sts_test’);
    3. 使用Cursor Cache加载STS:DBMS_SQLTUNE.LOAD_SQLSET();
    4. 查看STS的内容:SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘sts_test’));
    5. 使用STS创建一个优化任务:DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name=>’sts_test’, task_name=>’sta_test’);
  14. 使用EM,在EM->Performance->Advisor Central->SQL Advisors->SQL Tuning Advisor;                      
  15. 单条sql的调优可以使用sqltrpt.sql(执行SQL>@?/rdbms/admin/sqltrpt.sql sqlid即可)脚本传入sqlid即可;对于多条sql语句可以使用dbmssqlt.sql脚本,这个脚本给出了dbms_sqltune包的定义和相关的例子;
— 创建测试用户SQLTUNE;
CREATE USER SQLTUNE IDENTIFIED BY “oracle”;
GRANT CONNECT, RESOURCE, ADVISOR TO SQLTUNE;
CONN SQLTUNE/ORACLE;
— 创建测试表;
CREATE TABLE bigtable AS SELECT rownum id, t.* FROM sys.all_objects t;
CREATE TABLE smalltable AS SELECT rownum id, t.* FROM sys.all_objects t;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
COMMIT;
— 创建优化任务;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := ‘SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => ‘SQLTUNE’, task_name => ‘sta_test’);
END;
—  执行优化任务;
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘sta_test’);
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’sta_test’;
— 查看优化结果;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sta_test’) from dual;
— 执行优化建议;
CREATE INDEX IDX_BIGTABLE_OBJECTNAME ON BIGTABLE(‘OBJECT_NAME’);
CREATE INDEX IDX_SMALLTABLE_OBJECTNAME ON SMALLTABLE(‘OBJECT_NAME’);
EXEC dbms_stats.gather_table_stats(USER, ‘BIGTABLE’, CASCADE=>TRUE);
EXEC dbms_stats.gather_table_stats(USER, ‘SMALLTABLE’, CASCADE=>TRUE);
— 删除优化任务;
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(‘sta_test’);
— 创建SQL Tuning Set创建优化任务;
— create a STS
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => ‘my_sts’);
END;
/

— load STS using cursor cache
DECLARE
l_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN l_cur FOR
SELECT VALUE(t)
FROM TABLE(dbms_sqltune.select_cursor_cache(‘sql_id in (”588rxmp05xt7g”,”7rucbfq8vcr7d”)’)) t;

dbms_sqltune.load_sqlset(sqlset_name => ‘sts_test’, populate_cursor => l_cur);
END;
/

— display contents of STS
SELECT * FROM TABLE(dbms_sqltune.select_sqlset(‘sts_test’));

— drop a sql tuning task
BEGIN
dbms_sqltune.drop_tuning_task(task_name => ‘my_sql_tuning_task’);
END;
/

— create a sql tuning task by using STS
DECLARE
l_task_name VARCHAR2(30);
l_sqltext CLOB;
BEGIN
l_task_name := dbms_sqltune.create_tuning_task(sqlset_name => ‘sts_test’, task_name => ‘my_sql_tuning_task’);

END;
/
标签:
本文的评论功能被关闭了.