今天上午,用户反应一条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