oracle物理读优化,oracle 性能优化 06_sql优化

一.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两种方式