Usage of the dbms_stats.gather_database_stats() procedure

I've just become aware of an instance that has 130 schemas with a total of 15,000 tables, 12,700 of which have no optimizer statistics. Even though things run relatively well without the stats we are sometimes bitten by poor performance without warning and have to compute stats as a fix. I'm thinking I want to get stats on all tables by default. I'm looking for recommendations on updating stats on all the tables and indexes. I'm thinking I want to use a big shotgun approach as a first pass then fine tune after that.  

I've used gather_schema_stats, gather_table_stats, and gather_index_stats with good effect but have not been confronted with the need to gather so many stats en masse. The database supports 24X7 Internet services and there is less activity over the weekend but it's still significant. I've never used the gather_database_stats procedure and I'm thinking that using it in these circumstances could take a long time and significantly degrade end user response times.  

(For the most part the 130 schemas have the same tables with variations for the version of the hosted software they are running and the number of customizations they have made. Many tables are empty because they are for product features the customer has not purchased or implemented and the product installs all the tables by default.)  

To lighten the impact of gathering stats I think I should use estimate at about 10%.  

I'm thinking this would be too much:

Gather_database_stats(10,method_opt=>'for all indexed columns size auto',cascade=>true);  

I'm thinking about creating a script that loops through all the schemas and does this:

Gather_schema_stats(schema,10,options=>'gather_empty',cascade=>true);  

But I'm not too sure about using the cascade option. Maybe I need to further script to get index stats separately.  

Any recommendations on the estimate percentage? Using cascade? Using gather_database_stats? Other?    

AtDhVaAnNkCsE,

Steve Orr

Bozeman, Montana  

--
http://www.freelists.org/webpage/oracle-l