Re: Performance issues when sorting.

Hi,

I have basicly tried all this. With no real change. I can run queries against a copy of our database that is several months old and things run fine. A report that runs 3 hours or so in our production data base will run in 2-3 minutes in the older copy (the current production database is not much larger). Are there any tools or way to look deeper into exactly what Oracle is doing? I've compared the v$parameters for each instance and they are the same. Any more ideas?

Thanks for your help.

JohnB

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<400D1483.733E_at_yahoo.com>...
> 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:
>
> a) 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
>
> b) hash params will default to 2x sort memory typically, unless you're
> using auto mem mgt in 9i.
>
> c) 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 Wed Jan 21 2004 - 10:18:45 CST