Oracle数据库的查询优化方案
摘 要原有管理系统无法满足生产、生活需求,因此建立跨区域的办公应用系统,实现数据共享已十分重要,本文论述如何改进Oracle数据库及其应用程序的优化。
关键词Oracle数据库;SQL;数据库索引;查询优化
数据库技术对于大量复杂的信息能够进行科学高效的管理。在数据库领域中各种应用软件大量涌现,但其中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
Thenumberofbytesallocatedfortheredologbuffer.
SHARED_POOL_SIZE
ThesizeinbytesoftheareadevotedtosharedSQLandPL/SQLstatements.
LARGE_POOL_SIZE
Thesizeofthelargepool;thedefaultis0.
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)在经常用作过滤器或者查询频率较高字段上建立索引;
2)在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引;
3)在不同值较少的字段上不必要建立索引,如性别字段;
4)对于经常存取的列避免建立索引;
5)用于联接的列(主健/外健)建立索引;
6)在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定。
2.2基于SQL语句的优化
要对查询语句进行优化,一个最简单直接有效的方法是对SQL语句进行调整,减少计算量,提高查询的响应速度。对SQL语句进行调整时要遵循下列原则: 首先,选择运算尽可能先做,并在对同一个表进行多个选择运算时,选择影响较大的语句放在前面。 其次,在执行连接前对关系作适当的预处理,预处理的方法有两种,在连接属性上建立索引和对关系进行排序。 最后,应避免相关子查询。例如,有以下相关子查询语句:
SELECTsname
FROMstudent
WHEREexists(SELECT*FROMstudent_grade
WHEREscore>90andstudent.sno=student_grade.sno)
用连接查询实现以上子查询如下:
SELECTsname
FROMstudent,student_grade
WHEREscore>90ANDstudent.sno=student_grade.sno)
3结束语
本文所提出的优化方法,能够起到提高oracle数据库性能以及提高oracle应
用程序执行效率的作用。这仅是我们在实际工作中的经验总结,希望能对广大使用者起到一定帮助作用。
参考文献
[1]萨师萱,王珊著.数据库系统概论[M].北京:高等教育出版社,2000.
[2]李逸波,王华驹,马赛红,等译.Eyal Aronoff,Kevin Loney,Noorali Sonawalla著. Oracle8性能优化和管理手册[M].北京:电子工业出版社,2000.
周晓红(1981-),女,长春职业技术学院教师,主要从事计算机应用技术专业的教学和研究工作。