Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
Date: Fri, 19 Feb 2010 09:33:03 -0800 (PST)
Message-ID: <222767.41250.qm_at_web32001.mail.mud.yahoo.com>
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_at_gmail.com> wrote:
From: Adam Musch <ahmusch_at_gmail.com>
Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
To: kjped1313_at_yahoo.com
Cc: "oracle Freelists" <oracle-l_at_freelists.org>
Date: Friday, February 19, 2010, 9:55 AM
Are the stats on those tables locked (STATTYPE_LOCKED in DBA_TAB_STATISTICS)?
On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
Here's my stupid question for the week- I'm sure I'm just missing something here...
Oracle 10.2.0.4 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.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 19 2010 - 11:33:03 CST