Oracle数据库优化策略

梦想与现实

大家都希望数据库是很快的，任何查询都能在毫秒级得到结果，不管数据量有多大，不管表之间关联有多复杂。
实际上大部分性能问题都是因数据库处理数据不够快而引起，很多查询速度奇慢无比，经常导致数据库疲惫不堪，表之间关联比较多，特别是当这些关联的表都是数据量几百万的大表的时候更慢。要特别指出的一点是，很多项目组完全没有DBA，很多开发人员对数据库的了解都不够深入。
梦想与现实往往反差巨大，对数据库性能的考虑应越早越好！越早对性能予以关注和修正其收益越大，在需求及设计时就应当仔细考量性能，当出现性能问题再来调整时常常是代价极其高昂的。

生产数据库的配置

强烈建议找专门的DBA去搭建生产环境数据库，不要自己去搞
调节共享池空间
调节数据高速缓存空间
调节其他SGA空间
数据库配置和磁盘I/O问题
优化排序操作
诊断锁存器竞争
优化回滚段
监视和诊断锁
优化Oracle共享服务器
调节Oracle块
优化操作系统和使用资源管理器

正确使用索引

索引与表的表空间分开
该建的索引一定要建，不该建的索引一定不要建
建立合适的索引
删除选择性差的索引
EOS工作流表有些索引建得不对
注意索引是否失效
注意索引的使用，‘!=’ 将不使用索引，尽量不要使用“like ‘%...%’”等
对于部分表的设计可以考虑不存在NULL，如列定义为NOT NULL，对于NULL值的话可以使用如“9999999999999”等特殊值来代替，这样对于大表的 is Null的查询可以修改为 =“”，可进行索引查询
绑定变量要带入date类型的值，同时，对此表的查询访问尽可能地使用小范围的时间段
Date VS timestamp，建议全部用timestamp
索引不是越多越好，大量从来或者几乎不用的索引，对系统只有损害，可以监控无用的索引
更多索引的知识可以到网上去搜索

SQL执行路径与hints

《sql性能的调整-总结.pdf》
Oracle的优化器：RBO/CBO
建议使用CBO，使用CBO的话要定时分析大表，得到统计信息
optimizer_mode：RULE/CHOOSE/ALL_ROWS/FIRST_ROWS
Oracle的执行计划：explain plan/PLAN_TABLE/toad
使用hints：/*+rule*/、/*+first_rows(10)*/、/*+all_rows*/、/*+choose*/、/*+full(table)*/、/*+index(table_index_name)*/、/*+ordered*/等
一般不需要把hints写入sql中，oracle本身的优化器完全可以找到最优的路径，只是当oracle找到的不是最优的路径时一个补充手段，这种情况一般来说极少出现。

物化视图（快照）

物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
快照snapshot是oracie 8i提出的，快照snapshot 和物化视图materialized view 是一个概念的不同说法，oracle在9i的文档中提及snapshot将不被引用，通过materialized view 替代。
命名：快照名称、指标字段名称，SHOT_YBGZ_XXX
注释：有足够的注释
刷新时间的指定：影响整个系统的负荷
脚本的可读性：长度、复杂的、最优性、效率
最多建立四层物化视图

修改应用或设计

数据库表结构、视图的设计要合理，如果它不合理的话，通过其他方式优化性能是很难的，有时候一定要修改不合理的数据库设计
long型字段应尽量从主表分离
减少数据库查询次数 ，如使用EOS的业务数据字典这种方式把一些使用频率非常高的数据缓存起来
尽量避免使用order by和group by字句
确定能用union all的地方就不要用union
尽量少用子查询，特别是相关子查询
数据冗余
减少分页查询
在系统设计时尽量不要设计大而全的表

数据表分区

一般由DBA来做
数据量千万级以上分区可带来明显的性能提升，数据量少的时候分区可能导致性能更差，EOMS目前的百万级的数据量使用分区方式不能带来较大性能提升
使用分区的优点：
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
Oracle数据库提供对表或索引的分区方法有三种：
范围分区
Hash分区（散列分区）
复合分区
http://www.cnoug.org/viewthread.php?tid=3171&highlight=%B7%D6%C7%F8%B1%ED
http://www.cnoug.org/viewthread.php?tid=62895&highlight=%B7%D6%C7%F8%B1%ED

数据转存

EOS工作流表的数据保存到历史表中
应用表的数据保存到历史表中

限制某些耗资源操作的使用

把一些非常耗资源的功能，比如导入导出操作，批量操作，复杂查询等，在系统使用高峰时段限制使用，比如在早晨9点到10间这些功能不开放使用，从而错开高峰时段。
把一些非常耗资源的功能限制用户使用，比如用户使用一次后，要过1分钟才能再次允许使用

其他途径

网上搜索解决方法
找更专业的DBA支援
买ORACLE服务

相关资源

http://www.cnoug.org/
《Database Performance Tuning Guide and Reference.pdf》
http://www.oracle.com/