Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Excessive BUFFER sort

Re: Excessive BUFFER sort

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 16 Mar 2007 11:01:54 -0700
Message-ID: <1174068114.602625@bubbleator.drizzle.com>


S L Niemann 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.

Please rerun your explain plan using DBMS_XPLAN if you still need help.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Mar 16 2007 - 13:01:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US