We're facing a weird issue with 10.2.0.2 which did not exist in 10.1 and before.
We've disabled the standard stats gatherign job since it is generating histograms which doesnt work well with the application.
this is what my job looks like
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'abc'
,Granularity => 'DeFAULT'
,Options => 'GATHER STALE'
,Gather_Temp => FALSE
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_opt => 'FOR ALL COLUMNS SIZE 1'
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
but every so often i see histograms back on the tables and the peformance starts sinking.
operationtargetstart_timeend_time
gather_database_stats 10/17/2006 1:35:13.261525 AM -05:0010/17/2006 1:35:13.853932 AM -05:00
gather_database_stats 10/17/2006 1:35:14.157003 AM -05:0010/17/2006 1:35:14.319034 AM -05:00
gather_database_stats 10/19/2006 1:20:17.302342 AM -05:0010/19/2006 1:20:18.074960 AM -05:00
gather_database_stats 10/19/2006 1:20:18.560200 AM -05:0010/19/2006 1:20:18.737658 AM -05:00
gather_database_stats 10/20/2006 1:35:27.848579 AM -05:0010/20/2006 1:35:28.617191 AM -05:00
gather_database_stats 10/20/2006 1:35:28.953961 AM -05:0010/20/2006 1:35:29.153783 AM -05:00
gather_database_stats 10/27/2006 1:15:12.106323 AM -05:0010/27/2006 1:15:12.790376 AM -05:00
gather_database_stats 10/27/2006 1:15:13.117510 AM -05:0010/27/2006 1:15:13.286650 AM -05:00
gather_database_stats 10/31/2006 1:20:13.260779 AM -06:0010/31/2006 1:20:13.976838 AM -06:00
gather_database_stats 10/31/2006 1:20:14.305219 AM -06:0010/31/2006 1:20:14.457594 AM -06:00
gather_database_stats 11/1/2006 1:45:09.231076 AM -06:0011/1/2006 1:45:09.906855 AM -06:00
gather_database_stats 11/1/2006 1:45:10.211063 AM -06:0011/1/2006 1:45:10.376020 AM -06:00
gather_database_stats 11/15/2006 1:15:07.822728 AM -06:0011/15/2006 1:15:08.403452 AM -06:00
gather_database_stats 11/15/2006 1:15:08.746648 AM -06:0011/15/2006 1:15:08.899510 AM -06:00
i've looked in the scheduler ,crontab and everywhere to see where this job is coming from but cannot find it .
the scheduler for this job is flase and has been for a while .
the statistics_level on this instance is typical
job_creatorprogram_ownerprogram_namejob_classenabledauto_droprestartablestatejob_priorityrun_count
SYSSYSGATHER_STATS_PROGAUTO_TASKS_JOB_CLASSFALSEFALSETRUEDISABLED384
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 17 2006 - 09:21:12 CST
