Forum Stats

  • 3,875,787 Users
  • 2,266,971 Discussions
  • 7,912,337 Comments

Discussions

Query is taking long time for completion

User_G2K3P
User_G2K3P Member Posts: 73 Blue Ribbon
edited Oct 3, 2022 6:55AM in SQL & PL/SQL
«13

Answers

  • Saubhik
    Saubhik Member Posts: 5,937 Gold Crown

    Your plan is NOT readable, put that and the SQL query within the code block.

    The plean is taken from 1046 session level trace with tkprof, do you have a plan with display_cursor (statistic level all).

  • User_G2K3P
    User_G2K3P Member Posts: 73 Blue Ribbon
  • Saubhik
    Saubhik Member Posts: 5,937 Gold Crown
    edited Sep 27, 2022 3:17PM

    Not went through all the trace files (wait events missing because of level you set), but I noticed one thing you are using mtl_system_items_b within the sub query and selecting three different columns with same join condition. Table accessed three times (look at the top of the plan). You can write that differently by placing those three columns directly in the query with suitable joins.

    Also, can you pleas post a plan with DBMS_XPLAN.DISPLAY_CURSOR with statics level all.

    EDIT: What is hr_locations used for three times? not select anything from this table and outer joined.

  • Paulzip
    Paulzip Member Posts: 8,811 Blue Diamond

    I wouldn't hold your breath for answer if you can't even be bothered format your query and execution plan with a monospaced font, so it is readable.

  • User_G2K3P
    User_G2K3P Member Posts: 73 Blue Ribbon

    in toad from sql id i can paste explain plan but the issue is that again it will come in that format only.i don't know how to make it as a readable format and because of that i uploaded trace file for understanding.

        (SELECT items.preprocessing_lead_time

         FROM apps.mtl_system_items_b items

         WHERE   items.inventory_item_id = pll.item_id

            AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)

          "PreProcessing_LT",

    i placed all the three column in one select but it throw error as "too many values".coming to HR_LOCATION,I commented out that table because it is not selecting any column in select clause and after that also i feel SELECT clause is only problem.if i pulled out trace with commenting subquery inside select it return with 5M BG but with all the select clause it return 30M BG

  • BluShadow
    BluShadow Member, Moderator Posts: 42,589 Red Diamond

    Note for formatting...

    - In the comments editor box, type a character

    - Highlight that character and click the paragraph mark to the left of it

    - Click the double quotes on that popup to get quoting options...

    - Select the </> Code Block option

    - Now that your character is inside a code block, go and copy the code/text you want from your application, and then highlight your single character inside that code block and paste to overwrite it.

    Your code should now be inserted and formatted generally correctly (there may be some additional blank lines - that's a glitch in the forum software unfortunately).

    If you try and paste your code in first and then highlight that and mark it up as Code Block, then that can affect the formatting as the original paste of the code will be 'interpreted' in the editor and sometimes changed so that whitespace etc. isn't kept. Always better to create a code block first and then paste code into it.

  • User_G2K3P
    User_G2K3P Member Posts: 73 Blue Ribbon
    New sql after modification
    SELECT 
             DISTINCT
           pll.po_line_id,
           ploc.line_location_id,
           (SELECT ptl.line_type
              FROM apps.po_line_types_tl ptl
             WHERE ptl.line_type_id = pll.line_type_id AND ptl.LANGUAGE = 'US')
               "Line_Type",
           ploc.quantity_accepted,
           NULL
               release_approved_date,
           NULL
               release_date,
           NULL
               release_hold_flag,
           NULL
               release_type,
           DECODE (ploc.po_release_id, NULL, NULL, ploc.quantity)
               released_quantity,
           (SELECT /*+ use_nl (msib ploc) */ items.preprocessing_lead_time
              FROM apps.mtl_system_items_b items
             WHERE     items.inventory_item_id = pll.item_id
                   AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
               "PreProcessing_LT",
           (SELECT /*+ use_nl (msib ploc) */items.full_lead_time
              FROM apps.mtl_system_items_b items
             WHERE     items.inventory_item_id = pll.item_id
                   AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
               "Processing_LT",
           (SELECT /*+ use_nl (msib ploc) */ items.postprocessing_lead_time
              FROM apps.mtl_system_items_b items
             WHERE     items.inventory_item_id = pll.item_id
                   AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
               "PostProcessing_LT",
           ploc.firm_status_lookup_code,
           NVL (
               (SELECT pla.promised_date
                  FROM apps.po_line_locations_archive_all pla
                 WHERE     pla.po_header_id = pha.po_header_id
                       AND pla.po_line_id = pll.po_line_id
                       AND pla.line_location_id = ploc.line_location_id
                       AND pla.revision_num =
                           (SELECT MIN (revision_num)
                              FROM apps.po_line_locations_archive_all plla2
                             WHERE     plla2.promised_date IS NOT NULL
                                   AND plla2.line_location_id =
                                       ploc.line_location_id)),
               ploc.promised_date)
               "Original_Promise_Date",
           (SELECT items.long_description
              FROM apps.mtl_system_items_tl items
             WHERE     items.inventory_item_id = pll.item_id
                   AND items.organization_id IN
                           (SELECT fin.inventory_organization_id
                              FROM apps.financials_system_params_all fin
                             WHERE fin.org_id = pha.org_id)
                   AND items.LANGUAGE = 'US')
               "Item_Long_Description",
           NVL (ploc.approved_flag, 'N')
               approved_code,
           pvs.country
               "Supplier_Site_Country",
           pll.note_to_vendor,
             NVL (ploc.quantity, 0)
           - NVL (ploc.quantity_cancelled, 0)
           - NVL (ploc.quantity_received, 0) * ploc.price_override
               "Shipment_Amount",
           ploc.attribute4
               "PO_Ship_Date",
           (SELECT meaning
              FROM apps.fnd_lookup_values
             WHERE     lookup_type = 'SHIP_METHOD'
                   AND lookup_code = ploc.attribute9
                   AND language = 'US')
               "Ship_Method",
           (SELECT prla.note_to_receiver
              FROM apps.po_req_distributions_all  prda
                   INNER JOIN apps.po_requisition_lines_all prla
                       ON prda.requisition_line_id = prla.requisition_line_id
             WHERE prda.distribution_id = pdi.req_distribution_id)
               "Note_To_Receiver",
           DECODE (pha.USER_HOLD_FLAG, 'Y', 'Y', pll.USER_HOLD_FLAG)
               "Hold_Flag",
           (SELECT ABC_CLASS_NAME
              FROM APPS.MTL_ABC_ASSIGNMENT_GROUPS  ASG
                   INNER JOIN APPS.MTL_ABC_ASSIGNMENTS ASSI
                       ON ASG.ASSIGNMENT_GROUP_ID = ASSI.ASSIGNMENT_GROUP_ID
                   INNER JOIN APPS.MTL_ABC_CLASSES classes
                       ON ASSI.ABC_CLASS_ID = classes.ABC_CLASS_ID
             WHERE     ASG.organization_id = ploc.SHIP_TO_ORGANIZATION_ID
                   AND ASG.ASSIGNMENT_GROUP_NAME = 'MIN ABC Assignment'
                   AND ASSI.inventory_item_id = pll.item_id)
               ABCClass,
           (SELECT CONCATENATED_SEGMENTS AS charge_accountsfrom
              FROM apps.gl_code_combinations_kfv gcc
             WHERE gcc.code_combination_id = pdi.code_combination_id)
               AS charge_accounts
      FROM apps.po_headers_all         pha,
           apps.po_lines_all           pll,
           apps.po_line_locations_all  ploc,
           apps.po_distributions_all   pdi,
           --       apps.per_all_people_f       papf,
           --    apps.AP_SUPPLIERS           pv,
           apps.AP_SUPPLIER_SITES_ALL  pvs,
           --  apps.AP_SUPPLIER_CONTACTS   pvc,
           --       apps.ap_terms               apt,
           --       apps.po_lookup_codes        plc1,
           --       apps.po_lookup_codes        plc2,
           -- apps.hr_locations           hlv_line_ship_to,
           --       apps.hr_locations           hlv_ship_to,
           --       apps.hr_locations           hlv_bill_to,
           apps.hr_organization_units  hou
     --       ,
     --       apps.hr_locations_no_join   loc,
     --       apps.hr_locations_all_tl    hrl1,
     --       apps.hr_locations_all_tl    hrl2
     WHERE     1 = 1
           AND pll.po_header_id(+) = pha.po_header_id
           AND ploc.po_line_id(+) = pll.po_line_id
           AND pdi.line_location_id(+) = ploc.line_location_id
           AND ploc.shipment_type IN ('STANDARD', 'PLANNED')
           /*  AND papf.person_id(+) = pha.agent_id
             AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                     AND papf.effective_end_date
             AND papf.employee_number IS NOT NULL
             AND pv.vendor_id(+) = pha.vendor_id */
           AND pvs.vendor_site_id(+) = pha.vendor_site_id
           AND pvs.ORG_ID = pha.org_id
           --  AND pvc.vendor_contact_id(+) = pha.vendor_contact_id
           --       AND apt.term_id(+) = pha.terms_id
           /*   AND plc1.lookup_code(+) = pha.fob_lookup_code
              AND plc1.lookup_type(+) = 'FOB'
              AND plc2.lookup_code(+) = pha.freight_terms_lookup_code
              AND plc2.lookup_type(+) = 'FREIGHT TERMS'
              AND hlv_line_ship_to.location_id(+) = ploc.ship_to_location_id
              AND hlv_ship_to.location_id(+) = pha.ship_to_location_id
              AND hlv_bill_to.location_id(+) = pha.bill_to_location_id */
           AND hou.organization_id = pha.org_id
           --  AND hou.location_id = loc.location_id(+)
           /*  AND hrl1.location_id(+) = pha.ship_to_location_id
             AND hrl1.LANGUAGE(+) = 'US'
             AND hrl2.location_id(+) = pha.bill_to_location_id
             AND hrl2.LANGUAGE(+) = 'US' */
           AND hou.organization_id IN (2763)
           AND NVL (pha.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
           AND NVL (pll.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
           AND NVL (ploc.cancel_flag, 'N') = 'N'
           AND pha.authorization_status IN
                   ('APPROVED', 'REQUIRES REAPPROVAL', 'IN PROCESS');
    
    Shortly i wil upload dbms.xplan
    
  • User_G2K3P
    User_G2K3P Member Posts: 73 Blue Ribbon


    SELECT       DISTINCT     

       pll.po_line_id,        ploc.line_location_id,        (SELECT 
    ptl.line_type           FROM apps.po_line_types_tl ptl          WHERE 
    ptl.line_type_id = pll.line_type_id AND ptl.LANGUAGE = 'US')            
    "Line_Type",        ploc.quantity_accepted,        NULL            
    release_approved_date,        NULL            release_date,        NULL 
               release_hold_flag,        NULL            release_type,      
      DECODE (ploc.po_release_id, NULL, NULL, ploc.quantity)            
    released_quantity,        (SELECT /*+ use_nl (msib ploc) */ 
    items.preprocessing_lead_time           FROM apps.mtl_system_items_b 
    items          WHERE     items.inventory_item_id = pll.item_id          
          AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)         
       "PreProcessing_LT",        (SELECT /*+ use_nl (msib ploc) 
    */items.full_lead_time           FROM apps.mtl_system_items_b items     
         WHERE     items.inventory_item_i
     
    Plan hash value: 2509098827
     
    -------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name                           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                                |        |       | 75331 (100)|          |       |       |          |
    |   1 |  TABLE ACCESS BY INDEX ROWID       | PO_LINE_TYPES_TL               |      1 |    32 |     2   (0)| 00:00:01 |       |       |          |
    |*  2 |   INDEX UNIQUE SCAN                | PO_LINE_TYPES_TL_U1            |      1 |       |     1   (0)| 00:00:01 |       |       |          |
    |   3 |  TABLE ACCESS BY INDEX ROWID       | MTL_SYSTEM_ITEMS_B             |      1 |    13 |     4   (0)| 00:00:01 |       |       |          |
    |*  4 |   INDEX UNIQUE SCAN                | MTL_SYSTEM_ITEMS_B_U1          |      1 |       |     3   (0)| 00:00:01 |       |       |          |
    |   5 |  TABLE ACCESS BY INDEX ROWID       | MTL_SYSTEM_ITEMS_B             |      1 |    13 |     4   (0)| 00:00:01 |       |       |          |
    |*  6 |   INDEX UNIQUE SCAN                | MTL_SYSTEM_ITEMS_B_U1          |      1 |       |     3   (0)| 00:00:01 |       |       |          |
    |   7 |  TABLE ACCESS BY INDEX ROWID       | MTL_SYSTEM_ITEMS_B             |      1 |    14 |     4   (0)| 00:00:01 |       |       |          |
    |*  8 |   INDEX UNIQUE SCAN                | MTL_SYSTEM_ITEMS_B_U1          |      1 |       |     3   (0)| 00:00:01 |       |       |          |
    |*  9 |  TABLE ACCESS BY INDEX ROWID       | PO_LINE_LOCATIONS_ARCHIVE_ALL  |      1 |    27 |     3   (0)| 00:00:01 |       |       |          |
    |* 10 |   INDEX UNIQUE SCAN                | PO_LINE_LOCATIONS_ARCHIVE_U1   |      1 |       |     2   (0)| 00:00:01 |       |       |          |
    |  11 |    SORT AGGREGATE                  |                                |      1 |    15 |            |          |       |       |          |
    |* 12 |     TABLE ACCESS BY INDEX ROWID    | PO_LINE_LOCATIONS_ARCHIVE_ALL  |      1 |    15 |     5   (0)| 00:00:01 |       |       |          |
    |* 13 |      INDEX RANGE SCAN              | PO_LINE_LOCATIONS_ARCHIVE_U1   |      2 |       |     3   (0)| 00:00:01 |       |       |          |
    |  14 |  TABLE ACCESS BY INDEX ROWID       | MTL_SYSTEM_ITEMS_TL            |      1 |    34 |     5   (0)| 00:00:01 |       |       |          |
    |* 15 |   INDEX RANGE SCAN                 | MTL_SYSTEM_ITEMS_TL_U1         |      1 |       |     4   (0)| 00:00:01 |       |       |          |
    |* 16 |    TABLE ACCESS BY INDEX ROWID     | FINANCIALS_SYSTEM_PARAMS_ALL   |      1 |    10 |     2   (0)| 00:00:01 |       |       |          |
    |* 17 |     INDEX SKIP SCAN                | FINANCIALS_SYSTEM_PARAMS_U1    |      1 |       |     1   (0)| 00:00:01 |       |       |          |
    |* 18 |  TABLE ACCESS BY INDEX ROWID       | FND_LOOKUP_VALUES              |      1 |    60 |     5   (0)| 00:00:01 |       |       |          |
    |* 19 |   INDEX RANGE SCAN                 | FND_LOOKUP_VALUES_X99          |      1 |       |     4   (0)| 00:00:01 |       |       |          |
    |  20 |  NESTED LOOPS                      |                                |      1 |    28 |     5   (0)| 00:00:01 |       |       |          |
    |  21 |   TABLE ACCESS BY INDEX ROWID      | PO_REQ_DISTRIBUTIONS_ALL       |      1 |    12 |     3   (0)| 00:00:01 |       |       |          |
    |* 22 |    INDEX UNIQUE SCAN               | PO_REQ_DISTRIBUTIONS_U1        |      1 |       |     2   (0)| 00:00:01 |       |       |          |
    |  23 |   TABLE ACCESS BY INDEX ROWID      | PO_REQUISITION_LINES_ALL       |      1 |    16 |     2   (0)| 00:00:01 |       |       |          |
    |* 24 |    INDEX UNIQUE SCAN               | PO_REQUISITION_LINES_U1        |      1 |       |     1   (0)| 00:00:01 |       |       |          |
    |  25 |  NESTED LOOPS                      |                                |      1 |    55 |     5   (0)| 00:00:01 |       |       |          |
    |  26 |   NESTED LOOPS                     |                                |      1 |    45 |     4   (0)| 00:00:01 |       |       |          |
    |  27 |    TABLE ACCESS BY INDEX ROWID     | MTL_ABC_ASSIGNMENT_GROUPS      |      1 |    29 |     2   (0)| 00:00:01 |       |       |          |
    |* 28 |     INDEX UNIQUE SCAN              | MTL_ABC_ASSIGNMENT_GROUPS_U2   |      1 |       |     1   (0)| 00:00:01 |       |       |          |
    |  29 |    TABLE ACCESS BY INDEX ROWID     | MTL_ABC_ASSIGNMENTS            |      1 |    16 |     3   (0)| 00:00:01 |       |       |          |
    |* 30 |     INDEX UNIQUE SCAN              | MTL_ABC_ASSIGNMENTS_U1         |      1 |       |     2   (0)| 00:00:01 |       |       |          |
    |  31 |   TABLE ACCESS BY INDEX ROWID      | MTL_ABC_CLASSES                |      1 |    10 |     1   (0)| 00:00:01 |       |       |          |
    |* 32 |    INDEX UNIQUE SCAN               | MTL_ABC_CLASSES_U1             |      1 |       |     0   (0)|          |       |       |          |
    |  33 |  TABLE ACCESS BY INDEX ROWID       | GL_CODE_COMBINATIONS           |      1 |    49 |     3   (0)| 00:00:01 |       |       |          |
    |* 34 |   INDEX UNIQUE SCAN                | GL_CODE_COMBINATIONS_U1        |      1 |       |     2   (0)| 00:00:01 |       |       |          |
    |  35 |  HASH UNIQUE                       |                                |   4476 |   804K| 75331   (1)| 00:06:17 |  1817K|  1003K|          |
    |  36 |   NESTED LOOPS OUTER               |                                |   4476 |   804K| 75330   (1)| 00:06:17 |       |       |          |
    |  37 |    NESTED LOOPS                    |                                |   4432 |   722K| 62440   (1)| 00:05:13 |       |       |          |
    |  38 |     NESTED LOOPS                   |                                |   9548 |   979K| 27835   (1)| 00:02:20 |       |       |          |
    |* 39 |      HASH JOIN                     |                                |   2940 |   209K| 17462   (1)| 00:01:28 |  1687K|  1687K|     1/0/0|
    |  40 |       NESTED LOOPS                 |                                |   2654 |   101K|  3932   (1)| 00:00:20 |       |       |          |
    |  41 |        NESTED LOOPS                |                                |      1 |    26 |     2   (0)| 00:00:01 |       |       |          |
    |* 42 |         INDEX UNIQUE SCAN          | HR_ALL_ORGANIZATION_UNTS_TL_PK |      1 |    17 |     1   (0)| 00:00:01 |       |       |          |
    |* 43 |         TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS      |      1 |     9 |     1   (0)| 00:00:01 |       |       |          |
    |* 44 |          INDEX UNIQUE SCAN         | HR_ORGANIZATION_UNITS_PK       |      1 |       |     0   (0)|          |       |       |          |
    |  45 |        TABLE ACCESS BY INDEX ROWID | AP_SUPPLIER_SITES_ALL          |   2654 | 34502 |  3930   (1)| 00:00:20 |       |       |          |
    |* 46 |         INDEX SKIP SCAN            | AP_SUPPLIER_SITES_U2           |   2654 |       |  2662   (1)| 00:00:14 |       |       |          |
    |* 47 |       TABLE ACCESS BY INDEX ROWID  | PO_HEADERS_ALL                 |  17426 |   578K| 13530   (1)| 00:01:08 |       |       |          |
    |* 48 |        INDEX SKIP SCAN             | PO_HEADERS_ALL_X3              |  37674 |       |   352   (1)| 00:00:02 |       |       |          |
    |* 49 |      TABLE ACCESS BY INDEX ROWID   | PO_LINES_ALL                   |      3 |    96 |     5   (0)| 00:00:01 |       |       |          |
    |* 50 |       INDEX RANGE SCAN             | PO_LINES_U2                    |      5 |       |     2   (0)| 00:00:01 |       |       |          |
    |* 51 |     TABLE ACCESS BY INDEX ROWID    | PO_LINE_LOCATIONS_ALL          |      1 |    62 |     4   (0)| 00:00:01 |       |       |          |
    |* 52 |      INDEX RANGE SCAN              | PO_LINE_LOCATIONS_N15          |      2 |       |     3   (0)| 00:00:01 |       |       |          |
    |  53 |    TABLE ACCESS BY INDEX ROWID     | PO_DISTRIBUTIONS_ALL           |      1 |    17 |     3   (0)| 00:00:01 |       |       |          |
    |* 54 |     INDEX RANGE SCAN               | PO_DISTRIBUTIONS_N1            |      1 |       |     2   (0)| 00:00:01 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$1B7D9AE9 / [email protected]$7
       2 - SEL$1B7D9AE9 / [email protected]$7
       3 - SEL$8        / [email protected]$8
       4 - SEL$8        / [email protected]$8
       5 - SEL$9        / [email protected]$9
       6 - SEL$9        / [email protected]$9
       7 - SEL$10       / [email protected]$10
       8 - SEL$10       / [email protected]$10
       9 - SEL$11       / [email protected]$11
      10 - SEL$11       / [email protected]$11
      11 - SEL$12      
      12 - SEL$12       / [email protected]$12
      13 - SEL$12       / [email protected]$12
      14 - SEL$13       / [email protected]$13
      15 - SEL$13       / [email protected]$13
      16 - SEL$14       / [email protected]$14
      17 - SEL$14       / [email protected]$14
      18 - SEL$45068F18 / [email protected]$17
      19 - SEL$45068F18 / [email protected]$17
      20 - SEL$904DB506
      21 - SEL$904DB506 / [email protected]$1
      22 - SEL$904DB506 / [email protected]$1
      23 - SEL$904DB506 / [email protected]$1
      24 - SEL$904DB506 / [email protected]$1
      25 - SEL$61342203
      27 - SEL$61342203 / [email protected]$2
      28 - SEL$61342203 / [email protected]$2
      29 - SEL$61342203 / [email protected]$2
      30 - SEL$61342203 / [email protected]$2
      31 - SEL$61342203 / [email protected]$3
      32 - SEL$61342203 / [email protected]$3
      33 - SEL$5EC70623 / [email protected]$21
      34 - SEL$5EC70623 / [email protected]$21
      35 - SEL$0DF845C6
      42 - SEL$0DF845C6 / [email protected]$24
      43 - SEL$0DF845C6 / [email protected]$22
      44 - SEL$0DF845C6 / [email protected]$22
      45 - SEL$0DF845C6 / [email protected]$4
      46 - SEL$0DF845C6 / [email protected]$4
      47 - SEL$0DF845C6 / [email protected]$4
      48 - SEL$0DF845C6 / [email protected]$4
      49 - SEL$0DF845C6 / [email protected]$4
      50 - SEL$0DF845C6 / [email protected]$4
      51 - SEL$0DF845C6 / [email protected]$4
      52 - SEL$0DF845C6 / [email protected]$4
      53 - SEL$0DF845C6 / [email protected]$4
      54 - SEL$0DF845C6 / [email protected]$4
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
          DB_VERSION('11.2.0.4')
          OPT_PARAM('_b_tree_bitmap_plans' 'false')
          OPT_PARAM('_fast_full_scan_enabled' 'false')
          OPT_PARAM('_or_expand_nvl_predicate' 'false')
          OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
          OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
          OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
          OPT_PARAM('_fix_control' '7449971:0 13020272:0 14254795:0 14127824:0 14707009:0')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1B7D9AE9")
          MERGE(@"SEL$68B588A0")
          OUTLINE_LEAF(@"SEL$8")
          OUTLINE_LEAF(@"SEL$9")
          OUTLINE_LEAF(@"SEL$10")
          OUTLINE_LEAF(@"SEL$12")
          OUTLINE_LEAF(@"SEL$11")
          OUTLINE_LEAF(@"SEL$14")
          OUTLINE_LEAF(@"SEL$13")
          OUTLINE_LEAF(@"SEL$45068F18")
          MERGE(@"SEL$97CCBC9E")
          OUTLINE_LEAF(@"SEL$904DB506")
          MERGE(@"SEL$1")
          OUTLINE_LEAF(@"SEL$61342203")
          MERGE(@"SEL$64EAE176")
          OUTLINE_LEAF(@"SEL$5EC70623")
          MERGE(@"SEL$21")
          OUTLINE_LEAF(@"SEL$0DF845C6")
          MERGE(@"SEL$C0285F1C")
          OUTLINE(@"SEL$5")
          OUTLINE(@"SEL$68B588A0")
          MERGE(@"SEL$7")
          OUTLINE(@"SEL$15")
          OUTLINE(@"SEL$97CCBC9E")
          MERGE(@"SEL$17")
          OUTLINE(@"SEL$18")
          OUTLINE(@"SEL$1")
          OUTLINE(@"SEL$19")
          OUTLINE(@"SEL$64EAE176")
          MERGE(@"SEL$2")
          OUTLINE(@"SEL$20")
          OUTLINE(@"SEL$21")
          OUTLINE(@"SEL$1702AD0B")
          OUTER_JOIN_TO_INNER(@"SEL$4" "PLL"@"SEL$4")
          OUTER_JOIN_TO_INNER(@"SEL$4" "PLOC"@"SEL$4")
          OUTER_JOIN_TO_INNER(@"SEL$4" "PVS"@"SEL$4")
          OUTLINE(@"SEL$C0285F1C")
          MERGE(@"SEL$43F09110")
          OUTLINE(@"SEL$6")
          OUTLINE(@"SEL$7")
          OUTLINE(@"SEL$16")
          OUTLINE(@"SEL$17")
          OUTLINE(@"SEL$3")
          OUTLINE(@"SEL$2")
          OUTLINE(@"SEL$4")
          OUTLINE(@"SEL$22")
          OUTLINE(@"SEL$43F09110")
          MERGE(@"SEL$24")
          OUTLINE(@"SEL$23")
          OUTLINE(@"SEL$24")
          INDEX(@"SEL$0DF845C6" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$24" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" 
                  "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE" "HR_ALL_ORGANIZATION_UNITS_TL"."ZD_EDITION_NAME"))
          INDEX_RS_ASC(@"SEL$0DF845C6" "HAO"@"SEL$22" ("HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID"))
          INDEX_SS(@"SEL$0DF845C6" "PVS"@"SEL$4" ("AP_SUPPLIER_SITES_ALL"."VENDOR_ID" "AP_SUPPLIER_SITES_ALL"."VENDOR_SITE_CODE" 
                  "AP_SUPPLIER_SITES_ALL"."ORG_ID"))
          INDEX_SS(@"SEL$0DF845C6" "PHA"@"SEL$4" ("PO_HEADERS_ALL"."APPROVED_FLAG" "PO_HEADERS_ALL"."ORG_ID" 
                  "PO_HEADERS_ALL"."AUTHORIZATION_STATUS" "PO_HEADERS_ALL"."TYPE_LOOKUP_CODE"))
          INDEX_RS_ASC(@"SEL$0DF845C6" "PLL"@"SEL$4" ("PO_LINES_ALL"."PO_HEADER_ID" "PO_LINES_ALL"."LINE_NUM"))
          INDEX_RS_ASC(@"SEL$0DF845C6" "PLOC"@"SEL$4" ("PO_LINE_LOCATIONS_ALL"."PO_LINE_ID" "PO_LINE_LOCATIONS_ALL"."CONSIGNED_FLAG" 
                  "PO_LINE_LOCATIONS_ALL"."QUANTITY" "PO_LINE_LOCATIONS_ALL"."AMOUNT" "PO_LINE_LOCATIONS_ALL"."AMOUNT_CANCELLED" 
                  "PO_LINE_LOCATIONS_ALL"."QUANTITY_CANCELLED" "PO_LINE_LOCATIONS_ALL"."PRICE_OVERRIDE"))
          INDEX_RS_ASC(@"SEL$0DF845C6" "PDI"@"SEL$4" ("PO_DISTRIBUTIONS_ALL"."LINE_LOCATION_ID"))
          LEADING(@"SEL$0DF845C6" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$24" "HAO"@"SEL$22" "PVS"@"SEL$4" "PHA"@"SEL$4" "PLL"@"SEL$4" "PLOC"@"SEL$4" 
                  "PDI"@"SEL$4")
          USE_NL(@"SEL$0DF845C6" "HAO"@"SEL$22")
          USE_NL(@"SEL$0DF845C6" "PVS"@"SEL$4")
          USE_HASH(@"SEL$0DF845C6" "PHA"@"SEL$4")
          USE_NL(@"SEL$0DF845C6" "PLL"@"SEL$4")
          USE_NL(@"SEL$0DF845C6" "PLOC"@"SEL$4")
          USE_NL(@"SEL$0DF845C6" "PDI"@"SEL$4")
          USE_HASH_AGGREGATION(@"SEL$0DF845C6")
          INDEX_RS_ASC(@"SEL$5EC70623" "GL_CODE_COMBINATIONS"@"SEL$21" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))
          INDEX_RS_ASC(@"SEL$61342203" "ASG"@"SEL$2" ("MTL_ABC_ASSIGNMENT_GROUPS"."ORGANIZATION_ID" 
                  "MTL_ABC_ASSIGNMENT_GROUPS"."ASSIGNMENT_GROUP_NAME"))
          INDEX_RS_ASC(@"SEL$61342203" "ASSI"@"SEL$2" ("MTL_ABC_ASSIGNMENTS"."ASSIGNMENT_GROUP_ID" "MTL_ABC_ASSIGNMENTS"."INVENTORY_ITEM_ID"))
          INDEX_RS_ASC(@"SEL$61342203" "CLASSES"@"SEL$3" ("MTL_ABC_CLASSES"."ABC_CLASS_ID"))
          LEADING(@"SEL$61342203" "ASG"@"SEL$2" "ASSI"@"SEL$2" "CLASSES"@"SEL$3")
          USE_NL(@"SEL$61342203" "ASSI"@"SEL$2")
          USE_NL(@"SEL$61342203" "CLASSES"@"SEL$3")
          INDEX_RS_ASC(@"SEL$904DB506" "PRDA"@"SEL$1" ("PO_REQ_DISTRIBUTIONS_ALL"."DISTRIBUTION_ID"))
          INDEX_RS_ASC(@"SEL$904DB506" "PRLA"@"SEL$1" ("PO_REQUISITION_LINES_ALL"."REQUISITION_LINE_ID"))
          LEADING(@"SEL$904DB506" "PRDA"@"SEL$1" "PRLA"@"SEL$1")
          USE_NL(@"SEL$904DB506" "PRLA"@"SEL$1")
          INDEX_RS_ASC(@"SEL$45068F18" "FND_LOOKUP_VALUES"@"SEL$17" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."LOOKUP_CODE" 
                  "FND_LOOKUP_VALUES"."LANGUAGE"))
          INDEX_RS_ASC(@"SEL$13" "ITEMS"@"SEL$13" ("MTL_SYSTEM_ITEMS_TL"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_TL"."ORGANIZATION_ID" 
                  "MTL_SYSTEM_ITEMS_TL"."LANGUAGE"))
          PUSH_SUBQ(@"SEL$14")
          INDEX_RS_ASC(@"SEL$11" "PLA"@"SEL$11" ("PO_LINE_LOCATIONS_ARCHIVE_ALL"."LINE_LOCATION_ID" 
                  "PO_LINE_LOCATIONS_ARCHIVE_ALL"."REVISION_NUM"))
          PUSH_SUBQ(@"SEL$12")
          INDEX_RS_ASC(@"SEL$10" "ITEMS"@"SEL$10" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
          INDEX_RS_ASC(@"SEL$9" "ITEMS"@"SEL$9" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
          INDEX_RS_ASC(@"SEL$8" "ITEMS"@"SEL$8" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
          INDEX_RS_ASC(@"SEL$1B7D9AE9" "PO_LINE_TYPES_TL"@"SEL$7" ("PO_LINE_TYPES_TL"."LINE_TYPE_ID" "PO_LINE_TYPES_TL"."LANGUAGE" 
                  "PO_LINE_TYPES_TL"."ZD_EDITION_NAME"))
          INDEX_RS_ASC(@"SEL$12" "PLLA2"@"SEL$12" ("PO_LINE_LOCATIONS_ARCHIVE_ALL"."LINE_LOCATION_ID" 
                  "PO_LINE_LOCATIONS_ARCHIVE_ALL"."REVISION_NUM"))
          INDEX_SS(@"SEL$14" "FIN"@"SEL$14" ("FINANCIALS_SYSTEM_PARAMS_ALL"."SET_OF_BOOKS_ID" "FINANCIALS_SYSTEM_PARAMS_ALL"."ORG_ID"))
          END_OUTLINE_DATA
      */
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("LINE_TYPE_ID"=:B1 AND "LANGUAGE"='US' AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE')
       4 - access("ITEMS"."INVENTORY_ITEM_ID"=:B1 AND "ITEMS"."ORGANIZATION_ID"=:B2)
       6 - access("ITEMS"."INVENTORY_ITEM_ID"=:B1 AND "ITEMS"."ORGANIZATION_ID"=:B2)
       8 - access("ITEMS"."INVENTORY_ITEM_ID"=:B1 AND "ITEMS"."ORGANIZATION_ID"=:B2)
       9 - filter(("PLA"."PO_LINE_ID"=:B1 AND "PLA"."PO_HEADER_ID"=:B2))
      10 - access("PLA"."LINE_LOCATION_ID"=:B1 AND "PLA"."REVISION_NUM"=)
      12 - filter("PLLA2"."PROMISED_DATE" IS NOT NULL)
      13 - access("PLLA2"."LINE_LOCATION_ID"=:B1)
      15 - access("ITEMS"."INVENTORY_ITEM_ID"=:B1 AND "ITEMS"."LANGUAGE"='US')
           filter(("ITEMS"."LANGUAGE"='US' AND  IS NOT NULL))
      16 - filter("FIN"."INVENTORY_ORGANIZATION_ID"=:B1)
      17 - access("FIN"."ORG_ID"=:B1)
           filter("FIN"."ORG_ID"=:B1)
      18 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
      19 - access("LOOKUP_TYPE"='SHIP_METHOD' AND "LOOKUP_CODE"=:B1 AND "LANGUAGE"='US')
      22 - access("PRDA"."DISTRIBUTION_ID"=:B1)
      24 - access("PRDA"."REQUISITION_LINE_ID"="PRLA"."REQUISITION_LINE_ID")
      28 - access("ASG"."ORGANIZATION_ID"=:B1 AND "ASG"."ASSIGNMENT_GROUP_NAME"='MIN ABC Assignment')
      30 - access("ASG"."ASSIGNMENT_GROUP_ID"="ASSI"."ASSIGNMENT_GROUP_ID" AND "ASSI"."INVENTORY_ITEM_ID"=:B1)
      32 - access("ASSI"."ABC_CLASS_ID"="CLASSES"."ABC_CLASS_ID")
      34 - access("CODE_COMBINATION_ID"=:B1)
      39 - access("PVS"."VENDOR_SITE_ID"="PHA"."VENDOR_SITE_ID" AND "PVS"."ORG_ID"="PHA"."ORG_ID")
      42 - access("ORGANIZATION_ID"=2763 AND "LANGUAGE"=USERENV('LANG') AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE')
           filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"ORGANIZATION_ID"))='T
                  RUE')
      43 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_BUSINESS_
                  GROUP_ID"()))
      44 - access("HAO"."ORGANIZATION_ID"=2763)
      46 - access("PVS"."ORG_ID"=2763)
           filter("PVS"."ORG_ID"=2763)
      47 - filter((NVL("PHA"."CLOSED_CODE",'OPEN')='OPEN' OR NVL("PHA"."CLOSED_CODE",'OPEN')='CLOSED'))
      48 - access("PHA"."ORG_ID"=2763)
           filter(("PHA"."ORG_ID"=2763 AND INTERNAL_FUNCTION("PHA"."AUTHORIZATION_STATUS")))
      49 - filter((NVL("PLL"."CLOSED_CODE",'OPEN')='OPEN' OR NVL("PLL"."CLOSED_CODE",'OPEN')='CLOSED'))
      50 - access("PLL"."PO_HEADER_ID"="PHA"."PO_HEADER_ID")
      51 - filter((INTERNAL_FUNCTION("PLOC"."SHIPMENT_TYPE") AND NVL("PLOC"."CANCEL_FLAG",'N')='N'))
      52 - access("PLOC"."PO_LINE_ID"="PLL"."PO_LINE_ID")
      54 - access("PDI"."LINE_LOCATION_ID"="PLOC"."LINE_LOCATION_ID")
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - "PO_LINE_TYPES_TL".ROWID[ROWID,10], "LINE_TYPE"[VARCHAR2,25]
       2 - "PO_LINE_TYPES_TL".ROWID[ROWID,10]
       3 - "ITEMS".ROWID[ROWID,10], "ITEMS"."PREPROCESSING_LEAD_TIME"[NUMBER,22]
       4 - "ITEMS".ROWID[ROWID,10]
       5 - "ITEMS".ROWID[ROWID,10], "ITEMS"."FULL_LEAD_TIME"[NUMBER,22]
       6 - "ITEMS".ROWID[ROWID,10]
       7 - "ITEMS".ROWID[ROWID,10], "ITEMS"."POSTPROCESSING_LEAD_TIME"[NUMBER,22]
       8 - "ITEMS".ROWID[ROWID,10]
       9 - "PLA".ROWID[ROWID,10], "PLA"."PO_HEADER_ID"[NUMBER,22], "PLA"."PO_LINE_ID"[NUMBER,22], "PLA"."PROMISED_DATE"[DATE,7]
      10 - "PLA".ROWID[ROWID,10]
      11 - (#keys=0) MIN("REVISION_NUM")[22]
      12 - "REVISION_NUM"[NUMBER,22]
      13 - "PLLA2".ROWID[ROWID,10], "REVISION_NUM"[NUMBER,22]
      14 - "ITEMS".ROWID[ROWID,10], "ITEMS"."LONG_DESCRIPTION"[VARCHAR2,4000]
      15 - "ITEMS".ROWID[ROWID,10]
      16 - "FIN".ROWID[ROWID,10], "FIN"."INVENTORY_ORGANIZATION_ID"[NUMBER,22]
      17 - "FIN".ROWID[ROWID,10]
      18 - "FND_LOOKUP_VALUES".ROWID[ROWID,10], "MEANING"[VARCHAR2,80], NVL("ZD_EDITION_NAME",'ORA$BASE')[30]
      19 - "FND_LOOKUP_VALUES".ROWID[ROWID,10]
      20 - "PRLA"."NOTE_TO_RECEIVER"[VARCHAR2,480]
      21 - "PRDA"."REQUISITION_LINE_ID"[NUMBER,22]
      22 - "PRDA".ROWID[ROWID,10]
      23 - "PRLA"."NOTE_TO_RECEIVER"[VARCHAR2,480]
      24 - "PRLA".ROWID[ROWID,10]
      25 - "CLASSES"."ABC_CLASS_NAME"[VARCHAR2,40]
      26 - "ASSI"."ABC_CLASS_ID"[NUMBER,22]
      27 - "ASG"."ASSIGNMENT_GROUP_ID"[NUMBER,22]
      28 - "ASG".ROWID[ROWID,10]
      29 - "ASSI"."ABC_CLASS_ID"[NUMBER,22]
      30 - "ASSI".ROWID[ROWID,10]
      31 - "CLASSES"."ABC_CLASS_NAME"[VARCHAR2,40]
      32 - "CLASSES".ROWID[ROWID,10]
      33 - "GL_CODE_COMBINATIONS".ROWID[ROWID,10], "CHART_OF_ACCOUNTS_ID"[NUMBER,22], "SEGMENT1"[VARCHAR2,25], "SEGMENT2"[VARCHAR2,25], 
           "SEGMENT3"[VARCHAR2,25], "SEGMENT4"[VARCHAR2,25], "SEGMENT5"[VARCHAR2,25], "SEGMENT6"[VARCHAR2,25], "SEGMENT7"[VARCHAR2,25], 
           "SEGMENT8"[VARCHAR2,25]
      34 - "GL_CODE_COMBINATIONS".ROWID[ROWID,10]
      35 - "PLL"."PO_LINE_ID"[NUMBER,22], "PLOC"."LINE_LOCATION_ID"[NUMBER,22], [25], "PLOC"."QUANTITY_ACCEPTED"[NUMBER,22], NULL[0], 
           NULL[0], NULL[0], NULL[0], DECODE(TO_CHAR("PLOC"."PO_RELEASE_ID"),NULL,NULL,TO_CHAR("PLOC"."QUANTITY"))[40], [22], [22], [22], 
           "PLOC"."FIRM_STATUS_LOOKUP_CODE"[VARCHAR2,25], NVL(,"PLOC"."PROMISED_DATE")[7], [4000], NVL("PLOC"."APPROVED_FLAG",'N')[1], 
           "PVS"."COUNTRY"[VARCHAR2,60], "PLL"."NOTE_TO_VENDOR"[VARCHAR2,480], NVL("PLOC"."QUANTITY",0)-NVL("PLOC"."QUANTITY_CANCELLED",0)-NVL("PLOC
           "."QUANTITY_RECEIVED",0)*"PLOC"."PRICE_OVERRIDE"[22], "PLOC"."ATTRIBUTE4"[VARCHAR2,150], [80], [480], 
           DECODE("PHA"."USER_HOLD_FLAG",'Y','Y',"PLL"."USER_HOLD_FLAG")[1], [40], [207]
      36 - "PHA"."ORG_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PHA"."PO_HEADER_ID"[NUMBER,22], "PHA"."USER_HOLD_FLAG"[VARCHAR2,1], 
           "PLL"."PO_LINE_ID"[NUMBER,22], "PLL"."LINE_TYPE_ID"[NUMBER,22], "PLL"."ITEM_ID"[NUMBER,22], "PLL"."NOTE_TO_VENDOR"[VARCHAR2,480], 
           "PLL"."USER_HOLD_FLAG"[VARCHAR2,1], "PLOC"."LINE_LOCATION_ID"[NUMBER,22], "PLOC"."QUANTITY"[NUMBER,22], 
           "PLOC"."QUANTITY_RECEIVED"[NUMBER,22], "PLOC"."QUANTITY_ACCEPTED"[NUMBER,22], "PLOC"."QUANTITY_CANCELLED"[NUMBER,22], 
           "PLOC"."PO_RELEASE_ID"[NUMBER,22], "PLOC"."PROMISED_DATE"[DATE,7], "PLOC"."PRICE_OVERRIDE"[NUMBER,22], 
           "PLOC"."APPROVED_FLAG"[VARCHAR2,1], "PLOC"."FIRM_STATUS_LOOKUP_CODE"[VARCHAR2,25], "PLOC"."ATTRIBUTE4"[VARCHAR2,150], 
           "PLOC"."ATTRIBUTE9"[VARCHAR2,150], "PLOC"."SHIP_TO_ORGANIZATION_ID"[NUMBER,22], "PDI"."CODE_COMBINATION_ID"[NUMBER,22], 
           "PDI"."REQ_DISTRIBUTION_ID"[NUMBER,22]
      37 - "PHA"."ORG_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PHA"."PO_HEADER_ID"[NUMBER,22], "PHA"."USER_HOLD_FLAG"[VARCHAR2,1], 
           "PLL"."PO_LINE_ID"[NUMBER,22], "PLL"."LINE_TYPE_ID"[NUMBER,22], "PLL"."ITEM_ID"[NUMBER,22], "PLL"."NOTE_TO_VENDOR"[VARCHAR2,480], 
           "PLL"."USER_HOLD_FLAG"[VARCHAR2,1], "PLOC"."LINE_LOCATION_ID"[NUMBER,22], "PLOC"."QUANTITY"[NUMBER,22], 
           "PLOC"."QUANTITY_RECEIVED"[NUMBER,22], "PLOC"."QUANTITY_ACCEPTED"[NUMBER,22], "PLOC"."QUANTITY_CANCELLED"[NUMBER,22], 
           "PLOC"."PO_RELEASE_ID"[NUMBER,22], "PLOC"."PROMISED_DATE"[DATE,7], "PLOC"."PRICE_OVERRIDE"[NUMBER,22], 
           "PLOC"."APPROVED_FLAG"[VARCHAR2,1], "PLOC"."FIRM_STATUS_LOOKUP_CODE"[VARCHAR2,25], "PLOC"."ATTRIBUTE4"[VARCHAR2,150], 
           "PLOC"."ATTRIBUTE9"[VARCHAR2,150], "PLOC"."SHIP_TO_ORGANIZATION_ID"[NUMBER,22]
      38 - "PHA"."ORG_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PHA"."PO_HEADER_ID"[NUMBER,22], "PHA"."USER_HOLD_FLAG"[VARCHAR2,1], 
           "PLL"."PO_LINE_ID"[NUMBER,22], "PLL"."LINE_TYPE_ID"[NUMBER,22], "PLL"."ITEM_ID"[NUMBER,22], "PLL"."NOTE_TO_VENDOR"[VARCHAR2,480], 
           "PLL"."USER_HOLD_FLAG"[VARCHAR2,1]
      39 - (#keys=2) "PHA"."ORG_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PHA"."PO_HEADER_ID"[NUMBER,22], 
           "PHA"."USER_HOLD_FLAG"[VARCHAR2,1]
      40 - "PVS"."VENDOR_SITE_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PVS"."ORG_ID"[NUMBER,22]
      44 - "HAO".ROWID[ROWID,10]
      45 - "PVS"."VENDOR_SITE_ID"[NUMBER,22], "PVS"."COUNTRY"[VARCHAR2,60], "PVS"."ORG_ID"[NUMBER,22]
      46 - "PVS".ROWID[ROWID,10], "PVS"."ORG_ID"[NUMBER,22]
      47 - "PHA"."PO_HEADER_ID"[NUMBER,22], "PHA"."VENDOR_SITE_ID"[NUMBER,22], "PHA"."USER_HOLD_FLAG"[VARCHAR2,1], "PHA"."ORG_ID"[NUMBER,22]
      48 - "PHA".ROWID[ROWID,10], "PHA"."ORG_ID"[NUMBER,22]
      49 - "PLL"."PO_LINE_ID"[NUMBER,22], "PLL"."LINE_TYPE_ID"[NUMBER,22], "PLL"."ITEM_ID"[NUMBER,22], "PLL"."NOTE_TO_VENDOR"[VARCHAR2,480], 
           "PLL"."USER_HOLD_FLAG"[VARCHAR2,1]
      50 - "PLL".ROWID[ROWID,10]
      51 - "PLOC"."LINE_LOCATION_ID"[NUMBER,22], "PLOC"."QUANTITY"[NUMBER,22], "PLOC"."QUANTITY_RECEIVED"[NUMBER,22], 
           "PLOC"."QUANTITY_ACCEPTED"[NUMBER,22], "PLOC"."QUANTITY_CANCELLED"[NUMBER,22], "PLOC"."PO_RELEASE_ID"[NUMBER,22], 
           "PLOC"."PROMISED_DATE"[DATE,7], "PLOC"."PRICE_OVERRIDE"[NUMBER,22], "PLOC"."APPROVED_FLAG"[VARCHAR2,1], 
           "PLOC"."FIRM_STATUS_LOOKUP_CODE"[VARCHAR2,25], "PLOC"."ATTRIBUTE4"[VARCHAR2,150], "PLOC"."ATTRIBUTE9"[VARCHAR2,150], 
           "PLOC"."SHIP_TO_ORGANIZATION_ID"[NUMBER,22]
      52 - "PLOC".ROWID[ROWID,10], "PLOC"."QUANTITY"[NUMBER,22], "PLOC"."QUANTITY_CANCELLED"[NUMBER,22], "PLOC"."PRICE_OVERRIDE"[NUMBER,22]
      53 - "PDI"."CODE_COMBINATION_ID"[NUMBER,22], "PDI"."REQ_DISTRIBUTION_ID"[NUMBER,22]
      54 - "PDI".ROWID[ROWID,10]
     
    Note
    -----
       - Warning: basic plan statistics not available. These are only collected when:
           * hint 'gather_plan_statistics' is used for the statement or
           * parameter 'statistics_level' is set to 'ALL', at session or system level
     
    
    
    
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Sep 28, 2022 8:07PM

    The trace file shows you're returning 750,000 rows in 50,000 fetches, which looks like the default arraysize from SQL*Plus. Setting a larger arraysize will help a bit (maybe up to about hundred sixty seconds reduction in network time with an arraysize of about 200). [Updated - I'd missed the 157 seconds maxwait which almost certainly didn't happen during the execution]

    Big question, though - why do you want to fetch so many rows, and how long do you think is a reasonable time for that much data.


    Observation - the trace file shows which which of the in-line scalar subqueries take the most time - largely because you don't get much scalar subquery caching. The initial query (before scalar subquery application and uniqueness operation) seems to take about 245 seconds - then you start running all the subqueries.

    Individually, this one seems to take 500 seconds - but the figures may be a little suspect because of the problems of granularity that Oracle will experience:

               (SELECT pla.promised_date
                  FROM apps.po_line_locations_archive_all pla
                 WHERE     pla.po_header_id = pha.po_header_id
                       AND pla.po_line_id = pll.po_line_id
                       AND pla.line_location_id = ploc.line_location_id
                       AND pla.revision_num =
                           (SELECT MIN (revision_num)
                              FROM apps.po_line_locations_archive_all plla2
                             WHERE     plla2.promised_date IS NOT NULL
                                   AND plla2.line_location_id =
                                       ploc.line_location_id)),
    

    No time for further comment, I might be able to write more at some point in the next few days.

    Regards

    Jonathan Lewis

  • BEDE
    BEDE Oracle Developer Member Posts: 2,478 Gold Trophy

    To me, the following looks like a killer:

           NVL (
               (SELECT pla.promised_date
                  FROM apps.po_line_locations_archive_all pla
                 WHERE     pla.po_header_id = pha.po_header_id
                       AND pla.po_line_id = pll.po_line_id
                       AND pla.line_location_id = ploc.line_location_id
                       AND pla.revision_num =
                           (SELECT MIN (revision_num)
                              FROM apps.po_line_locations_archive_all plla2
                             WHERE     plla2.promised_date IS NOT NULL
                                   AND plla2.line_location_id =
                                       ploc.line_location_id)),
               ploc.promised_date)
    

    first of all try to eliminate this column from the select and see how it runs. If it performs significantly better, then use:

    with tb_promise1 as (
    SELECT pla.promised_date, pla.po_header_id, pla.po_line_id
      ,pla.line_location_id
      ,pla.revision_num 
      ,min(revision_num) over (partition by line_location_id) min_rev_num
    FROM apps.po_line_locations_archive_all pla
    where promised_date IS NOT NULL
    ,tb_promise2 as (
    select pla.promised_date, pla.po_header_id, pla.po_line_id
      ,pla.line_location_id
      ,pla.revision_num 
    from tb_promise1 pla
    where revision_num=min_rev_num
    )
    
    

    and outer join the other tables with tb_promise2.