RE: Why the monstrous SORT?

Steve, yes you're right about adding the != and dropping the whole MINUS clause. That cuts the query and execution plan in half, but unfortunately it doesn't cut out that much work because most of the work (the huge SORT) was being done in the top half, so I still have to find a way to minimize that huge SORT operation. It does cut out a significant amount of work though so I will forward the recommendation to the developers.

Thanks!

-----Original Message-----
From: Steve Rospo [mailto:Steve.Rospo_at_vallent.com] Sent: Wednesday, July 06, 2005 5:40 PM
To: Allen, Brandon
Cc: oracle-l_at_freelists.org
Subject: Re: Why the monstrous SORT?

Where are you getting the "Rows" values from? If it's from an explain plan those are just estimates so I wouldn't put a lot of weight in those numbers. Does this query really return 237K rows? It looks very fishy that it comes out with different rows on either side of the MINUS.

SELECT inv.ord_no order_no, inv.line_no line_no, max(nvl(inv.price, 0

                 )) price, sum(nvl(inv.qty, 0)) quantity, max(inv.acctg_date)
                 invoice_date
              FROM ar_doc_line inv, oe_control oec
              WHERE oec.entity = inv.entity
                AND inv.cr_account != oec.oe_deferred_account

How many rows come out if you slap the GROUP BY on there?

If you have the patience and 9i run it all of the way through in a session with STATISTICS_LEVEL set to "ALL". This will drop some excellent statistics into V$SQL_PLAN_STATISTICS_ALL that will show the actual elapsed time for each level of the query. I think OEM will display this or I have a very cool query that will do it w/o OEM.

Taking a quick look at the query I think you could simplify the query so there's no need for the MINUS. Can you just add a "c.ord_type != 'Q'" and do away with the MINUS and cut the work in half?

On Wed, 6 Jul 2005, Allen, Brandon wrote:

> Hi,
>
> I've got an application query that is taking hours to run. After
> tracing and running tkprof, I see the execution plan below. Yes, it's a
> monster of a query, but Oracle seems to handle it pretty well except for
> the huge amount of rows being returned by the SORT (GROUP BY) step.
> How can it possibly have to sort so many rows (1.1 Billion!), when it is
> only getting 237,018 rows from the previous NESTED LOOP step? Any idea
> how to prevent or minimize this sort?
>
> Thanks!
> Brandon
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 11 MINUS
> 14 SORT (UNIQUE)
> 14 FILTER
> 285 SORT (GROUP BY)
> 4768 NESTED LOOPS (OUTER)
> 4769 NESTED LOOPS (OUTER)
> 4769 NESTED LOOPS
> 4769 NESTED LOOPS
> 285 HASH JOIN
> 197 VIEW OF 'AR_SALESPERSON_CODE'
> 197 SORT (UNIQUE)
> 197 SORT (GROUP BY)
> 197 HASH JOIN
> 212 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER'
> 5637 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES'
> 284 NESTED LOOPS
> 2 MERGE JOIN (CARTESIAN)
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER'
> 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE)
> 2 SORT (JOIN)
> 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
> 284 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR'
> 285 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE)
> 5052 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_LINE'
> 5052 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE)
> 9536 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY'
> 9536 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE)
> 4768 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL'
> 9536 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE)
> 4334 VIEW
> 1127322080 SORT (GROUP BY)
> 237018 NESTED LOOPS
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
> 237018 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE'
> 3 SORT (UNIQUE)
> 3 FILTER
> 4 SORT (GROUP BY)
> 8 NESTED LOOPS (OUTER)
> 9 NESTED LOOPS (OUTER)
> 9 NESTED LOOPS
> 9 NESTED LOOPS
> 4 HASH JOIN
> 3 NESTED LOOPS
> 2 MERGE JOIN (CARTESIAN)
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER'
> 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE)
> 2 SORT (JOIN)
> 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
> 3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR'
> 285 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE)
> 197 VIEW OF 'AR_SALESPERSON_CODE'
> 197 SORT (UNIQUE)
> 197 SORT (GROUP BY)
> 197 HASH JOIN
> 212 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER'
> 5637 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES'
> 11 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
> OF 'OE_LINE'
> 11 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE)
> 16 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY'
> 16 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE)
> 8 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL'
> 16 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE)
> 0 VIEW
> 1891480 SORT (GROUP BY)
> 237018 NESTED LOOPS
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
> 237018 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE'
>
> ********************************************************************************
>
> Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Stephen Rospo        Principal Software Architect
Vallent Corporation (formerly Watchmark-Comnitel)
Stephen.Rospo_at_vallent.com           (425)564-8145

This email may contain confidential information. If you received this in
error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.


--
http://www.freelists.org/webpage/oracle-l