Chinfa DRA480-24A

修改统计信息值恢复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. /