Home » RDBMS Server » Performance Tuning » How to remove left join and ON JOIN from query and use better things in place of that (11i)
Re: Error while running script [message #620099 is a reply to message #620097] Mon, 28 July 2014 13:57 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
no just to make it simple rather than ON join....right join ...on JOIN,thats creating lot of confusion .so to make it little bit(not more) easier to goahead with the fix
Re: Error while running script [message #620100 is a reply to message #620099] Mon, 28 July 2014 14:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ANSI syntax is easier understand, to easier to write, and less prone to error. To re-write your code to the ancient syntax is lunacy. You are ignoring the simple tuning advice I have given.

Clearly, I cannot help you. I'll stop attempting to assist. Goodbye.

Re: Error while running script [message #620128 is a reply to message #620100] Tue, 29 July 2014 05:49 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
This construct that you use several times is a bug:
CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END

It will always return a null. That will mess up the rest of the query considerably.

You also have this
Code: [Select all] [Show/ hide]
WHEN l.item_type_code = 'NULL'
and this
Code: [Select all] [Show/ hide]
CASE
                  WHEN TRUNC (l.promise_date) =
                           TRUNC (l.creation_date)
                         + NULL
                     THEN 'NULL'


As per you john these all are bug ,but that is the business requirement,if we can remove it then might be it will impact the functionality
Re: Error while running script [message #620129 is a reply to message #620128] Tue, 29 July 2014 05:54 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It's the business requirement to be wrong?

SQL> select * from dual where null=null;

no rows selected

SQL> select * from dual where null is null;

D
-
X
Re: Error while running script [message #620131 is a reply to message #620129] Tue, 29 July 2014 06:28 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
How can we handle (CASE STATEMENT) that in the query or simply replace it?
Re: Error while running script [message #620146 is a reply to message #620131] Tue, 29 July 2014 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I pointed out previously the actual query doesn't contain many (or possibly any) nulls. John's advice about nulls was pointless because you posted a query where you replaced binds with nulls without telling us that you had done that.
The advice John gave that you should still be following is about checking if all the outer joins need to be outer joins or if they can safely be converted to inner joins.
Re: Error while running script [message #620149 is a reply to message #620146] Tue, 29 July 2014 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I see your latest version of the query has put the nulls back in. STOP DOING THAT.
It changes the meaning of the query.
You should leave the binds in, if you really feel the need to replace them do so with sensible values.
Re: Error while running script [message #620166 is a reply to message #620149] Tue, 29 July 2014 11:15 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
SELECT *
  FROM (WITH a AS
             ((SELECT c.LOCATION, c.site_use_code, p.party_site_number
                FROM apps.hz_cust_site_uses_all c,
                     apps.hz_cust_acct_sites_all s,
                     apps.hz_party_sites p
               WHERE s.cust_acct_site_id = c.cust_acct_site_id
                 AND s.org_id = c.org_id
                 AND p.party_site_id = s.party_site_id
                 AND c.org_id = 4680
                 AND c.status =  :"SYS_B_01"),
             b AS
             (SELECT   h.org_id, h.header_id, h.line_id,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2011,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h0003,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2021,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h0060,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2035,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2421,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2422,
                       MAX (CASE
                               WHEN s.hold_id = NULL
                                  THEN 'NULL'
                            END) AS h2423
                  FROM apps.oe_order_holds_all h, apps.oe_hold_sources_all s
                 WHERE s.org_id(+) = h.org_id
                   AND s.hold_source_id(+) = h.hold_source_id
                   AND s.hold_id IN(:"SYS_B_18",
                             :"SYS_B_19",
                             :"SYS_B_20",
                             :"SYS_B_21",
                             :"SYS_B_22",
                             :"SYS_B_23",
                             :"SYS_B_24",
                             :"SYS_B_25"
                            )
                 WHERE h.org_id = :"SYS_B_26" AND h.released_flag =
                                                                   :"SYS_B_27"
              GROUP BY h.org_id, h.header_id, h.line_id),
             v AS
             (SELECT   l.org_id, l.header_id, l.line_number,
                       MAX (n.last_update_date) AS ddu,
                       COUNT (DISTINCT (c.meaning)) AS ddc,
                       MAX (c.meaning) AS dds,
                       COUNT (DISTINCT (h.meaning)) AS dlc,
                       MAX (h.meaning) AS dls
                  FROM apps.oe_order_lines_all l JOIN apps.wsh_delivery_details d
                       ON d.org_id = l.org_id
                     AND d.source_header_id = l.header_id
                     AND d.source_line_id = l.line_id
                       JOIN apps.wsh_delivery_assignments m
                       ON m.delivery_detail_id = d.delivery_detail_id
                       JOIN apps.wsh_new_deliveries n
                       ON n.organization_id = l.ship_from_org_id
                     AND n.delivery_id = m.delivery_id
                       JOIN apps.fnd_lookup_values c
                       ON c.lookup_code = d.released_status
                     AND c.lookup_type = :"SYS_B_28"
                     AND c.LANGUAGE = :"SYS_B_29"
                       JOIN apps.fnd_lookup_values h
                       ON h.lookup_code = n.status_code
                     AND h.lookup_type = :"SYS_B_30"
                     AND h.LANGUAGE = :"SYS_B_31"
                 WHERE l.org_id = :"SYS_B_32"
                   AND l.item_type_code IN (:"SYS_B_33", :"SYS_B_34")
              GROUP BY l.org_id, l.header_id, l.line_number
              ORDER BY l.org_id, l.header_id, l.line_number)
        SELECT h.order_number AS o_order_nr, i.NAME AS o_order_type,
               l.line_number AS o_ln_nr,
               l.orig_sys_document_ref AS o_orig_sys_document_ref,
               l.orig_sys_line_ref AS o_ln_orig_sys_line_ref,
               p.orig_sys_line_ref AS o_sato_orig_sys_line_ref,
               NVL (p.orig_sys_line_ref,
                    l.orig_sys_line_ref
                   ) AS o_ln_full_line_ref,
               l.ordered_item AS o_ln_ordered_item,
               p.model_string AS o_sato_model,
               NVL (NVL (t.model_string, p.model_string),
                    l.ordered_item
                   ) AS o_ln_full_model,
               p.pato_parent_item AS o_sato_parent_item,
               p.component_item AS o_sato_component_item,
               h.flow_status_code AS o_hd_flow_status,
               l.flow_status_code AS o_ln_flow_status,
               DECODE (v.ddc,
                       NULL, :"SYS_B_35",
                       :"SYS_B_36", v.dds,
                       :"SYS_B_37"
                      ) AS o_ln_pick_status,
               DECODE (v.dlc,
                       NULL, :"SYS_B_38",
                       :"SYS_B_39", v.dls,
                       :"SYS_B_40"
                      ) AS o_ln_delivery_status,
               b.h2011 AS o_ln_configuration_hold,
               b.h0003 AS o_ln_conf_validation_hold,
               b.h2021 AS o_ln_calibration_hold,
               b.h0060 AS o_ln_conf_exception_hold,
               b.h2035 AS o_ln_auto_conf_hold,
               b.h2421 AS o_ln_engineering_hold,
               b.h2422 AS o_ln_scheduling_hold,
               b.h2423 AS o_ln_invoicing_hold,
               CASE
                  WHEN TRUNC (l.promise_date) =
                           TRUNC (l.creation_date)
                         + :"SYS_B_41"
                     THEN :"SYS_B_42"
                  ELSE :"SYS_B_43"
               END AS o_ln_hub_promised,
               CASE
                  WHEN TRUNC (l.schedule_ship_date) =
                           TRUNC (l.creation_date)
                         + :"SYS_B_44"
                     THEN :"SYS_B_45"
                  ELSE :"SYS_B_46"
               END AS o_ln_hub_scheduled,
               h.creation_date AS o_hd_creation_dt,
               p.creation_date AS o_sato_creation_dt,
               l.creation_date AS o_ln_creation_dt,
               v.ddu AS o_ln_delivery_update_dt,
               s.subscriber_name AS o_hd_subscriber_name,
               r.q_tot AS o_ln_quantity, r.q_split AS o_ln_split_count,
               l.order_quantity_uom AS o_ln_uom,
               h.transactional_curr_code AS o_hd_trans_currency,
               ROUND
                  (CASE
                      WHEN l.top_model_line_id IS NULL
                         THEN NVL (l.unit_selling_price, :"SYS_B_47")
                              * r.q_tot
                      WHEN p.component_item IS NULL
                         THEN (SELECT SUM (  NVL (q.unit_selling_price,
                                                  :"SYS_B_48"
                                                 )
                                           * r.q_tot
                                          )
                                 FROM apps.oe_order_lines_all q
                                WHERE q.org_id = l.org_id
                                  AND q.header_id = l.header_id
                                  AND q.top_model_line_id = l.line_id)
                      ELSE (SELECT   NVL (q.unit_selling_price, :"SYS_B_49")
                                   * r.q_tot
                              FROM apps.oe_order_lines_all q
                             WHERE q.org_id = l.org_id
                               AND q.header_id = l.header_id
                               AND q.top_model_line_id = l.top_model_line_id
                               AND q.ordered_item = p.component_item
                               AND ROWNUM = :"SYS_B_50")
                   END,
                   :"SYS_B_51"
                  ) AS o_ln_extended_price,
               ROUND
                  (CASE
                      WHEN l.item_type_code = :"SYS_B_52"
                         THEN NVL (c.item_cost, :"SYS_B_53") * r.q_tot
                      WHEN l.item_type_code = :"SYS_B_54"
                         THEN (SELECT NVL (v.item_cost, :"SYS_B_55") * r.q_tot
                                 FROM apps.oe_order_lines_all q JOIN apps.cst_item_cost_type_v v
                                      ON v.organization_id =
                                                            q.ship_from_org_id
                                    AND v.inventory_item_id =
                                                           q.inventory_item_id
                                    AND v.cost_type = :"SYS_B_56"
                                WHERE q.org_id = l.org_id
                                  AND q.header_id = l.header_id
                                  AND q.top_model_line_id = l.line_id
                                  AND q.item_type_code = :"SYS_B_57")
                   END,
                   :"SYS_B_58"
                  ) AS o_ln_extended_cost,
               h.cust_po_number AS o_hd_customer_po,
               h.fob_point_code AS o_hd_shipping_terms,
               u.rep_order_nbr AS o_hd_representative_order,
               u.project_number AS o_hd_project_nr, u.sic_code AS o_hd_sic,
               u.ultimate_dest AS o_hd_ultimate_dest,
               h.end_customer_site_use_id AS o_hd_end_user_nr,
               (SELECT a.party_site_number
                  FROM a
                 WHERE a.LOCATION =
                              h.end_customer_site_use_id)
                                                         AS o_hd_end_user_psn,
               h.ship_to_org_id AS o_hd_ship_to_org_id,
               (SELECT a.party_site_number
                  FROM a
                 WHERE a.LOCATION = h.ship_to_org_id
                   AND a.site_use_code = :"SYS_B_59") AS o_hd_ship_to_psn,
               h.invoice_to_org_id AS o_hd_bill_to_org_id,
               (SELECT a.party_site_number
                  FROM a
                 WHERE a.LOCATION = h.invoice_to_org_id
                   AND a.site_use_code = :"SYS_B_60") AS o_hd_bill_to_psn,
               u.named_place AS o_hd_named_place,
               t.shiptoaddr AS o_ln_ship_to_nr,
               (SELECT a.party_site_number
                  FROM a
                 WHERE a.LOCATION = t.shiptoaddr
                   AND a.site_use_code = :"SYS_B_61") AS o_ln_ship_to_psn,
               u.po_originated AS o_hd_cust_po_dt,
               u.poreceiptdate AS o_hd_branch_po_received_dt,
               u.requested_delivery_date AS o_hd_cust_req_delivery_dt,
               t.requested_delivery_date AS o_ln_cust_req_delivery_dt,
               l.request_date AS o_ln_branch_req_ship_dt,
               GREATEST (l.promise_date,
                         TO_DATE (:"SYS_B_62", :"SYS_B_63")
                        ) AS o_ln_plant_prom_ship_dt,
               l.schedule_ship_date AS o_ln_plant_sched_ship_dt,
               t.creation_date AS o_ln_3lp_creation_dt,
               t.model_string AS o_ln_3lp_model,
               t.mse_config_status AS o_ln_3lp_mse_config_status
          FROM apps.oe_order_lines_all l
               ,
               (SELECT   org_id, header_id, line_number,
                         SUM (ordered_quantity) AS q_tot, COUNT
                                                               (*) AS q_split
                    FROM apps.oe_order_lines_all
                   WHERE org_id = :"SYS_B_64"
                     AND item_type_code IN (:"SYS_B_65",:"SYS_B_66")
                GROUP BY org_id, header_id, line_number) r,
               apps.oe_order_headers_all h,                    
               (SELECT DISTINCT *
                           FROM xxom.xxom_xml1_pato_stg) p,
                xxom.xxom_3lp_sym_ora_order_lines t,
               xxom.xxom_3lp_sym_ora_order_hdr u,
               apps.xxont_som_scheduler s,
               apps.oe_transaction_types_tl i,
               apps.cst_item_cost_type_v c,
               b,
               v
                WHERE r.org_id = l.org_id
           AND r.header_id = l.header_id
           AND r.line_number = l.line_number
           AND h.org_id = l.org_id
           AND h.header_id = l.header_id
           AND p.config_hdr_id(+) = l.config_header_id
           AND t.org_id(+) = l.org_id
           AND t.header_id(+) = l.header_id
           AND t.line_id(+) = l.line_id
           AND u.org_id(+) = l.org_id
           AND u.header_id(+) = l.header_id
           AND s.subscriber_id(+) = u.order_admin
           AND i.transaction_type_id(+) = h.order_type_id
             AND i.LANGUAGE = :"SYS_B_67"
               AND c.organization_id(+) = l.ship_from_org_id
           AND c.inventory_item_id(+) = l.inventory_item_id
             AND c.cost_type = :"SYS_B_68"
               AND b.org_id(+) = l.org_id
           AND b.header_id(+) = l.header_id
           AND b.line_id(+) = l.line_id
           AND v.org_id(+) = l.org_id
           AND v.header_id(+) = l.header_id
           AND v.line_number(+) = l.line_number
         WHERE l.org_id = :"SYS_B_69"
           AND l.item_type_code IN (:"SYS_B_70", :"SYS_B_71")
           AND SUBSTR (l.orig_sys_line_ref, :"SYS_B_72", :"SYS_B_73") !=
                                                                   :"SYS_B_74")

Re: Error while running script [message #620329 is a reply to message #620166] Thu, 31 July 2014 05:25 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2     11.24     134.50      79991     133693        379           0
Fetch        1    218.91    5014.55     667432    2213252         17         500
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    230.15    5149.06     747423    2346945        396         500

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 173  (APPS)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       145        145        145  SORT AGGREGATE (cr=2362 pr=452 pw=0 time=4683738 us)
      5347       5347       5347   TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=2362 pr=452 pw=0 time=4114475 us cost=15 size=20 card=1)
      5347       5347       5347    INDEX RANGE SCAN OE_ORDER_LINES_ALL_X14 (cr=448 pr=0 pw=0 time=22730 us cost=4 size=0 card=27)(object id 33170971)
       355        355        355  COUNT STOPKEY (cr=2413 pr=181 pw=0 time=2726232 us)
       355        355        355   TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=2413 pr=181 pw=0 time=2721624 us cost=15 size=33 card=1)
      2405       2405       2405    INDEX RANGE SCAN OE_ORDER_LINES_ALL_X14 (cr=1420 pr=0 pw=0 time=25443 us cost=4 size=0 card=27)(object id 33170971)
         0          0          0  FILTER  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us cost=20 size=168 card=1)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=17 size=151 card=1)
         0          0          0     NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us cost=15 size=137 card=1)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=12 size=96 card=1)
         0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=10 size=81 card=1)
         0          0          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=10 size=77 card=1)
         0          0          0         NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=9 size=59 card=1)
         0          0          0          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=5 size=42 card=1)
         0          0          0           TABLE ACCESS BY INDEX ROWID MTL_DEFAULT_CATEGORY_SETS (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)
         0          0          0            INDEX UNIQUE SCAN MTL_DEFAULT_CATEGORY_SETS_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 37410)
         0          0          0           TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=34 card=1)
         0          0          0            INDEX RANGE SCAN OE_ORDER_LINES_ALL_X14 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 33170971)
         0          0          0          TABLE ACCESS BY INDEX ROWID CST_ITEM_COSTS (cr=0 pr=0 pw=0 time=0 us cost=4 size=17 card=1)
         0          0          0           INDEX RANGE SCAN CST_ITEM_COSTS_U1 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 3033257)
         0          0          0         TABLE ACCESS BY INDEX ROWID CST_COST_TYPES (cr=0 pr=0 pw=0 time=0 us cost=1 size=18 card=1)
         0          0          0          INDEX UNIQUE SCAN CST_COST_TYPES_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 30232)
         0          0          0        INDEX UNIQUE SCAN CST_COST_TYPES_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)(object id 30232)
         0          0          0       TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
         0          0          0        INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 38017)
         0          0          0      INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=0 pr=0 pw=0 time=0 us cost=3 size=41 card=1)(object id 32878)
         0          0          0     INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_TL_U1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=28 card=2)(object id 38056)
         0          0          0    INDEX RANGE SCAN MTL_ITEM_CATEGORIES_U1 (cr=0 pr=0 pw=0 time=0 us cost=3 size=17 card=1)(object id 99625)
       291        291        291  VIEW  (cr=109818 pr=369 pw=0 time=14667382 us cost=142 size=2445885 card=62715)
  26361464   26361464   26361464   TABLE ACCESS FULL SYS_TEMP_0FD9D6612_360ACFB4 (cr=109818 pr=369 pw=0 time=7181368 us cost=142 size=1630590 card=62715)
       251        251        251  VIEW  (cr=93492 pr=0 pw=0 time=13060083 us cost=142 size=3512040 card=62715)
  22442868   22442868   22442868   TABLE ACCESS FULL SYS_TEMP_0FD9D6612_360ACFB4 (cr=93492 pr=0 pw=0 time=5683961 us cost=142 size=1630590 card=62715)
        30         30         30  VIEW  (cr=11130 pr=0 pw=0 time=1553709 us cost=142 size=3512040 card=62715)
   2671770    2671770    2671770   TABLE ACCESS FULL SYS_TEMP_0FD9D6612_360ACFB4 (cr=11130 pr=0 pw=0 time=677706 us cost=142 size=1630590 card=62715)
       242        242        242  VIEW  (cr=90153 pr=0 pw=0 time=13380444 us cost=142 size=3512040 card=62715)
  21641337   21641337   21641337   TABLE ACCESS FULL SYS_TEMP_0FD9D6612_360ACFB4 (cr=90153 pr=0 pw=0 time=5443993 us cost=142 size=1630590 card=62715)
       500        500        500  VIEW  (cr=2346945 pr=747423 pw=14035 time=905302557 us cost=927889 size=60401 card=17)
       500        500        500   TEMP TABLE TRANSFORMATION  (cr=2037577 pr=746421 pw=14035 time=836649134 us)
         0          0          0    LOAD AS SELECT  (cr=133693 pr=79991 pw=369 time=134501334 us)
     89059      89059      89059     HASH JOIN  (cr=133693 pr=79991 pw=0 time=181896483 us cost=62602 size=3762900 card=62715)
     89059      89059      89059      TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL (cr=36404 pr=22878 pw=0 time=33875983 us cost=15613 size=1881480 card=62716)
     89059      89059      89059       INDEX RANGE SCAN HZ_CUST_SITE_USES_ALL_X2 (cr=181 pr=118 pw=0 time=4326909 us cost=119 size=0 card=62716)(object id 8172308)
     93091      93091      93091      HASH JOIN  (cr=97289 pr=57113 pw=0 time=108070346 us cost=46714 size=2193870 card=73129)
     93091      93091      93091       TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL (cr=71379 pr=31206 pw=0 time=77563144 us cost=39553 size=1170064 card=73129)
     93091      93091      93091        INDEX RANGE SCAN HZ_CUST_ACCT_SITES_X1 (cr=192 pr=102 pw=0 time=3283658 us cost=135 size=0 card=73129)(object id 621255)
   1125285    1125285    1125285       TABLE ACCESS FULL HZ_PARTY_SITES (cr=25910 pr=25907 pw=0 time=16634830 us cost=7142 size=15738142 card=1124153)
       500        500        500    NESTED LOOPS  (cr=1903884 pr=666430 pw=13666 time=702146935 us cost=865287 size=19006 card=17)
       500        500        500     NESTED LOOPS  (cr=1902382 pr=666370 pw=13666 time=692895496 us cost=865265 size=12144 card=11)
       500        500        500      FILTER  (cr=1902378 pr=666370 pw=13666 time=692842144 us)
      8171       8171       8171       NESTED LOOPS OUTER (cr=1902378 pr=666370 pw=13666 time=666265639 us cost=865265 size=12100 card=11)
       500        500        500        NESTED LOOPS OUTER (cr=1900829 pr=666342 pw=13666 time=687265903 us cost=864995 size=97470 card=90)
       500        500        500         HASH JOIN  (cr=1898794 pr=665961 pw=13666 time=678492316 us cost=864715 size=93960 card=90)
         1          1          1          TABLE ACCESS BY INDEX ROWID CST_COST_TYPES (cr=2 pr=0 pw=0 time=68251 us cost=2 size=18 card=1)
         1          1          1           INDEX RANGE SCAN CST_COST_TYPES_U2 (cr=1 pr=0 pw=0 time=34458 us cost=1 size=0 card=1)(object id 30237)
      1462       1462       1462          HASH JOIN RIGHT OUTER (cr=1898792 pr=665961 pw=13666 time=659489093 us cost=864713 size=4911462 card=4787)
         2          2          2           INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=4 pr=0 pw=0 time=21064 us cost=4 size=41 card=1)(object id 32878)
      1462       1462       1462           NESTED LOOPS  (cr=1898788 pr=665961 pw=13666 time=659447389 us)
      1462       1462       1462            NESTED LOOPS  (cr=1898323 pr=665917 pw=13666 time=657625717 us cost=864708 size=4715195 card=4787)
      1462       1462       1462             NESTED LOOPS  (cr=1895411 pr=665898 pw=13666 time=657521497 us cost=845391 size=9353710 card=9643)
       501        501        501              HASH JOIN  (cr=1892445 pr=665786 pw=13666 time=655581863 us cost=806773 size=9189779 card=9643)
     71026      71026      71026               VIEW  (cr=617944 pr=212572 pw=0 time=2516630836 us cost=237650 size=4346300 card=88700)
     71026      71026      71026                HASH GROUP BY (cr=617944 pr=212572 pw=0 time=2516606386 us cost=237650 size=2128800 card=88700)
     78435      78435      78435                 TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=617944 pr=212572 pw=0 time=2236039573 us cost=237015 size=2128800 card=88700)
   2431145    2431145    2431145                  INDEX RANGE SCAN OE_ORDER_LINES_ALL_X7 (cr=5641 pr=2126 pw=0 time=62412458 us cost=2425 size=0 card=862872)(object id 33170976)
      4186       4186       4186               HASH JOIN OUTER (cr=1274501 pr=453214 pw=13261 time=1044925197 us cost=568446 size=8717272 card=9643)
     70896      70896      70896                HASH JOIN RIGHT OUTER (cr=1273711 pr=452396 pw=8488 time=1167745102 us cost=566213 size=3812492 card=6857)
      5390       5390       5390                 TABLE ACCESS FULL XXONT_SOM_SCHEDULER (cr=106 pr=30 pw=0 time=38025 us cost=30 size=118580 card=5390)
     70896      70896      70896                 HASH JOIN RIGHT OUTER (cr=1273605 pr=452366 pw=8488 time=1167581203 us cost=566182 size=3661638 card=6857)
     17674      17674      17674                  TABLE ACCESS FULL XXOM_3LP_SYM_ORA_ORDER_HDR (cr=27493 pr=27488 pw=0 time=5314387 us cost=7828 size=355036 card=8069)
     70896      70896      70896                  HASH JOIN RIGHT OUTER (cr=1246112 pr=424878 pw=8488 time=1167351053 us cost=558354 size=3359930 card=6857)
      9108       9108       9108                   VIEW  (cr=315895 pr=50367 pw=0 time=104526618 us cost=15149 size=64380 card=870)
      9108       9108       9108                    HASH GROUP BY (cr=315895 pr=50367 pw=0 time=104521291 us cost=15149 size=33930 card=870)
      9540       9540       9540                     NESTED LOOPS  (cr=315895 pr=50367 pw=0 time=184109551 us)
    170119     170119     170119                      NESTED LOOPS  (cr=261126 pr=46887 pw=0 time=23875285 us cost=15148 size=33930 card=870)
    170962     170962     170962                       TABLE ACCESS FULL OE_HOLD_SOURCES_ALL (cr=46186 pr=46177 pw=0 time=9881780 us cost=13028 size=12012 card=858)
    170119     170119     170119                       INDEX RANGE SCAN OE_ORDER_HOLDS_ALL_N3 (cr=214940 pr=710 pw=0 time=20175178 us cost=2 size=0 card=5)(object id 41992)
      9540       9540       9540                      TABLE ACCESS BY INDEX ROWID OE_ORDER_HOLDS_ALL (cr=54769 pr=3480 pw=0 time=74044314 us cost=4 size=25 card=1)
     70896      70896      70896                   HASH JOIN RIGHT OUTER (cr=930217 pr=374511 pw=8368 time=1167163593 us cost=543205 size=2852512 card=6857)
     58950      58950      58950                    VIEW  (cr=813332 pr=366990 pw=5428 time=942917748 us cost=320422 size=59356 card=418)
     58950      58950      58950                     SORT GROUP BY (cr=813332 pr=366990 pw=5428 time=942890633 us cost=320422 size=82764 card=418)
     69947      69947      69947                      HASH JOIN  (cr=813332 pr=366002 pw=4440 time=941898523 us cost=320421 size=82764 card=418)
         8          8          8                       TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=5 pr=2 pw=0 time=63139 us cost=6 size=810 card=15)
         8          8          8                        INDEX RANGE SCAN XXAR_FND_LOOKUP_VALUES_N1 (cr=3 pr=2 pw=0 time=45746 us cost=3 size=0 card=15)(object id 1405422)
     69947      69947      69947                       HASH JOIN  (cr=813327 pr=366000 pw=4440 time=941828073 us cost=320415 size=62640 card=435)
        11         11         11                        TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES (cr=8 pr=0 pw=0 time=205870 us cost=6 size=810 card=15)
        11         11         11                         INDEX RANGE SCAN XXAR_FND_LOOKUP_VALUES_N1 (cr=3 pr=0 pw=0 time=21655 us cost=3 size=0 card=15)(object id 1405422)
     69947      69947      69947                        HASH JOIN  (cr=813319 pr=366000 pw=4440 time=941746830 us cost=320408 size=40770 card=453)
     75412      75412      75412                         TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=619159 pr=202570 pw=0 time=2296470122 us cost=237015 size=2655680 card=82990)
   2431145    2431145    2431145                          INDEX RANGE SCAN OE_ORDER_LINES_ALL_X7 (cr=6856 pr=2087 pw=0 time=46725394 us cost=2425 size=0 card=862872)(object id 33170976)
     69947      69947      69947                         HASH JOIN  (cr=194160 pr=161990 pw=3090 time=208780979 us cost=82907 size=5388896 card=92912)
     69947      69947      69947                          HASH JOIN  (cr=82495 pr=48543 pw=1260 time=171470009 us cost=43567 size=3437744 card=92912)
     79318      79318      79318                           TABLE ACCESS BY INDEX ROWID WSH_DELIVERY_DETAILS (cr=46159 pr=11016 pw=0 time=167477036 us cost=28671 size=2322700 card=92908)
     79318      79318      79318                            INDEX SKIP SCAN WSH_DELIVERY_DETAILS_N99 (cr=330 pr=156 pw=0 time=4805828 us cost=9696 size=0 card=92908)(object id 20929428)
   3858432    3858432    3858432                           TABLE ACCESS FULL WSH_DELIVERY_ASSIGNMENTS (cr=36336 pr=36327 pw=0 time=10359656 us cost=10229 size=46261956 card=3855163)
   4253804    4253804    4253804                          TABLE ACCESS FULL WSH_NEW_DELIVERIES (cr=111665 pr=111647 pw=0 time=32321647 us cost=32343 size=89345193 card=4254533)
     70896      70896      70896                    NESTED LOOPS  (cr=116885 pr=4581 pw=15 time=28394299 us)
     78435      78435      78435                     NESTED LOOPS  (cr=72532 pr=4577 pw=15 time=40628394 us cost=222782 size=1878818 card=6857)
     17680      17680      17680                      HASH JOIN  (cr=13008 pr=214 pw=15 time=791267 us cost=11816 size=5830644 card=51146)
      2398       2398       2398                       NESTED LOOPS  (cr=436 pr=200 pw=0 time=70506 us cost=132 size=93522 card=2398)
         1          1          1                        TABLE ACCESS BY INDEX ROWID MTL_DEFAULT_CATEGORY_SETS (cr=2 pr=2 pw=0 time=29654 us cost=1 size=8 card=1)
         1          1          1                         INDEX UNIQUE SCAN MTL_DEFAULT_CATEGORY_SETS_U1 (cr=1 pr=1 pw=0 time=12780 us cost=0 size=0 card=1)(object id 37410)
      2398       2398       2398                        TABLE ACCESS FULL OE_TRANSACTION_TYPES_TL (cr=434 pr=198 pw=0 time=39719 us cost=131 size=74338 card=2398)
     17680      17680      17680                       TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (cr=12572 pr=14 pw=0 time=327663 us cost=11683 size=2481000 card=33080)
     17680      17680      17680                        INDEX RANGE SCAN XXOM_OE_ORDER_HEADERS_N5 (cr=61 pr=3 pw=0 time=101419 us cost=95 size=0 card=33080)(object id 1810444)
     78435      78435      78435                      INDEX RANGE SCAN OE_ORDER_LINES_ALL_X14 (cr=59524 pr=4363 pw=0 time=97402118 us cost=3 size=0 card=5)(object id 33170971)
     70896      70896      70896                     TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=44353 pr=4 pw=0 time=927441 us cost=5 size=160 card=1)
     35297      35297      35297                VIEW  (cr=790 pr=713 pw=496 time=572874 us cost=1381 size=13572000 card=39000)
     35297      35297      35297                 HASH UNIQUE (cr=790 pr=713 pw=496 time=558669 us cost=1381 size=5109000 card=39000)
     38807      38807      38807                  TABLE ACCESS FULL XXOM_XML1_PATO_STG (cr=790 pr=217 pw=0 time=20981 us cost=230 size=5109000 card=39000)
      1462       1462       1462              TABLE ACCESS BY INDEX ROWID CST_ITEM_COSTS (cr=2966 pr=112 pw=0 time=2968620 us cost=4 size=17 card=1)
      1462       1462       1462               INDEX RANGE SCAN CST_ITEM_COSTS_U1 (cr=1505 pr=42 pw=0 time=673725 us cost=3 size=0 card=1)(object id 3033257)
      1462       1462       1462             INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=2912 pr=19 pw=0 time=437126 us cost=1 size=0 card=1)(object id 38017)
      1462       1462       1462            TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=465 pr=44 pw=0 time=1107362 us cost=2 size=15 card=1)
       500        500        500         TABLE ACCESS BY INDEX ROWID XXOM_3LP_SYM_ORA_ORDER_LINES (cr=2035 pr=381 pw=0 time=8503745 us cost=4 size=39 card=1)
       500        500        500          INDEX RANGE SCAN XXOM_3LP_SYM_ORA_ORDER_LIN_N4 (cr=1503 pr=142 pw=0 time=3461436 us cost=3 size=0 card=1)(object id 13127890)
      8171       8171       8171        INDEX RANGE SCAN MTL_ITEM_CATEGORIES_U1 (cr=1549 pr=28 pw=0 time=517439 us cost=3 size=17 card=1)(object id 99625)
       500        500        500      INDEX UNIQUE SCAN CST_COST_TYPES_U1 (cr=4 pr=0 pw=0 time=47709 us cost=0 size=4 card=1)(object id 30232)
       500        500        500     INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_TL_U1 (cr=1502 pr=60 pw=0 time=1175489 us cost=2 size=28 card=2)(object id 38056)

Re: Error while running script [message #620331 is a reply to message #620329] Thu, 31 July 2014 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you followed John's advise about checking to see if you can get rid of some (or all) of the outer-joins?
Re: Error while running script [message #620333 is a reply to message #620331] Thu, 31 July 2014 05:37 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
how to get rid of outer join that i am trying to find out and do you have any alternate for that?
Re: Error while running script [message #620338 is a reply to message #620333] Thu, 31 July 2014 05:50 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I had not intended to reply to this any more. But...

Nishant, can you confirm that you do know what an outer join and an inner join are?

Consider these queries:
select ename,dname  from emp join dept using (deptno);
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,dname from emp right join dept using (deptno);
select ename,dname from emp,dept where emp.deptno(+)=dept.deptno;

Which, if any, do you think are outer joins or inner joins?
Previous Topic: After Table analyze, the number of blocks goes Down
Next Topic: gather table stats
Goto Forum:
  


Current Time: Fri Mar 29 04:31:37 CDT 2024