Re: Performance issues when sorting.

JohnB wrote:
>
> 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

Event 10104 (if memory serves) will dump out hash partitioning information. Similarly, 10053 will dump out optimizer decision making information.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"