Oracle查询优化4大方面的主要途径(1)

数据库最基本的任务是存储、管理数据，而终端用户唯一能看到的数据库特性就是其性能：数据库以何速度处理某一指定查询的结果，并且将结果返回到用户所用的工具和应用程序。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大、查阅新闻、 查看文件、 查询统计信息等。因此，数据库查询操作的效率是影响一个应用系统响应时间的关键因素。随着一个应用系统中数据的动态增长，数据量变大，数据库查询效率就会有所降低，应用系统的响应速度也随之减慢，尤其对于海量数据的管理和查询问题就更加突出，Oracle查询优化就显得尤为重要。   

目前通用的数据库产品有很多种，其中Oracle数据库以其支持大数据库、多用户的高性能事务处理, 对业界各项工业标准的支持,完整的安全和完整性控制，支持分布式数据库利分布处理 具有可移植性、可兼容性和可连接性等突出优点倍受用户喜爱，应用较为广泛，在互联网数据库平台上处于领先地位、其Spatial技术能更加有效地管理地理信息，实现海量空间信息的存储和管理。本文结合Oracle数据库应用经验，从命中率提高、多表查询优化、大表查询优化和SQL优化等四个方面阐述Oracle查询优化的经验和方法。

Oracle查询优化第一方面：Oracle数据查询命中率的提高   

“命中率（HITRATIO） 是指直接从内存中取得数据而不从磁盘中取得数据的比率，也就是查询请求的数据块已经在内存中次数的百分比”。影响命中率的因素有四种：字典表活动、临时段活动、回滚段活动、表扫描， 应用DBA可以对这四种因素进行分析，找出数据库命中率低的症结所在。

1)字典表活动  

当一个SQL语句第一次到达Oracle内核时数据库对SQL语句进行分析，包含在查询中的数据字典对象被分解，产生SQL执行路径。如果SQL语句指向一个不在SGA中的对象??表或视图，Oracle执行SQL语句到数据典中查询有关对象的信息。数据块从数据字典表被读取到SGA的数据缓存中。由于每个数据字典都很小，因此，我们可缓存这些表以提高对这些表的命中率。但是由于数据字典表的数据块在SGA中占据空间，当增加全部的命中率时，它们会降低表数据块的可用空间， 所以若查询所需的时间字典信息已经在SGA缓存中，那么就没有必要递归调用。

2)临时段的活动   

当用户执行一个需要排序的查询时，Oracle设法对内存中排序区内的所有行进行排序，排序区的大小由数据库的init.ora文件的数确定。如果排序区域不够大，数据库就会在排序操作期间开辟临时段。临时段会人为地降低OLTP(online transaction processing）应用命中率,也会降低查询进行排序的性能。如果能在内存中完成全部排序操作，就可以消除向临时段写数据的开销。所以应将SORT_AREA_SIZE设置得足够大，以避免对临时段的需要。这个参数的具体调整方法是:查询相关数据，以确定这个参数的调整。   
select * from v$sysstat where name='sorts(disk)'or name='sorts(memory);

大部分排序是在内存中进行的，但还有小部分发生在临时段， 需要调整 值，查看init.ora文件的 SORT_AREA_SIZE值，参数为：SORT_AREA_SIZE＝65536;将其调整到SORT_AREA_SIZE＝131072、这个值调整后，重启ORACLE数据库即可生效。

3)回滚段的活动   

回滚段活动分为回滚活动和回滚段头活动。对回滚段头块的访问会降低应用的命中率， 对OLTP系统命中率的影响最大。为确认是否因为回滚段影响了命中率，可以查看监控输出报表中的“数据块相容性读一重写记录应用” 的统计值，这些统计值是用来确定用户从回滚段中访问数据的发生次数。

4)表扫描   

通过大扫描读得的块在数据块缓存中不会保持很长时间， 因此表扫描会降低命中率。为了避免不必要的全表扫描，首先是根据需要建立索引，合理的索引设计要建立人对各种查询的分析和预测上，笔者会在SQL优化中详细谈及；其次是将经常用到的表放在内存中，以降低磁盘读写次数。例如 Alter table your_table_name cathe。