Re: Sort/Merge problem

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? Received on Tue Oct 03 2006 - 13:44:16 CDT