一.SQL优化概述
1.查找需要优化的TOP SQL,可以通过AWR报告,v$sql,v$sqlarea视图获取,CPU开销,逻辑读,物理读,执行次数,
解析次数,执行时间等
2.优化可能的选择
合理使用索引 索引的选择性
多表链接的顺序,多表复杂连接拆分进行,将复杂SQL语句使用PL/SQL完成,ordered固化连接顺序
使用临时表,在过程中完成SQL操作,一次访问多次使用
大批量数据操作尽可能使用BULK操作 forall
优化相关表的存储结构
数据归档
表和索引分析策略调整
调整SQL执行时间窗口
限制数据查询范围
尽可能分拆过大的SQL 过程替代SQL
注意绑定变量的使用 状态,性别类不适合使用绑定变量
优化应用架构对应用性能影响深远
3.优化的注意事项
风险考虑,收集基线情况,备份回退方案。
4.SQL编写原则
写简单的SQL
只选出需要的字段
如果SQL的逻辑太复杂,拆分为多个SQL,通过一个PL/SQL对象来实现
通过临时表来减少SQL的开销
不要使用占位操作 占位符可能影响优化器的执行计划
适当使用绑定变量
完整的注释
不要把所有操作都交给数据库服务器去做
尽可能将表达式计算好 where a>1.2*3+1 ==> where a>7.6
不要做隐式类型转换 where a=123 ==> where a='123'
子查询的时候注意IN和EXISTS的使用 如果子查询的过滤条件强就用In,如果父查询的过滤条件强就用exists
使用函数索引,组合索引,位图索引的使用
使用HINT来固化执行计划,http://blog.csdn.net/tianlesoftware/article/details/4969702
对于小表,可以放入KEEP缓冲池中,进行全表扫描,而不要通过索引访问
使用表分区和索引分区技术,降低访问大型对象的开销
定期对索引进行重建,恢复索引的性能
二、SQL分析优化工具
1.执行计划进行开销分析,参见执行计划解析
2.PL/SQL分析工具PROFILER
安装PROFILER
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/profload.sql --sysdba执行
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/proftab.sql --调试用户执行
调试对应PL/SQL程序
declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER ('SEQ CACHE IS 1k ');
testseq(20000);--PL/SQL程序
err:=DBMS_PROFILER.STOP_PROFILER ;
end;
格式化输出调试结果:
column RUN_COMMENT format a40 truncate;
select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;
column unit_name format a15 truncate;
column occured format 999999 ;
column line# format 99999 ;
column tot_time format 999999.999999 ;
select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &RUN_ID) p,
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;
3.SQL Tuning Advisor
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT * ' ||
'FROM scott.emp ' || 'WHERE job_id = 'HR_REP' AND ' || 'salary = 6000 ';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'TEST_sql_tuning_task',
description => 'Sample Task');
END;
Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
4.SQL PERFORMANCE ANALYZER(SPA)
捕获相关SQL形成SQL tuning set,改前性能评估,修改,改后评估
手动和EM两种方式