HowTo: Trace the statistics data gathtering

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-11-19 05:59:21

Share to 

Sometimes, we want to know which objects failed to be gathered the stats data after the gathering job complete? What's the reason? We may set an undocumented parameter "TRACE" to capture such information.     There are several levels for this setting, which are bit flags.     1 (DSC_DBMS_OUTPUT_TRC); // output the trace log, not into trace file   2 (DSC_SESSION_TRC); // trace in session only, will not modify the value of TRACE parameter   4 (DSC_TAB_TRC);  // trace log when gathering table stats data   8 (DSC_IND_TRC);  // trace log when gathering index stats data   16 (DSC_COL_TRC);  // trace log when gathering column stats data   32 (DSC_AUTOST_TRC); // save session state log into sys.stats_target$_log   64 (DSC_SCALING_TRC); // trace the scaling log   128 (DSC_ERROR_TRC);  // trace the errors/exceptions   256 (DSC_DUBIOUS_TRC);  // trace the dubious stats data   512 (DSC_AUTOJOB_TRC);  // trace the auto gathering job's event & error   1024 (DSC_PX_TRC);  // trace the parallel log   2048 (DSC_Q_TRC);  // dump the queries during gathering stats data   4096 (DSC_CCT_TRC);  // trace the internal process of gathering MV stats data   8192 (DSC_DIFFST_TRC);  // trace the differences of stats data before/after gathering     You can check the setting by query SYS.OPTSTAT_HIST_CONTROL$.   SQL代码 HELLODBA.COM>select spare1, spare2, spare3, spare4, spare5, spare6 from SYS.OPTSTAT_HIST_CONTROL$ wh    ere sname='TRACE';           SPARE1     SPARE2     SPARE3    SPARE4     SPARE5     SPARE6    ---------- ---------- ---------- --------- ---------- ----------                                           512        For example, set it to be 512, it will trace the events & error of auto gathering job.   SQL代码 HELLODBA.COM>exec dbms_stats.set_param('TRACE', 512);       PL/SQL procedure successfully completed.       HELLODBA.COM>alter session set tracefile_identifier=stats_trace;       Session altered.       HELLODBA.COM>exec dbms_stats.GATHER_DATABASE_STATS_JOB_PROC();       PL/SQL procedure successfully completed.        And you will find a trace file under UDUMP folder after your job started. It traced the events and errors.   SQL代码 *** 2009-11-19 12:01:07.539    *** SERVICE NAME:(SYS$USERS) 2009-11-19 12:01:07.461    *** SESSION ID:(310.812) 2009-11-19 12:01:07.461    *** gather_database_stats_job_proc started: sid,ser#=310,812    *** 2009-11-19 12:01:28.915    ORA-03001: unimplemented feature    *** 2009-11-19 12:01:28.915    GATHER_STATS_JOB: GATHER_INDEX_STATS('"DEMO"','"NOR_INDEX_DEMO"','""', ...)    ORA-03001: unimplemented feature    GATHER_STATS_JOB: Stopped by Scheduler.    Consider increasing the maintenance window duration if this happens frequently.    The following objects/segments were not analyzed due to timeout:      TABLE: "SYS"."WRI$_ALERT_OUTSTANDING".""     TABLE: "SYS"."WRH$_LATCH_MISSES_SUMMARY"."WRH$_LATCH__1712582900_31484"   ...    *** 2009-11-19 12:04:20.982    *** gather_database_stats_job_proc finished        --- Fuyuncat Mark ---