数据库

img ern

Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

发表于2004/10/3 1:24:00  1392人阅读

Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

Tom Kyte的新书Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介绍了参数OPTIMIZER_INDEX_COST_ADJ,并认为可以理解为Oracle执行多块(MultiBlock)I/O(比如全表扫描)的代价与执行单块(Single-block)I/O代价的相对比例。Tom指出,9i下该参数缺省值为100,意即二者拥有相同的代价,若减小,则CBO倾向于使用索引(即单块I/O),反之则倾向于全表扫描(多块I/O)。Tom同样给出了一个不错的例子。
    在自己的手提上试验了一下,硬盘大小原因,只装了一个Oracle 10g(10.1.0.2),谁知就发现该参数在10g下取值发生了改变。我们先来看一下参照Tom的实验:
    A. 创建数据表。
      SQL> drop table t1;
     
      表已删除。
     
      SQL> drop table t2;
     
      表已删除。
     
      SQL> create table t1
        2  as
        3  select mod(rownum,1000) id,rpad('x',300,'x') data
        4  from all_objects
        5  where rownum<=5000;
     
      表已创建。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  create table t2
        2  as
        3  select rownum id,rpad('x',300,'x') data
        4  from all_objects
        5* where rownum<=1000
      SQL> /
     
      表已创建。
     
    B.创建索引并分析。
      SQL> create index idx_t1 on t1(id);
     
      索引已创建。
     
      SQL> create index idx_t2 on t2(id);
     
      索引已创建。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  begin
        2    dbms_stats.gather_table_stats
        3    (user,'T1',method_opt=>'for all indexed columns',cascade=>true);
        4    dbms_stats.gather_table_stats
        5    (user,'T2',method_opt=>'for all indexed columns',cascade=>true);
        6* end;
      SQL> /
     
      PL/SQL 过程已成功完成。
   
    C.查询缺省值,并设置好环境。
      SQL> set autot off
      SQL> show parameters optimizer_index_cost_adj;
     
      NAME                                 TYPE        VALUE                         
      ------------------------------------ ----------- ------------------------------
      optimizer_index_cost_adj             integer     100                           
      SQL> set autot traceonly exp stat;
     
    D.在缺省值下查询的结果。
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=13 Card=5 Bytes=1000)                         
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
              367  recursive calls                                                   
                0  db block gets                                                     
              101  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
               12  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                     
                             
    E.修改参数值。
      
      SQL> alter session set optimizer_index_cost_adj=1;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)         
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=2 Card=5 Bytes=1000)                          
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
               48  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
      SQL> alter session set optimizer_index_cost_adj=50;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000)         
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=7 Card=5 Bytes=1000)                          
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
               48  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
   
    F.继续修改参数值,改大。
      SQL> alter session set optimizer_index_cost_adj=200;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=26 Card=5 Bytes=1000)                         
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=6          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
               48  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
      SQL> alter session set optimizer_index_cost_adj=500;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=10 Car         
                d=1 Bytes=100)                                                       
                                                                                     
         2    1     NESTED LOOPS (Cost=63 Card=5 Bytes=1000)                         
         3    2       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 By         
                tes=500)                                                             
                                                                                     
         4    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
               90  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
      SQL> alter session set optimizer_index_cost_adj=1000;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   HASH JOIN (Cost=66 Card=5 Bytes=1000)                              
         2    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte         
                s=500)                                                               
                                                                                     
         3    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt         
                es=2600)                                                             
                                                                                     
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
              271  consistent gets                                                   
              213  physical reads                                                    
                0  redo size                                                         
             1651  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
      SQL> alter session set optimizer_index_cost_adj=10000;
     
      会话已更改。
     
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   HASH JOIN (Cost=66 Card=5 Bytes=1000)                              
         2    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte         
                s=500)                                                               
                                                                                     
         3    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt         
                es=2600)                                                             
                                                                                     
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                1  recursive calls                                                   
                0  db block gets                                                     
              271  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1651  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
    G.现在看看对性能的影响。
      SQL> set timing on
      SQL> alter session set optimizer_index_cost_adj=100;
     
      会话已更改。
     
      已用时间:  00: 00: 00.00
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
      已用时间:  00: 00: 00.02
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=13 Card=5 Bytes=1000)                         
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                0  recursive calls                                                   
                0  db block gets                                                     
               48  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
      SQL> alter session set optimizer_index_cost_adj=1;
     
      会话已更改。
     
      已用时间:  00: 00: 00.00
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
      已用时间:  00: 00: 00.02
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)         
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card         
                =1 Bytes=100)                                                        
                                                                                     
         2    1     NESTED LOOPS (Cost=2 Card=5 Bytes=1000)                          
         3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1          
                Card=5 Bytes=500)                                                    
                                                                                     
         4    3         INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=         
                5)                                                                   
                                                                                     
         5    2       INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)         
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                0  recursive calls                                                   
                0  db block gets                                                     
               48  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1507  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    


      SQL> alter session set optimizer_index_cost_adj=10000;
     
      会话已更改。
     
      已用时间:  00: 00: 00.00
      SQL> ed
      已写入 file afiedt.buf
     
        1  select * from t1,t2
        2  where t1.id=t2.id
        3*   and t2.id between 50 and 55
      SQL> /
     
      已选择30行。
     
      已用时间:  00: 00: 00.03
     
      执行计划
      ----------------------------------------------------------                     
         0      SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000         
                )                                                                    
                                                                                     
         1    0   HASH JOIN (Cost=66 Card=5 Bytes=1000)                              
         2    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte         
                s=500)                                                               
                                                                                     
         3    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt         
                es=2600)                                                             
                                                                                     
     
     
     
     
      统计信息
      ----------------------------------------------------------                     
                0  recursive calls                                                   
                0  db block gets                                                     
              271  consistent gets                                                   
                0  physical reads                                                    
                0  redo size                                                         
             1651  bytes sent via SQL*Net to client                                  
              523  bytes received via SQL*Net from client                            
                3  SQL*Net roundtrips to/from client                                 
                0  sorts (memory)                                                    
                0  sorts (disk)                                                      
               30  rows processed                                                    
     
    来详细分析一下。
    首先,由于测试环境的不同,Tom的测试结果是在缺省值(100)的环境下,就已经和上面取值500时一样了,即对T2全表扫描而T1使用索引。Tom试验中,减小取值直至0,访问路径就变成使用两个索引,而并不会出现均不使用索引的情况。另一方面,正如Tom的结论所说,OPTIMIZER_INDEX_COST_ADJ的取值越大,优化器越倾向于使用全表扫描,取值越小,优化器越倾向于使用索引。
    再次,我们对比相同访问路径下的不同点。在取值从1变化到200(1-50-100-200)的过程中,优化器计算出的代价是持续增长的,而从1000到10000则是不变的。这说明这个参数与索引I/O的代价有关,而和全表扫描并无关系,这与Tom所说的并不矛盾,不过显然更精确一点。
    最后我们其实应该看到,虽然有如上所说的代价变化问题,同一访问路径下实际的运行性能并无区别,由于数据量比较小,上面的例子也许不能很好的说明这一点,不过想想Oracle用相同的路径去执行,也没有理由不同性能吧。
    好,来看看官方文档吧。10G的官方文档(Reference)中对这个参数描述如下:
    OPTIMIZER_INDEX_COST_ADJ
      Parameter type Integer    参数类型为整数
      Default value 100         缺省值为100
      Modifiable ALTER SESSION, ALTER SYSTEM  可通过ALTER SESSION, ALTER SYSTEM来修改
      Range of values 1 to 10000   取值范围是1-10000
      OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, TO MAKE THE OPTIMIZER MORE OR LESS PRONE TO SELECTING AN INDEX ACCESS PATH OVER A FULL TABLE SCAN.
      The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
      Note:
        The adjustment does not apply to user-defined cost functions for domain indexes.
      注意:调整对用户为域索引自定义的代价函数无效。
    描述的第一段正证实了参数的作用。第二段值得注意,正如Tom所说(我觉得如果他把原文versus前后颠倒一下会更好,即单块I/O代价比之多块I/O),参数表达了索引访问代价对比
普通(表扫描)代价的比值。不过还有一个疑问我暂时还没办法想通,什么条件下索引I/O居然比扫描慢100倍(取值10000)?
   
结论:
    OPTIMIZER_INDEX_COST_ADJ通过指明索引I/O代价与扫描全表I/O代价的相对比值来影响CBO的行为,取值越小,CBO越倾向于使用索引,取值越大,越倾向于全表扫描。而缺省值100,指明缺省下,二者的代价是相等。

阅读全文
0 0

相关文章推荐

img
取 消
img