|
RE: Why the monstrous SORT?
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Jul 2005 16:58:33 -0700
Hi All, I just wanted to reply as promised with the solution on this one and
give credit where it's due. By removing the upper() function on these two
lines of the query-
AND NOT upper(a.item_no) LIKE 'AF-%'
AND NOT upper(a.item_no) LIKE 'IFF%'
-the CBO makes a much better estimation of the cardinality from oe_line, and
therefore chooses a better execution plan using a hash join instead of nested
loops, and thereby avoids the huge cost of the repeated sort on the inline
view.
I wasn't aware until now that when applying functions in the predicate like
this the CBO (apparently) uses a default selectivity of only 1%.
Thanks to Vladimir Sadilovskiy for this suggestion and thanks again to all the
rest of you that provided helpful suggestions as well!
Regards,
Brandon
-----Original Message-----
From: Allen, Brandon
Sent: Thursday, July 07, 2005 11:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why the monstrous SORT?
Thank you all for the suggestions! I haven't solved the problem yet, but will
take some time today to try your suggestions and will let you know if I find a
solution. To address a few of the questions you all brought up:
. . .
-----Original Message-----
From: Allen, Brandon
Sent: Wednesday, July 06, 2005 4:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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
Other related posts:Why the monstrous SORT? RE: Why the monstrous SORT? Re: Why the monstrous SORT? Re: Why the monstrous SORT? Re: Why the monstrous SORT? RE: Why the monstrous SORT? Re: Why the monstrous SORT? Re: Why the monstrous SORT? RE: Why the monstrous SORT? RE: Why the monstrous SORT? Re: Why the monstrous SORT? RE: Why the monstrous SORT? Re: Why the monstrous SORT? RE: Why the monstrous SORT? Re: Why the monstrous SORT?
|
|
|
[ Home |
Signup |
Help |
Login |
Archives |
Lists
]
All trademarks and copyrights within the FreeLists archives are owned
by their respective owners.
Everything else ©2008 Avenir Technologies, LLC.
|
|
|
