Re: Optimizer assitance needed

industrialstr_at_yahoo.com (Masterhit) wrote in message news:<f3b449ab.0408031314.761308bb_at_posting.google.com>...
> In the process of re-analyzed the table some things went 'bad'.
>
> (deleted old stats 1st)
> exec dbms_stats.gather_table_stats(ownname =>'IBS', tabname
> =>'SALES_DETAIL', method_opt =>'FOR COLUMNS TRANSACTION_TYPE SIZE
> 9', cascade =>TRUE, degree => 10, estimate_percent => 1) (Subsequently
> I started using .01 pct which is still 125K rows)

This doesn't help you now, but dbms_stats has built in CYA functionality with the opportunity to save existing stats to a table.

> I don't know what else to include.. my chief question is what can I
> check to determine why PROD chose the plan it did vs TEST? The
> init.ora params are the same (except regarding #processors) Test is a
> clone of PROD only about a month old (stale).

It looks to me as though your prod system has chosen parallel query. I'm assuming that that is what you mean by you comment about #processors? is Parallel Query available on the test system?

Oh and yes I'd have though a transaction history table of 1bn rows would be an excellent candidate for partitioning.

Niall Litchfield
Oracle DBA Received on Wed Aug 04 2004 - 04:58:45 CDT