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: Helping the CBO

Re: Helping the CBO

From: Nigel Puntridge <nigel_puntridge_at_hotmail.com>
Date: Sat, 27 Jul 2002 00:28:53 GMT
Message-ID: <9Pl09.142731$%%2.6091790@news2.east.cox.net>


We use PeopleSoft HR as well. If this is a view, you can add a hint to the view text in the Data Designer, or generate the SQL, add the hint, and then create it, thereby avoiding the customization

If it is a Stored Statement, find the .DMS script and add the hint there.

As long as you don't get carried away, it is very manageable.

Chuck wrote:

> 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 Fri Jul 26 2002 - 19:28:53 CDT

Original text of this message

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