改变优化参数来适应库缓冲区中SQL的变化

3/14/2008来源:Oracle教程人气:4951

虽然系统级性能调优不是结构化查询语言(SQL)性能调优的最好解决方案,但是,当你不能改变某些SQL语句时(例如,提供商提供的某些包中的SQL语句),使用SQL处理的常规模式来调节系统性能是极其有帮助的。在日常工作经验的基础上,Oracle数据库管理员改变某些优化参数来适应库缓冲区中SQL类型的变化是很常见的事。

一些较为常见的变更:

1、针对Oracle参数的更改。对optimizer_mode,optimizer_index_cost_adj和optimizer_index_caching的改变能对SQL执行计划产生巨大影响。  

2、 针对统计参数的更改。使用dbms_stats包导入特定的统计参数(针对当然处理模式作了调整的)可对SQL的执行速度产生巨大影响。  

3、 使用自动化查询重新写入。使用Oracle实体化视图能够预先聚集、预先汇总数据,从而减少运行时刻表连接的数量。对于更新比较少的数据库,也可以通过预先连接表来提高处理速度。  

一些对性能调优最重要的Oracle优化参数如下:

* optimizer_mode(优化模式)-在Oracle 9i中,有许多优化模式,都是由参数optimizer_mode的值决定的。这个参数的取值范围是rule, choose, all_rows, first_rows, first_rows_1, first_rows_10 和 first_rows_100.  

我们以定义“最好的”执行计划作为开始点。在任何给定的时间,库缓冲区中的所有SQL语句都需要有“最好的”执行计划(当然,由于在任何给定的时间里处理需求可能不同,所以这个最优执行计划可能会经常发生变化)。什么是“最好的”执行计划?是返回结果最快的执行计划还是使用最少计算资源的执行计划?很明显,答案依赖于你的数据库的处理过程,Oracle提供了两种优化模式,允许你选择你认为的“最好的”执行计划:  

1、 optimizer_mode=first_rows――相对全表扫描访问,这个优化模式更注重索引访问。当你想要一个查询以最快的速度返回结果行时,即使它的逻辑输入输出总量比全表扫描高,也要使用这个模式在线访问系统一般都使用这个模式,因为终端用户想要尽快地看到第一页查询结果。  

2、optimizer_mode=all_rows――这个优化模式更注重全表扫描(特别是并发全表扫描),因为在这种情况下服务器资源的开销最小。这个模式一般被用于批处理进程和数据仓库中,它们的目标都是使服务器消耗的资源最小化。

3、 optimizer_mode=first_rows_n――从Oracle 9i开始,又有一种新的优化模式针对某些返回小结果集的查询进行优化。其取值范围是first_rows_1, first_rows_10 和 first_rows_100,使用这些参数值可以确保Oracle能够优化这类SQL。  

虽然参数optimizer_mode控制了“基于代价的优化”的总体行为,还有其他Oracle参数也会对“基于代价的优化”产生相当大的影响。Oracle提供了一些重要的参数来控制“基于代价的优化”做出的选择:  

1、optimizer_index_cost_adj――这个参数可用来调整“基于代价的优化”相对于全表扫描访问而言,更加倾向于索引访问的程度。这个值越小,“基于代价的优化”就越有可能使用一个可用的索引。  

2、 optimizer_index_caching――这个参数告诉Oracle你的索引在内存的数据缓冲区中的可能性有多大。对这个参数的设置将会影响到“基于代价的优化” 做出的对一个表连接(嵌套循环)使用索引还是使用全表扫描选择。  

3、 db_file_multiblock_read_count――当把这个值设置得比较大时(使用更大的服务器),“基于代价的优化”识别出分散的(多块)读操作的代价或许比识别顺序读操作的代价更小一些。这就使得“基于代价的优化”更加倾向于全表扫描。  

但是从Oracle 9.2版本开始,情况不再是这样了。当计算系统统计表时,它包含了“多块读操作记数”(MBRC),这个数字决定了全表扫描的成本。Oracle 10g则更进一步,加入了一些“系统默认值”,这些默认值是非常不合适的。对于Oracle 9.2版本而言,请注意Metalink上的149560.1。  

1、 parallel_automatic_tuning――当该参数设置为“开启”时,对于含有许多CPU的Oracle服务器,全表扫描并发执行。因为并发全表扫描的速度可以非常快,所以“基于代价的优化”对于索引访问开销很大,因此更加倾向于使用全表扫描。  

2、 hash_area_size(假如不使用pga_aggregate_target的话)――这个参数设置“基于代价的优化”相对于使用嵌套循环和排序合并表连接来说,更倾向于使用哈希连接的程度。  

3、sort_area_size(只当不使用参数pga_aggregate_target时)――这个参数影响了“基于代价的优化”做出的执行索引访问还是执行对结果集的排序的决定。这个参数值越高,则在内存中执行排序(比使用临时表空间快上千倍)的可能性就越大,同时“基于代价的优化”相对于使用预先排序好的索引检索,更倾向于使用直接排序。