Re: Performance issues when sorting.

On 19 Jan 2004 11:56:14 -0800, john_at_homer.fsc.edu (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

I don't think anyone leaves sort_area_size to it's default of 65535, 1M (1048576) is definitely recommended.
Secondly you must be sure the default storage clause of your temp tablespace (which shouldn't be a dictionary managed tablespace, but preferably a locally managed temp tablespace) is correct, which means : initial extent 1 M next extent 1M pctincrease 0 maxextents unlimited)
sort_direct_writes must be set to auto
and sort_multiblock_read_count * block_size should equal 64k or anything the O/S can read with one disk access.

--
Sybrand Bakker, Senior Oracle DBA