Re: Helping the CBO

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