hi.baidu/davey_deng/blog/item/288764fbda9ad416a8d311a9.html
oracle 10g自动收集统计信息引起索引失效问题分析2009-04-16 23:12一、原因分析        根据进件项目组针对交叉检查运行慢分析，初步定位造成交叉检查有时不能按时完成的原因，是由于oracle自动收集统计信息功能，导致交叉检查在途件表(s08s1_chk_app_ontheway)的统计信息有误，直接导致执行计划中不能使用到正确的索引，最终导致交叉检查运行慢！具体原因如下：      从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。这个自动任务默认情况下在工作日晚上22：00-6：00和周末全天开启（该时间指的是oracle数据库时间，下文中没有特殊说明所述时间均指oracle数据的系统时间）。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。然后确定优先级，再开始进行统计信息。可以通过以下查询这个JOB的运行情况：
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'
其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：
SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;
JOB_NAME      LAST_START_DATE------------------------------ --------------------------------------AUTO_SPACE
_ADVISOR_JOB    04-DEC-07 10.00.00.692269 PM +08:00GATHER_STATS_JOB          04-DEC-07 10.00.00.701152 PM +08:00FGR$AUTOPURGE_JOBPURGE_LOG?                05-DEC-07 03.00.00.169059 AM PRC然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。所以建议最好关闭这个自动统计信息收集功能，自动化永远而严重的隐患相伴随！        具体到我们的情况如下，当我们的autosys里交叉检查批s08crosscheck里的子任务s08crosscheck_data，如果该任务在晚上22:00前启动且在22:00前执行完成，上文中Oracle自动收集统计信息功能中收集到的就是正确的信息，利用以下SQL语句查询相关情况：SELECT a.last_analyzed, a.num_rows, a.user_stats  FROM user_tables a    WHERE a.table_name = 'S08S1_CHS_APP_ONTHEWAY'last_analyzed            num_rows    user_stats------------------------------------------------------------------------2009-2-19 22:00:07    705973            NO这种情况下该晚交叉检查能正常跑完。反之如果交叉检查批s08crosscheck里的子任务s08crosscheck_data在晚上22:00前启动，但到22:00前还未完成，最Oracle自动收集统计信息功能中收集到的就是不正确的信息，利用以下SQL语句查询到的相关情况如下：SELECT a.last_analyzed, a.num_rows, a.user_stats  FROM user_tables a    WHERE a.table_name = 'S08S1_CHS_APP_ONTHEWAY'last_analyzed            num_rows    user_
stats------------------------------------------------------------------------2009-2-19 22:00:07        0                YES这
种情况下，由于Oracle自动收集信息统计到的ontheway这张表中的数据记录条数为零，故当22时后交叉检查十六个存储过程启动时，所用的执行策略有问题，没有用到正确的索引，导致交叉检查运行速度缓慢（大部份会导致当天交叉检查不能正常跑完）。      正是由于以上原因，导致交叉检查在随机某天不能正常跑完。    二、解决方案    以上问题有如下三种解决方案：    1、关闭oracle自动收集信息功能，改为手工收集信息或定时收集信息；    2、在晚上22:00左右运行的存储过程，特别是涉及到装数和卸数的存储过程后，加上一个分析表的语句（可以通加在autosys中新加一个任务实现），该方法的缺点是，以后在22:00附近在安排装数和卸数的存储过程时，都要注意加一句分析表的语句，同时在日常维护过程中如果有批HOLD到晚上22:00运行时，且该批存在装数和卸数的存储过程，也要注意分析表的问题；      3、将交叉检查改到22:00以后（例如22:30）开始运行；    针对以上三种解决方案，具体方法或步骤如下：      方案一：关闭oracle自动收集信息功能，改为手工收集信息或定时收集信息；      方法:1、关闭及开启自动搜集功能，有两种方法，分别如下：                方法①： exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');                            exec able('SYS.GATHER_STATS_JOB');                方法②： alter system set "_optimizer_autostats_job"=false scope=spfile;                            alter system set "_optimizer_autostats_job"=true scope=spfile;                Pfile可以直接修改初始化参数文件，重新启动数据库。            2、AWR默认通过MMON及MMNL进程来每小自动运行一次，为了节省空间，采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改。要查看当前的设            置，您可以使用
下面的语句：                  select snap_interval, retention                  from dba_hist_wr_control;                  SNAP_INTERVAL      RETENTION                  ------------------- -------------------
+00000 01:00:00.0  +00007 00:00:00.0                  这些 SQL 语句显示快照每小时采集一次，采集的数据保留 7 天。要修改设置 — 例如，快照时间间隔为 20 分钟，保留时间为两天 — 您可以发出以下命令。              参数以分钟为单位。                  begin                          dbms_dify_snapshot_settings (                              interval => 20,                              retention => 2*24*60                          );end;                      AWR 使用几个表来存
储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命                名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。（我们应该可能已经猜到，H 代表“历史数据 (historica              l)”而 M 代表“元数据 (metadata)”。）在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写我们自己的性能诊断工具。视图的名称直接              与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上构建的。                    我们的处理计划一般是有规律的，并且通常基于我们对各种事件的了解和您处理它们的经验。现在设想相同的事情由一个引擎来完成，这个引擎采集量度并根据预              先确定的逻辑来推出可能的计划。我们的工作不就变得更轻松了吗？ 现在在 Oracle Database 10g 中推出的这个引擎称为自动数据库
诊断监控程序 (ADDM)。为              了作出决策，ADDM 使用了由 AWR 采集的数据。                    在 AWR 进行的每一次快照采集之后，调用 ADDM 来检查量度并生成建议。因此，实际上我们拥有了一个一天二十四小时工作的自动数据库管理员，它主动地分              析数据并生成建议，从而把我们解放出来，使我们能够关注更具有战略意义的问题。                    快照默认是自动采集的，但我们也可以按需要采集它们。所有的 AWR 功能都在程序包 DBMS_WORKLOAD_REPOSITORY 中实施。要采集一次快照，只需发出              下面的命令：                    execute dbms_ate_snapshot它立即采集一次快照，快照被记录在表 WRM$_SNAPSHOT 中。采集的量度是针对 TYPICAL 级                别的。如果我们想采集更详细的统计数据，我们可以在上面的过程中将参数 FLUSH_LEVEL 设置为 ALL。统计数据自动删除，但也可以通过调用过程 drop_snaps                hot_range() 来手动删除。          方案二：在装数和卸数的存储过程后，加上一个分析表的语句。具体方法如下：                  1、评估哪些存储过程需要加表分析语句，目前初步评估需要加的存储有普通件交叉检查和商务卡交叉检查；                  2、在s08crosscheck_data任务后再加上一个新任务，主要语句类似如下：dbms_stats.gather_table_stats('cissaps', 's08s1_crs_app_ontheway');                  3、以后在22:00加批需要进行评估，小心操作。        方案三：在autosys上调整跑批时间，以后在22:00附近加批时需要注意。    三、目前生产上解决的方案：          1、加表分析语句              存储过程：s08p1_batch_crsc
hk_data.prc                            第140行 加分析表语句 dbms_stats.gather_table_stats
oracle手动调用存储过程存储过程：s08p1_batch_crschk_pre
第192行 dbms_stats.gather_table_stats('cissaps','s08s1_chk_batch_crs');
第193行 dbms_stats.gather_table_stats('cissaps','S08S1_CHK_BATCH_CRS_CROP');          2、加索引暗示                存储过程：s08p_chk_difapp.prc                            第107行 /*+ index(b,IDX_S08S1_CHK_APP_ONTHEWAY4)*/
存储过程：s08p_chk_difcust                            第161行 /*+ index(a,IDX_S08S1_CHK_APP_ONTHEWAY7)*/