Re: SYS is sorting to disk

In article <89q878$ndp$1_at_nnrp1.deja.com>,   yong321_at_yahoo.com wrote:
> In article <38c0906e.7956761_at_news.earthlink.net>,
> andreyNSPAM_at_bookexchange.net wrote:
> > When I upped SORT_AREA_RETAINED_SIZE to 128K the problem seemed to
> > have gone away, but I still would like to know someone's opinion why
> > this happenned in the first place, since my sort_area_size is 16M.
> >
> > Unfortunately all my other DB's are configured with retained size of
> > 1m, so I can't compare to them (and they're Ora7 anyway), but I
 have a
> > different 8i db which has 64K of retained size and is not showing
 any
> > temp problems.
> >
> > Thanx again.
> >
> > On Sat, 04 Mar 2000 04:08:50 GMT, andreyNSPAM_at_bookexchange.net
> > (NetComrade ) wrote:
> >
> > >I set up a new db with a fairly large TEMP tablespace, later on I
> > >started getting pages that I am running out of TEMP space. So I
> > >started looking for the problem.
> > >
> > >I found that SYS schema is sorting to disk, and even when I
 decreased
> > >INITIAL and NEXT default TEMP extents to 1/16th of SORT_AREA_SIZE,
 SYS
> > >is still writing to disk, and in exactly 1M segments (my latest
> > >defualt Initial and NEXT)
> > >
> > >So it looks like SYS is avoiding sorting in memory, does anybody
 know
> > >why?
> > >
> > >Thanx.
> >
> >
>
> Is it possible your SYS schema has been analyzed? If so, do this:
>
> dbms_utility.analyze_schema('sys', 'delete', null)
>
> --
> Yong Huang
>

The sort_area_retained_size is used for performing sort/merge joins. the sorts to disk you see could be the result of SQL query plans. Haung mentioned checking for statistics existing on the sys owned objects. I will ask how do you know that these sorts belong to sys? The reason I ask is it seems to me that one of the v$sort* views always shows sys as the owner and you have to join it to another view to see the real application user.

Also as a rule per the Oracle 8 Database Performance and Tuning Work Shop notes the temp tablespace extent allocations should always be multiples of the sort_area_size plus one block.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.