Sort/Merge problem

Giganews Newsgroups
Subject: Sort/Merge problem
Posted by:  Ben (balv…@comcast.net)
Date: 28 Sep 2006

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.

Replies