Re: questions about cbo explain plan

lsllcm wrote:
> Thanks for your answer, it is very good,
>
> Yes, It is not the run text. I just replace the table and column name.
>
> I add the hint nest loop and re-run the sql, could you please give some
> suggestion to tune the sql?

Is it only this sql that is giving you issues, or is this a more problematic system in general? If it is, and this query is typical, both big ifs, then you may wish on your test system to investigate whether you don't get a better use of your system resources by setting PGA_AGGREGATE_TARGET to an appropriate number and WORK_AREA_SIZE_POLICY to AUTO rather than by setting SORT_AREA and HASH_AREA sizes.

This is obviously a large change, and not directed purely at this query (though I'd expect it to make some reasonable difference here) so should definitely be tested thoroughly. My reasons for making the suggestion are:

1. You've already attempted to tune the 'system' by setting OICA and OIC.
2. You've already set parameters to control work area size (unless I'm having a brain fart and 2 and 4m are the defaults in 9207)

This suggests to me that you are concerned about more than one query, and about sorting and hashing in general. If so automatic PGA memory management is likely to be a pretty good default option for you. I'm also assuming a dedicated server OracleNet architecture.

cheers

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Wed Nov 30 2005 - 02:31:32 CST