Oracle 配置查询优化器


          一. 背景介绍

          查询优化器对于SQL语句的性能非常重要,因为我们写的SQL语句最后被数据库执行,是通过查询优化器生成执行计划实现的。如果查询优化器生成的执行计划低效,那么就会导致低劣的性能。有一些参数的配置能够影响到查询优化器生成高效的执行计划,但也是有风险的。总之,可以这么说,配置查询优化器的代价是昂贵的,但值得为此付出。

        

        二. 配置线路图

        我们可以按照如下图所示的路线,来一个个参数进行配置


        下面是对图中标有数字步骤的描述:(后面再详细描述各个参数的含义)

        1. 这两个参数需要调整:optimizer_mode 和 db_file_mutiblock_read_count

        2. 这一步的参数默认值已经很合适了,通常没必要调整。这些参数主要是要启用或者禁止查询优化器某一些特性

        3. 收集系统统计信息和对象统计信息,这一步是必须做到,不过oracle10g中已经自己做了。

        4. 内存中存储数据时选择手动还是自动调整内存的使用量。

        5. 自动调整的话,需要设置pga_aggregate_target参数。

        6. 手动调整的话,需要设置这些参数。

        7. 测试应用程序是否达到预期的性能

        8. 如果查询优化器能为大多数SQL生成高效执行计划,则配置成功,否则需要进行第9步。

        9. 如果查询优化器倾向于使用过多或者过少索引,通常需要调整参数optimizer_index_caching和optimizer_index_cost_adj


        三. 参数说明

        根据参数的功能分为两组:查询优化器参数-只对查询优化器的操作起作用,pga参数-影响程序全局区(PGA)的内存布局。

        

        查询优化器参数

        1. optimizer_mode 

        查询优化器是为了找一个最高效的执行计划,这个参数用来定义什么是“高效”,比如是更快还是占用资源更少。在oracle10g中只支持两个参数值:

        all_rows:提供全部数据

        first_rows(n):n为大于0的自然数,表示尽快传输前面n条数据(比如分页查询的时候,我第一次只查询前面10条数据)

        oracle10g默认为all_rows,可以再数据库级别,会话级别,或者执行SQL的时候修改该参数的值。

        数据库级别:alter system set optimizer_mode=first_rows(10) scope=spfile;

        会话级别:ALTER SESSION SET OPTIMIZER_MODE=first_rows(10);

        SQL级别:SELECT /*+ first_rows(10) */ id,name from t1 order by id;

        其实,默认all_rows是最好的方式,如果确实是只要查询小部分数据,可以在sql级别加上提示,看是否能提高性能。


        2. db_file_multiblock_read_count

        在多块读的情况下(比如全表扫描),该参数说明一次最多可读取的数据块数目。设置得太小的话,效率低。设置得太高也不见得就好(太高,会受I/O最大吞吐量限制。比如设置成一次最多读取1024块,但I/O最大吞吐量只允许32块,那一次最多也只读取32块。而且一次读取很多块,开销会偏高。)应该通过测试,才能知道应该把该参数设置成哪一个值。

        测试过程:

        1. 创建一张大表,比如上千万行级别的数据

        2. 循环设置该参数的值,查看全表扫描的速度。类似于以下语句:

   declare
   l_count pls_integer;
   l_time pls_integer;
   l_starting_time pls_integer;
   l_ending_time pls_integer;
   begin
   dbms_output.put_line('dbfmbrc seconds');
   for l_dbfmbrc in 1..32
   loop
   execute immediate 'alter session set db_file_multiblock_read_count='||l_dbfmbrc;
   l_starting_time := dbms_utility.get_time();
   select /*+ full(t) */ count(*) into l_count from big_table t;
   l_ending_time := dbms_utility.get_time();
   l_time := round((l_ending_time-l_starting_time)/100);
   dbms_output.put_line(l_dbfmbrc||'  '||l_time);
   end loop;
   end;
   /
        

        3. optimizer_index_cost_adj

       影响走索引扫描的开销计算。 取值范围1到10000.默认值为100,超过100后,走索引扫描的开销越高,从而使得查询优化器更加倾向于使用全表扫描。相反,小于100,索引扫描的开销就越低,从而使得查询优化器更加倾向于使用索引扫描。从下面索引扫描开销计算公式可以看出:

        io_cost=(blevel+(leaf_blocks+culstering_factor)*selectivity) *optimizer_index_cost_adj/100.

        一般是默认值不需要修改,但是如果发现本应该走索引扫描结果走了全表扫描,可以适当调低该值,但是,这个值不应该设置过低,因为,过低的话,如果两个索引扫描的开销不同,可能通过该公式一算,开销就变成一样的了。总之,不建议修改该参数的值。


        PGA参数

        1. workarea_size_policy

        管理工作区域内存(PGA)的方式,

        auto:oracle10g默认方式,委托给内存管理器自动管理(建议不需要修改)

        manual:oracle9i默认方式,oracle9i没有自动管理功能。


        2. pga_aggregate_target

        如果是自动管理PGA,那么该参数用于指定实例可用的PGA的大小,默认是SGA的20%。即使今后用的内存超过了设置的值,也没有关系,oracle会自动增大PGA的值。比如该参数设置的是200M,今后某一时刻,需要300M,也是没问题的,会自动增长。


        3. sort_area_size

        手动管理PGA,该参数指定分配多大的内存用于排序操作,过小的话,会影响性能,过大的话浪费空间。很难说一个合适的默认值,因为用户场景变化非常大,实际情况得实际处理。


        4. hash_area_size

        手动管理PGA,该参数用于指定哈希连接的工作区域大小,同样建议它的值也很困难。如果过小,那么查询优化器就会高估哈希连接的开销,偏向于合并连接。

        



       

没有更多推荐了,返回首页