当前位置: 代码迷 >> 综合 >> consistent gets减少,cost增加?
  详细解决方案

consistent gets减少,cost增加?

热度:16   发布时间:2023-12-15 00:17:41.0
    在一条SQL语句中,当使用索引时,cosistent gets 减少,而cost增加。理论上在稳定后的执行计划中,physical reads为零值的前提下,
cost应当相应减少。下面来看看其原由。

1、原始的SQL语句  

SQL> SELECT acc_num, amount, curr_cd                                                                                   
2  FROM   voucher_tbl                                                                                                
3  WHERE  value_date > '20110929'              -->谓词value_date,存在索引的情况下通常会走索引                       
4  AND vou_type NOT IN ('H', 'Q')    ;         -->谓词vou_type,使用了NOT IN,容易引起全表扫描                       
4519 rows selected.                              -->返回数据量4519行                                                   
Elapsed: 00:00:00.21                   -->耗用时间                                                                     
Execution Plan                                                                                                         
------------------------------------   -->从执行计划来看Rows为664K,Bytes为22M,Cost为3718,表访问方式为全表扫描          
Plan hash value: 4182658551                                                                                            
---------------------------------------------------------------------------------                                      
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                      
---------------------------------------------------------------------------------                                      
|   0 | SELECT STATEMENT  |             |   664K|    22M|  3718   (2)| 00:00:45 |                                      
|*  1 |  TABLE ACCESS FULL| VOUCHER_TBL |   664K|    22M|  3718   (2)| 00:00:45 |                                      
---------------------------------------------------------------------------------                                      
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
1 - filter("VOU_TYPE"<>'Q' AND "VOU_TYPE"<>'H' AND   -->注意这里的谓词使用了两个<>,该列上的索引将不可用            
"VALUE_DATE">'20110929')                                                                                 
Statistics                                                                                                             
----------------------------------------------------------                                                             
0  recursive calls                                                                                           
0  db block gets                                                                                             
16878  consistent gets                           -->consistent gets 数目16878                                    
0  physical reads                                                                                            

2、强制走索引    

SQL> SELECT /*+ index(voucher_tbl voucher_tbl_value_date_idx) */    -->使用索引提示                             
2   acc_num, amount, curr_cd                                                                                  
3  FROM   voucher_tbl                                                                                         
4  WHERE  value_date > '20110929'                                                                             
5  AND vou_type NOT IN ('H', 'Q');                                                                            
4519 rows selected.                                                                                             
Elapsed: 00:00:00.04              -->耗用时间从00.21下降到00.04                                                 
Execution Plan                                                                                                  
-------------------------------------      -->执行计划中Rows为664K,Bytes为22M,Cost大的吓人,为38449,远大于3718 
Plan hash value: 3884537217                                                                                     
----------------------------------------------------------------------------------------------------------      
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |      
----------------------------------------------------------------------------------------------------------      
|   0 | SELECT STATEMENT            |                            |   664K|    22M| 38449   (1)| 00:07:42 |      
|*  1 |  TABLE ACCESS BY INDEX ROWID| VOUCHER_TBL                |   664K|    22M| 38449   (1)| 00:07:42 |      
|*  2 |   INDEX RANGE SCAN          | VOUCHER_TBL_VALUE_DATE_IDX |   692K|       |  1941   (1)| 00:00:24 |      
----------------------------------------------------------------------------------------------------------      
Predicate Information (identified by operation id):                                                             
---------------------------------------------------                                                             
1 - filter("VOU_TYPE"<>'Q' AND "VOU_TYPE"<>'H')                                                              
2 - access("VALUE_DATE">'20110929')                                                                          
Statistics                                                                                                      
----------------------------------------------------------                                                      
1  recursive calls                                                                                    
0  db block gets                                                                                      
1760  consistent gets    -->consistent gets从16878下降到1760                                             
0  physical reads                                                                                     
--从上面的情况对比来看,走索引尽管consistent gets从16878下降到1760,而所致的cost开销远大于3718,为38449。       

3、表上的索引情况  

SQL> @Idx_Info                      -->表上存在多个索引,是否全部用到,有待考证,因为存在VALUE_DATE谓词,且列上存在索引   
Enter value for owner: goex_admin   -->故不加索引提示的前提下,应该走索引范围扫描                                         
Enter value for table_name: voucher_tbl                                                                                   
TABLE_NAME     INDEX_NAME                     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD                   
-------------- ------------------------------ -------------------- ------ -------- --------------- ----                   
VOUCHER_TBL    PK_VOUCHER_TBL                 VOUCHER_NUM               1 VALID    NORMAL          ASC                    
VOUCHER_TBL    VOUCHER_TABLE_VOU_TYPE_IDX     VOU_TYPE                  1 VALID    NORMAL          ASC                    
VOUCHER_TBL    VOUCHER_TBL_10G_IDX            SYS_NC00042$              1 VALID    FUNCTION-BASED  ASC                    
NORMAL                                 
VOUCHER_TBL    VOUCHER_TBL_APPRV_DATE_IDX     APPRV_DATE                1 VALID    NORMAL          ASC                    
VOUCHER_TBL    VOUCHER_TBL_GF_DATE_IDX        GOOD_FUND_DATE            1 VALID    NORMAL          ASC                    
VOUCHER_TBL    VOUCHER_TBL_VALUE_DATE_IDX     VALUE_DATE                1 VALID    NORMAL          ASC                    
SQL> @Idx_Stat                                  -->索引列的统计信息,聚簇因子等                                           
Enter value for input_table_name: voucher_tbl   -->表上的块数为16744,行数为692725                                        
Enter value for input_owner: goex_admin                                                                                   
BLEV IDX_NAME                   LEAF_BLKS   DST_KEYS LEAF_PER_KEY DATA_PER_KEY CLUST_FACT LAST_ANAL TAB_BLOCKS   TAB_ROWS 
---- -------------------------- --------- ---------- ------------ ------------ ---------- --------- ---------- ---------- 
2 PK_VOUCHER_TBL                  1446     692725            1            1      78732 10-NOV-11      16744     692725 
2 VOUCHER_TABLE_VOU_TYPE_IDX      1255         18           69         3189      57404 10-NOV-11      16744     692725 
2 VOUCHER_TBL_VALUE_DATE_IDX      1930        697            2           52      36457 10-NOV-11      16744     692725 
2 VOUCHER_TBL_GF_DATE_IDX         1930        705            2           57      40734 10-NOV-11      16744     692725 
2 VOUCHER_TBL_APPRV_DATE_IDX      1930        665            2           47      31808 10-NOV-11      16744     692725 
2 VOUCHER_TBL_10G_IDX             2121      18754            1           21     410313 10-NOV-11      16744     692725 

4、列上的统计信息    

SQL> @Col_Stat                                 -->列上的统计信息,列太多,部分省略                                  
Enter value for input_table_name: voucher_tbl  -->列上有两个直方图VOU_TYPE和CURR_CD                                 
Enter value for input_owner: goex_admin        -->VALUE_DATE列的density值为664,无任何直方图信息。                  
-->初步判断是缺乏直方图信息所致原SQL语句无法使用索引                 
NAME               #DST LOW_VALUE            HIGH_VALUE                  DENS   #NULL AVGLEN HISTOGRAM        #BKT  
--------------- ------- -------------------- -------------------- ----------- ------- ------ --------------- -----  
VOUCHER_NUM      692725 C20407               C34D0510              .000001444       0      5 NONE                1  
VOU_DESC            464 233132393920414941   E9878DE685B6E8BEB2E6  .002155172  427507     10 NONE                1  
VOU_TYPE             15 41                   59                    .000000714       0      2 FREQUENCY          15  
INOROUT               2 49                   4F                    .500000000       0      2 NONE                1  
VALUE_DATE          664 3230303930323032     3939393931323331      .001506024       0      9 NONE                1  
PRINT_DATE          665 3230303930323032     3230313130393330      .001503759  591482      3 NONE                1  
CURR_CD               9 415544               555344                .000000714       0      4 FREQUENCY           9  
CASH_T0            3155 3A640C2C444C2F66     C5032E0C4129          .000316957  688002      2 NONE                1  

5、收集统计信息  

SQL> BEGIN                                                                                                               
2  DBMS_STATS.GATHER_TABLE_STATS(                                                                                      
3  'GOEX_ADMIN',                                                                                                       
4  'VOUCHER_TBL',                                                                                                      
5  METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',   -->使用size auto方式,收集统计信息之后,VALUE_DATE列上仍无直方图信息       
6  CASCADE=>TRUE);                            -->其它列上存在直方图信息                                                
7  END;                                       -->由于density值为664大于254因此,即是生成直方图,一定是等高直方图       
8  /                                                                                                                   
PL/SQL procedure successfully completed.                                                                                 
SQL> BEGIN                                                                                                               
2  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'GOEX_ADMIN',                                                           
3  TABNAME    => 'VOUCHER_TBL',                                                                                        
4  CASCADE    => TRUE,                                                                                                 
5  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');     -->清除表上所有直方图的信息                                            
6  END;                                                                                                                
7  /                                                                                                                   
PL/SQL procedure successfully completed.                                                                                 
-->再次收集统计信息,依然不能为列VALUE_DATE 生成直方图                                                                   
SQL> exec dbms_stats.gather_table_stats('GOEX_ADMIN','VOUCHER_TBL',method_opt=>'for all columns size auto',cascade=>true)
PL/SQL procedure successfully completed.                                                                                 
-->查看列VALUE_DATE的使用情况                                                                                            
SQL> @Col_Usage.sql      -->该SQL语句查询了SYS.col_usage$,SYS.col$等多个视图来获得列的使用情况                          
Enter value for input_ownname: goex_admin        -->下面的结果中表明该列被使用                                           
Enter value for input_objname: voucher_tbl                                                                               
COL_NAME          COL_UNUM   COL_INUM    COL_OBJ     H_BCNT     H_PNDV    COL_LEN CU_TIME        CU_RP                   
--------------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------                   
VALUE_DATE              18         18     264356          1        665          8 10-NOV-11         28                   
-->尝试手动生成列上的直方图信息                                                                                          
SQL> BEGIN                                                                                                               
2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'GOEX_ADMIN',                                                              
3  tabname => 'VOUCHER_TBL',                                                                                           
4  estimate_percent => dbms_stats.auto_sample_size,                                                                    
5  method_opt => 'for columns size 254 VALUE_DATE',  -->注:此处设置为254不一定合理                                    
6  cascade => TRUE,                                                                                                    
7  degree => 7);                                                                                                       
8  END;                                                                                                                
9  /                                                                                                                   
PL/SQL procedure successfully completed.                                                                                 
SQL> @Col_Stat                                                                                                           
Enter value for input_table_name: voucher_tbl -->此时成功为列VALUE_DATE生成了直方图信息,且桶数为254,该值是否合理待确认 
Enter value for input_owner: goex_admin                                                                                  
NAME             #DST LOW_VALUE            HIGH_VALUE                  DENS   #NULL AVGLEN HISTOGRAM        #BKT         
------------- ------- -------------------- -------------------- ----------- ------- ------ --------------- -----         
VOUCHER_NUM    692725 C20407               C34D0510              .000001444       0      5 NONE                1         
VOU_DESC          464 233132393920414941   E9878DE685B6E8BEB2E6  .002155172  427507     10 NONE                1         
VOU_TYPE           15 41                   59                    .000000714       0      2 FREQUENCY          15         
INOROUT             2 49                   4F                    .500000000       0      2 NONE                1         
VALUE_DATE        660 3230303930323032     3939393931323331      .001953125       0      9 HEIGHT BALANCED   254         
PRINT_DATE        665 3230303930323032     3230313130393330      .001503759  591482      3 NONE                1         
CURR_CD             9 415544               555344                .000000714       0      4 FREQUENCY           9         
CASH_T0          3155 3A640C2C444C2F66     C5032E0C4129          .000316957  688002      2 NONE                1         

6、更新统计信息后SQL语句执行情况

SQL> set autotrace traceonly;                                                                                         
SQL> SELECT acc_num, amount, curr_cd       -->SQL语句未加任何索引提示,此时已正确使用索引                             
2  FROM   voucher_tbl                    -->Rows,Bytes,Cost明显降低                                               
3  WHERE  value_date > '20110929'                                                                                   
AND vou_type NOT IN ('H', 'Q')  4  ;                                                                       
4519 rows selected.                                                                                                   
Elapsed: 00:00:00.03                                                                                                  
Execution Plan                                                                                                        
---------------------------------------------   -->此时 Rows,Bytes,Cost相对准确,且Cost明显下降                       
Plan hash value: 3884537217                                                                                           
----------------------------------------------------------------------------------------------------------            
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT            |                            |  6940 |   237K|   407   (1)| 00:00:05 |            
|*  1 |  TABLE ACCESS BY INDEX ROWID| VOUCHER_TBL                |  6940 |   237K|   407   (1)| 00:00:05 |            
|*  2 |   INDEX RANGE SCAN          | VOUCHER_TBL_VALUE_DATE_IDX |  7273 |       |    23   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------------------------            
Predicate Information (identified by operation id):                                                                   
---------------------------------------------------                                                                   
1 - filter("VOU_TYPE"<>'Q' AND "VOU_TYPE"<>'H')                                                                    
2 - access("VALUE_DATE">'20110929')                                                                                
Statistics                                                                                                            
----------------------------------------------------------                                                            
0  recursive calls                                                                                          
0  db block gets                                                                                            
1760  consistent gets                                                                                          
0  physical reads                                                                                           
SQL> exec dbms_stats.delete_column_stats('GOEX_ADMIN','VOUCHER_TBL','VALUE_DATE');  -->去掉VALUE_DATE列上的统计信息   
PL/SQL procedure successfully completed.                                                                              

7、使用自动生成直方图方式收集统计信息并查看SQL执行情况 

/**************************************************/                                                                    
/* Author: Robinson Cheng                         */                                                                    
/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                    
/* MSN:    robinson_0612@hotmail.com              */                                                                    
/* QQ:     645746311                              */                                                                    
/**************************************************/                                                                    
SQL> BEGIN                                                  -->再次收集统计信息                                           
2  dbms_stats.gather_table_stats(ownname => 'GOEX_ADMIN',                                                               
3  tabname          => 'VOUCHER_TBL',                                                                                   
4  estimate_percent => dbms_stats.auto_sample_size,                                                                     
5  method_opt       => 'for all indexed columns',         -->此时未指定size auto,且为所有的索引列创建直方图             
6  cascade          => TRUE);                                                                                           
7  END;                                                                                                                 
8  /                                                                                                                    
PL/SQL procedure successfully completed.                                                                                  
SQL> @Col_Stat                                                                                                            
Enter value for input_table_name: voucher_tbl   -->此时成功为列VALUE_DATE生成了直方图信息,且桶数为75                     
Enter value for input_owner: goex_admin                                                                                   
NAME             #DST LOW_VALUE            HIGH_VALUE                  DENS   #NULL AVGLEN HISTOGRAM        #BKT          
------------- ------- -------------------- -------------------- ----------- ------- ------ --------------- -----          
VOUCHER_NUM    692725 C20407               C34D0510              .000001444       0      5 NONE                1          
VOU_DESC          464 233132393920414941   E9878DE685B6E8BEB2E6  .002155172  427507     10 NONE                1          
VOU_TYPE           15 41                   59                    .000000714       0      2 FREQUENCY          15          
INOROUT             2 49                   4F                    .500000000       0      2 NONE                1          
VALUE_DATE        660 3230303930323032     3939393931323331      .001953125       0      9 HEIGHT BALANCED    75          
PRINT_DATE        665 3230303930323032     3230313130393330      .001503759  591482      3 NONE                1          
CURR_CD             9 415544               555344                .000000714       0      4 FREQUENCY           9          
CASH_T0          3155 3A640C2C444C2F66     C5032E0C4129          .000316957  688002      2 NONE                1          
SQL> SELECT acc_num, amount, curr_cd                                                                                      
2  FROM   voucher_tbl                                                                                                   
3  WHERE  value_date > '20110929'                                                                                       
4   AND vou_type NOT IN ('H', 'Q')                                                                                      
5  ;                                                                                                                    
4519 rows selected.                                                                                                       
Execution Plan                                                                                                            
-----------------------------------------   -->当桶的数量为75时,Rows,Bytes,Cost等值高于桶数量为254时的计算值              
Plan hash value: 3884537217                                                                                               
----------------------------------------------------------------------------------------------------------                
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                
----------------------------------------------------------------------------------------------------------                
|   0 | SELECT STATEMENT            |                            |  8829 |   301K|   516   (1)| 00:00:07 |                
|*  1 |  TABLE ACCESS BY INDEX ROWID| VOUCHER_TBL                |  8829 |   301K|   516   (1)| 00:00:07 |                
|*  2 |   INDEX RANGE SCAN          | VOUCHER_TBL_VALUE_DATE_IDX |  9236 |       |    28   (0)| 00:00:01 |                
----------------------------------------------------------------------------------------------------------                
Predicate Information (identified by operation id):                                                                       
---------------------------------------------------                                                                       
1 - filter("VOU_TYPE"<>'Q' AND "VOU_TYPE"<>'H')                                                                        
2 - access("VALUE_DATE">'20110929')                                                                                    
Statistics                                                                                                                
----------------------------------------------------------                                                                
0      recursive calls                                                                                              
0      db block gets                                                                                                
16879  consistent gets        -->此时consistent gets竟然与全表扫描时的值一样                                        
0      physical reads                                                                                               

8、使用skewonly 再次生成统计信息并查看SQL执行情况    

SQL> BEGIN                                                                                                                
2  dbms_stats.gather_table_stats(ownname  => 'GOEX_ADMIN',                                                              
3  tabname          => 'VOUCHER_TBL',                                                                                   
4  estimate_percent => dbms_stats.auto_sample_size,                                                                     
5  method_opt       => 'for all indexed columns size skewonly',    -->使用size skewonly方式                             
6  cascade          => TRUE);                                                                                           
7  END;                                                                                                                 
8  /                                                                                                                    
PL/SQL procedure successfully completed.                                                                                  
SQL> @Col_Stat                                                                                                            
Enter value for input_table_name: voucher_tbl   -->此时列VALUE_DATE直方图使用的桶数竟然也为254                            
Enter value for input_owner: goex_admin                                                                                   
NAME             #DST LOW_VALUE            HIGH_VALUE                  DENS   #NULL AVGLEN HISTOGRAM        #BKT          
------------- ------- -------------------- -------------------- ----------- ------- ------ --------------- -----          
VOUCHER_NUM    692725 C20407               C34D0510              .000001444       0      5 NONE                1          
VOU_DESC          464 233132393920414941   E9878DE685B6E8BEB2E6  .002155172  427507     10 NONE                1          
VOU_TYPE           15 41                   59                    .000000714       0      2 FREQUENCY          15          
INOROUT             2 49                   4F                    .500000000       0      2 NONE                1          
VALUE_DATE        660 3230303930323032     3939393931323331      .001953125       0      9 HEIGHT BALANCED   254          
PRINT_DATE        665 3230303930323032     3230313130393330      .001503759  591482      3 NONE                1          
CURR_CD             9 415544               555344                .000000714       0      4 FREQUENCY           9          
CASH_T0          3155 3A640C2C444C2F66     C5032E0C4129          .000316957  688002      2 NONE                1          
SQL> SQL> SELECT acc_num, amount, curr_cd                                                                                 
2  FROM   voucher_tbl                                                                                                   
3  WHERE  value_date > '20110929'                                                                                       
AND vou_type NOT IN ('H', 'Q')                                                                                           
4    5  ;                                                                                                               
4519 rows selected.                                                                                                       
Execution Plan                                                                                                            
------------------------------------------   -->此时的Rows,Bytes,Cost较桶数为75时低,但与之前的桶数为254也不一致           
Plan hash value: 3884537217                                                                                               
----------------------------------------------------------------------------------------------------------                
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                
----------------------------------------------------------------------------------------------------------                
|   0 | SELECT STATEMENT            |                            |  3932 |   134K|   230   (0)| 00:00:03 |                
|*  1 |  TABLE ACCESS BY INDEX ROWID| VOUCHER_TBL                |  3932 |   134K|   230   (0)| 00:00:03 |                
|*  2 |   INDEX RANGE SCAN          | VOUCHER_TBL_VALUE_DATE_IDX |  4091 |       |    14   (0)| 00:00:01 |                
----------------------------------------------------------------------------------------------------------                
Predicate Information (identified by operation id):                                                                       
---------------------------------------------------                                                                       
1 - filter("VOU_TYPE"<>'Q' AND "VOU_TYPE"<>'H')                                                                        
2 - access("VALUE_DATE">'20110929')                                                                                    
Statistics                                                                                                                
----------------------------------------------------------                                                                
0  recursive calls                                                                                              
0  db block gets                                                                                                
1760  consistent gets     -->此时consistent gets为之前的最低数1760                                                 
0  physical reads                                                                                               

9、总结
 a、当SQL语句出现consistent gets减少,而cost增加的情形,应考虑统计信息是否正确。因为rows,bytes,cost是计算值,不稳定.
 b、索引未能正确使用的情况同样会由于缺乏最新且正确的统计信息而导致不可用.
 c、尽管统计信息为最新,但非均衡列上无直方图信息亦同样导致索引失效.  
 d、收集统计信息时 size auto会自动收集非均衡分布列上的直方图信息(前提是where子句中引用到该列,系统根据列使用历史确定是否为其生成).
 e、size auto不能保证完全收集到正确的统计信息时,使用skewonly模式(本例中即是,因为是测试数据库,可能由于列VALUE_DATE使用的历史
   统计信息不够,而不能生成正确的直方图).
 f、等高直方图容易导致错误的估算以及引起查询优化器预估值不稳定(笔者尝试多次,的确如此,有时候在VALUE_DATE的桶数为75时也出现过
   consistent gets为1760的情况).
 

  相关解决方案