Re: Sort/Merge problem

Ben wrote:
> joel garry wrote:
> > Ben wrote:
> > > DA Morgan wrote:
> > > > Ben wrote:
> > > > > DA Morgan wrote:
> > > > >> Ben wrote:
> > > > >>> Ben wrote:
> > > > >>>> Oracle Ent Ed. 9.2.0.5, AIX5L
> > > > >>>>
> > > > >>>> came in this morning to 3 ora-1652 errors.
> > > > >>>>
> > > > >>>> I'm checking to see what is using all my temp space and there are 5
> > > > >>>> processes running the same select statement that each have almost 1 Gig
> > > > >>>> of TEMP space.
> > > > >>>>
> > > > >>>> When I look at the Long Operation of those processes it is a
> > > > >>>> Sort/Merge, and the explain plan shows a select that has an indexed
> > > > >>>> column in the predicate and is ordering by four other columns that make
> > > > >>>> up the primary key.
> > > > >>>>
> > > > >>>> Forgive my ignorance but why does this cause a Sort/Merge? Is it the
> > > > >>>> fact that it is binding by one index and sorting by a different index?
> > > > >>>>
> > > > >>>> Also, just a side note but the table and indexes were analyze about six
> > > > >>>> weeks ago. The table has 3 million rows and there have only been
> > > > >>>> 300,000 added since the last analyze. It is probably due for an analyze
> > > > >>>> this weekend, as we are analyzing using gather_stale.
> > > > >>>>
> > > > >>>> I'm trying to track down the cause of these processes to no avail, as
> > > > >>>> our ERP system has a generic os username that is used for all business
> > > > >>>> functions.
> > > > >>> here's the statement :
> > > > >>> SELECT *
> > > > >>> FROM pd.f42119
> > > > >>> WHERE sdpsn = :key1
> > > > >>> ORDER BY sddoco ASC, sddcto ASC, sdkcoo ASC, sdlnid ASC
> > > > >>>
> > > > >>> Here's the explain plan:
> > > > >>> 0 4 SELECT STATEMENT Cost = 10
> > > > >>> 1 4 SORT ORDER BY
> > > > >>> 2 4 TABLE ACCESS BY INDEX ROWID F42119
> > > > >>> 3 4 INDEX RANGE SCAN F42119_D3
> > > > >>>
> > > > >>> This returns 4 rows? I know that our sort_area_size is not very large
> > > > >>> but it should be large enough to accomodate 4 rows from this table.
> > > > >>> Can someone shed a little more light on this?
> > > > >> Shed some light on what?
> > > > >>
> > ...
> > >
> > > Just wondered if you could comment on one of my questions from the
> > > original post. Why would that select cause a sort/merge? Is it because
> > > of the different indexes being used, one for the predicate and another
> > > for the sort?
> >
> > Try:
> > SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,
> > a.serial#, a.username, a.osuser, a.status FROM
> > v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY
> > b.tablespace, b.segfile#, b.segblk#, b.blocks;
> > It is also possible you are running into bugs. Try
> > optimizer_feature_enable=8.1.7 to determine that, if so there may be
> > events that support could help you with. Range scanning an index
> > shouldn't use gigs of temp space. Could you also post the F42119_D3
> > index definition? Maybe the scan _is_ simply evaluating to a huge set
> > with just one column specified. Is there another index your where
> > clause could be more specific than spdsn? (Watch out for INDEX_SS
> > costing problems on some 920x's if spdsn isn't the leading column of
> > your index).
> >
> > jg
> > --
> > @home.com is bogus.
> > Challenges and Efforts to Secure Control Systems:
> > http://www.gao.gov/new.items/d04354.pdf

>
>

>

Another piece of information, I ran the new explain plan on a different query that actually used TEMP space and it shows up in the explain plan. I don't think this query is my culprit. This still doesn't explain why that Sort/Merge was taking so long though. Received on Fri Oct 06 2006 - 08:05:12 CDT