Oracle数据库查询优化方案.docVIP

Oracle数据库查询优化方案 　　摘 要原有管理系统无法满足生产、生活需求,因此建立跨区域的办公应用系统,实现数据共享已十分重要,本文论述如何改进Oracle数据库及其应用程序的优化。 　　关键词Oracle数据库;SQL;数据库索引;查询优化 　　中图分类号TP2文献标识码A文章编号1673-9671-(2010)031-0019-01 　　 　　数据库技术对于大量复杂的信息能够进行科学高效的管理。在数据库领域中各种应用软件大量涌现,但其中Oracle是这个领域中最优秀也是最流行的大型数据库应用软件,我们针对在Oracle中如何优化数据库及其应用程序进行了研究。 　　1优化数据库 　　1.1 调整SGA 内存区结构 　　SGA 是一组共享的内存结构,它里面存储了oracle数据库实例的数据和控制文件信息。如果有多个用户同时连接到数据库,他们会共享这一区域,而且进程之间的一些交流的信息也会存储在SGA中。如果使用共享server模式,有些PGA的内容也会存储在SGA中。 SGA是可以动态调整大小的,其越大越有利于数据库高效的运行,可以缓存更多的数据块,提高缓存命中率,节省物理磁盘读取的时间。 　　对于性能的考虑,SGA区域的内存应该是真正的内存,如果使用虚拟内存,会大大降低性能。下面这几个参数最严重的影响到了SGA的大小: 　　DB_CACHE_SIZE 　　The size of the cache of standard blocks. 　　LOG_BUFFER 　　The number of bytes allocated for the redo log buffer. 　　SHARED_POOL_SIZE The size in bytes of the area devoted to shared SQL and PL/SQL statements. 　　LARGE_POOL_SIZE 　　The size of the large pool; the default is 0. 　　1.2优化设计表空间 　　绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的,只有少部分问题根植于内存模块配置不合理,I/O争用,CPU争用等DBA职责范围上。 所以除非是面对一个业已完成不可变更的系统,否则我们不应过多地将关注点投向内存、I/O、CPU等性能调整项目上,而应关注数据库表本身的设计是否合理。任何一个应用程序的库表至少需要创建两个表空间,其中之一用于存储表数据,而另一个用于存储表索引数据。因为假如将表数据和索引数据放在一起,表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争,降低系统的响应时间。 　　1.3优化设计日志文件 　　Oracle数据库的日志文件记录了数据库启动和关闭、数据库结构的改变、回退段的修改、死锁、内部错误等信息. 大家都知道,文件越大,其打开和读写的开销越大。如果日志文件太大了, 可直接删除。而有些日志文件数据库是可以循环使用,而且当LGWR进程在两个日志文件切换时,将自动产生一个检测点,当检测点的出现频率过大时,会极大影响数据库性能。我们应考虑适当增加重演日志文件组的个数或把重演日志文件存档之处设置在磁盘读写更快的物理设备上以减少日志文件存档时间等等。 　　1.4 Oracle常见资源瓶颈的优化 　　内存瓶颈:当Oracle进程需要更大的内存空间时,我们可以采用更改虚拟页面文件大小的方式,从硬盘空间划出来一部分,它和物理内存一起构成虚拟内存 。磁盘I/0瓶颈:由于内存缓冲区的空间不足,或者表空间及其文件在硬件上的分配不合理而导致进程对磁盘的频繁I/0读写。我们通常可以用索引扫描来代替对大表操作的全表扫描,还可以修改Oracle系统全局区(SGA)改造表来减少磁盘I/O操作。CPU瓶颈:过多的应用进程对CPU使用周期激烈竞争的现象。一个数据库的访问工作可以用多个CPU相互配合来完成,加上分布式计算机已经相当普遍,只要可能应将数据库服务器和应用程序的CPU请求分开,或可将CPU请求从一个服务器移到另一个服务器。 　　2查询优化的策略 　　2.1合理使用索引 　　数据库服务器对数据进行访问一般采用两种方式: 　　1)索引扫描通过索引访问数据; 　　2)表扫描,读表中的所有页。 　　当对一个表进行查询时,如果返回的行数占全表总行数的10%到15%时,使用索引可以极大的优化查询的性能。但是如果查询涉及到全表40%以上的行时,表扫描的效率比使用索引扫描的效率高。 　　在具体使用的过程中,要结合实际的数据库和用户的需求来确定要不要索引以及在什么字段上建立什么样的索引。下面给出一些通用的规则: 　　1)在经常用作过滤器