的大数据解决方案.docVIP

基于Oracle的海量数据查询优化策略 梅浩 (宿迁学院 10软件2班 20100311227) 摘　要：当对Oracle中存储的海量数据进行访问查询时，经常出现系统响应过慢的情况，并且有占用系统资源过多的情况。本文运用实例进行对Oracle数据库中的查询优化进行了深入的研究，实验结果显示采用的方法是有很大作用的，较大幅度的缩短了测试用例表的响应时间，最终并对海量数据的优化策略提出了实用性的建议。　　关键词：Oracle数据库 查询优化 海量数据　　1 引言　　一些大的应用系统如医保、移动、银行等行业的应用系统，出于节约管理成本、提高数据共享度等方面的考虑，业务数据一般以省为单位集中，数据库 中存放的数据量很大(一般为T级)。如何对其进行快速访问，提高系统响应时间就显得十分重要。在实际应用中，往往采用各种优化措施，使得SQL查询经过数据库优化器的处理，得到最佳的执行计划，即数据访问路径，来达到提高响应速度的目的。　 　2 查询顺序及其调整　　Oracle一般进行如下：环境调整、实例调整、对象调整、SQL调整。我们的测试采用的数据表容量为60 M，记录条数为60万条。测试策略为：依据用户的查询要求得到用户需要浏览的数据记录的开始位置，然后在SQL语句中加入此位置，执行SQL语句。使用的SQL语句如下：SELECT＊FROM（SELECT＊FROM（SELECT＊FROM BIG）WHERE ROWNUM＜TOPOS ORDER BYROWNUM DESC）WHERE ROWNUM＜TOPOSFROMPOS＋1；使用该SQL查询得到结果集需要5～6 s，这个响应速度难以满足用户浏览要求，因此必须对其优化以提高响应速度。我们是在假定环境调整已经完成的条件下通过对实例、对象、SQL查询语句的调整得出结论的，其中重点是对SQL语句的调整。 　2．1 实例调整　　首先进行实例调整。实例涉及到SGA内存区和一组Oracle后台处理进程。对实例的调整就是对SGA内存区和Oracle后台处理进程的调整。在对该问题的解决中，主要是针对SGA内存区的调整。　 2．1．1 内存区结构　　SGA就是系统全局区，是指内存中允许多个进程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局的可用的。图1为SGA结构图。 2．1．2 调整结构　　较大的缓冲区高速缓存可以缓存较多的数据块，从而提高命中率，进而节约物理磁盘读取的代价；大的共享池意味着大的库高速缓存。库缓存的内存结构如图2所示。 　　 2．2　对象调整　　在解决该问题时，我们没有对对象的存储参数进行调整。对象的存储参数的调整要建立在具体对象的基础之上，我们使用的测试大表除了在容量上模拟可能的实际对象之外，和实际对象并没有多少可比之处。举例来说，pctfree参数是指在实际的存储块中，留出来为块中的记录扩展所用的空闲空间占该存储块容量的百分比，这个参数和实际表记录的长度及可扩展性有关，要在设计出实际表之后进行调整。 　2．3　SQL语句调整　　（1）添加索引　　在对大表进行扫描时，首先要避免不必要的全表扫描。最常见的做法就是给大表添加索引。给大表添加索引后，我们可以通过访问索引的方式获得记录的物理位置，从而达到访问表的目的。设想一下，对于一个拥有大量字段的表，如果只需要返回其中少量字段，那么在这些字段上建立索引，通过索引访问获得记录，将大大降低物理磁盘读写次数，从而降低了整个查询响应时间。在我们的测试用表中，只有4个字段，显然无法发挥索引的这一优势。我们的测试用表的特点是记录条数多，达到60万条，但每次返回的记录数只有几十条，在这种情况下，使用索引同样可以起到好的效果。首先在检索条件上建立索引，在表访问时，我们通过索引来获取目的记录集的物理地址，因为返回的记录数不多，所以这种方式造成的物理读写很少，应该可以获得较满意的访问时间。在测试中，我们对测试用表的条件字段添加索引，但事实上并没有提高查询的响应时间，这是因为在SQL语句的执行计划中，在添加索引之前和之后，Oracle生成的执行计划中，表访问方式都是全表扫描，根本没有使用我们添加的索引。那么此时我们可以采用添加提示的方法。　　（2）添加提示　　我们在SQL语句中加入提示，强制优化器在生成执行计划时将表的访问方式从全表扫描改为索引范围扫描。结果该查询花费了比全表扫描多得多的响应时间。仔细查看这个SQL语句的执行计划，我们发现Oracle对这个索引进行了全索引扫描，这样造成的物理读写数量比起全表扫描不仅没有减少，反而大幅上升。寻找原因，问题出在我们索引的字段上。我们索引的AGE字段为了测试方便，每条记录的值都不相同，这样的字段其实并不具备建立索引的条件。在