Re: MONITORING

Brian Tkatch wrote:
>
> 8.1.6
>
> I want to make sure statistics gathering happens on a normal basis.
> I'd also like to know that I am going to do it properly. Please take a
> look at this. I'd appreciate any comments on it.
>
> Here's what I am assuming.
>
> 1) ALTER all existing tables and add the MONITORING keyword.
>
> 2) DBMS_STATS.GATHER_SCHEMA_STATS(
> OWNNAME => '<schema>',
> CASCADE => TRUE
> );
>
> 3) DBMS_STATS.CREATE_STAT_TABLE( '<schema>', '<stattable>');
>
> 4) Periodically,
> DBMS_STATS.GATHER_SCHEMA_STATS(
> OWNNAME => '<schema>',
> CASCADE => TRUE,
> STATTAB => '<stattable>',
> STATID => TO_CHAR(SYSDATE),
> OPTIONS => 'GATHER STALE'
> );
>
> 5) Whenever adding a new table,
> a) Add the MONITORING keyword to it.
> b) DBMS_STATS.GATHER_TABLE_STATS(
> OWNNAME => '<schema>',
> CASCADE => TRUE
> );
>
> Is this correct?
>
> Is there any way to add MONITORING to all tables without adding the
> keyword. That is, can it be added to a SCHEMA instead?
>
> Is there a reason not to put MONITORING on all tables?
>
> Is there a reason not to cascade to INDEXES?
>
> Is it accurate to say, that the only reason to use ESTIMATE is to save
> time and resources, but if I have the time and resources COMPUTE will
> always be better?
>
> How often should STALE statistics be checked for? (If it relies on
> MONITORING, there should be no harm in checking daily.)
>
> Is there a way to GATHER STALE statistics automatically (E.G. with
> DBMS_JOBS), or is that a bad idea?
>
> I looked at HISTOGRAMS, but I am not sure that we need them. Being I'd
> have to look at the CARDINALITY of a column to decide how many buckets
> to use, and that may change on some basis, I'd see how the rest goes
> before playing with them. Are HISTOGRAMS used often?
>
> I was wondering if a BOOLEAN column where the values are not evenly
> distributed, rather very weighted, such as 80/20 or even 90/10, if
> that would be a good use of an INDEX with a HISTOGRAM to check for the
> uncommon value.
>
> Brian

MONITORING is meant to be very unintrusive, but even so, I'd still be relying on the best source of information for which tables to monitor - namely, the brain. For example, the vast majority of tables in a system will be static - so monitoring (and the associated re-analyze) would seem a waste on these objects.

IMHO, "we" (ie the oracle community) generally analyze too much, too often.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."