Performance issues when sorting.

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 Received on Mon Jan 19 2004 - 13:56:14 CST