Re: Sort/Merge problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 03 Oct 2006 15:30:36 -0700
Message-ID: <1159914633.994064@bubbleator.drizzle.com>

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?

The above is NOT an explain plan and hasn't been since Oracle 8i.

Go to Morgan's Library at www.psoug.org and look up Explain Plan. Then run a proper plan with DBMS_XPLAN.DISPLAY which will show you temp space usage.

-- 
Daniel Morgan
Puget Sound Oracle Users Group

Received on Tue Oct 03 2006 - 17:30:36 CDT