ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120214]异常数据导致执行计划改变.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-02-15 09:18:09 0 删除 编辑
今天上午,用户反应一条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 < TO_DATE ('2012-02-15 23:59', 'yyyy-mm-dd hh24:mi')
        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

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2349
  • 访问量
    6091550