Re: Sort/Merge problem

From: <yong321_at_yahoo.com>
Date: 4 Oct 2006 21:15:31 -0700
Message-ID: <1160021731.827235.174520@e3g2000cwe.googlegroups.com>

Ben wrote:
> Lothar Armbrüster wrote:
> > "Ben" <balvey_at_comcast.net> writes:
> >
> > > 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?
> > >
> > [...]
> >
> > Hello Ben,
> >
> > ordering by varchar2 columns does not use any index unless nls_sort
> > is set to binary.
> > Normally, Oracle has locale specific sort orders that are different
> > from the binary order of the index.
> >
> > Hope that helps,
> > Lothar
> >
> > --
> > Lothar Armbrüster | lothar.armbruester_at_t-online.de
> > Hauptstr. 26 |
> > 65346 Eltville |

>
>

> Hello Lothar,

> Thanks for the info. Our nls_sort parameter is not set to binary.
> Although the primary key index that is being used in the order by
> clause has four fields that are Number, Char, Char, Number, in that
> order. So I don't think this would be the reason on this case.

Hi, Ben,

Can you do a little more to pursue Lothar's explanation? Documentation says "Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer." Two things you can do. Alter session set sql_trace=true and run the query and check the trace, or look at v$sql_plan for the actual plan used. This is to avoid the discrepancy between "explain plan" command output and the real execution plan. Secondly, while the query is running using temp space, what's shown in v$sort_usage (i.e. v$tempseg_usage)?

Explain plan won't show the TempSpc column if Oracle doesn't think any temp space is needed. You can test this by alter session set sort_area_size to a very small number and explain plan (assuming workarea_size_policy is MANUAL). Then set it to bigger than the number under TempSpc and explain again.

Yong Huang Received on Wed Oct 04 2006 - 23:15:31 CDT