作者：
fuyuncat
来源：
www.HelloDBA.com
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 ---