CBO & empty tables & empty databases (schemas).. but growing fast

We have a number (10-20) of similarly structured (table-wise) databases. Recently I started looking into CBO, analyzing tables etc. The initial results/tests went pretty well (especially after looking into OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ as suggested by some group members here), Only one query performed worse, then before the CBO, we just stuck /*+ RULE */ into it for now. All other queries started performing way better (our developers weren't considering much for order of tables lately, which is important in RBO) my first find was that I couldn't run dbms_stats.analyze_schema while db was accessibly by other users. I am not sure if dbms_stats.analyze_schema was doing any locking, or simply some of the queries were going 'bad' as some of the tables were analyzed and some weren't. Therefore it seems like I'd need a maintenance window to analyze tables (at least initially, i haven't had the time to try again)

my second problem is that some of the tables are completely empty, but they get filled very quickly and get used right away. My concern is that CBO would do full table scans of the table, since the rowcount would be like 0. The easy solution here was to delete statistics on the tables with 0 rows.

my third problem, is that our databases are 'seasonal' (tried to sporting events). They can start out very small to empty, but grow "very" fast. I don't want to analyze every night, especially if it's going to be a performance hit (we have heavy batch jobs running at night), but at the same time, I don't want the statistics to get out of date. I might explore the whole 'monitoring' idea here.

Any suggestions?

Thanks
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Feb 27 2003 - 16:53:24 CST