• From: Adam Musch <ahmusch@xxxxxxxxx>
  • To: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • Date: Fri, 19 Feb 2010 12:52:37 -0600

From what I can tell, the OCM job isn't supposed to.  In my systems,
submits this to run on each instance:


That' puts out to a file in the directory the results of


That job is all about monitoring what features have of the database have
been used, I suppose for automatic licensure compliance.  It's not about
gathering database optimizer stats, that's SYS.GATHER_STATS_JOB.

That job is subject to the maintenance windows, and if you have too many
objects which need to have their statistics gathered -- after all, Oracle's
being helpful with their defaults of gathering histograms all over creation
according to undocumented logic -- you may have a problem where (defaults in

1.  The stats job kicks off at the start of the maintenance window (10pm
local M-F; 12 AM Saturday)
2.  The stats job iterates over each table in DBA_TAB_MODIFICATIONS,
serially -- one segment at a time -- gathering table, table partition, table
subpartition, index, index partition, and index subpartition statistics,
including histograms.  This appears to be in schema-table order.  As each
table finishes, it's removed from the DBA_TAB_MODIFICATIONS view.
3.  It gathers what it can until the window closes (6am local; 6AM Monday) .

The next window, it starts over.  However, any tables which didn't get to in
the previous run didn't get gathered, and if there's enough activity (approx
10% of table volume inserted/updated/deleted and/or table truncated), a
table which got gathered yesterday will jump ahead of tables which got
skipped in the previous run.

You may wish to issue a call to DBMS_STATS.FLUSH_DATABASE_MONITORING and get
a snapshot of what those views looked like at the start and end of the
maintenance window to see what tables getting fresher statistics.  Also, the
see what got touched.  If the job isn't finishing, you should get rows in

The process Oracle delivered here, in short, is awful.  It works fine for
small systems, but for larger systems including partitioning and
subpartitioning, the histogram defaults require reiterative scanning of
partitions and tables as it doesn't do any rational digesting.  It's
riduclously underdocumented, to boot.  I could go off on how evil histogram
defaults are, especially in a world where you can't tell Oracle about column
correlation and how badly that skews cardinality estimates, but that's for
another day.

On Fri, Feb 19, 2010 at 11:33 AM, Kellyn Pedersen <kjped1313@xxxxxxxxx>wrote:

>   Good thought, but nope, not in this environment, no stats are locked in
> this OLTP database...
> I have this problem in all my databases and for the other databases that I
> manage exclusively, I've moved to my own scripts that utilize this table as
> it's source, but I really want to know what's up and why the OCM auto stats
> job doesn't clear this table out..:(
> The other issue I have, is that the job that inserts into
> DBA_TAB_MODIFICATIONS appears to scan different areas of the database in
> certain intervals, so it can suddenly populate up to 14,000 rows to gather
> stats on when there are many partitions involved, (I would think that a
> partition level trigger to populate would be more efficient...)
> Due to this, I have my script looking at the row count by date and if it
> reaches a threshold, it will exit and keep that days updates for the weekend
> run to not impact production.  To me, that just seems like a bug-  to not
> view the staleness at a partition level vs. table level...
> Kellyn
> --- On *Fri, 2/19/10, Adam Musch <ahmusch@xxxxxxxxx>* wrote:
> From: Adam Musch <ahmusch@xxxxxxxxx>
> Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
> To: kjped1313@xxxxxxxxx
> Cc: "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
> Date: Friday, February 19, 2010, 9:55 AM
> Are the stats on those tables locked (STATTYPE_LOCKED in
> On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen 
> <kjped1313@xxxxxxxxx<>
> > wrote:
>>   Here's my stupid question for the week-  I'm sure I'm just missing
>> something here...
>> Oracle on Linux-  I'm under the impression from all that I've
>> read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row
>> count changes for tables that are considered "stale" and need to have stats
>> gathered.
>> Should the OCM auto job for gathering stats, that's driven off the
>> DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once
>> it runs on it's schedule?
>> I have tables back from the conception of the database in
>> DBA_TAB_MODIFICATIONS.  No gathering of stats have been taken by the auto
>> stats job.  This is both for the SYS and the application schema, so it's not
>> just at a level where I would look for a setting that says, "don't gather
>> stats on system schema objects".
>> I've verified that if I gather stats on a table that is in
>> DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so
>> it's not an issue of not updating the table and the stats job doing it's
>> part-  it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS
>> table.  The jobs are scheduled precisely as they should and are running to
>> completion, no issues there.
>> There isn't a lot of documentation on the OCM stats module, but maybe I'm
>> not searching for the right terms...
>> Can anyone tell me-  is there a bug in 10g for this feature?  Is this
>> table utilized differently than what I think it is? Is Kellyn just not
>> searching with the right terminology? :)
>> Thanks!
>> Kellyn Pedersen
>> Multi-Platform DBA
>> I-Behavior Inc.
>> "Go away before I replace you with a very small and efficient shell
>> script..."
> --
> Adam Musch
> ahmusch@xxxxxxxxx<>

Adam Musch

Other related posts: