oracle 优化

1. PLSQL程序优化原则

1.1 导致性能问题的内在原因 ,从系统底层分析也就是如下几个原因：

     l  CPU占用率过高，资源争用导致等待

     l  内存使用率过高，内存不足需要磁盘虚拟内存

     l  IO占用率过高，磁盘访问需要等待

1.2 PLSQL优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。

    l PLSQL程序占用CPU的情况

        n 系统解析SQL语句执行，会消耗CPU的使用

        n 运算（计算）会消耗CPU的使用

    l PLSQL程序占用内存的情况

        n 读写数据都需要访问内存

        n 内存不足时，也会使用磁盘

    l PLSQL程序增大IO的情况

        n 读写数据都需要访问磁盘IO

        n 读取的数据越多，IO就越大

 

大家都知道CPU现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL性能优化的重点也就是减少IO的瓶颈，换句话说就是尽量减少IO的访问。

性能的优先级CPU->内存->IO，影响性能的因素依次递增。

根据上面的分析，PLSQL优化的核心思想为：

1.       避免过多复杂的SQL脚本，减少系统的解析过程

2.       避免过多的无用的计算，例如：死循环

3.       避免浪费内存空间没有必要的SQL脚本，导致内存不足

4.       内存中计算和访问速度很快

5.       尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想。

6.       尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。

 

Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式.

RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。

CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。 这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

优化模式包括Rule、Choose、First rows、All rows四种方式：   自从Oracle8.1.5引入dbms_stats包，Oracle及专家们就推荐使用dbms_stats取代analyze。

理由如下：

1.    dbms_stats可以并行分析

2.    dbms_stats有自动分析的功能(alter table monitor ) 

3.    analyze分析统计信息的有些时候不准确

表分析  索引分析

exec dbms_stats.gather_table_stats(ownname => 'cy_test',tabname => 'COM_T_PARAM',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for all indexed columns') ;

exec dbms_stats.gather_index_stats(ownname => 'cy_test',indname => 'PK_COM_T_PARAM',estimate_percent => '10',degree => '4') ;

ANALYZE   TABLE   tablename   COMPUTE|ESTIMATE|DELETE   STATISTICS;

analyze index indexname COMPUTE|ESTIMATE|DELETE  statistics;

 

sql 优化

1.并行

 insert  /*+ append parallel(test) */  into test select * from  test

2.将指定的表作为连接次序中的首表,一般将大表作为首表

select /*+LEADING(test2)*/  count(1) from test,test2

3.SELECT子句中避免使用*

4.用EXISTS替代IN

5.用NOT EXISTS替代NOT IN

6.用表连接替换EXISTS

7.用EXISTS替换DISTINCT

8.减少对表的查询

9.避免循环（游标）里面嵌查询

10.尽量用union all替换union

11.使用DECODE函数来减少处理时间

（低效)

 select count(1) from ac01 where aab001=’100001’and aac008=’1’;

 select count(1) from ac01 where aab001=’100001’and aac008=’2’;

（低效)

Select count(1),aac008  From ac01 Where aab001=’100001’   and aac008 in (’1’,’2’) group by aac008;  

（高效）

select count(decode(aac008,’1’,’1’,null)) zz, count(decode(aac008,’2’,’1’,null))tx from ac01 where aab001=’100001’;

12.group by优化

13.尽量避免用order by

14.用Where子句替换HAVING子句

15.使用表的别名(Alias)

16.删除重复记录 最高效的删除重复记录方法 (因为使用了ROWID) DELETE FROM ac01 a WHERE a.rowid > (SELECT MIN(b.rowid) FROM ac01 b WHERE a.aac002=b.aac002 and a.aac003=b.aac003 );

17.COMMIT使用 数据转换的程序需要关注这一点。

18.减少多表关联

索引优化

索引的选择

建议一：在基数小的字段上要善于使用位图索引。

建议二：创建索引的一些限制条件。

建议三：多表连接查询的索引设计。

建议四：在表的更新速度与查询速度之间寻求一个平衡点。

1.避免在索引列上使用函数或运算

2.避免改变索引列的类型

3.避免在索引列上使用NOT

4.用>=替代>

5.避免在索引列上使用IS NULL和IS NOT NULL

6.带通配符（%）的like语句,%在常量前面索引就不会使用.

7.总是使用索引的第一个列,如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。