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

Hi!
Answers in text.

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3e5e92ef.279898963_at_nyc.news.speakeasy.net...
> 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)

What was the exact problem? An error message? Or analyze remained hanging? Analyze should be able to run on live db.

>
> 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.

You could set the statistics gathering after yout heavy batch jobs with low priority (look at resource manager), then it won't affect your production performance much.

If you really can't analyze data on live DB, then you can restore your DB on a backup machine, do the statistics collection there, then import the collected statistics to your prod DB using DBMS_STATS.

DBMS_STATS is able to store statistics of a segment in your-defined schema/table and copy them back to DD for CBO use. You can even edit statistics manually in your custom tables.

In 9.2 you could check optimizer dynamic sampling as well.. but this helps only with column stats as cardinality and predicate selectivity as I understand.

Also look for stored outlines and plan stability.

Tanel.

>
> 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 - 17:18:11 CST