Query is taking long time [message #609539] |
Sun, 09 March 2014 21:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c106fe5d66f2d62aba509f770bd5721e?s=64&d=mm&r=g) |
Vivek_Garg
Messages: 10 Registered: September 2012
|
Junior Member |
|
|
Hi,
Below query is taking more than 3+ hrs to run
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 224 | 793K (1)| 02:38:43 |
| 1 | NESTED LOOPS | | 1 | 9 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KCCDI | 1 | 9 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KCCDI2 | 1 | | 0 (0)| 00:00:01 |
| 4 | SORT ORDER BY | | 1 | 224 | 793K (1)| 02:38:43 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 1 | 224 | 793K (1)| 02:38:43 |
| 7 | NESTED LOOPS | | 1 | 208 | 793K (1)| 02:38:43 |
| 8 | NESTED LOOPS | | 19 | 3800 | 793K (1)| 02:38:43 |
|* 9 | HASH JOIN | | 216 | 40392 | 793K (1)| 02:38:38 |
| 10 | VIEW | index$_join$_008 | 102K| 700K| 2445 (2)| 00:00:30 |
|* 11 | HASH JOIN | | | | | |
|* 12 | INDEX FAST FULL SCAN | XXOM_3LP_SYM_ORA_ORDER_HDR_N3 | 102K| 700K| 696 (2)| 00:00:09 |
|* 13 | INDEX FAST FULL SCAN | XXOM_3LP_SYM_ORA_ORDER_HDR_N2 | 102K| 700K| 958 (2)| 00:00:12 |
| 14 | MERGE JOIN CARTESIAN | | 2245K| 385M| 790K (1)| 02:38:08 |
| 15 | NESTED LOOPS | | 513 | 83619 | 778K (1)| 02:35:44 |
|* 16 | HASH JOIN | | 1188 | 180K| 778K (1)| 02:35:43 |
|* 17 | VIEW | index$_join$_012 | 69 | 552 | 15 (7)| 00:00:01 |
|* 18 | HASH JOIN | | | | | |
| 19 | INDEX FAST FULL SCAN | HR_ORGANIZATION_UNITS_FK1 | 69 | 552 | 9 (0)| 00:00:01 |
| 20 | INDEX FAST FULL SCAN | HR_ORGANIZATION_UNITS_PK | 69 | 552 | 9 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 2878 | 415K| 778K (1)| 02:35:43 |
|* 22 | HASH JOIN | | 101K| 6906K| 17596 (1)| 00:03:32 |
| 23 | NESTED LOOPS | | 1784 | 62440 | 133 (4)| 00:00:02 |
|* 24 | TABLE ACCESS FULL | OE_TRANSACTION_TYPES_TL | 1784 | 55304 | 132 (3)| 00:00:02 |
|* 25 | INDEX UNIQUE SCAN | OE_TRANSACTION_TYPES_ALL_U1 | 1 | 4 | 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL | 130K| 4462K| 17461 (1)| 00:03:30 |
|* 27 | INDEX RANGE SCAN | OE_ORDER_HEADERS_N8 | 174K| | 400 (2)| 00:00:05 |
|* 28 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 78 | 11 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | OE_ORDER_LINES_ALL_X17 | 21 | | 3 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 7 | 0 (0)| 00:00:01 |
| 31 | BUFFER SORT | | 4376 | 74392 | 790K (1)| 02:38:08 |
| 32 | TABLE ACCESS FULL | XXONT_SOM_SCHEDULER | 4376 | 74392 | 23 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | XXOM_3LP_SYM_ORA_ORDER_LINES | 1 | 13 | 793K (1)| 02:38:43 |
| 34 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 35 | BITMAP INDEX RANGE SCAN | XXOM_3LP_SYM_ORA_ORDER_N11 | | | | |
|* 36 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 8 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | OE_ORDER_SOURCES_U1 | 1 | | 0 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_SOURCES | 1 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
*BlackSwan added {code} tags
[Updated on: Sun, 09 March 2014 22:02] by Moderator Report message to a moderator
|
|
|
Re: Query is taking long time [message #609540 is a reply to message #609539] |
Sun, 09 March 2014 21:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c106fe5d66f2d62aba509f770bd5721e?s=64&d=mm&r=g) |
Vivek_Garg
Messages: 10 Registered: September 2012
|
Junior Member |
|
|
SELECT /*+ ordered */ --PRN 13909
oh.demand_class_code,
TO_CHAR(order_number, '99999999'),
TO_CHAR(ol.line_number, '9999'),
ol.ordered_item,
mp.organization_code,
--NVL(frs.email, 'linda.bauer@emerson.com') sc_email,
NVL(NVL(oss.primary_email_address,oss.alternate_email_address),'linda.bauer@emerson.com') sc_email,
TO_CHAR(ol.promise_date,'DD-Mon-YYYY') promise_dt,
TO_CHAR(ol.schedule_ship_date,'DD-Mon-YYYY') sched_dt,
(SELECT d.name FROM V$database d) db_name
FROM apps.oe_transaction_types_tl ottt,
apps.oe_transaction_types_all otta,
apps.oe_order_headers_all oh,
apps.oe_order_lines_all ol,
apps.hr_organization_units hou,
apps.xxont_som_scheduler oss,
apps.xxom_3lp_sym_ora_order_hdr xxooh,
apps.xxom_3lp_sym_ora_order_lines xxool,--Added by OCD Project on 7th aug 2012
apps.mtl_parameters mp,
apps.oe_order_sources oos
WHERE oh.header_id = ol.header_id
AND ol.ship_from_org_id = hou.organization_id
AND hou.organization_id = mp.organization_id
AND oh.header_id = xxooh.header_id
AND xxooh.order_admin = oss.subscriber_id
AND xxool.line_id=ol.line_id --Added by OCD Project on 7th aug 2012
AND ol.org_id=xxool.org_id --Added by OCD Project on 7th aug 2012
--AND oss.subscriber_type = 'BUSINESS_ADMIN' Commented for SI
AND oh.order_type_id = otta.transaction_type_id
AND otta.transaction_type_id = ottt.transaction_type_id
AND ottt.language = USERENV('LANG')
-- AND ol.link_to_line_id IS NULL commented by OCD Project on 7th aug 2012
AND ( (xxool.assembly_type IS NOT NULL --Added by OCD Project on 7th aug 2012
AND ol.link_to_line_id = ol.top_model_line_id)
OR ( (xxool.assembly_type IS NULL OR xxool.assembly_type NOT IN('I','R')) AND ol.link_to_line_id IS NULL ) ) --Added by OCD Project on 7th aug 2012
AND ol.ordered_quantity <> 0
AND oh.open_flag = 'Y'
AND ol.open_flag = 'Y'
AND oh.flow_status_code <> 'CLOSED'
AND oos.order_source_id = oh.order_source_id
AND oos.name <> 'Internal'
AND (ottt.name NOT LIKE '%QUOTE%'
AND ottt.name NOT LIKE '%DUMMY%'
AND ottt.name NOT LIKE '%RMA%'
AND ottt.name NOT LIKE '%BILLING ONLY%'
AND ottt.name NOT LIKE '%CREDIT ONLY%')
AND ol.ordered_item NOT LIKE 'MISC%'
AND TRUNC(ol.schedule_ship_date) = TRUNC(ol.promise_date)
AND mp.organization_code = 'RTF'
ORDER BY 1,2,3
I feel that Merge join cartesian is the culprit.
|
|
|
Re: Query is taking long time [message #609541 is a reply to message #609540] |
Sun, 09 March 2014 21:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c106fe5d66f2d62aba509f770bd5721e?s=64&d=mm&r=g) |
Vivek_Garg
Messages: 10 Registered: September 2012
|
Junior Member |
|
|
/* Formatted on 2014/03/10 08:20 (Formatter Plus v4.8.8) */
SELECT /*+ ordered */ --PRN 13909
oh.demand_class_code, TO_CHAR (order_number, '99999999')
, TO_CHAR (ol.line_number, '9999'), ol.ordered_item
, mp.organization_code
,
--NVL(frs.email, 'linda.bauer@emerson.com') sc_email,
NVL (NVL (oss.primary_email_address, oss.alternate_email_address)
, 'linda.bauer@emerson.com'
) sc_email
, TO_CHAR (ol.promise_date, 'DD-Mon-YYYY') promise_dt
, TO_CHAR (ol.schedule_ship_date, 'DD-Mon-YYYY') sched_dt
, (SELECT d.NAME
FROM v$database d) db_name
FROM apps.oe_transaction_types_tl ottt
, apps.oe_transaction_types_all otta
, apps.oe_order_headers_all oh
, apps.oe_order_lines_all ol
, apps.hr_organization_units hou
, apps.xxont_som_scheduler oss
, apps.xxom_3lp_sym_ora_order_hdr xxooh
, apps.xxom_3lp_sym_ora_order_lines xxool
, --Added by OCD Project on 7th aug 2012
apps.mtl_parameters mp
, apps.oe_order_sources oos
WHERE oh.header_id = ol.header_id
AND ol.ship_from_org_id = hou.organization_id
AND hou.organization_id = mp.organization_id
AND oh.header_id = xxooh.header_id
AND xxooh.order_admin = oss.subscriber_id
AND xxool.line_id = ol.line_id --Added by OCD Project on 7th aug 2012
AND ol.org_id = xxool.org_id --Added by OCD Project on 7th aug 2012
--AND oss.subscriber_type = 'BUSINESS_ADMIN' Commented for SI
AND oh.order_type_id = otta.transaction_type_id
AND otta.transaction_type_id = ottt.transaction_type_id
AND ottt.LANGUAGE = USERENV ('LANG')
-- AND ol.link_to_line_id IS NULL commented by OCD Project on 7th aug 2012
AND ( ( xxool.assembly_type IS NOT NULL
--Added by OCD Project on 7th aug 2012
AND ol.link_to_line_id = ol.top_model_line_id
)
OR ( ( xxool.assembly_type IS NULL
OR xxool.assembly_type NOT IN ('I', 'R')
)
AND ol.link_to_line_id IS NULL
)
) --Added by OCD Project on 7th aug 2012
AND ol.ordered_quantity <> 0
AND oh.open_flag = 'Y'
AND ol.open_flag = 'Y'
AND oh.flow_status_code <> 'CLOSED'
AND oos.order_source_id = oh.order_source_id
AND oos.NAME <> 'Internal'
AND ( ottt.NAME NOT LIKE '%QUOTE%'
AND ottt.NAME NOT LIKE '%DUMMY%'
AND ottt.NAME NOT LIKE '%RMA%'
AND ottt.NAME NOT LIKE '%BILLING ONLY%'
AND ottt.NAME NOT LIKE '%CREDIT ONLY%'
)
AND ol.ordered_item NOT LIKE 'MISC%'
AND TRUNC (ol.schedule_ship_date) = TRUNC (ol.promise_date)
AND mp.organization_code = 'RTF'
ORDER BY 1, 2, 3
*BlackSwan added code tags. Please do so yourself in the future.
see URL below
http://www.orafaq.com/forum/t/174502/
[Updated on: Sun, 09 March 2014 22:00] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: Query is taking long time [message #609569 is a reply to message #609552] |
Mon, 10 March 2014 02:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Before you can tune anything, you need information. And your information is wrong. Compare the estimated cardinalities in the plan with the actual cardinalities in the trace, there are huge divergencies at many points. It is impossible to check exactly what the errors are because you have not provided the execution plan in the form that is needed: with the predicates. You need to generate a plan and print it with dbms_xplan.display, please
But I know what the first answer will be: gather better statistics. As this is an EBS database, you are probably gathering them with fnd_stats called by concurrent manager. Well, that is up to you. But I don't think it does a very good job. What release of EBS? What release of the database?
|
|
|
|
|
|
|