[原创]Oracle数据库性能优化的方案[有删节]

本方案在实际的问题处理中有着不错的效果，只使用了方案中的部分措施，已经提高了几百倍的性能。特别是一个关于三张百万数据的表联合查询并经过业务运算后，再单个事务提交130万行数据写入的存储过程，由原来的几小时无法执行完成，变成119秒完成。因此这里给初学者分享经验的同时，希望得到数据库高手们的进一步指点。

1      问题
XXXXXXX系统是一个业务数据处理集中，单事务中有很多百万级别大批量的数据操读写和查询操作。

目前性能比较低，部分运算步骤需要长时间运行。超过30分钟以上，甚至个别过程运算1.5小时无结果。

 

2      目的
本文试图做一些特定分析，并针对性提出性能的优化和改进方案；

 

3      分析
3.1     分析方法
l         使用性能检测日志

l         使用Oracle管理控制台的锁查看机制

l         SQL状态跟踪

l         数据库scheme浏览

 

3.2     分析结果
l         目前整个运算逻辑已经全部使用Oracle存储过程。

l         使用显式游标。并尽量使用了批量操作方式提高性能。

l         数据库索引建立不够合理。

l         目前一期的数据就会在多个单表删除或者插入26万到105万的数据

l         没有数据历史表，也没有后台自动运行机制。

 

4      改进措施
4.1     优先使用的方法
4.1.1              索引的建立
l         字典类表的数据量比较小，查询中使用较多，需要把查询时相关的字段全部建立索引。适合数据量小于5000以下的情况。

l         经过测试发现，实际运行的数据多为字符串型。并且字符串的前半部分内容几乎一样，后半部分差异较大。因此推荐索引类型为——反向B树索引。

l         大数据量的表因为往往在一次事务中，提交百万级别的数据。建议建立在insert操作前DROP索引，insert后创建新的索引。虽然索引的创建和删除也消耗时间。但中间的空白块的消除，会对性能提高有好处。

【备注：大数据量的表建立索引一定要尽量少和高效】

 

4.1.2              物化视图的使用
在数据需要变更时再REFRESH视图，节省每次都动态查询带来的额外开销。

【备注：可以视情况，为物化视图创建索引，进一步提高检索速度】

 

4.1.3              临时表的使用
和物化视图类似，但有几个特别的特点。

l         是减少REDO日志的开销。

l         是不需要物理存储到硬盘上，减少磁盘操作时间。

l         如果是需要每次重建数据的场合，不需要删除数据的时间开销。

【关联措施：参见4.2.1】

 

4.1.4              经济的使用字段大小设计。
避免数据库空间浪费带来的性能开销。

 

4.1.5              避免不必要的字段函数处理后的关联查询
注意建立函数索引或者取消函数处理后的关联。避免全表扫描。

 

4.2     数据库服务器的改进措施
4.2.1              建立使用9.20以上版本的Oracle数据库
因为这样可以较好的支持临时表，使用临时表大幅度减少REDO日志的开销。

 

4.2.2              使用RAID策略，成倍加快硬盘IO操作
可以同时分写不同的数据库硬盘，并行操作。

 

4.2.3              将大的数据表分散建立在多个硬盘上，提高并行能力
分区建表。

 

4.2.4              建立单独的数据索引空间
提高索引大数据量时候的性能。

 

4.3     进一步改进的方法
4.3.1              历史数据系列表的建立，分散单表的数据压力
随着系统使用时间的发展，数据量还会进一步加大。不分表将无法支持。另外业务特性决定了每期的关联不十分紧密，适合建立历史数据表。

由于数据库设计的改动将导致处理逻辑的变化，带来开发和测试的一定工作量。

【提示：历史表的一个附加的好处是，遇到数据整期清除的时候，可以使用TRUNCATE，性能将会比目前使用的DELETE提高许多倍，而且数据量越大，索引越多，效果差别越大】

 

4.3.2              数据表建议重新以性能为优先重新设计
目前全是INSERT、SELECT、DELETE，应该合理使用UPDATE。

 

4.3.3              建立后台运行机制
做后台运行机制，并给出运行大致进度比例。

 

4.3.4              单无意义字段标识代替联合字段标识
发现有多个大表都使用六到八个字段作为数据唯一的标识，建议用单个无意义字段代替。这个涉及开发测试工作，所以放在进一步的工作建议里。

 

5      最后补充
从系统长远使用考虑，建议相关人员能逐步完成所有的优化工作。