RE: Why the monstrous SORT?

Hi,

I think the reason might be the not equal
comparison
              WHERE oec.entity = inv.entity
                AND inv.cr_account != oec.oe_deferred_account
              GROUP BY inv.ord_no, inv.line_no) b, oe_line a, oe_hdr c,

You see 2 full table scans, then the sort so I think you have a cartesian
product
all rows selected, then the sort. I think the 2 rows mentioned are not meant
to
indicate that 2 rows are selected. Am guessing here so be aware of possible
other interpretations.
I think you should look into indexes, relations between those 2 tables 

Regards,

Jeroen

-----Oorspronkelijk bericht-----
Van: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx] 
Verzonden: Thursday, July 07, 2005 01:32
Aan: oracle-l@xxxxxxxxxxxxx
Onderwerp: RE: Why the monstrous SORT?

Sorry, I forgot to include the query - here it is in all its ugliness, along
with the execution statistics from tkprof:

****************************************************************************
****

SELECT c.entity bl_entity, c.sa_ent_ref bl_ent_ref,
       e.bitmap_location_path bl_logo, e.company_name bl_company_name,
       c.salesperson_no bl_salesperson_no, f.salesperson_no || ' - ' ||
f.des1
       bl_salesperson, c.customer_no bl_customer_no,
       c.cust_addr_code bl_customer_loc, g.des1 bl_customer_name,
       a.ord_no bl_order_no, lpad(a.ord_no, 15) bl_order_by_ord_no,
       c.project_no bl_project_no, c.ord_date bl_ord_date,
sum(nvl(a.qty_ord, 0
       ) * nvl(a.mod_unit_price, 0)) bl_order_amount, sum(nvl(a.qty_ord, 0)
*
       nvl(decode(a.activity_type_code, 'LAB', decode(a.mod_unit_cost, 0, 0,
       a.unit_cost), a.line_avg_cost), 0)) bl_cost_amount,
sum(nvl(b.quantity
       * b.price, 0)) bl_invoice_amount, sum(nvl(b.quantity * decode(
       a.activity_type_code, 'LAB', decode(a.mod_unit_cost, 0, 0,
a.unit_cost),
       a.line_avg_cost), 0)) bl_invoice_cost_amount, max(b.invoice_date)
       bl_invoice_date
    FROM (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
              GROUP BY inv.ord_no, inv.line_no) b, oe_line a, oe_hdr c,
         oe_sub_entity d, hm_invoice_sub_entity_control e,
         ar_salesperson_code f, ar_customer_master g, oe_control h
    WHERE a.ord_no = c.ord_no
      AND a.line_no != 0
      AND a.ord_no = b.order_no (+)
      AND a.line_no = b.line_no (+)
      AND NOT upper(a.item_no) LIKE 'AF-%'
      AND NOT upper(a.item_no) LIKE 'IFF%'
      AND (nvl(a.stk_loc_hierarchy_flag, 'N') = 'N'
      OR  a.stk_loc_hierarchy_flag = 'Y'
      AND a.line_no != nvl(a.parent_line_no, 0))
      AND (nvl(a.kit_flag, 'N') = 'N'
      OR  a.kit_flag = 'Y'
      AND a.line_no = nvl(a.parent_line_no, a.line_no))
      AND c.sa_ent_ref = d.ent_ref
      AND c.entity = d.entity
      AND e.entity (+) = d.entity
      AND e.sub_entity (+) = d.sub_entity
      AND f.entity = c.entity
      AND f.salesperson_no = c.salesperson_no
      AND g.customer_no = c.customer_no
      AND g.entity_all IN (c.entity, 'ALL')
      AND h.entity = c.entity
      AND a.sales_account != h.oe_deferred_account
      AND c.customer_no = '1958'
      AND 500000000 = c.entity
    GROUP BY c.entity, c.sa_ent_ref, e.bitmap_location_path, e.company_name,
             c.salesperson_no, f.salesperson_no || ' - ' || f.des1,
c.ord_date,
             a.ord_no, c.project_no, c.customer_no, c.cust_addr_code, g.des1
    HAVING sum(a.qty_ord) != nvl(sum(b.quantity), 0) MINUS SELECT c.entity
bl_entity, c.sa_ent_ref bl_ent_ref,
       e.bitmap_location_path bl_logo, e.company_name bl_company_name,
       c.salesperson_no bl_salesperson_no, f.salesperson_no || ' - ' ||
f.des1
       bl_salesperson, c.customer_no bl_customer_no,
       c.cust_addr_code bl_customer_loc, g.des1 bl_customer_name,
       a.ord_no bl_order_no, lpad(a.ord_no, 15) bl_order_by_ord_no,
       c.project_no bl_project_no, c.ord_date bl_ord_date,
sum(nvl(a.qty_ord, 0
       ) * nvl(a.mod_unit_price, 0)) bl_order_amount, sum(nvl(a.qty_ord, 0)
*
       nvl(decode(a.activity_type_code, 'LAB', decode(a.mod_unit_cost, 0, 0,
       a.unit_cost), a.line_avg_cost), 0)) bl_cost_amount,
sum(nvl(b.quantity
       * b.price, 0)) bl_invoice_amount, sum(nvl(b.quantity * decode(
       a.activity_type_code, 'LAB', decode(a.mod_unit_cost, 0, 0,
a.unit_cost),
       a.line_avg_cost), 0)) bl_invoice_cost_amount, max(b.invoice_date)
       bl_invoice_date
    FROM (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
              GROUP BY inv.ord_no, inv.line_no) b, oe_line a, oe_hdr c,
         oe_sub_entity d, hm_invoice_sub_entity_control e,
         ar_salesperson_code f, ar_customer_master g, oe_control h
    WHERE a.ord_no = c.ord_no
      AND a.line_no != 0
      AND a.ord_no = b.order_no (+)
      AND a.line_no = b.line_no (+)
      AND NOT upper(a.item_no) LIKE 'AF-%'
      AND NOT upper(a.item_no) LIKE 'IFF%'
      AND (nvl(a.stk_loc_hierarchy_flag, 'N') = 'N'
      OR  a.stk_loc_hierarchy_flag = 'Y'
      AND a.line_no != nvl(a.parent_line_no, 0))
      AND (nvl(a.kit_flag, 'N') = 'N'
      OR  a.kit_flag = 'Y'
      AND a.line_no = nvl(a.parent_line_no, a.line_no))
      AND c.sa_ent_ref = d.ent_ref
      AND c.entity = d.entity
      AND e.entity (+) = d.entity
      AND e.sub_entity (+) = d.sub_entity
      AND f.entity = c.entity
      AND f.salesperson_no = c.salesperson_no
      AND g.customer_no = c.customer_no
      AND g.entity_all IN (c.entity, 'ALL')
      AND c.ord_type = 'Q'
      AND h.entity = c.entity
      AND a.sales_account != h.oe_deferred_account
      AND c.customer_no = '1958'
      AND 500000000 = c.entity
    GROUP BY c.entity, c.sa_ent_ref, e.bitmap_location_path, e.company_name,
             c.salesperson_no, f.salesperson_no || ' - ' || f.des1,
c.ord_date,
             a.ord_no, c.project_no, c.customer_no, c.cust_addr_code, g.des1
    HAVING sum(a.qty_ord) != nvl(sum(b.quantity), 0) ORDER BY 1 ASC, 2 ASC,
5 ASC, bl_entity, bl_ent_ref, bl_salesperson_no,
         bl_ord_date, bl_order_by_ord_no

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      8.50       8.55          0          0          0
0
Execute      1      0.01       0.00          0          0          0
0
Fetch        2   4475.38    4565.19   10411769      34204       1723
11
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4   4483.89    4573.74   10411769      34204       1723
11


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Allen, Brandon
Sent: Wednesday, July 06, 2005 4:30 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Why the monstrous SORT?


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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: