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: Performance problem - help appreciated.

Re: Performance problem - help appreciated.

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 31 Mar 2004 01:51:11 -0800
Message-ID: <217ac5a8.0403310151.5f33e9ad@posting.google.com>


Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<qqoj60hmmu5gdh8u69nkbj5dicob4ieink_at_4ax.com>...
> On 30 Mar 2004 08:16:19 -0800, charlie3101_at_hotmail.com (Charlie
> Edwards) wrote:
>
> >So can anyone help?
> >Why does it suddenly ignore my first_rows hint?
> >Has anyone got any other suggestions on how I can solve my problem.
>
> Swap the order of the tables in the from clause, you have the driving
> table listed last and that should be first, using CBO
You surprise me - I was sure the CBO was cleverer than this - anyway it had no effect

> Make sure there are histograms on the indexed columns
There certainly are

> Make also sure optimizer_index_cost_adj and optimizer_index_caching
> are NOT at their defaults.

SQL> select * from v$parameter
  2 where name like 'optimizer_ind%';

      NUM NAME                                TYPE VALUE        
--------- ------------------------------ --------- -----------
      392 optimizer_index_cost_adj               3 100          
      393 optimizer_index_caching                3 0            
 

But I did ...
SQL> alter session set optimizer_index_cost_adj = 25;

Session altered.

SQL> alter session set optimizer_index_caching = 80;

Session altered.

And still the same problem

> Realize any outer join is just asking for awful execution plans.

I realise this, but of course I limited by the data.

However, even if I take out the outer joins I get exactly the same problem! Received on Wed Mar 31 2004 - 03:51:11 CST

Original text of this message

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