[20120214]异常数据导致执行计划改变.txt

今天上午,用户反应一条sql执行有点慢。我检查发现，原来使用索引的语句现在变成了全表扫描，而且昨晚oracle数据库自动分析过这个表。

语句很复杂，抽取有问题的部分：

SELECT *
  FROM med_operation_schedule a
 WHERE (       scheduled_date_time >= TO_DATE ('2012-02-15 00:00', 'yyyy-mm-dd hh24:mi')
           AND scheduled_date_time         OR scheduled_date_time IS NULL
        OR NVL (emergency_indicator, 0) = 1 AND scheduled_date_time > TRUNC (SYSDATE, 'dd')
       )
执行计划是全表扫描。把那个or单独拆开来分析，发现这个条件走的是全表扫描NVL (emergency_indicator, 0) = 1 AND scheduled_date_time > TRUNC (SYSDATE, 'dd')。奇怪！这个条件scheduled_date_time > TRUNC (SYSDATE, 'dd')的记录不会很多。

select * from med_operation_schedule a where a.scheduled_date_time > TRUNC (SYSDATE, 'dd');

这才发现原来里面存在一条scheduled_date_time='5011-7-17 16:30:00' 异常记录。

这样造成优化器认为大于TRUNC (SYSDATE, 'dd')的记录不会很多，执行计划选择全表扫描。

解决方法：
1.要求操作员更正数据，再分析表，这个不能保证以后不再出现，或者程序要做必要的检查，不能输入这样的日期。
2.在该字段建立直方图，不过10g很麻烦，后台的分析Method_Opt=> 'FOR ALL COLUMNS SIZE AUTO '，这样可能下一次分析直方图又会被取消。看来自己该修改自动分析的缺省参数为Method_Opt=> 'FOR ALL COLUMNS SIZE REPEAT'
3.我实在不想跟他们提，我选择的方法是修改统计信息。

方法如下：
1.取出表定义:
exp system/xxxx@yyyy tables=(zzzz.med_operation_schedule) rows=N file=med.dmp

2.过滤出脚本:[注意要加-3参数，具体看man strings文档，不然会丢失信息]
strings -3 med.dmp > med.txt

3.找到如下内容:

DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '303231363032'; SREC.MAXVAL := '303231363036'; SREC.EAVS := 0; SREC.CHVALS := NULL; #
SREC.NOVALS := DBMS_STATS.NUMARRAY(I
250248268640273000000000000000000000,250248268640292000000000000000000000&
); SREC.BKVALS := DBMS_STATS.NUMARRAY(
0,1
); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"MED_OPERATION_SCHEDULE"','"OPERATING_ROOM"', NULL ,NULL,NULL,4,.25,0,srec,7,6); END;

--删除一些怪异的字符，重新排版，这样可以很好检查是否写错!
DECLARE
   srec   DBMS_STATS.statrec;
BEGIN
   srec.minval := '786E0B1E090101';
   srec.maxval := '966F0711111F01';
   srec.eavs := 0;
   srec.chvals := NULL;
   srec.novals := DBMS_STATS.numarray (2455531.33333333, 3551487.6875);
   srec.bkvals := DBMS_STATS.numarray (0,1);
   srec.epc := 2;
   DBMS_STATS.set_column_stats (NULL,
                                '"MED_OPERATION_SCHEDULE"',
                                '"SCHEDULED_DATE_TIME"',
                                NULL,
                                NULL,
                                NULL,
                                7555,
                                .000132362673726009,
                                0,
                                srec,
                                8,
                                6
                               );
END;

4.最大最小如何修改呢？需要了解srec.minval以及srec.maxval转换。google找到如下链接：
http://mwidlake.wordpress.com/2010/02/24/update-to-decoding-high-and-low-values/

SELECT column_name, data_type, low_value,high_value, density,rtrim(
               to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
                      + (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00')) l,
               rtrim(
               to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
                      + (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00')) h
  FROM dba_tab_cols
 WHERE table_name = 'MED_OPERATION_SCHEDULE' AND column_name = 'SCHEDULED_DATE_TIME'

COLUMN_NAME,DATA_TYPE,LOW_VALUE,HIGH_VALUE,DENSITY,L,H
SCHEDULED_DATE_TIME,DATE,786E0B1E090101,966F0711111F01,0.000132362673726009,2010-11-30 08:00:00,5011-07-17 16:30:00,

--比较麻烦。放弃这样算的方法！

5.采用建立一个表的方法,在测试库建立：
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

SQL> create table t(vd date);
Table created.

SQL> insert into t values('2010-11-30 08:00:00');
1 row created.

SQL> insert into t values('2012-2-15 18:00:00');
1 row created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=> 't', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

SQL> column column_name format a10
SQL> column data_type format a10
SQL> column lower_value format a20
SQL> column high_value format a20
SQL> SELECT column_name, data_type, low_value, high_value, density   FROM dba_tab_cols WHERE table_name = 'T' AND column_name = 'VD';
COLUMN_NAM DATA_TYPE  LOW_VALUE                                                        HIGH_VALUE              DENSITY
---------- ---------- ---------------------------------------------------------------- -------------------- ----------
VD         DATE       786E0B1E090101                                                   7870020F130101               .5

HIGH_VALUE=7870020F130101

这个如何修改呢？srec.novals := DBMS_STATS.numarray (2455531.33333333, 3551487.6875);
还是使用emp的方法（略),与上面相同:

SREC.NOVALS := DBMS_STATS.NUMARRAY(2455531.33333333,2455973.75)

最后修改如下：

DECLARE
   srec   DBMS_STATS.statrec;
BEGIN
   srec.minval := '786E0B1E090101';
   srec.maxval := '7870020F130101';
   srec.eavs := 0;
   srec.chvals := NULL;
--   srec.novals := DBMS_STATS.numarray (2455531.33333333, 3551487.6875);
   SREC.NOVALS := DBMS_STATS.NUMARRAY(2455531.33333333,2455973.75);
   srec.bkvals := DBMS_STATS.numarray (0,1);
   srec.epc := 2;
   DBMS_STATS.set_column_stats (NULL,
                                '"MED_OPERATION_SCHEDULE"',
                                '"SCHEDULED_DATE_TIME"',
                                NULL,
                                NULL,
                                NULL,
                                7555,
                                .000132362673726009,
                                0,
                                srec,
                                8,
                                6
                               );
END;

6.在测试上面的sql语句，发现可以使用索引了。只不过加了bitmap convert+bitmap or操作。

7.锁定以后不分析表。
BEGIN
  SYS.DBMS_STATS.LOCK_TABLE_STATS (
      OwnName        => 'MEDSURGERY'
     ,TabName        => 'MED_OPERATION_SCHEDULE');
END;
/

整个优化完成！

8.BTW最终没有选择这样的方式，我还是建立了直方图。仅仅是为了学习！

9.补充学习：
关于SREC.NOVALS := DBMS_STATS.NUMARRAY(2455531.33333333,2455973.75);里面的数字，表示的是Julian format, ie number of days since 1st Jan 4712BC.
http://mwidlake.wordpress.com/2010/02/06/format-of-date-histograms/

SQL> select 2455973.75+to_date(1,'J')-1 from dual;

2455973.75+TO_DATE
-------------------
2012-02-15 18:00:00

SQL>  select TO_DATE(TRUNC(2455973.75),'J')+(2455973.75-TRUNC(2455973.75)) from dual;
TO_DATE(TRUNC(24559
-------------------
2012-02-15 18:00:00

反过来呢？如果知道日期如何转换呢?'J'仅仅取到整数部分。修改如下:

SQL> select to_char(to_date('2012-02-15 18:00:00','YYYY-MM-DD HH24:MI:SS') ,'J')+to_date('2012-02-15 18:00:00','YYYY-MM-DD HH24:MI:SS')
-trunc(to_date('2012-02-15 18:00:00','YYYY-MM-DD HH24:MI:SS')) x from dual
SQL> /
         X
----------
2455973.75