Oracle 性能优化

1. 系统级优化

• 数据库参数配置

  • 合理分配SGA及其内部参数（经验值如下）：

    • SGA=phy*（60%-80%）

    • Share pool=SAG*45%

    • DB Cache=SGA*45%

    • Log Buffer: 1~3M

    注：Oracle9i在Windows下有bug， 是由Windows下的SGA最大值有2G的限制造成的

  • 注意调整process和open cursor参数，这两个参数直接影响数据库的session量

• 分离表和索引：将表和索引建立在不同的表空间，决不要将不属于Oracle内部系统的对象存放到SYSTEM表空间。同时，确保数据表空间和索引表空间置于不同的硬盘，减少I/O竞争；

• 如果是企业版数据库，大表可以考虑采取分区存储措施，提高系统的性能；

• 优化Export和Import工作：使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度

• 定期分析查询计划，提高数据库的性能；

2. 索引相关

• 要对经常查询的字段建立索引，但是由于索引管理的开销，在增删改操作频繁的情况下避免建立不必要的索引；

• 对于只读或者接近只读的场合，如数据仓库，对于势值比较小的列可以考虑使用bitmap索引；

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

3. SQL相关

• Oracle的From子句表的顺序：记录越多的表放在越前面（左）；

• Oracle的where子句表达式的顺序：过滤掉最大数目记录的条件放到where子句的末尾；

• Select子句中避免使用'*'，增加了查询表的列的开销；

• 在执行结果等效的情况下，使用Truncate代替Delete；

• 为了在查询过程中要尽量使用索引，对于like语句避免使用右匹配或者中间匹配的模糊查询；

• 将过滤条件尽可能放到Where子句中，而不是放到Having子句中；

• 在SQL语句中，要减少对表的查询，特别是在含有子查询的SQL子句中；

• 使用表的别名可以减少解析的时间并避免引起歧义；

• 使用exists替代in；

• 用NOT EXISTS替代NOT IN；

• 通常情况下，采用表连接的方式比exists更有效率；

• 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换；

• 使用>=替代〉，这样DBMS可直接跳到等于的记录上，可能会避免向前的扫描工作；

• 用union替代or，通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

• 避免在索引列上使用计算，如where语句中的表达式sal *12 > 2500应该改为 sal 〉 2500/12；

• 避免在索引列上使用IS NULL和IS NOT NULL工作；

• 如果必要，使用函数索引，如对Upper（name）建立索引；

• 避免在索引列上使用Not，这样将执行全表扫描；

• 如果可能，用union-all替换union语句：当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

• 避免使用耗费资源的操作：带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎，执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

• 尽量避免不必要的排序；

4. 表设计和其它

• 建立数据库表时要尽量避免不必要的冗余项，但是为了提高以后的查询效率，可以考虑适当的冗余，这需要做权衡；

• 能够批量查询的情况，不要使用循环语句单次查询，避免IO开销；

• 多使用Commit语句：在一个事务中，避免大批量修改的，这样可能由于不断扩充回滚段而影响性能；

• 在写完一条SQL语句后，使用适当的工具（如Explain plan）检查SQL语句的性能；