第十六课
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;
/
升级