Re: Sort/Merge problem

DA Morgan wrote:
> Ben wrote:
> > 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
> >>
> >> The F42119_D3 index is a one column index on that field (SDPSN).
> >>
> >> Is it possible that a previous query by that session is what grabbed
> >> such a large chunk of TEMP space and then this Sort/Merge was running
> >> slow due to the large chunk of TEMP space that is was trying to utilize?
> >
> >
> > 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.

>

I didn't get a good trace file, nor can I re-create the event since we have no idea what generated the process. I did start the trace and saw that the wait event that executed for the entire 5M file was 'db file sequential read'. Received on Mon Oct 09 2006 - 09:17:35 CDT