Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Helping the CBO
We need versions !
In any event you need to show the contents of Explain Plan
"Chuck" <chuckh_at_softhome.net> wrote in message
news:ahp2mo$unid4$1_at_ID-85580.news.dfncis.de...
> I have a query which if run under the CBO with no hints takes about 1
minute
> to complete. It joins about 8 tables in a Peoplsoft database using NL for
> all but one join. The last remaining join is a hash join. If I force it to
> use hash joins with a hint for all tables it runs in 9 seconds even though
> the cost is slightly higher. What can I do to get the CBO to pick the
> better hash join based query automatically since I can't change the app to
> use hints?
>
> I have tried increasing db_file_multiblock_read_count, sort_area_size, and
> hash_area_size. I've even tried increasing optimizer_index_cost_adj to the
> max of 10000. None of these changes helps it choose more hash joins. I
have
> tried analyzing the tables in the join several ways - compute and
estimate -
> with and without histgrams - nothing seems to help.
>
> I don't want to drop indexes because I don't know how it will affect the
> rest of the application. Is there anything I can do?
>
>
Received on Thu Jul 25 2002 - 10:44:24 CDT