修改统计信息值恢复Oracle执行计划

经常会遇到由于日期字段统计信息的边界值不准确，导致Oracle SQL执行计划发生改变，通常是最大值没有及时更新或者最大值过大。

下面是遇到的一个案例，SQL的执行计划突然改变，原先使用的是index range scan，突然变成了index full scan。 导致SQL语句非常缓慢。

点击(此处)折叠或打开

1. select count(*) from prd_t1 where input_date>trunc(sysdate-1)
   
2. and input_date

原来的执行计划：

点击(此处)折叠或打开

1. SQL> select count(*) from prd_t1 where input_date > trunc(sysdate);
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5. 
   
6.     193311
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. 
    
11.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
    
12.    1 0 SORT (AGGREGATE)
    
13.    2 1 INDEX (RANGE SCAN) OF 'IDX_PRD_T1' (NON-UNIQU
    
14.           E) (Cost=4 Card=2 Bytes=16)

变差的执行计划：

点击(此处)折叠或打开

1. SQL> select count(*) from prd_t1 where input_date > trunc(sysdate);
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5. 
   
6.     193311
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. 
    
11.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
    
12.    1 0 SORT (AGGREGATE)
    
13.    2 1 INDEX (FAST FULL SCAN) OF 'IDX_PRD_T1' (NON-UNIQU
    
14.           E) (Cost=6458 Card=6130163 Bytes=49041304)

检查和字段的统计信息有关系，字段input_date的最小值和最大值分别是18-JAN-2016和15-MAY-2026。所以选择了fast full scan。

修改完最大值和最小值，执行计划恢复成使用rang scan

点击(此处)折叠或打开

1. SQL> select table_name,TO_CHAR(TO_DATE(TRUNC(ENDPOINT_VALUE),'J'),'DD-MON-YYYY')
   
2.   2 from dba_tab_histograms where table_name='PRD_T1' and column_name='INPUT_DATE';
   
3. 
   
4. TABLE_NAME TO_CHAR(TO_DATE(TRUNC(ENDPOINT
   
5. ------------------------------ ------------------------------
   
6. 
   
7. PRD_T1 19-APR-2016
   
8. PRD_T1 18-APR-2017

具体方法如下：

点击(此处)折叠或打开

1. DECLARE
   
2.    srec DBMS_STATS.STATREC;
   
3.    v_distcnt NUMBER;
   
4.    v_density NUMBER;
   
5.    v_nullcnt NUMBER;
   
6.    v_avgclen NUMBER;
   
7.    numvals DBMS_STATS.NUMARRAY;
   
8.    charvals DBMS_STATS.CHARARRAY;
   
9.    datevals DBMS_STATS.DATEARRAY;
   
10. BEGIN
    
11.    -- Date Type
    
12. 
    
13. 
    
14.    DBMS_STATS.get_column_stats (ownname => 'DBO',
    
15.                                 tabname => 'PRD_T1',
    
16.                                 colname => 'INPUT_DATE',
    
17.                                 distcnt => v_distcnt,
    
18.                                 density => v_density,
    
19.                                 nullcnt => v_nullcnt,
    
20.                                 srec => srec,
    
21.                                 avgclen => v_avgclen
    
22.                                );
    
23.    datevals := DBMS_STATS.datearray (sysdate-365, sysdate);
    
24.    DBMS_STATS.prepare_column_values (srec, datevals);
    
25.        DBMS_STATS.set_column_stats (ownname => 'DBO',
    
26.                                 tabname => 'PRD_T1',
    
27.                                 colname => 'INPUT_DATE',
    
28.                                 distcnt => v_distcnt,
    
29.                                 density => v_density,
    
30.                                 nullcnt => v_nullcnt,
    
31.                                 srec => srec,
    
32.                                 avgclen => v_avgclen
    
33.                                );
    
34.    COMMIT;
    
35. END;
    
36. /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-2132015/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-2132015/