Re: why does CBO not use available indices

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Tue, 7 Oct 2003 20:42:06 +1000
Message-ID: <3f829925$0$24515$afc38c87@news.optusnet.com.au>

"Anke Heinrich" <anke.heinrich_at_marconi.com> wrote in message news:9042145d.0310060903.4ed4e354_at_posting.google.com...

>
> Is there a way to force the optimizer to use a specific plan for
> dynamically generated statements without changing source code?
>
> Are there any other options left?

Besides the other excellent replies, and in order of trial:

1- Use DBMS_STATS to get the stats off the tables wit few rows. Snapshot that value and use it to set the stats of the fully populated tables to what they were before full loading.

2- optimizer_index_caching.  Set it between 80-90 and see if it helps.
3- remove histograms and use DBMS_STATS to gather the statistics.
4- remove stats altogether from ONE of the tables.  I'd say TP.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam

Received on Tue Oct 07 2003 - 05:42:06 CDT