10 Replies Latest reply: Dec 6, 2012 5:12 AM by 905562 RSS

    Help in Performance Tunning

    Vishalaksha
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.