MONITORING

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,
6. Add the MONITORING keyword to it.
7. 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 Received on Mon Jan 07 2002 - 10:15:20 CST