Re: Performance problem - help appreciated.

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