consistent gets减少，cost增加?

 在一条SQL语句中，当使用索引时，cosistent gets 减少，而cost增加。理论上在稳定后的执行计划中，physical reads为零值的前提下，
cost应当相应减少。下面来看看其原由。

1、原始的SQL语句  

[sql] view plain copy print ?

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

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] view plain copy print ?

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

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] view plain copy print ?

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

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] view plain copy print ?

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

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] view plain copy print ?

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

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] view plain copy print ?

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

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执行情况 

[sql] view plain copy print ?

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

/**************************************************/ /* 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] view plain copy print ?

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

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的情况).