整理Oracle数据库中数据查询优化的一些关键点

数据库  /  管理员 发布于 6年前   246

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

Oracle查询优化第二方面：多表查询的优化

在进行多表联合查询时，数据库可能会采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中，不论什么时候哈希联结要比另两种联结开销要小。

我们可以使用哈希联结代替MERGEJOINS、NESTED LOOP联结、因此，在应用中，可添加一些设置使得数据库在有多大联合查询发生时使用哈希联结。其方法是：以 oracle用户身份登录数据库服务器，在initosid.ora文件中添加：

HASH_JOIN_ENABLED=TRUEHASJ_AREA_SIZE=26000

修改完后，重新启动数据库，使这些参数值生效。

Oracle查询优化第三方面：大表查询优化

数据库中有些表是增长非常快的，记录量很大，对这种表进行访问时，索引的好处就微乎其微了，通常采用两种办法来进行大表访问的优化。

1）大表建立在哈希簇中

create cluster TRADE_CLUSTER（vuserid integer）storage（initial 50M next 50M）hash is vuseridsize 60 hashkeys 10000000;/*hashkeys指定了在哈希表里的所期望的行数。*/ create tabletrade_detail_new as select * from trade_detail clusterTRADE_CLUSTER（userid）;drop table trade_detail;rename trade_detail_new to trade_detail;

2）建分区表

将一个大表分开放置在几个逻辑分区中或者是将一个大表分成了几张小表 ，即可以单独对这些小表进行查询，也可以union all一起查询。

例如：将 一个记录交易详情的表拆分：

create trade_detail_1 as select * from trade_detailwhere trade_time between to_date（'mm-dd‘，'01-01‘）and to_date（'mm-dd‘，'03-31‘）;alter table trade_detail_1 add constraint check_trade_detail_1check （trade_time between to_date（'mm-dd‘，'01-01‘）and to_date（'mm-dd‘，'03-31‘））;

同样，建立起另几张按交易发生的季度而划分的表。然后创建执行四个表联合的视图；

create view trade_detail as select * from trade_detail_1union all select * from trade_detail_2union all select * from trade_detail_3union all select * from trade_detail_4;

这样在查询某段时间内的数据时只访问小表就可以了，需要时也可进行联合查询。

Oracle查询优化第四方面：SQL优化

应用程序的执行最终将归结为数据库中的SQL语句执行，SQL语句消耗了70%到90%的数据库资源。因此SQL语句的执行效率最终决定了ORACLE数据库的性能。许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。另外，SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

SQL优化的主要途径是：

a.有效索引的建立。在经常进行连接，但是没有指定为外键的列上建立索引；在频繁进行排序或分组（即进行group by 或 order by 操作）的列上建立索引；在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引；如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。

为了降低I/O竟争， 索引要建在与用户表空间不在同一磁盘上的索引空间里。索引分为：分区索引、完全索引、唯一索引、位图索引等几种类型，在建立索引前，应该测量这个索引的选择性，索引的选择性是指索引列里不同值的数目与表中记录数的比。

b.在有大量重复值并且经常有范围查询（例如 between，》，《》=，《=）的列，或是用到order by、group by的列，可考虑建立群集索引 ；

c.要经常同时存取多列，目每列都含有重复值可考虑建立组合索引

d.优化表达式，在能使用范围查询时尽可能使用范围索引， 而少用“like”，因为“LIKE”关键字支持的通配符匹配特别耗费时间。

f.使用Oracle语句优化器（oracle optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。