Helping the CBO

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 - 09:41:27 CDT