16 - Oracle的SQL调优

第十六课

16、 SQL调优

一般情况下，效果最明显的优化工作是SQL 优化。没有很好地优化的SQL 会不必要地使用过多资源。

可以使用一组SQL 指导来确定和优化语句（单独优化或集体优化）。

SQL Tuning Advisor:STA SQL优化指导
SQL Access Advisor:SAA SQL访问指导
SQL Performance Analyzer:SPA SQL性能分析
SQL Tuning Set SQL调优集

统计信息收集:




SPD:SQL计划指令

select * from dba_sql_plan_directives;
select * from dba_sql_plan_dir_objects;

自适应执行计划: 该特性让优化器optimizer 可以在运行时(runtime)自动适配一个性能不良的执行计划， 并避免在后续仍选择该性能糟糕的执行计划。
12.2参数:
optimizer_adaptive_plans=TRUE
optimizer_adaptive_statistics=FALSE

使用SQL指导:

SQL访问指导、SQL优化指导、SQL修复指导

自动SQL优化结果:





创建SQL调优集:

SQL Tuning Advisor:STA
SQL 优化指导分析单个 或多个SQL 语句, 并建议索引, SQL 概要文件, 重构的 SQL 和统计信息, 它们会改善 SQL 语句的性能。
执行以下分析:
 分析统计信息
 分析SQL
 访问路径:分析新建索引的影响
 分析结构
SQL优化指导可以使用EMDE。



大表关联查询:

SELECT a.id, b.id
  FROM t a, t b
 WHERE a.id = b.id
       AND b.id = 500;

使用dbms_sqltune:

DECLARE
  task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'select max(object_id) from t';
  task_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext);
  dbms_output.put_line(task_name);
  dbms_sqltune.execute_tuning_task(task_name => task_name);
END;

select dbms_sqltune.report_tuning_task(‘任务_11’) from dual;

重复的SQL：




SQL Access Advisor:SAA
SQL访问指导主要对段结构使用工作负荷规范进行总体SQL性能的分析,优化指导给出建议比较少,访问指导全面一些。
SQL优化指导有自己的API,但SQL访问指导没有,可以使用dbms_advisor.quick_tune。
SQL访问指导的建议包括:
 索引(B树、位图、函数)
 物化视图和物化视图日志
 分区策略

创建SQL优化集:

select * from t where id=4000001;
select * from table(dbms_xplan.display_cursor);

BEGIN
  dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor
                         ,'task1'
                         ,'select count(*) from t');
END;

select * from user_advisor_actions;

create directory acc_scripts as '/u01/access';
grant read,write on directory acc_scripts to public;

BEGIN
  dbms_advisor.create_file(dbms_advisor.get_task_script('task1')
                          ,'ACC_SCRIPTS'
                          ,'acc.sql');
END;

CREATE MATERIALIZED VIEW LOG ON
    "OUZY"."T"
    WITH ROWID, SEQUENCE 
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "OUZY"."MV$$_00270000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT COUNT(*) M1 FROM OUZY.T;

begin
  dbms_stats.gather_table_stats('"OUZY"','"MV$$_00270000"',NULL,dbms_stats.auto_sample_size);
end;
/

升级