Home » RDBMS Server » Performance Tuning » Query is taking long time (11i)
Query is taking long time [message #609539] Sun, 09 March 2014 21:42 Go to next message
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 messageGo to next message
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 messageGo to next message
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 #609542 is a reply to message #609541] Sun, 09 March 2014 21:51 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
If you want i can attach the tkprof so it be easier
Re: Query is taking long time [message #609543 is a reply to message #609542] Sun, 09 March 2014 22:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query is taking long time [message #609545 is a reply to message #609543] Sun, 09 March 2014 22:15 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
Can anyone let me know how to paste explain plan, i have pasted that at the starting,But i think it's unreadable.How to format explain plan?
Re: Query is taking long time [message #609546 is a reply to message #609545] Sun, 09 March 2014 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
scroll to the top & read every entry AGAIN
Re: Query is taking long time [message #609548 is a reply to message #609546] Sun, 09 March 2014 23:07 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
i have attached the tkprof
Re: Query is taking long time [message #609549 is a reply to message #609548] Sun, 09 March 2014 23:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Query is taking long time [message #609550 is a reply to message #609549] Sun, 09 March 2014 23:30 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
All above requested file attached
Re: Query is taking long time [message #609551 is a reply to message #609550] Sun, 09 March 2014 23:31 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
Just like to know how to avoid Merge join cartesian..As i know it mainly occur if the joins are missing in where clause.I checked it and didn't found that things and neither the index problem is there
Re: Query is taking long time [message #609552 is a reply to message #609551] Sun, 09 March 2014 23:32 Go to previous messageGo to next message
Vivek_Garg
Messages: 10
Registered: September 2012
Junior Member
In tkprof as i can see and also in explain plan Merge join cartesian cost is very high
Re: Query is taking long time [message #609569 is a reply to message #609552] Mon, 10 March 2014 02:03 Go to previous messageGo to next message
John Watson
Messages: 8922
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?
Re: Query is taking long time [message #609604 is a reply to message #609539] Mon, 10 March 2014 04:35 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Release Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Re: Query is taking long time [message #609606 is a reply to message #609604] Mon, 10 March 2014 04:39 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
-------------------------------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------------------------------
Re: Query is taking long time [message #609607 is a reply to message #609606] Mon, 10 March 2014 04:41 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
Attached is the explain plan
Re: Query is taking long time [message #609608 is a reply to message #609607] Mon, 10 March 2014 04:49 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have still failed to provide the execution plan correctly formatted, including the predicates. Do you know how to use dbms_xplan?

You have not read the guide,OraFAQ Forum Guide

You have not made any attempt to use [code] tags,How to use [code] tags and make your code easier to read

You have changed your name from Vivek to Nishant.

You have ignored my whole post, except for giving the DB release. I did try to help, you know.
Previous Topic: How to find throughput by query, session and database wise?
Next Topic: database performance tuning
Goto Forum:
  


Current Time: Thu Mar 28 04:14:41 CDT 2024