DBMS_STATS monitoring

[stmontgo]

Anyone using dbms_stats and monitoring option with a dash of gather stale?

We are in a PeopleSoft env with 6000+ tables so to compute stats nightly takes 8 hours.

We also have a PS delivered program that rebuilds a table every night.

What I am thinking of doing is
> Enable monitoring for all application tables
> Run a nightly job that (a) enables monitoring for all new tables (b) gathers scale stats for all application tables

Now the thing is the timing of the whole deal.

According to ref guide the user_tab_modifications view may not get updated for a few hours. So what if we do the table rebuild at 1am
and then the gen stats at 2am? The table will have been rebuilt, will the view pick it intime so that the gen stats proc knows that the data is stale and needs stats?

merci

[slimdave]

If you have a particular table that you know is rebuilt as a certain time, and will need stats gathering, then I wouldn't bother with monitoring -- just build the stats gathering into your rebuild process, or schedule it appropriately.

Doesn't generally hurt to have monitoring turned on though ... makes interesting information and let's you know where the activity is.

[tamilselvan]

I have been working with PS appln since 2002. We do not "compute statistics". We do only "estimate statistics".

Tamil

[pando]

are you using 9i?

[stmontgo]

Originally posted by pando
are you using 9i?

yep

[pando]

hi

in 9i the refresh interval is roughly 45 minutes but uf you want you can refresh manually using

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO or
DBMS_STATS.FLUSH_SCHEMA_MONITORING_INFO

[stmontgo]

Originally posted by pando
hi

in 9i the refresh interval is roughly 45 minutes but uf you want you can refresh manually using

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO or
DBMS_STATS.FLUSH_SCHEMA_MONITORING_INFO

outstanding, thanks!

[stmontgo]

Originally posted by stmontgo
outstanding, thanks!

someone do me a large one and see if they have FLUSH_SCHEMA_MONITORING_INFO before I go and run catproc again.

I can see FLUSH_DATABASE_MONITORING_INFO but not FLUSH_SCHEMA_MONITORING_INFO

I'm on 92020

tks

steve

[stmontgo]

Originally posted by stmontgo
someone do me a large one and see if they have FLUSH_SCHEMA_MONITORING_INFO before I go and run catproc again.

I can see FLUSH_DATABASE_MONITORING_INFO but not FLUSH_SCHEMA_MONITORING_INFO

I'm on 92020

tks

steve

ok i see it's a bug,
bug 2684982 removed from Docs apparently as doesn't really exist.

steve