Oracle入门指南：查询优化4主要途径

会计考友

数据库最基本的任务是存储、管理数据，而终端用户唯一能看到的数据库特性就是其性能：数据库以何速度处理某一指定查询的结果，并且将结果返回到用户所用的工具和应用程序。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大、查阅新闻、 查看文件、 查询统计信息等。因此，数据库查询操作的效率是影响一个应用系统响应时间的关键因素。随着一个应用系统中数据的动态增长，数据量变大，数据库查询效率就会有所降低，应用系统的响应速度也随之减慢，尤其对于海量数据的管理和查询问题就更加突出，Oracle查询优化就显得尤为重要。
# M. P4 Y( x$ n' ]4 N   
    目前通用的数据库产品有很多种，其中Oracle数据库以其支持大数据库、多用户的高性能事务处理， 对业界各项工业标准的支持，完整的安全和完整性控制，支持分布式数据库利分布处理 具有可移植性、可兼容性和可连接性等突出优点倍受用户喜爱，应用较为广泛，在互联网数据库平台上处于领先地位、其Spatial技术能更加有效地管理地理信息，实现海量空间信息的存储和管理。本文结合Oracle数据库应用经验，从命中率提高、多表查询优化、大表查询优化和SQL优化等四个方面阐述Oracle查询优化的经验和方法。
   
- o& ~+ N; x$ `+ h1 T1 k    Oracle查询优化第一方面：Oracle数据查询tb命中率的提高
   
3 N* `+ D  O  n2 f$ K9 [3 h! V    "命中率（HITRATIO） 是指直接从内存中取得数据而不从磁盘中取得数据的比率，也就是查询请求的数据块已经在内存中次数的百分比".影响命中率的因素有四种：字典表活动、临时段活动、回滚段活动、表扫描， 应用DBA可以对这四种因素进行分析，找出数据库命中率低的症结所在。
   
1 _1 S2 z2 E+ C& I) [    1）字典表活动
   
    当一个SQL语句第一次到达Oracle内核时数据库对SQL语句进行分析，包含在查询中的数据字典对象被分解，产生SQL执行路径。如果SQL语句指向一个不在SGA中的对象？？表或视图，Oracle执行SQL语句到数据典中查询有关对象的信息。数据块从数据字典表被读取到SGA的数据缓存中。由于每个数据字典都很小，因此，我们可缓存这些表以提高对这些表的命中率。但是由于数据字典表的数据块在SGA中占据空间，当增加全部的命中率时，它们会降低表数据块的可用空间， 所以若查询所需的时间字典信息已经在SGA缓存中，那么就没有必要递归调用。
2 M) ~6 U& A8 i6 ~. R   
    2）临时段的活动
   
+ m7 G. k6 W+ [7 Q# d8 `    当用户执行一个需要排序的查询时，Oracle设法对内存中排序区内的所有行进行排序，排序区的大小由数据库的init.ora文件的数确定。如果排序区域不够大，数据库就会在排序操作期间开辟临时段。临时段会人为地降低OLTP（online transaction processing）应用命中率，也会降低查询进行排序的性能。如果能在内存中完成全部排序操作，就可以消除向临时段写数据的开销。所以应将SORT_AREA_SIZE设置得足够大，以避免对临时段的需要。这个参数的具体调整方法是：查询相关数据，以确定这个参数的调整。
   
; k) y+ ?; [! \7 z( D# R    select * from v$sysstat where name='sorts（disk）'or name='sorts（memory）；
7 x. F9 u! T6 ]3 a6 W8 l. ^) a   
! s7 d( K% P' l$ {* I    大部分排序是在内存中进行的，但还有小部分发生在临时段， 需要调整 值，查看init.ora文件的 SORT_AREA_SIZE值，参数为：SORT_AREA_SIZE=65536;将其调整到SORT_AREA_SIZE=131072、这个值调整后，重启ORACLE数据库即可生效。
, D: k  {  i6 L% Z' r& G4 ?   
6 Q$ C+ L& S9 C0 g! {4 {    3）回滚段的活动
+ y, _* Y* v' I! E   
    回滚段活动分为回滚活动和回滚段头活动。对回滚段头块的访问会降低应用的命中率， 对OLTP系统命中率的影响最大。为确认是否因为回滚段影响了命中率，可以查看监控输出报表中的"数据块相容性读一重写记录应用" 的统计值，这些统计值是用来确定用户从回滚段中访问数据的发生次数。
! }- }9 k( K% P1 [   
) n5 N) \1 h) ]8 N, }; f0 p    4）表扫描
* X$ u" {3 {# E" V   
    通过大扫描读得的块在数据块缓存中不会保持很长时间， 因此表扫描会降低命中率。为了避免不必要的全表扫描，首先是根据需要建立索引，合理的索引设计要建立人对各种查询的分析和预测上，笔者会在SQL优化中详细谈及；其次是将经常用到的表放在内存中，以降低磁盘读写次数。