Re: Excessive BUFFER sort

On Mar 16, 12:54 pm, "S L Niemann" <slniem..._at_gmail.com> wrote:
> On Mar 16, 10:54 am, "S L Niemann" <slniem..._at_gmail.com> wrote:
> > I have an unusual query that shows an awful cost.
> > Optimizer Cost Cardinality Bytes
> > SELECT STATEMENT ALL_ROWS 103631 39190235 3840643030
> > HASH(GROUP BY) 39190235 3840643030
> > CONCATENATION
> > MERGE JOIN(CARTESIAN) 51831 19603151 1921108798
> > MERGE JOIN(CARTESIAN) 366 46017 3819411
> > TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 3 1 23
> > INDEX(RANGE SCAN)INDX_Y ANALYZED 2 1
> > BUFFER(SORT) 363 68464 4107840
> > TABLE ACCESS(FULL)OM ANALYZED 363 68464 4107840
> > BUFFER(SORT) 51468 426 6390
> > TABLE ACCESS(FULL) AMO ANALYZED 1 426 6390
> > MERGE JOIN(CARTESIAN) 51788 19587083 1919534134
> > MERGE JOIN(CARTESIAN) 366 45979 3816257
> > TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 3 1 23
> > INDEX(RANGE SCAN)INDX_Y ANALYZED 2 1
> > BUFFER(SORT) 363 68464 4107840
> > TABLE ACCESS(FULL)OM ANALYZED 363 68464 4107840
> > BUFFER(SORT) 51425 426 6390
> > TABLE ACCESS(FULL) AMO ANALYZED 1 426 6390
> > NESTED LOOPS 12 4 392
> > HASH JOIN 8 4 152
> > TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 4 2 46
> > INDEX(RANGE SCAN)INDX_Y ANALYZED 2 4
> > TABLE ACCESS(FULL) AMO ANALYZED 3 426 6390
> > TABLE ACCESS(BY INDEX ROWID)OM ANALYZED 1 1 60
> > INDEX(UNIQUE SCAN) PK_OI ANALYZED 0 1
>
> > The query does join all three tables explicitly by their foreign keys.
> > But the buffer sorts are showing at 50K from a full table scan off of
> > one of the parent tables. It has a primary key and should be a full
> > table scan as there (other than the join) is no limiter on the table.
> > The three tables are M:M parents with the joined middle 'child'.
>
> > running 10gr2 base with the manual DST patch.
>
> Double checking - this may be moot.. looks like a subquery was using
> OR in a bad area, causing cartesian joins

How was the explain plan generated? What happened to the parent-child indentation in the plan? What does the original query look like?

One part of the query appears to be returning 1.9GB and the final query appears to be returning 3.8GB - is that the intention?

Please post the query and the output from DBMS Xplan.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Mar 16 2007 - 12:28:36 CDT