Re: Performance issues when sorting.

JohnB wrote:
>
> Hi,
>
> We are currently running Oracle 8.1.7 (since Feb 2002) on an
> HP9000/N4000 with HP-UX 11. Around the end of November we suddenly
> started having problems with applications and SQL queries/reports
> hanging. After some research we found that the processes that were
> hanging were ones that had an 'order by' or 'group by' clause (i.e.
> removing the sort allowed the process to run). Increasing the
> sort_area_size parameter from it's 65k default seem to fix the problem
> with hanging but the processes are running extreamly slow (hours
> instead of minutes). I have watched queries run using a monitoring
> tool and can see the physical reads, consistent gets, etc increment
> normally and then stop. The query would then sit there for an extended
> period of time and eventually finish.
>
> Around the same time we started getting ORA-03232 errors. This was
> 'fixed' by setting the parameter hash_join_enable to false. We
> continue to experience the performance problems (particularly with
> queries that use views). Our last attempt to fix the issue has been to
> rebuild the database (export, recreate and import), but this did not
> seem to help.
>
> One final note: we have migrated a copy of our production database to
> Oracle 9i and the problems seem to have disappeared, although we have
> not been able to do any extensive testing. Getting to 9i with our
> production database at this point is not possible so I really need to
> get the performance problem fixed. Any ideas????
>
> Thanks,
>
> JohnB

Couple of things that are important in this regard:

1. set sort_... params to something that is appropriate for your users. Note that certain sessions can have larger ones than the default, and also that the memory is a ceiling - not an allocation that everyone gets immediately
2. hash params will default to 2x sort memory typically, unless you're using auto mem mgt in 9i.
3. hash joins work best when your optimizer stats are accurate, and are susceptible to serious degradation when your stats are not.

hth
connor Received on Tue Jan 20 2004 - 05:44:03 CST