This discussion is archived
10 Replies Latest reply: Dec 6, 2012 3:12 AM by 905562 RSS

Help in Performance Tunning

Vishalaksha Newbie
Currently Being Moderated
Hi,

I am looking for some help in tuning a query and details are below.
SELECT NVL(B.START_DATE_TIME,
           A.START_DATE_TIME) START_DATE,
       NVL(B.CUST_PRODUCTION_SEQ_NUM,
           A.CUST_PRODUCTION_SEQ_NUM) PROD_NUM,
       A.HEADER_ID NEW_HEADER_ID,
       B.HEADER_ID OLD_HEADER_ID,
       A.LINE_ID,
       B.LINE_ID,
       A.QTY_TYPE_CODE NEW_CODE,
       B.QTY_TYPE_CODE OLD_CODE,
       NVL(B.SHIP_FROM_ORG_ID,
           A.SHIP_FROM_ORG_ID) SHIP_FROM_ORG_ID,
       -- hrorg.name ship_from_name ,
       NVL(B.SHIP_TO_ORG_ID,
           A.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID,
       NVL(B.BILL_TO_ADDRESS_ID,
           A.BILL_TO_ADDRESS_ID) BILL_TO_ADDRESS_ID,
       NVL(B.SHIP_TO_ADDRESS_ID,
           A.SHIP_TO_ADDRESS_ID) SHIP_TO_ADDRESS_ID,
       NVL(B.SHIP_TO_NAME_EXT,
           A.SHIP_TO_NAME_EXT) SHIP_TO_NAME,
       NVL(B.INTRMD_SHIP_TO_ID,
           A.INTRMD_SHIP_TO_ID) INTRMD_SHIP_TO_ID,
       NVL(B.CUSTOMER_ITEM_ID,
           A.CUSTOMER_ITEM_ID) CUSTOMER_ITEM_ID,
       --mci.customer_item_desc customer_item_desc,
       NVL(B.INVENTORY_ITEM_ID,
           A.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
       NVL(B.ITEM_DETAIL_SUBTYPE,
           A.ITEM_DETAIL_SUBTYPE) ITEM_DETAIL_SUBTYPE,
       FND1.MEANING DETAIL_SUBTYPE,
       NVL(B.UOM_CODE,
           A.UOM_CODE) UOM_CODE,
       NVL(B.CUST_PO_NUMBER,
           A.CUST_PO_NUMBER) CUST_PO_NUMBER,
       NVL(B.INDUSTRY_ATTRIBUTE1,
           A.INDUSTRY_ATTRIBUTE1) CUST_RECORD_YEAR,
       A.ITEM_DETAIL_QUANTITY,
       A.ITEM_DETAIL_TYPE,
       FND2.MEANING NEW_DETAIL_TYPE,
       B.ITEM_DETAIL_QUANTITY,
       B.ITEM_DETAIL_TYPE,
       FND3.MEANING OLD_DETAIL_TYPE
  FROM RLM_SCHEDULE_LINES_ALL A,
       (SELECT * FROM RLM_SCHEDULE_LINES_ALL b_in
        WHERE TRUNC(b_in.START_DATE_TIME) >=
              (SELECT TRUNC(SCHED_HORIZON_START_DATE)
                 FROM RLM_SCHEDULE_HEADERS
                WHERE HEADER_ID = :P_NEW_HEADER_ID)---
        AND TRUNC(b_in.START_DATE_TIME) <=
              (SELECT TRUNC(SCHED_HORIZON_END_DATE)
                 FROM RLM_SCHEDULE_HEADERS
                WHERE HEADER_ID = :P_NEW_HEADER_ID)
        AND HEADER_ID = (SELECT header_id
        FROM apps.rlm_schedule_headers_all
       WHERE header_id =
             (SELECT MAX(rsha.header_id)
                FROM apps.rlm_schedule_headers_all rsha,
                     apps.rlm_schedule_lines_all rsla
               WHERE rsha.customer_id = :p_customer_id
                 AND rsha.schedule_type = :p_schedule_type
                 AND rsla.inventory_item_id = b_in.inventory_item_id ---
                 AND rsla.org_id = rsha.org_id
                 AND (((rsla.cust_po_number = b_in.cust_po_number)AND
                       EXISTS (SELECT 'x'
                               FROM FND_LOOKUP_VALUES_VL FLV,
                                    RA_CUSTOMERS         RC
                               WHERE FLV.LOOKUP_TYPE = 'TEST_RLM_CHECK_CUST'
                               AND FLV.MEANING = RC.CUSTOMER_NAME
                               AND RC.CUSTOMER_ID = :P_CUSTOMER_ID)) 
                               OR 
                               NOT EXISTS (SELECT 'x'
                               FROM FND_LOOKUP_VALUES_VL FLV,
                                    RA_CUSTOMERS         RC
                               WHERE FLV.LOOKUP_TYPE = 'TEST_RLM_CHECK_CUST'
                               AND FLV.MEANING = RC.CUSTOMER_NAME
                               AND RC.CUSTOMER_ID = :P_CUSTOMER_ID) )---
                 and rsha.header_id = rsla.header_id
                 and rsla.ship_from_org_id = :p_ship_from_org_id
                 AND rsha.ece_tp_location_code_ext = :p_ece_tp_location_code_ext
                 AND NVL(rsha.header_ref_value_1, '1') =
                     NVL(rsha.header_ref_value_1, '1')
                 AND rsha.header_id NOT IN
                     (SELECT header_id
                        FROM apps.rlm_schedule_headers_all
                       WHERE attribute1 = :p_request_id)))) B,
       --rlm_schedule_headers c,
       -- hr_organization_units hrorg,
       --mtl_customer_items mci,
       FND_LOOKUPS FND1,
       FND_LOOKUPS FND2,
       FND_LOOKUPS FND3
-- ra_addresses_all radd2,
-- ra_site_uses_all rsu2
 WHERE A.HEADER_ID(+) = :P_NEW_HEADER_ID
   ----AND B.HEADER_ID = :P_OLD_HEADER_ID
   AND A.CUSTOMER_ITEM_ID(+) = B.CUSTOMER_ITEM_ID
   AND A.SHIP_TO_ORG_ID(+) = B.SHIP_TO_ORG_ID
   AND A.SHIP_FROM_ORG_ID(+) = B.SHIP_FROM_ORG_ID
   AND A.INVENTORY_ITEM_ID(+) = B.INVENTORY_ITEM_ID
   AND NVL(A.INTRMD_SHIP_TO_ID(+),
           NVL(B.INTRMD_SHIP_TO_ID,
               0)) = NVL(B.INTRMD_SHIP_TO_ID,
                         0)
   AND A.ITEM_DETAIL_SUBTYPE(+) = B.ITEM_DETAIL_SUBTYPE
   AND A.UOM_CODE(+) = B.UOM_CODE
   AND NVL(A.CUST_PO_NUMBER(+),
           NVL(B.CUST_PO_NUMBER,
               0)) = NVL(B.CUST_PO_NUMBER,
                         0)
   AND A.ITEM_DETAIL_TYPE(+) < :P_ITEM_DETAIL_TYPE
   AND B.ITEM_DETAIL_TYPE < :P_ITEM_DETAIL_TYPE
   AND TRUNC(A.START_DATE_TIME(+)) = TRUNC(B.START_DATE_TIME)
   AND FND1.LOOKUP_CODE = NVL(A.ITEM_DETAIL_SUBTYPE,
                              B.ITEM_DETAIL_SUBTYPE)
   AND FND1.LOOKUP_TYPE = :P_RLM_DETAIL_SUBTYPE_CODE
   AND NVL(A.ITEM_DETAIL_TYPE,
           '0') = FND2.LOOKUP_CODE
   AND FND2.LOOKUP_TYPE = :P_RLM_DETAIL_TYPE_CODE
   AND FND3.LOOKUP_CODE = B.ITEM_DETAIL_TYPE
   AND A.SHIP_FROM_ORG_ID(+) = :P_SHIP_FROM_ORG_ID
   AND B.SHIP_FROM_ORG_ID = :P_SHIP_FROM_ORG_ID
   AND FND3.LOOKUP_TYPE = :P_RLM_DETAIL_TYPE_CODE
   AND B.CUSTOMER_ITEM_ID IN
       (SELECT DISTINCT CUSTOMER_ITEM_ID
          FROM RLM_SCHEDULE_LINES_ALL
         WHERE HEADER_ID = :P_NEW_HEADER_ID) &P_WHERE4 &P_WHERE3
-- CHANGES
 &P_WHERE_DATE_A
UNION
SELECT NVL(A.START_DATE_TIME,
           B.START_DATE_TIME) START_DATE,
       NVL(A.CUST_PRODUCTION_SEQ_NUM,
           B.CUST_PRODUCTION_SEQ_NUM) PROD_NUM,
       A.HEADER_ID NEW_HEADER_ID,
       B.HEADER_ID OLD_HEADER_ID,
       A.LINE_ID,
       B.LINE_ID,
       A.QTY_TYPE_CODE NEW_CODE,
       B.QTY_TYPE_CODE OLD_CODE,
       NVL(A.SHIP_FROM_ORG_ID,
           B.SHIP_FROM_ORG_ID) SHIP_FROM_ORG_ID,
       -- hrorg.name ship_from_name ,
       NVL(A.SHIP_TO_ORG_ID,
           B.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID,
       NVL(A.BILL_TO_ADDRESS_ID,
           B.BILL_TO_ADDRESS_ID) BILL_TO_ADDRESS_ID,
       NVL(A.SHIP_TO_ADDRESS_ID,
           B.SHIP_TO_ADDRESS_ID) SHIP_TO_ADDRESS_ID,
       NVL(A.SHIP_TO_NAME_EXT,
           B.SHIP_TO_NAME_EXT) SHIP_TO_NAME,
       NVL(A.INTRMD_SHIP_TO_ID,
           B.INTRMD_SHIP_TO_ID) INTRMD_SHIP_TO_ID,
       NVL(A.CUSTOMER_ITEM_ID,
           B.CUSTOMER_ITEM_ID) CUSTOMER_ITEM_ID,
       --mci.customer_item_desc customer_item_desc,
       NVL(A.INVENTORY_ITEM_ID,
           B.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
       NVL(A.ITEM_DETAIL_SUBTYPE,
           B.ITEM_DETAIL_SUBTYPE) ITEM_DETAIL_SUBTYPE,
       FND1.MEANING DETAIL_SUBTYPE,
       NVL(A.UOM_CODE,
           B.UOM_CODE) UOM_CODE,
       NVL(A.CUST_PO_NUMBER,
           B.CUST_PO_NUMBER) CUST_PO_NUMBER,
       NVL(A.INDUSTRY_ATTRIBUTE1,
           B.INDUSTRY_ATTRIBUTE1) CUST_RECORD_YEAR,
       A.ITEM_DETAIL_QUANTITY,
       A.ITEM_DETAIL_TYPE,
       FND2.MEANING NEW_DETAIL_TYPE,
       B.ITEM_DETAIL_QUANTITY,
       B.ITEM_DETAIL_TYPE,
       FND3.MEANING OLD_DETAIL_TYPE
  FROM RLM_SCHEDULE_LINES_ALL A,
       (SELECT * FROM RLM_SCHEDULE_LINES_ALL b_in
       WHERE  TRUNC(b_in.START_DATE_TIME) >=
       (SELECT TRUNC(SCHED_HORIZON_START_DATE)
          FROM RLM_SCHEDULE_HEADERS
         WHERE HEADER_ID = :P_NEW_HEADER_ID)
       AND HEADER_ID = (SELECT header_id
        FROM apps.rlm_schedule_headers_all
       WHERE header_id =
             (SELECT MAX(rsha.header_id)
                FROM apps.rlm_schedule_headers_all rsha,
                     apps.rlm_schedule_lines_all rsla
               WHERE rsha.customer_id = :p_customer_id
                 AND rsha.schedule_type = :p_schedule_type
                 AND rsla.inventory_item_id = b_in.inventory_item_id ---
                 AND rsla.org_id = rsha.org_id
                 AND (((rsla.cust_po_number = b_in.cust_po_number)AND
                       EXISTS (SELECT 'x'
                               FROM FND_LOOKUP_VALUES_VL FLV,
                                    RA_CUSTOMERS         RC
                               WHERE FLV.LOOKUP_TYPE = 'TEST_RLM_CHECK_CUST'
                               AND FLV.MEANING = RC.CUSTOMER_NAME
                               AND RC.CUSTOMER_ID = :P_CUSTOMER_ID)) 
                               OR 
                               NOT EXISTS (SELECT 'x'
                               FROM FND_LOOKUP_VALUES_VL FLV,
                                    RA_CUSTOMERS         RC
                               WHERE FLV.LOOKUP_TYPE = 'TEST_RLM_CHECK_CUST'
                               AND FLV.MEANING = RC.CUSTOMER_NAME
                               AND RC.CUSTOMER_ID = :P_CUSTOMER_ID) )---
                 and rsha.header_id = rsla.header_id
                 and rsla.ship_from_org_id = :p_ship_from_org_id
                 AND rsha.ece_tp_location_code_ext = :p_ece_tp_location_code_ext
                 AND NVL(rsha.header_ref_value_1, '1') =
                     NVL(rsha.header_ref_value_1, '1')
                 AND rsha.header_id NOT IN
                     (SELECT header_id
                        FROM apps.rlm_schedule_headers_all
                       WHERE attribute1 = :p_request_id)))) B,
       --rlm_schedule_headers c,
       -- hr_organization_units hrorg,
       --mtl_customer_items mci,
       FND_LOOKUPS FND1,
       FND_LOOKUPS FND2,
       FND_LOOKUPS FND3
-- ra_addresses_all radd2,
-- ra_site_uses_all rsu2
 WHERE A.HEADER_ID = :P_NEW_HEADER_ID
  ---- AND B.HEADER_ID(+) = :P_OLD_HEADER_ID
      --a.header_id=c.header_id and
      -- a.header_id = 803 and
      -- b.header_id (+) = 804 and
   AND B.CUSTOMER_ITEM_ID(+) = A.CUSTOMER_ITEM_ID
   AND B.SHIP_TO_ORG_ID(+) = A.SHIP_TO_ORG_ID
   AND B.SHIP_FROM_ORG_ID(+) = A.SHIP_FROM_ORG_ID
   AND B.INVENTORY_ITEM_ID(+) = A.INVENTORY_ITEM_ID
   AND NVL(B.INTRMD_SHIP_TO_ID(+),
           NVL(A.INTRMD_SHIP_TO_ID,
               0)) = NVL(A.INTRMD_SHIP_TO_ID,
                         0)
   AND B.ITEM_DETAIL_SUBTYPE(+) = A.ITEM_DETAIL_SUBTYPE
   AND B.UOM_CODE(+) = A.UOM_CODE
   AND NVL(B.CUST_PO_NUMBER(+),
           NVL(A.CUST_PO_NUMBER,
               0)) = NVL(A.CUST_PO_NUMBER,
                         0)
   AND B.ITEM_DETAIL_TYPE(+) < :P_ITEM_DETAIL_TYPE
   AND A.ITEM_DETAIL_TYPE < :P_ITEM_DETAIL_TYPE
   AND TRUNC(B.START_DATE_TIME(+)) = TRUNC(A.START_DATE_TIME)
   AND FND1.LOOKUP_CODE = NVL(B.ITEM_DETAIL_SUBTYPE,
                              A.ITEM_DETAIL_SUBTYPE)
   AND FND1.LOOKUP_TYPE = :P_RLM_DETAIL_SUBTYPE_CODE
   AND A.ITEM_DETAIL_TYPE = FND2.LOOKUP_CODE
   AND FND2.LOOKUP_TYPE = :P_RLM_DETAIL_TYPE_CODE
   AND NVL(B.ITEM_DETAIL_TYPE,
           '0') = FND3.LOOKUP_CODE
   AND A.SHIP_FROM_ORG_ID = :P_SHIP_FROM_ORG_ID
   AND B.SHIP_FROM_ORG_ID(+) = :P_SHIP_FROM_ORG_ID
   AND FND3.LOOKUP_TYPE = :P_RLM_DETAIL_TYPE_CODE
   &P_WHERE5 &P_WHERE3
-- CHANGES
 &P_WHERE_DATE_B
The above query is related to the Oracle Release Management 11.5.10.2

The main header table is RLM_SCHEDULE_HEADERS_ALL which contains the information like schedule_reference_number and other details. In one schedule there can be many item records for different dates which are stored in the child table RLM_SCHEDULE_LINES_ALL. Both the tables are linked with HEADER_ID. Each schedule in header table has one horizon start date and horizon end date. Customer used to send these schedules every day for each item because of there business needs.
The requirement is to create a query to calculate the Net Change between the two schedules (Between the new and old). All the item records from child table for new schedule will be printed but only those item records from old schedule will be printed where schedule date in child table is within the horizon start date and end date of new schedule.

There are following indexes on the table RLM_SCHEDULE_LINES_ALL
IDX$$_199350001                     Normal     INVENTORY_ITEM_ID, SHIP_TO_ORG_ID, SHIP_FROM_NAME_EXT
IDX$$_199350002                     Normal     HEADER_ID, INVENTORY_ITEM_ID
RLM_SCHEDULE_LINE_CUSTOM     Normal     ITEM_DETAIL_TYPE, HEADER_ID
RLM_SCHEDULE_LINE_N1             Normal     HEADER_ID, INTERFACE_LINE_ID
RLM_SCHEDULE_LINE_N2             Normal     SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID, CUSTOMER_ITEM_ID
RLM_SCHEDULE_LINE_N99             Normal     INVENTORY_ITEM_ID
RLM_SCHEDULE_LINE_U1             Normal     LINE_ID
XXRLM_RLM_SCHEDULE_LINES     Normal     SHIP_TO_ORG_ID, SHIP_FROM_NAME_EXT, PROCESS_STATUS, ITEM_DETAIL_TYPE, INVENTORY_ITEM_ID, CUSTOMER_ITEM_ID
Database Version: 11.1.0.7.0
Optimizer Parameters:
SQL> show parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_push_pred_cost_based      boolean     TRUE
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.1.0.6
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     FALSE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
 
SQL> 
SQL> show parameter db_file_multi
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
 
SQL> 
SQL> show parameter db_block_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
SQL> 
SQL> show parameter cursor_sharing
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
 
SQL> 
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> 
SQL> 
SQL> SELECT SNAME,
  2         PNAME,
  3         PVAL1,
  4         PVAL2
  5    FROM SYS.AUX_STATS$;
 
SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          03-30-2011 06:20
SYSSTATS_INFO        DSTOP                           03-30-2011 06:20
SYSSTATS_INFO        FLAGS                         0 
SYSSTATS_MAIN        CPUSPEEDNW              976.063 
SYSSTATS_MAIN        IOSEEKTIM                    10 
SYSSTATS_MAIN        IOTFRSPEED                 4096 
SYSSTATS_MAIN        SREADTIM                        
SYSSTATS_MAIN        MREADTIM                        
SYSSTATS_MAIN        CPUSPEED                        
SYSSTATS_MAIN        MBRC                            
SYSSTATS_MAIN        MAXTHR                          
SYSSTATS_MAIN        SLAVETHR                        
 
13 rows selected
 
SQL>
Output of the explain plan will be in the next thread because of size constraint.
  • 1. Re: Help on Performance Tunning
    Vishalaksha Newbie
    Currently Being Moderated
    Here is the output of explain plan
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                     | R
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                          |
    |   1 |  SORT UNIQUE                              |                          |
    |   2 |   UNION-ALL                               |                          |
    |*  3 |    FILTER                                 |                          |
    |   4 |     NESTED LOOPS                          |                          |
    |   5 |      NESTED LOOPS                         |                          |
    |   6 |       NESTED LOOPS                        |                          |
    |*  7 |        HASH JOIN                          |                          |
    |   8 |         TABLE ACCESS BY INDEX ROWID       | RLM_SCHEDULE_LINES_ALL   |
    |*  9 |          INDEX SKIP SCAN                  | RLM_SCHEDULE_LINE_CUSTOM |
    |* 10 |         HASH JOIN RIGHT OUTER             |                          |
    |* 11 |          TABLE ACCESS BY INDEX ROWID      | RLM_SCHEDULE_LINES_ALL   |
    |* 12 |           INDEX SKIP SCAN                 | RLM_SCHEDULE_LINE_CUSTOM |
    |  13 |          NESTED LOOPS                     |                          |
    |  14 |           NESTED LOOPS                    |                          |
    |  15 |            TABLE ACCESS BY INDEX ROWID    | FND_LOOKUP_VALUES        |
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |* 16 |             INDEX RANGE SCAN              | IDX$$_123E00001          |
    |* 17 |            INDEX RANGE SCAN               | RLM_SCHEDULE_LINE_CUSTOM |
    |* 18 |           TABLE ACCESS BY INDEX ROWID     | RLM_SCHEDULE_LINES_ALL   |
    |* 19 |            TABLE ACCESS BY INDEX ROWID    | RLM_SCHEDULE_HEADERS_ALL |
    |* 20 |             INDEX UNIQUE SCAN             | RLM_SCHEDULE_HEADERS_U1  |
    |* 21 |            TABLE ACCESS BY INDEX ROWID    | RLM_SCHEDULE_HEADERS_ALL |
    |* 22 |             INDEX UNIQUE SCAN             | RLM_SCHEDULE_HEADERS_U1  |
    |  23 |        TABLE ACCESS BY INDEX ROWID        | FND_LOOKUP_VALUES        |
    |* 24 |         INDEX RANGE SCAN                  | FND_LOOKUP_VALUES_U1     |
    |* 25 |       INDEX RANGE SCAN                    | FND_LOOKUP_VALUES_U1     |
    |  26 |      TABLE ACCESS BY INDEX ROWID          | FND_LOOKUP_VALUES        |
    |* 27 |     INDEX UNIQUE SCAN                     | RLM_SCHEDULE_HEADERS_U1  |
    |  28 |      SORT AGGREGATE                       |                          |
    |  29 |       NESTED LOOPS                        |                          |
    |  30 |        NESTED LOOPS                       |                          |
    |  31 |         NESTED LOOPS ANTI                 |                          |
    |* 32 |          TABLE ACCESS BY INDEX ROWID      | RLM_SCHEDULE_HEADERS_ALL |
    |* 33 |           INDEX RANGE SCAN                | RLM_SCHEDULE_HEADERS_N3  |
    |* 34 |          TABLE ACCESS BY INDEX ROWID      | RLM_SCHEDULE_HEADERS_ALL |
    |* 35 |           INDEX UNIQUE SCAN               | RLM_SCHEDULE_HEADERS_U1  |
    |* 36 |         INDEX RANGE SCAN                  | IDX$$_199350002          |
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |* 37 |        TABLE ACCESS BY INDEX ROWID        | RLM_SCHEDULE_LINES_ALL   |
    |  38 |         NESTED LOOPS                      |                          |
    |  39 |          NESTED LOOPS                     |                          |
    |  40 |           TABLE ACCESS BY INDEX ROWID     | HZ_CUST_ACCOUNTS         |
    |* 41 |            INDEX UNIQUE SCAN              | HZ_CUST_ACCOUNTS_U1      |
    |  42 |           TABLE ACCESS BY INDEX ROWID     | HZ_PARTIES               | 5
    |* 43 |            INDEX UNIQUE SCAN              | HZ_PARTIES_U1            |
    |* 44 |          INDEX RANGE SCAN                 | FND_LOOKUP_VALUES_U2     |
    |  45 |         NESTED LOOPS                      |                          |
    |  46 |          NESTED LOOPS                     |                          |
    |  47 |           TABLE ACCESS BY INDEX ROWID     | HZ_CUST_ACCOUNTS         |
    |* 48 |            INDEX UNIQUE SCAN              | HZ_CUST_ACCOUNTS_U1      |
    |  49 |           TABLE ACCESS BY INDEX ROWID     | HZ_PARTIES               | 5
    |* 50 |            INDEX UNIQUE SCAN              | HZ_PARTIES_U1            |
    |* 51 |          INDEX RANGE SCAN                 | FND_LOOKUP_VALUES_U2     |
    |  52 |    NESTED LOOPS                           |                          |
    |  53 |     NESTED LOOPS                          |                          |
    |  54 |      NESTED LOOPS                         |                          |
    |  55 |       NESTED LOOPS OUTER                  |                          |
    |  56 |        NESTED LOOPS                       |                          |
    |  57 |         TABLE ACCESS BY INDEX ROWID       | FND_LOOKUP_VALUES        |
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |* 58 |          INDEX RANGE SCAN                 | IDX$$_123E00001          |
    |* 59 |         TABLE ACCESS BY INDEX ROWID       | RLM_SCHEDULE_LINES_ALL   |
    |* 60 |          INDEX RANGE SCAN                 | RLM_SCHEDULE_LINE_CUSTOM |
    |* 61 |        VIEW PUSHED PREDICATE              |                          |
    |* 62 |         FILTER                            |                          |
    |* 63 |          FILTER                           |                          |
    |* 64 |           TABLE ACCESS BY INDEX ROWID     | RLM_SCHEDULE_LINES_ALL   |
    |* 65 |            INDEX RANGE SCAN               | IDX$$_199350001          |
    |* 66 |            TABLE ACCESS BY INDEX ROWID    | RLM_SCHEDULE_HEADERS_ALL |
    |* 67 |             INDEX UNIQUE SCAN             | RLM_SCHEDULE_HEADERS_U1  |
    |* 68 |          INDEX UNIQUE SCAN                | RLM_SCHEDULE_HEADERS_U1  |
    |  69 |           SORT AGGREGATE                  |                          |
    |  70 |            NESTED LOOPS                   |                          |
    |  71 |             NESTED LOOPS                  |                          |
    |  72 |              NESTED LOOPS ANTI            |                          |
    |* 73 |               TABLE ACCESS BY INDEX ROWID | RLM_SCHEDULE_HEADERS_ALL |
    |* 74 |                INDEX RANGE SCAN           | RLM_SCHEDULE_HEADERS_N3  |
    |* 75 |               TABLE ACCESS BY INDEX ROWID | RLM_SCHEDULE_HEADERS_ALL |
    |* 76 |                INDEX UNIQUE SCAN          | RLM_SCHEDULE_HEADERS_U1  |
    |* 77 |              INDEX RANGE SCAN             | IDX$$_199350002          |
    |* 78 |             TABLE ACCESS BY INDEX ROWID   | RLM_SCHEDULE_LINES_ALL   |
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |  79 |              NESTED LOOPS                 |                          |
    |  80 |               NESTED LOOPS                |                          |
    |  81 |                TABLE ACCESS BY INDEX ROWID| HZ_CUST_ACCOUNTS         |
    |* 82 |                 INDEX UNIQUE SCAN         | HZ_CUST_ACCOUNTS_U1      |
    |  83 |                TABLE ACCESS BY INDEX ROWID| HZ_PARTIES               | 5
    |* 84 |                 INDEX UNIQUE SCAN         | HZ_PARTIES_U1            |
    |* 85 |               INDEX RANGE SCAN            | FND_LOOKUP_VALUES_U2     |
    |  86 |              NESTED LOOPS                 |                          |
    |  87 |               NESTED LOOPS                |                          |
    |  88 |                TABLE ACCESS BY INDEX ROWID| HZ_CUST_ACCOUNTS         |
    |* 89 |                 INDEX UNIQUE SCAN         | HZ_CUST_ACCOUNTS_U1      |
    |  90 |                TABLE ACCESS BY INDEX ROWID| HZ_PARTIES               | 5
    |* 91 |                 INDEX UNIQUE SCAN         | HZ_PARTIES_U1            |
    |* 92 |               INDEX RANGE SCAN            | FND_LOOKUP_VALUES_U2     |
    |  93 |       TABLE ACCESS BY INDEX ROWID         | FND_LOOKUP_VALUES        |
    |* 94 |        INDEX RANGE SCAN                   | FND_LOOKUP_VALUES_U1     |
    |* 95 |      INDEX RANGE SCAN                     | FND_LOOKUP_VALUES_U1     |
    |  96 |     TABLE ACCESS BY INDEX ROWID           | FND_LOOKUP_VALUES        |
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ---------------------------------------------------
       3 - filter("HEADER_ID"= (SELECT "HEADER_ID" FROM "APPS"."RLM_SCHEDULE_HEADERS
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "HEADER_ID"= (SELECT MAX("RSHA"."
                  "APPS"."RLM_SCHEDULE_LINES_ALL" "RSLA","APPS"."RLM_SCHEDULE_HEADER
                  "RSHA","APPS"."RLM_SCHEDULE_HEADERS_ALL" "RLM_SCHEDULE_HEADERS_ALL
                  "RSHA"."HEADER_ID"="HEADER_ID" AND TO_NUMBER("ATTRIBUTE1")=1090072
                  "RSHA"."ECE_TP_LOCATION_CODE_EXT"='AG-60989' AND "RSHA"."CUSTOMER_
                  "RSHA"."SCHEDULE_TYPE"='PLANNING_RELEASE' AND NVL("RSHA"."HEADER_R
                  "RSLA"."INVENTORY_ITEM_ID"=:B1 AND "RSHA"."HEADER_ID"="RSLA"."HEAD
                  "RSLA"."SHIP_FROM_ORG_ID"=2904 AND ("RSLA"."CUST_PO_NUMBER"=:B2 AN
                  "AR"."HZ_CUST_ACCOUNTS" "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APP
                  WHERE "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=US
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
       7 - access("B_IN"."CUSTOMER_ITEM_ID"="CUSTOMER_ITEM_ID")
       9 - access("HEADER_ID"=1197995)
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
           filter("HEADER_ID"=1197995)
      10 - access("A"."CUSTOMER_ITEM_ID"(+)="B_IN"."CUSTOMER_ITEM_ID" AND
                  "A"."SHIP_TO_ORG_ID"(+)="B_IN"."SHIP_TO_ORG_ID" AND
                  "A"."SHIP_FROM_ORG_ID"(+)="B_IN"."SHIP_FROM_ORG_ID" AND
                  "A"."INVENTORY_ITEM_ID"(+)="B_IN"."INVENTORY_ITEM_ID" AND
                  "A"."ITEM_DETAIL_SUBTYPE"(+)="B_IN"."ITEM_DETAIL_SUBTYPE" AND "A".
                  AND TRUNC(INTERNAL_FUNCTION("A"."START_DATE_TIME"(+)))=TRUNC(INTER
                  IME")))
           filter(NVL("A"."INTRMD_SHIP_TO_ID"(+),NVL("B_IN"."INTRMD_SHIP_TO_ID",0))=
                  SHIP_TO_ID",0) AND NVL("A"."CUST_PO_NUMBER"(+),NVL("B_IN"."CUST_PO
                  O_NUMBER",'0'))
      11 - filter("A"."SHIP_FROM_ORG_ID"(+)=2904)
      12 - access("A"."HEADER_ID"(+)=1197995)
           filter("A"."HEADER_ID"(+)=1197995 AND TO_NUMBER("A"."ITEM_DETAIL_TYPE"(+)
      16 - access("LOOKUP_TYPE"='RLM_DETAIL_TYPE_CODE' AND "VIEW_APPLICATION_ID"=0 A
                  "LANGUAGE"=USERENV('LANG'))
           filter("SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("LV"."LOO
                  W_APPLICATION_ID"))
      17 - access("LOOKUP_CODE"="B_IN"."ITEM_DETAIL_TYPE")
           filter(TO_NUMBER("B_IN"."ITEM_DETAIL_TYPE")<3)
      18 - filter("B_IN"."SHIP_FROM_ORG_ID"=2904 AND TRUNC(INTERNAL_FUNCTION("B_IN".
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  >= (SELECT TRUNC(INTERNAL_FUNCTION("SCHED_HORIZON_START_DATE")) FR
                  "RLM"."RLM_SCHEDULE_HEADERS_ALL" "RLM_SCHEDULE_HEADERS_ALL" WHERE
                  NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
                  TRUNC(INTERNAL_FUNCTION("B_IN"."START_DATE_TIME"))<= (SELECT
                  TRUNC(INTERNAL_FUNCTION("SCHED_HORIZON_END_DATE")) FROM "RLM"."RLM
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "HEADER_ID"=1197995 AND
                  NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
      19 - filter(NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
      20 - access("HEADER_ID"=1197995)
      21 - filter(NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
      22 - access("HEADER_ID"=1197995)
      24 - access("LOOKUP_TYPE"='RLM_DETAIL_SUBTYPE_CODE' AND "VIEW_APPLICATION_ID"=
                  "LOOKUP_CODE"=NVL("A"."ITEM_DETAIL_SUBTYPE","B_IN"."ITEM_DETAIL_SU
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  "LANGUAGE"=USERENV('LANG'))
           filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"="FND_GLOBAL"."L
                  P"("LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID"))
      25 - access("LOOKUP_TYPE"='RLM_DETAIL_TYPE_CODE' AND "VIEW_APPLICATION_ID"=0 A
                  "LOOKUP_CODE"=NVL("A"."ITEM_DETAIL_TYPE",'0') AND "LANGUAGE"=USERE
           filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"="FND_GLOBAL"."L
                  P"("LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID"))
      27 - access("HEADER_ID"= (SELECT MAX("RSHA"."HEADER_ID") FROM "APPS"."RLM_SCHE
                  "RSLA","APPS"."RLM_SCHEDULE_HEADERS_ALL" "RSHA","APPS"."RLM_SCHEDU
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "RSHA"."HEADER_ID"="HEADER_ID" AN
                  TO_NUMBER("ATTRIBUTE1")=109007290 AND "RSHA"."ECE_TP_LOCATION_CODE
                  "RSHA"."CUSTOMER_ID"=64742 AND "RSHA"."SCHEDULE_TYPE"='PLANNING_RE
                  NVL("RSHA"."HEADER_REF_VALUE_1",'1') IS NOT NULL AND "RSLA"."INVEN
                  "RSHA"."HEADER_ID"="RSLA"."HEADER_ID" AND "RSLA"."SHIP_FROM_ORG_ID
                  ("RSLA"."CUST_PO_NUMBER"=:B2 AND  EXISTS (SELECT 0 FROM "AR"."HZ_C
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
      32 - filter("RSHA"."CUSTOMER_ID"=64742 AND "RSHA"."SCHEDULE_TYPE"='PLANNING_RE
                  NVL("RSHA"."HEADER_REF_VALUE_1",'1') IS NOT NULL)
      33 - access("RSHA"."ECE_TP_LOCATION_CODE_EXT"='AG-60989')
      34 - filter(TO_NUMBER("ATTRIBUTE1")=109007290)
      35 - access("RSHA"."HEADER_ID"="HEADER_ID")
      36 - access("RSHA"."HEADER_ID"="RSLA"."HEADER_ID" AND "RSLA"."INVENTORY_ITEM_I
      37 - filter("RSLA"."SHIP_FROM_ORG_ID"=2904 AND ("RSLA"."CUST_PO_NUMBER"=:B1 AN
                  0 FROM "AR"."HZ_CUST_ACCOUNTS" "CUST_ACCT","AR"."HZ_PARTIES" "PART
                  "B" WHERE "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
      41 - access("CUST_ACCT"."CUST_ACCOUNT_ID"=64742)
      43 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
      44 - access("B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."MEANING"=SUBSTRB("
                  AND "B"."LANGUAGE"=USERENV('LANG'))
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
           filter("B"."LANGUAGE"=USERENV('LANG') AND "B"."MEANING"=SUBSTRB("PARTY_NA
      48 - access("CUST_ACCT"."CUST_ACCOUNT_ID"=64742)
      50 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
      51 - access("B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."MEANING"=SUBSTRB("
                  AND "B"."LANGUAGE"=USERENV('LANG'))
           filter("B"."LANGUAGE"=USERENV('LANG') AND "B"."MEANING"=SUBSTRB("PARTY_NA
      58 - access("LOOKUP_TYPE"='RLM_DETAIL_TYPE_CODE' AND "VIEW_APPLICATION_ID"=0 A
                  "LANGUAGE"=USERENV('LANG'))
           filter("SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("LV"."LOO
                  W_APPLICATION_ID"))
      59 - filter("A"."SHIP_FROM_ORG_ID"=2904)
      60 - access("A"."ITEM_DETAIL_TYPE"="LOOKUP_CODE" AND "A"."HEADER_ID"=1197995)
           filter(TO_NUMBER("A"."ITEM_DETAIL_TYPE")<3)
      61 - filter(NVL("B"."INTRMD_SHIP_TO_ID"(+),NVL("A"."INTRMD_SHIP_TO_ID",0))=NVL
                  O_ID",0) AND "B"."ITEM_DETAIL_SUBTYPE"(+)="A"."ITEM_DETAIL_SUBTYPE
                  "B"."UOM_CODE"(+)="A"."UOM_CODE" AND NVL("B"."CUST_PO_NUMBER"(+),N
                  ("A"."CUST_PO_NUMBER",'0') AND TRUNC(INTERNAL_FUNCTION("B"."START_
                  NCTION("A"."START_DATE_TIME")))
      62 - filter("HEADER_ID"= (SELECT "HEADER_ID" FROM "APPS"."RLM_SCHEDULE_HEADERS
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "HEADER_ID"= (SELECT MAX("RSHA"."
                  "APPS"."RLM_SCHEDULE_LINES_ALL" "RSLA","APPS"."RLM_SCHEDULE_HEADER
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  "RSHA","APPS"."RLM_SCHEDULE_HEADERS_ALL" "RLM_SCHEDULE_HEADERS_ALL
                  "RSHA"."HEADER_ID"="HEADER_ID" AND TO_NUMBER("ATTRIBUTE1")=1090072
                  "RSHA"."ECE_TP_LOCATION_CODE_EXT"='AG-60989' AND "RSHA"."CUSTOMER_
                  "RSHA"."SCHEDULE_TYPE"='PLANNING_RELEASE' AND NVL("RSHA"."HEADER_R
                  "RSLA"."INVENTORY_ITEM_ID"=:B1 AND "RSHA"."HEADER_ID"="RSLA"."HEAD
                  "RSLA"."SHIP_FROM_ORG_ID"=2904 AND ("RSLA"."CUST_PO_NUMBER"=:B2 AN
                  "AR"."HZ_CUST_ACCOUNTS" "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APP
                  WHERE "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=US
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
      63 - filter("A"."SHIP_FROM_ORG_ID"=2904)
      64 - filter("B_IN"."CUSTOMER_ITEM_ID"="A"."CUSTOMER_ITEM_ID" AND
                  "B_IN"."SHIP_FROM_ORG_ID"="A"."SHIP_FROM_ORG_ID" AND TO_NUMBER("B_
                  TRUNC(INTERNAL_FUNCTION("B_IN"."START_DATE_TIME"))>= (SELECT
                  TRUNC(INTERNAL_FUNCTION("SCHED_HORIZON_START_DATE")) FROM "RLM"."R
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "HEADER_ID"=1197995 AND
                  NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
      65 - access("B_IN"."INVENTORY_ITEM_ID"="A"."INVENTORY_ITEM_ID" AND
                  "B_IN"."SHIP_TO_ORG_ID"="A"."SHIP_TO_ORG_ID")
      66 - filter(NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1
                  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBE
                  INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99))
      67 - access("HEADER_ID"=1197995)
      68 - access("HEADER_ID"= (SELECT MAX("RSHA"."HEADER_ID") FROM "APPS"."RLM_SCHE
                  "RSLA","APPS"."RLM_SCHEDULE_HEADERS_ALL" "RSHA","APPS"."RLM_SCHEDU
                  "RLM_SCHEDULE_HEADERS_ALL" WHERE "RSHA"."HEADER_ID"="HEADER_ID" AN
                  TO_NUMBER("ATTRIBUTE1")=109007290 AND "RSHA"."ECE_TP_LOCATION_CODE
                  "RSHA"."CUSTOMER_ID"=64742 AND "RSHA"."SCHEDULE_TYPE"='PLANNING_RE
                  NVL("RSHA"."HEADER_REF_VALUE_1",'1') IS NOT NULL AND "RSLA"."INVEN
                  "RSHA"."HEADER_ID"="RSLA"."HEADER_ID" AND "RSLA"."SHIP_FROM_ORG_ID
                  ("RSLA"."CUST_PO_NUMBER"=:B2 AND  EXISTS (SELECT 0 FROM "AR"."HZ_C
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
      73 - filter("RSHA"."CUSTOMER_ID"=64742 AND "RSHA"."SCHEDULE_TYPE"='PLANNING_RE
                  NVL("RSHA"."HEADER_REF_VALUE_1",'1') IS NOT NULL)
      74 - access("RSHA"."ECE_TP_LOCATION_CODE_EXT"='AG-60989')
      75 - filter(TO_NUMBER("ATTRIBUTE1")=109007290)
      76 - access("RSHA"."HEADER_ID"="HEADER_ID")
      77 - access("RSHA"."HEADER_ID"="RSLA"."HEADER_ID" AND "RSLA"."INVENTORY_ITEM_I
      78 - filter("RSLA"."SHIP_FROM_ORG_ID"=2904 AND ("RSLA"."CUST_PO_NUMBER"=:B1 AN
                  0 FROM "AR"."HZ_CUST_ACCOUNTS" "CUST_ACCT","AR"."HZ_PARTIES" "PART
                  "B" WHERE "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742) OR  NOT EXISTS (SELECT 0 FROM
                  "CUST_ACCT","AR"."HZ_PARTIES" "PARTY","APPLSYS"."FND_LOOKUP_VALUES
                  "B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."LANGUAGE"=USERENV(
                  "B"."MEANING"=SUBSTRB("PARTY_NAME",1,50) AND "CUST_ACCT"."PARTY_ID
                  "CUST_ACCT"."CUST_ACCOUNT_ID"=64742)) AND "RSLA"."ORG_ID"="RSHA"."
      82 - access("CUST_ACCT"."CUST_ACCOUNT_ID"=64742)
      84 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
      85 - access("B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."MEANING"=SUBSTRB("
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  AND "B"."LANGUAGE"=USERENV('LANG'))
           filter("B"."LANGUAGE"=USERENV('LANG') AND "B"."MEANING"=SUBSTRB("PARTY_NA
      89 - access("CUST_ACCT"."CUST_ACCOUNT_ID"=64742)
      91 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
      92 - access("B"."LOOKUP_TYPE"='ETN_RLM_CHECK_CUST' AND "B"."MEANING"=SUBSTRB("
                  AND "B"."LANGUAGE"=USERENV('LANG'))
           filter("B"."LANGUAGE"=USERENV('LANG') AND "B"."MEANING"=SUBSTRB("PARTY_NA
      94 - access("LOOKUP_TYPE"='RLM_DETAIL_SUBTYPE_CODE' AND "VIEW_APPLICATION_ID"=
                  "LOOKUP_CODE"=NVL("B"."ITEM_DETAIL_SUBTYPE","A"."ITEM_DETAIL_SUBTY
                  "LANGUAGE"=USERENV('LANG'))
           filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"="FND_GLOBAL"."L
                  P"("LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID"))
      95 - access("LOOKUP_TYPE"='RLM_DETAIL_TYPE_CODE' AND "VIEW_APPLICATION_ID"=0 A
                  "LOOKUP_CODE"=NVL("B"."ITEM_DETAIL_TYPE",'0') AND "LANGUAGE"=USERE
           filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"="FND_GLOBAL"."L
                  P"("LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID"))
    Note
    -----
       - 'PLAN_TABLE' is old version
     
    313 rows selected
    Please suggest me how can I work on the query part to improve the performance.

    Thanks
    Vishalaksha
  • 2. Re: Help in Performance Tunning
    BluShadow Guru Moderator
    Currently Being Moderated
    You haven't posted the full plan output, so we can't see all the figures that go with the explain plan.
  • 3. Re: Help in Performance Tunning
    Vishalaksha Newbie
    Currently Being Moderated
    Not able to set autotrace so got this plan from plsql developer... (getting error Cannot SET AUTOTRACE)

    Last three columns are 'COST', 'CARDINALITY' and 'BYTES' respectively.
    Here is example
    COST = 81456
    CARDINALITY = 2
    BYTES = 1064
    SELECT STATEMENT, GOAL = ALL_ROWS               81465     2     1064
     SORT UNIQUE               81465     2     1064
      UNION-ALL                         
       FILTER                         
        NESTED LOOPS                         
         NESTED LOOPS               80765     1     403
          NESTED LOOPS               80762     1     343
           HASH JOIN               80759     1     283
            TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     75     383     4213
             INDEX SKIP SCAN     RLM     RLM_SCHEDULE_LINE_CUSTOM     15     383     
            HASH JOIN RIGHT OUTER               80683     80     21760
             TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     75     1     106
              INDEX SKIP SCAN     RLM     RLM_SCHEDULE_LINE_CUSTOM     15     383     
             NESTED LOOPS                         
              NESTED LOOPS               80608     80     13280
               TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     4     2     120
                INDEX RANGE SCAN     APPLSYS     IDX$$_123E00001     3     2     
               INDEX RANGE SCAN     RLM     RLM_SCHEDULE_LINE_CUSTOM     20991     249515     
              TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     59613     37     3922
               TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     2     1     18
                INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     1     1     
               TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     2     1     18
                INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     1     1     
           TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     3     1     60
            INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U1     2     1     
          INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U1     2     1     
         TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     3     1     60
        TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     2     1     14
         INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     1     1     
          SORT AGGREGATE                    1     92
           NESTED LOOPS                         
            NESTED LOOPS               330     1     92
             NESTED LOOPS ANTI               318     1     64
              TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     316     2     82
               INDEX RANGE SCAN     RLM     RLM_SCHEDULE_HEADERS_N3     2     455     
              TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     1     1     23
               INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     0     1     
             INDEX RANGE SCAN     RLM     IDX$$_199350002     2     37     
            TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     12     1     28
             NESTED LOOPS               5     1     81
              NESTED LOOPS               3     1     36
               TABLE ACCESS BY INDEX ROWID     AR     HZ_CUST_ACCOUNTS     2     1     10
                INDEX UNIQUE SCAN     AR     HZ_CUST_ACCOUNTS_U1     1     1     
               TABLE ACCESS BY INDEX ROWID     AR     HZ_PARTIES     1     51223     1331798
                INDEX UNIQUE SCAN     AR     HZ_PARTIES_U1     0     1     
              INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U2     2     1     45
             NESTED LOOPS               5     1     81
              NESTED LOOPS               3     1     36
               TABLE ACCESS BY INDEX ROWID     AR     HZ_CUST_ACCOUNTS     2     1     10
                INDEX UNIQUE SCAN     AR     HZ_CUST_ACCOUNTS_U1     1     1     
               TABLE ACCESS BY INDEX ROWID     AR     HZ_PARTIES     1     51223     1331798
                INDEX UNIQUE SCAN     AR     HZ_PARTIES_U1     0     1     
              INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U2     2     1     45
       NESTED LOOPS                         
        NESTED LOOPS               362     1     661
         NESTED LOOPS               359     1     601
          NESTED LOOPS OUTER               356     1     541
           NESTED LOOPS               15     1     185
            TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     4     2     120
             INDEX RANGE SCAN     APPLSYS     IDX$$_123E00001     3     2     
            TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     7     1     125
             INDEX RANGE SCAN     RLM     RLM_SCHEDULE_LINE_CUSTOM     4     19     
           VIEW PUSHED PREDICATE     APPS          341     1     356
            FILTER                         
             FILTER                         
              TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     7     1     106
               INDEX RANGE SCAN     RLM     IDX$$_199350001     4     9     
               TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     2     1     18
                INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     1     1     
             TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     2     1     14
              INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     1     1     
               SORT AGGREGATE                    1     84
                NESTED LOOPS                         
                 NESTED LOOPS               330     1     84
                  NESTED LOOPS ANTI               318     1     56
                   TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     316     2     82
                    INDEX RANGE SCAN     RLM     RLM_SCHEDULE_HEADERS_N3     2     455     
                   TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_HEADERS_ALL     1     3     45
                    INDEX UNIQUE SCAN     RLM     RLM_SCHEDULE_HEADERS_U1     0     1     
                  INDEX RANGE SCAN     RLM     IDX$$_199350002     2     37     
                 TABLE ACCESS BY INDEX ROWID     RLM     RLM_SCHEDULE_LINES_ALL     12     1     28
                  NESTED LOOPS               5     1     81
                   NESTED LOOPS               3     1     36
                    TABLE ACCESS BY INDEX ROWID     AR     HZ_CUST_ACCOUNTS     2     1     10
                     INDEX UNIQUE SCAN     AR     HZ_CUST_ACCOUNTS_U1     1     1     
                    TABLE ACCESS BY INDEX ROWID     AR     HZ_PARTIES     1     51223     1331798
                     INDEX UNIQUE SCAN     AR     HZ_PARTIES_U1     0     1     
                   INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U2     2     1     45
                  NESTED LOOPS               5     1     81
                   NESTED LOOPS               3     1     36
                    TABLE ACCESS BY INDEX ROWID     AR     HZ_CUST_ACCOUNTS     2     1     10
                     INDEX UNIQUE SCAN     AR     HZ_CUST_ACCOUNTS_U1     1     1     
                    TABLE ACCESS BY INDEX ROWID     AR     HZ_PARTIES     1     51223     1331798
                     INDEX UNIQUE SCAN     AR     HZ_PARTIES_U1     0     1     
                   INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U2     2     1     45
          TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     3     1     60
           INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U1     2     1     
         INDEX RANGE SCAN     APPLSYS     FND_LOOKUP_VALUES_U1     2     1     
        TABLE ACCESS BY INDEX ROWID     APPLSYS     FND_LOOKUP_VALUES     3     1     60
  • 4. Re: Help in Performance Tunning
    Vishalaksha Newbie
    Currently Being Moderated
    Also this is from SQL*Plus...
    121 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=81465 Card=2 Bytes
              =1064)
    
       1    0   SORT (UNIQUE) (Cost=81465 Card=2 Bytes=1064)
       2    1     UNION-ALL
       3    2       FILTER
       4    3         NESTED LOOPS
       5    4           NESTED LOOPS (Cost=80765 Card=1 Bytes=403)
       6    5             NESTED LOOPS (Cost=80762 Card=1 Bytes=343)
       7    6               HASH JOIN (Cost=80759 Card=1 Bytes=283)
       8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHEDU
              LE_LINES_ALL' (TABLE) (Cost=75 Card=383 Bytes=4213)
    
       9    8                   INDEX (SKIP SCAN) OF 'RLM_SCHEDULE_LINE_CU
              STOM' (INDEX) (Cost=15 Card=383)
    
      10    7                 HASH JOIN (RIGHT OUTER) (Cost=80683 Card=80
              Bytes=21760)
    
      11   10                   TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHE
              DULE_LINES_ALL' (TABLE) (Cost=75 Card=1 Bytes=106)
    
      12   11                     INDEX (SKIP SCAN) OF 'RLM_SCHEDULE_LINE_
              CUSTOM' (INDEX) (Cost=15 Card=383)
    
      13   10                   NESTED LOOPS
      14   13                     NESTED LOOPS (Cost=80608 Card=80 Bytes=1
              3280)
    
      15   14                       TABLE ACCESS (BY INDEX ROWID) OF 'FND_
              LOOKUP_VALUES' (TABLE) (Cost=4 Card=2 Bytes=120)
    
      16   15                         INDEX (RANGE SCAN) OF 'IDX$$_123E000
              01' (INDEX) (Cost=3 Card=2)
    
      17   14                       INDEX (RANGE SCAN) OF 'RLM_SCHEDULE_LI
              NE_CUSTOM' (INDEX) (Cost=20991 Card=249515)
    
      18   13                     TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SC
              HEDULE_LINES_ALL' (TABLE) (Cost=59613 Card=37 Bytes=3922)
    
      19   18                       TABLE ACCESS (BY INDEX ROWID) OF 'RLM_
              SCHEDULE_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Bytes=18)
    
      20   19                         INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE
              _HEADERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      21   18                       TABLE ACCESS (BY INDEX ROWID) OF 'RLM_
              SCHEDULE_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Bytes=18)
    
      22   21                         INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE
              _HEADERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      23    6               TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_V
              ALUES' (TABLE) (Cost=3 Card=1 Bytes=60)
    
      24   23                 INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1'
               (INDEX (UNIQUE)) (Cost=2 Card=1)
    
      25    5             INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1' (IN
              DEX (UNIQUE)) (Cost=2 Card=1)
    
      26    4           TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_VALUE
              S' (TABLE) (Cost=3 Card=1 Bytes=60)
    
      27    3         TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHEDULE_HEADE
              RS_ALL' (TABLE) (Cost=2 Card=1 Bytes=14)
    
      28   27           INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE_HEADERS_U1' (
              INDEX (UNIQUE)) (Cost=1 Card=1)
    
      29   28             SORT (AGGREGATE)
      30   29               NESTED LOOPS
      31   30                 NESTED LOOPS (Cost=330 Card=1 Bytes=92)
      32   31                   NESTED LOOPS (ANTI) (Cost=318 Card=1 Bytes
              =64)
    
      33   32                     TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SC
              HEDULE_HEADERS_ALL' (TABLE) (Cost=316 Card=2 Bytes=82)
    
      34   33                       INDEX (RANGE SCAN) OF 'RLM_SCHEDULE_HE
              ADERS_N3' (INDEX) (Cost=2 Card=455)
    
      35   32                     TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SC
              HEDULE_HEADERS_ALL' (TABLE) (Cost=1 Card=1 Bytes=23)
    
      36   35                       INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE_H
              EADERS_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      37   31                   INDEX (RANGE SCAN) OF 'IDX$$_199350002' (I
              NDEX) (Cost=2 Card=37)
    
      38   30                 TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHEDU
              LE_LINES_ALL' (TABLE) (Cost=12 Card=1 Bytes=28)
    
      39   38                   NESTED LOOPS (Cost=5 Card=1 Bytes=81)
      40   39                     NESTED LOOPS (Cost=3 Card=1 Bytes=36)
      41   40                       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_C
              UST_ACCOUNTS' (TABLE) (Cost=2 Card=1 Bytes=10)
    
      42   41                         INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCO
              UNTS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      43   40                       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_P
              ARTIES' (TABLE) (Cost=1 Card=51223 Bytes=1331798)
    
      44   43                         INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U
              1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      45   39                     INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES
              _U2' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=45)
    
      46   38                   NESTED LOOPS (Cost=5 Card=1 Bytes=81)
      47   46                     NESTED LOOPS (Cost=3 Card=1 Bytes=36)
      48   47                       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_C
              UST_ACCOUNTS' (TABLE) (Cost=2 Card=1 Bytes=10)
    
      49   48                         INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCO
              UNTS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      50   47                       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_P
              ARTIES' (TABLE) (Cost=1 Card=51223 Bytes=1331798)
    
      51   50                         INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U
              1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      52   46                     INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES
              _U2' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=45)
    
      53    2       NESTED LOOPS
      54   53         NESTED LOOPS (Cost=362 Card=1 Bytes=661)
      55   54           NESTED LOOPS (Cost=359 Card=1 Bytes=601)
      56   55             NESTED LOOPS (OUTER) (Cost=356 Card=1 Bytes=541)
      57   56               NESTED LOOPS (Cost=15 Card=1 Bytes=185)
      58   57                 TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP
              _VALUES' (TABLE) (Cost=4 Card=2 Bytes=120)
    
      59   58                   INDEX (RANGE SCAN) OF 'IDX$$_123E00001' (I
              NDEX) (Cost=3 Card=2)
    
      60   57                 TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHEDU
              LE_LINES_ALL' (TABLE) (Cost=7 Card=1 Bytes=125)
    
      61   60                   INDEX (RANGE SCAN) OF 'RLM_SCHEDULE_LINE_C
              USTOM' (INDEX) (Cost=4 Card=19)
    
      62   56               VIEW PUSHED PREDICATE (Cost=341 Card=1 Bytes=3
              56)
    
      63   62                 FILTER
      64   63                   FILTER
      65   64                     TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SC
              HEDULE_LINES_ALL' (TABLE) (Cost=7 Card=1 Bytes=106)
    
      66   65                       INDEX (RANGE SCAN) OF 'IDX$$_199350001
              ' (INDEX) (Cost=4 Card=9)
    
      67   65                       TABLE ACCESS (BY INDEX ROWID) OF 'RLM_
              SCHEDULE_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Bytes=18)
    
      68   67                         INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE
              _HEADERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      69   63                   TABLE ACCESS (BY INDEX ROWID) OF 'RLM_SCHE
              DULE_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Bytes=14)
    
      70   69                     INDEX (UNIQUE SCAN) OF 'RLM_SCHEDULE_HEA
              DERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      71   70                       SORT (AGGREGATE)
      72   71                         NESTED LOOPS
      73   72                           NESTED LOOPS (Cost=330 Card=1 Byte
              s=84)
    
      74   73                             NESTED LOOPS (ANTI) (Cost=318 Ca
              rd=1 Bytes=56)
    
      75   74                               TABLE ACCESS (BY INDEX ROWID)
              OF 'RLM_SCHEDULE_HEADERS_ALL' (TABLE) (Cost=316 Card=2 Bytes
              =82)
    
      76   75                                 INDEX (RANGE SCAN) OF 'RLM_S
              CHEDULE_HEADERS_N3' (INDEX) (Cost=2 Card=455)
    
      77   74                               TABLE ACCESS (BY INDEX ROWID)
              OF 'RLM_SCHEDULE_HEADERS_ALL' (TABLE) (Cost=1 Card=3 Bytes=4
              5)
    
      78   77                                 INDEX (UNIQUE SCAN) OF 'RLM_
              SCHEDULE_HEADERS_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      79   73                             INDEX (RANGE SCAN) OF 'IDX$$_199
              350002' (INDEX) (Cost=2 Card=37)
    
      80   72                           TABLE ACCESS (BY INDEX ROWID) OF '
              RLM_SCHEDULE_LINES_ALL' (TABLE) (Cost=12 Card=1 Bytes=28)
    
      81   80                             NESTED LOOPS (Cost=5 Card=1 Byte
              s=81)
    
      82   81                               NESTED LOOPS (Cost=3 Card=1 By
              tes=36)
    
      83   82                                 TABLE ACCESS (BY INDEX ROWID
              ) OF 'HZ_CUST_ACCOUNTS' (TABLE) (Cost=2 Card=1 Bytes=10)
    
      84   83                                   INDEX (UNIQUE SCAN) OF 'HZ
              _CUST_ACCOUNTS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      85   82                                 TABLE ACCESS (BY INDEX ROWID
              ) OF 'HZ_PARTIES' (TABLE) (Cost=1 Card=51223 Bytes=1331798)
    
      86   85                                   INDEX (UNIQUE SCAN) OF 'HZ
              _PARTIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      87   81                               INDEX (RANGE SCAN) OF 'FND_LOO
              KUP_VALUES_U2' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=45)
    
      88   80                             NESTED LOOPS (Cost=5 Card=1 Byte
              s=81)
    
      89   88                               NESTED LOOPS (Cost=3 Card=1 By
              tes=36)
    
      90   89                                 TABLE ACCESS (BY INDEX ROWID
              ) OF 'HZ_CUST_ACCOUNTS' (TABLE) (Cost=2 Card=1 Bytes=10)
    
      91   90                                   INDEX (UNIQUE SCAN) OF 'HZ
              _CUST_ACCOUNTS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    
      92   89                                 TABLE ACCESS (BY INDEX ROWID
              ) OF 'HZ_PARTIES' (TABLE) (Cost=1 Card=51223 Bytes=1331798)
    
      93   92                                   INDEX (UNIQUE SCAN) OF 'HZ
              _PARTIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
    
      94   88                               INDEX (RANGE SCAN) OF 'FND_LOO
              KUP_VALUES_U2' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=45)
    
      95   55             TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_VAL
              UES' (TABLE) (Cost=3 Card=1 Bytes=60)
    
      96   95               INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1' (
              INDEX (UNIQUE)) (Cost=2 Card=1)
    
      97   54           INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1' (INDE
              X (UNIQUE)) (Cost=2 Card=1)
    
      98   53         TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_VALUES'
               (TABLE) (Cost=3 Card=1 Bytes=60)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
            398  recursive calls
              0  db block gets
       10769077  consistent gets
        3068075  physical reads
              0  redo size
          25716  bytes sent via SQL*Net to client
           8604  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
            121  rows processed
    
    SQL> 
  • 5. Re: Help in Performance Tunning
    Iordan Iotzov Expert
    Currently Being Moderated
    First, make sure that all statistics are up to date.

    There are a couple major reasons why a query execution is slow:

    1.     The Optimizer (CBO) does not pick the optimal, or even close to the optimal, execution plan because some of the assumptions it used turned out to be incorrect. You could follow “Tuning the Cardinality Feedback” http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf paper to resolve this situation.

    2.     The optimizer (CBO) picks the best plan, but the best plan is not fast enough. You can look at where time is spend and create DB structures, such as indexes and materialized views. A query rewrite should also be considered.

    Iordan Iotzov
    http://iiotzov.wordpress.com/

    Edited by: Iordan Iotzov on Dec 3, 2012 7:22 AM
  • 6. Re: Help in Performance Tunning
    Vishalaksha Newbie
    Currently Being Moderated
    Hi Iotzov/BluShadow,

    In the query I have found below two conditions which are causing the performance issue. When I remove these filters then the query completes in 3 second but with these filters which are required also it is taking more than 10 minutes as it involves the Index Range Scan.
    AND B.ITEM_DETAIL_TYPE(+) < 3
    AND A.ITEM_DETAIL_TYPE < 3
    Is there any workaround in this particular case? Any help will be appreciated.

    Thanks
    Vishalaksha
  • 7. Re: Help in Performance Tunning
    sb92075 Guru
    Currently Being Moderated
    Vishalaksha wrote:
    Hi Iotzov/BluShadow,

    In the query I have found below two conditions which are causing the performance issue. When I remove these filters then the query completes in 3 second but with these filters which are required also it is taking more than 10 minutes as it involves the Index Range Scan.
    AND B.ITEM_DETAIL_TYPE(+) < 3
    AND A.ITEM_DETAIL_TYPE < 3
    Is there any workaround in this particular case? Any help will be appreciated.

    Thanks
    Vishalaksha
    rebuild the index
  • 8. Re: Help in Performance Tunning
    Iordan Iotzov Expert
    Currently Being Moderated
    It looks like cardinality estimation issue…

    What percentage of the A table would be returned after applying A.ITEM_DETAIL_TYPE < 3 filter?
     SELECT COUNT(*) FROM .. A WHERE A.ITEM_DETAIL_TYPE < 3 
    What percentage Oracle CBO thinks it would be returned returned after applying A.ITEM_DETAIL_TYPE < 3 filter?
    Look at the cardinality of the execution plan for
     SELECT * FROM .. A WHERE A.ITEM_DETAIL_TYPE < 3
    Are those numbers very different?

    If they are very different, you can consider creating histograms (if not available) or forcing dynamic sampling on table A.

    You can do the same exercise for table B.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 9. Re: Help in Performance Tunning
    Vishalaksha Newbie
    Currently Being Moderated
    Here are the results
    SELECT COUNT(*) FROM RLM_SCHEDULE_LINES_ALL A WHERE A.ITEM_DETAIL_TYPE < 3;
    
    Returned 25307650 records
    Here is explain plan
    
    SELECT * FROM RLM_SCHEDULE_LINES_ALL A WHERE A.ITEM_DETAIL_TYPE < 3;
    
    Description                                         Object owner       Object name                   Cost        Cardinality       Bytes
    
    SELECT STATEMENT, GOAL = ALL_ROWS                                                                    670950       1497937          618647981
      TABLE ACCESS FULL                                 RLM                RLM_SCHEDULE_LINES_ALL        670950       1497937          618647981
    Please suggest.

    Thanks
    Vishalaksha
  • 10. Re: Help in Performance Tunning
    905562 Explorer
    Currently Being Moderated
    Your results show the optimizer is getting that estimate particularly wrong.

    As Iordan Iotzov said, consider a change in stats and/or forcing a dynamic sample.


    As to why it's faster without it, I'd suspect (though can't confirm without a plan) it's not touching that table at all and just running through the index. Your predicate of <3 would likely change that. Conjecture on my part though.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points