Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why does CBO not use available indices

Re: why does CBO not use available indices

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 9 Oct 2003 08:52:28 +0100
Message-ID: <bm342j$jj2$1$8300dec7@news.demon.co.uk>

I really dislike your suggestion 4, and I'm not too keen on option 1 either. Fixing a problem by kicking it until it slinks away bloody and bruised is not a strategic solution - it's only going to bounce back later, probably at the most awkward possible moment.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message
news:3f829925$0$24515$afc38c87_at_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 Thu Oct 09 2003 - 02:52:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US