1 2 3 Previous Next 34 Replies Latest reply: Sep 23, 2010 4:19 AM by bluefrog RSS

    Need help in tuning a SQL statement

    638677
      Hi friends,

      The following SQL statement has been identified to perform poorly. It currently takes up to 30 minutes to execute.
      The version of the db is 9.2.0.8.0.
      Below is the SQL statement along with explain plan and other information.
      APPS$show parameter user_dump_dest
      unknown SHOW option "parameter"
      USER is "APPS"
      unknown SHOW option "_dump_dest"
      APPS$show parameter optimizer
      unknown SHOW option "parameter"
      unknown SHOW option "optimizer"
      APPS$show parameter db_file_multi
      unknown SHOW option "parameter"
      unknown SHOW option beginning "db_file_mu..."
      APPS$show parameter db_block_size
      unknown SHOW option "parameter"
      unknown SHOW option beginning "db_block_s..."
      APPS$show parameter cursor_sharing
      unknown SHOW option "parameter"
      unknown SHOW option beginning "cursor_sha..."
      APPS$column sname format a20
      APPS$column pname format a20
      APPS$column pval2 format a20
      APPS$select sname
        2         , pname
        3         , pval1
        4         , pval2
        5  from sys.aux_stats$;
      
      no rows selected
      
       real: 406
      APPS$explain plan for
        2  -- put your statement here
        3  SELECT -- Linked Shipments data
        4   rsh.shipment_header_id asn_id,
        5   rsl.shipment_line_id asn_line_id,
        6   rsh.shipment_num shipment_number,
        7   rsh.creation_date asn_creation_date,
        8   rsh.attribute1 asn_status,
        9   (SELECT pv.segment1
       10        FROM apps.po_vendors pv
       11       WHERE pv.vendor_id = rsh.vendor_id) supplier_code,
       12   (SELECT pv.vendor_name
       13        FROM apps.po_vendors pv
       14       WHERE pv.vendor_id = rsh.vendor_id) supplier_name,
       15   rsh.attribute13 transportation, -- 19-FEB-2008 Removed initcap
       16   rsh.num_of_containers num_of_containers,
       17   rsh.attribute3 DIMENSION,
       18   rsh.attribute11 || '-' || rsh.gross_weight_uom_code gross_weight,
       19   rsh.comments comments,
       20   rsh.attribute4 sli_notes,
       21   rsh.attribute2 pick_up_location_code,
       22   (SELECT povsa.address_line1 || ' ' || povsa.address_lines_alt || ' ' ||
       23             povsa.address_line2 || ' ' || povsa.address_line3 || ' ' ||
       24             povsa.city || ' ' || povsa.state || ' ' || povsa.zip || ' ' ||
       25             povsa.province || ' ' || povsa.country || ' ' || povsa.area_code
       26        FROM apps.poc_asn_pickup_locations_v povsa
       27       WHERE vendor_site_code = rsh.attribute2
       28         AND vendor_id = rsh.vendor_id
       29         AND org_id = poh.org_id) pickup_loc_address,
       30   pla.line_num req_sched_num,
       31   rsh.waybill_airbill_num waybill_airbill_num,
       32   wcs.ship_method_code freight_carrier_code,
       33   wcs.ship_method_meaning ship_method_meaning,
       34   wc.scac_code scac_code,
       35   poh.segment1 itc_po_number,
       36   pla.unit_meas_lookup_code uom,
       37   oel.ordered_item item_num,
       38   pla.item_description item_desc,
       39   (SELECT msi.segment1
       40        FROM apps.mtl_system_items_b msi
       41       WHERE inventory_item_id = rsl.item_id
       42         AND ROWNUM = 1) m_item_num,
       43   (SELECT msi.description
       44        FROM apps.mtl_system_items_b msi
       45       WHERE inventory_item_id = rsl.item_id
       46         AND ROWNUM = 1) item_desc,
       47  /*commented 14072010 - item_number from oe_order_lines_all and item_description from po_lines_all */
       48   pll.promised_date agreed_shiped_date,
       49   (SELECT ftv.territory_short_name
       50        FROM apps.fnd_territories_tl ftv
       51       WHERE ftv.territory_code = rsl.country_of_origin_code
       52         AND ftv.LANGUAGE = USERENV('LANG')) country_of_origin,
       53   rsl.quantity_shipped qty_to_b_shiped,
       54   rsh.freight_bill_number freight_bill_number,
       55   rsh.expected_receipt_date shipment_date,
       56   rsh.ship_to_org_id ship_to_org_id,
       57   rsh.asn_type asn_type,
       58   rsh.packaging_code packaging_code,
       59   rsh.packing_slip packing_list,
       60   rsh.vendor_id supplier_ora_id,
       61   pll.attribute15 poll_attri15,
       62   rc.customer_number,
       63   rc.customer_name,
       64              (SELECT customer_name
       65               FROM apps.ra_customers
       66              WHERE customer_id = oeh.sold_to_org_id) customer_name,
       67  /*commented 14072010 - Sreekanth */
       68   (SELECT ftt.territory_short_name
       69        FROM apps.fnd_territories_tl ftt
       70       WHERE ftt.territory_code = ra_ship_to.country
       71         AND ftt.LANGUAGE = USERENV('LANG')) final_destination,
       72   site_ship_to.LOCATION ship_to_address_code,
       73   ra_ship_to.address1 ship_to_address_1,
       74   ra_ship_to.address2 ship_to_address_2,
       75   ra_ship_to.address3 ship_to_address_3,
       76   ra_ship_to.city ship_to_address_4,
       77   (SELECT ftt.territory_short_name
       78        FROM apps.fnd_territories_tl ftt
       79       WHERE ftt.territory_code = ra_ship_to.country
       80         AND ftt.LANGUAGE = USERENV('LANG')) ship_to_address_5,
       81   ra_ship_to.postal_code ship_to_address_6,
       82   ra_ship_to.location_id ship_to_address_7,
       83   NVL((SELECT freight_terms
       84           FROM apps.ontc_mtc_proforma_headers omph
       85          WHERE reference_sequence_id = TO_CHAR(rsh.shipment_header_id)
       86            AND process_type = 'DROPSHIP'
       87            AND ROWNUM = 1),
       88         oeh.freight_terms_code) AS freight_terms, --modified by zhang meng 2008-08-12 ---
       89   ra_bill_to.address1 bill_to_address_1,
       90   ra_bill_to.address2 bill_to_address_2,
       91   ra_bill_to.address3 bill_to_address_3,
       92   ra_bill_to.address4 bill_to_address_4,
       93   ra_bill_to.city bill_to_address_5,
       94   ra_bill_to.postal_code bill_to_address_6,
       95   NVL(ra_bill_to.state, ra_bill_to.province) bill_to_address_7,
       96   (SELECT ftt.territory_short_name
       97        FROM apps.fnd_territories_tl ftt
       98       WHERE ftt.territory_code = ra_bill_to.country
       99         AND ftt.LANGUAGE = USERENV('LANG')) bill_to_address_8,
      100   oel.request_date customer_request_date,
      101   (SELECT transaction_no
      102        FROM apps.ontc_mtc_proforma_headers omph
      103       WHERE reference_sequence_id = TO_CHAR(rsh.shipment_header_id)
      104         AND process_type = 'DROPSHIP'
      105         AND ROWNUM = 1) invoice_number,
      106   (SELECT SUM((ompl.quantity * ompl.unit_price) *
      107              (1 + omph.tax_percentage / 100))
      108        FROM apps.ontc_mtc_proforma_headers omph,
      109             apps.ontc_mtc_proforma_lines   ompl
      110       WHERE omph.transaction_no = ompl.transaction_no(+)
      111         AND omph.transaction_no = omph.transaction_no
      112         AND process_type = 'DROPSHIP'
      113         AND reference_sequence_id = to_char(rsh.shipment_header_id)) invoice_amount,
      114   NVL(pll.promised_date, pll.need_by_date) delivery_date,
      115   oeh.cust_po_number cust_po_number,
      116   oeh.order_number order_number,
      117   ra_ship_to.attribute20 business_unit,
      118   oeh.attribute2 cust_po_date,
      119   rsh.attribute10 net_weight,
      120   rsh.attribute12 no_of_pallets,
      121   oel.line_number bsa_release_line_no,
      122   rsh.packing_slip supp_inv_number,
      123   rsh.receipt_source_code vendor_type
      124    FROM apps.rcv_shipment_headers       rsh,
      125           apps.rcv_shipment_lines       rsl,
      126           apps.po_headers_all        poh,
      127           apps.po_lines_all            pla,
      128           apps.po_line_locations_all       pll,
      129           apps.wsh_carrier_services       wcs,
      130           apps.wsh_carriers            wc,
      131           apps.wsh_org_carrier_services wocs,
      132           apps.oe_order_lines_all       oel,
      133           apps.oe_order_headers_all       oeh,
      134           apps.ra_customers            rc,
      135           apps.ra_addresses_all       ra_bill_to,
      136           apps.ra_addresses_all       ra_ship_to,
      137           apps.ra_site_uses_all       site_bill_to,
      138           apps.ra_site_uses_all       site_ship_to
      139   WHERE
      140   --1=1
      141       --AND
      142       rsh.shipment_header_id = rsl.shipment_header_id
      143       AND rsl.po_header_id = poh.po_header_id
      144       AND rsl.po_line_id = pla.po_line_id
      145       AND rsl.po_line_location_id = pll.line_location_id
      146       AND poh.po_header_id = pla.po_header_id
      147       AND pll.po_line_id = pla.po_line_id
      148       AND pll.po_header_id = poh.po_header_id
      149       AND pll.ship_to_organization_id = rsl.to_organization_id
      150       AND wc.freight_code = rsh.freight_carrier_code
      151       AND wc.carrier_id = wcs.carrier_id
      152       AND wcs.mode_of_transport =
      153           DECODE(rsh.attribute13, 'NA', 'AIR', NULL, 'AIR', rsh.attribute13)
      154       AND rsh.asn_type = 'ASN'
      155       AND rsl.asn_line_flag = 'Y'
      156  -- AND rsh.shipment_num IS NOT NULL /* commented based on functionality */
      157       AND rsh.attribute7 IS NULL
      158       AND NVL(pll.approved_flag, 'N') = 'Y'
      159       AND pll.shipment_type = 'STANDARD'
      160       AND poh.type_lookup_code = 'STANDARD'
      161  -- AND   WCS.ENABLED_FLAG            = 'Y'
      162       AND wocs.carrier_service_id = wcs.carrier_service_id
      163       AND wocs.organization_id = pll.ship_to_organization_id
      164       AND oeh.header_id = oel.header_id
      165       AND oel.inventory_item_id = rsl.item_id
      166       AND rsl.item_id = pla.item_id
      167       AND oel.line_id = TO_NUMBER(pll.attribute15)
      168       AND rc.customer_id = oeh.sold_to_org_id
      169       AND rsl.asn_line_flag = 'Y'
      170       AND rsl.shipment_line_status_code IS NOT NULL
      171       AND ra_bill_to.customer_id = rc.customer_id
      172       AND ra_ship_to.customer_id = rc.customer_id
      173       AND ra_ship_to.address_id = site_ship_to.address_id
      174       AND ra_bill_to.address_id = site_bill_to.address_id
      175       AND site_bill_to.site_use_id = oeh.invoice_to_org_id
      176       AND site_ship_to.site_use_id = oeh.ship_to_org_id
      177       AND site_ship_to.site_use_code = 'SHIP_TO'
      178       AND site_bill_to.site_use_code = 'BILL_TO';
      
      Explained.
      
       real: 1235
      APPS$select *
        2  from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      ---------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                     |  Name                         | Rows  | Bytes | Cost  |
      ---------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                              |                               |     1 |   726 | 10680 |
      |   1 |  TABLE ACCESS BY INDEX ROWID                  | PO_VENDORS                    |     1 |    11 |     3 |
      |*  2 |   INDEX UNIQUE SCAN                           | PO_VENDORS_U1                 |     1 |       |     2 |
      |   3 |  TABLE ACCESS BY INDEX ROWID                  | PO_VENDORS                    |     1 |    27 |     3 |
      |*  4 |   INDEX UNIQUE SCAN                           | PO_VENDORS_U1                 |     1 |       |     2 |
      |   5 |  VIEW                                         | POC_ASN_PICKUP_LOCATIONS_V    |     2 |  2426 |    16 |
      |   6 |   UNION-ALL                                   |                               |       |       |       |
      |   7 |    NESTED LOOPS                               |                               |     1 |    75 |     4 |
      |   8 |     NESTED LOOPS                              |                               |     1 |    70 |     4 |
      |*  9 |      TABLE ACCESS BY INDEX ROWID              | PO_VENDOR_SITES_ALL           |     1 |    66 |     3 |
      |* 10 |       INDEX UNIQUE SCAN                       | PO_VENDOR_SITES_U2            |     1 |       |     2 |
      |* 11 |      INDEX UNIQUE SCAN                        | PO_VENDORS_U1                 |     1 |     4 |     1 |
      |* 12 |     INDEX UNIQUE SCAN                         | FND_TERRITORIES_TL_U1         |     1 |     5 |       |
      |  13 |    NESTED LOOPS                               |                               |     1 |    71 |    12 |
      |  14 |     NESTED LOOPS                              |                               |     1 |    66 |    12 |
      |  15 |      TABLE ACCESS BY INDEX ROWID              | PO_VENDORS                    |     1 |    11 |     3 |
      |* 16 |       INDEX UNIQUE SCAN                       | PO_VENDORS_U1                 |     1 |       |     2 |
      |* 17 |      TABLE ACCESS BY INDEX ROWID              | FND_LOOKUP_VALUES             |     1 |    55 |     9 |
      |* 18 |       INDEX RANGE SCAN                        | FND_LOOKUP_VALUES_U2          |    10 |       |     2 |
      |* 19 |     INDEX UNIQUE SCAN                         | FND_TERRITORIES_TL_U1         |     1 |     5 |       |
      |* 20 |  COUNT STOPKEY                                |                               |       |       |       |
      |  21 |   TABLE ACCESS BY INDEX ROWID                 | MTL_SYSTEM_ITEMS_B            |     5 |    75 |     8 |
      |* 22 |    INDEX RANGE SCAN                           | MTL_SYSTEM_ITEMS_B_U1         |     5 |       |     3 |
      |* 23 |  COUNT STOPKEY                                |                               |       |       |       |
      |  24 |   TABLE ACCESS BY INDEX ROWID                 | MTL_SYSTEM_ITEMS_B            |     5 |   170 |     8 |
      |* 25 |    INDEX RANGE SCAN                           | MTL_SYSTEM_ITEMS_B_U1         |     5 |       |     3 |
      |  26 |  TABLE ACCESS BY INDEX ROWID                  | FND_TERRITORIES_TL            |     1 |    21 |     2 |
      |* 27 |   INDEX UNIQUE SCAN                           | FND_TERRITORIES_TL_U1         |     1 |       |     1 |
      |  28 |  NESTED LOOPS                                 |                               |     1 |    39 |     5 |
      |  29 |   TABLE ACCESS BY INDEX ROWID                 | HZ_CUST_ACCOUNTS              |     1 |    10 |     3 |
      |* 30 |    INDEX UNIQUE SCAN                          | HZ_CUST_ACCOUNTS_U1           |     1 |       |     2 |
      |  31 |   TABLE ACCESS BY INDEX ROWID                 | HZ_PARTIES                    |     1 |    29 |     2 |
      |* 32 |    INDEX UNIQUE SCAN                          | HZ_PARTIES_U1                 |     1 |       |     1 |
      |  33 |  TABLE ACCESS BY INDEX ROWID                  | FND_TERRITORIES_TL            |     1 |    21 |     2 |
      |* 34 |   INDEX UNIQUE SCAN                           | FND_TERRITORIES_TL_U1         |     1 |       |     1 |
      |  35 |  TABLE ACCESS BY INDEX ROWID                  | FND_TERRITORIES_TL            |     1 |    21 |     2 |
      |* 36 |   INDEX UNIQUE SCAN                           | FND_TERRITORIES_TL_U1         |     1 |       |     1 |
      |* 37 |  COUNT STOPKEY                                |                               |       |       |       |
      |* 38 |   TABLE ACCESS BY INDEX ROWID                 | ONTC_MTC_PROFORMA_HEADERS     |     1 |    18 |     4 |
      |* 39 |    INDEX RANGE SCAN                           | ONTC_MTC_PROFORMA_HEADERS_U2  |     1 |       |     3 |
      |  40 |  TABLE ACCESS BY INDEX ROWID                  | FND_TERRITORIES_TL            |     1 |    21 |     2 |
      |* 41 |   INDEX UNIQUE SCAN                           | FND_TERRITORIES_TL_U1         |     1 |       |     1 |
      |* 42 |  COUNT STOPKEY                                |                               |       |       |       |
      |* 43 |   TABLE ACCESS BY INDEX ROWID                 | ONTC_MTC_PROFORMA_HEADERS     |     1 |    18 |     4 |
      |* 44 |    INDEX RANGE SCAN                           | ONTC_MTC_PROFORMA_HEADERS_U2  |     1 |       |     3 |
      |  45 |  SORT AGGREGATE                               |                               |     1 |    32 |       |
      |  46 |   NESTED LOOPS OUTER                          |                               |     2 |    64 |     7 |
      |* 47 |    TABLE ACCESS BY INDEX ROWID                | ONTC_MTC_PROFORMA_HEADERS     |     1 |    20 |     4 |
      |* 48 |     INDEX RANGE SCAN                          | ONTC_MTC_PROFORMA_HEADERS_U2  |     1 |       |     3 |
      |  49 |    TABLE ACCESS BY INDEX ROWID                | ONTC_MTC_PROFORMA_LINES       |     4 |    48 |     3 |
      |* 50 |     INDEX RANGE SCAN                          | ONTC_MTC_PROFORMA_LINES_PK    |     4 |       |     2 |
      |  51 |  NESTED LOOPS                                 |                               |     1 |   726 | 10680 |
      |  52 |   NESTED LOOPS                                |                               |     1 |   714 | 10678 |
      |  53 |    NESTED LOOPS                               |                               |     1 |   666 | 10676 |
      |  54 |     NESTED LOOPS                              |                               |     1 |   656 | 10674 |
      |  55 |      NESTED LOOPS                             |                               |     1 |   635 | 10672 |
      |  56 |       NESTED LOOPS                            |                               |     1 |   599 | 10670 |
      |  57 |        NESTED LOOPS                           |                               |     1 |   590 | 10668 |
      |  58 |         NESTED LOOPS                          |                               |     1 |   535 | 10666 |
      |  59 |          NESTED LOOPS                         |                               |     1 |   525 | 10664 |
      |  60 |           NESTED LOOPS                        |                               |     1 |   506 | 10662 |
      |  61 |            NESTED LOOPS                       |                               |     1 |   488 | 10660 |
      |  62 |             NESTED LOOPS                      |                               |     1 |   459 | 10658 |
      |  63 |              NESTED LOOPS                     |                               |     1 |   442 | 10656 |
      |  64 |               NESTED LOOPS                    |                               |     1 |   391 | 10655 |
      |  65 |                NESTED LOOPS                   |                               |     1 |   358 | 10652 |
      |  66 |                 NESTED LOOPS                  |                               |     1 |   333 | 10651 |
      |  67 |                  NESTED LOOPS                 |                               |     1 |   300 | 10649 |
      |  68 |                   NESTED LOOPS                |                               |  1563 |   370K|  7523 |
      |* 69 |                    HASH JOIN                  |                               |  1554 |   295K|  2861 |
      |  70 |                     TABLE ACCESS FULL         | WSH_ORG_CARRIER_SERVICES      |   620 |  4340 |     4 |
      |* 71 |                     HASH JOIN                 |                               |   451 | 84788 |  2856 |
      |  72 |                      TABLE ACCESS FULL        | WSH_CARRIER_SERVICES          |   180 | 11700 |     4 |
      |* 73 |                      HASH JOIN                |                               |  1595 |   191K|  2851 |
      |  74 |                       TABLE ACCESS FULL       | WSH_CARRIERS                  |   106 |  2332 |     2 |
      |* 75 |                       TABLE ACCESS FULL       | RCV_SHIPMENT_HEADERS          |  1595 |   157K|  2848 |
      |* 76 |                    TABLE ACCESS BY INDEX ROWID| RCV_SHIPMENT_LINES            |     1 |    48 |     3 |
      |* 77 |                     INDEX RANGE SCAN          | RCV_SHIPMENT_LINES_U2         |     1 |       |     2 |
      |* 78 |                   TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL                  |     1 |    57 |     2 |
      |* 79 |                    INDEX UNIQUE SCAN          | PO_LINES_U1                   |     1 |       |     1 |
      |* 80 |                  TABLE ACCESS BY INDEX ROWID  | PO_LINE_LOCATIONS_ALL         |     1 |    33 |     2 |
      |* 81 |                   INDEX UNIQUE SCAN           | PO_LINE_LOCATIONS_U1          |     1 |       |     1 |
      |* 82 |                 TABLE ACCESS BY INDEX ROWID   | PO_HEADERS_ALL                |     1 |    25 |     1 |
      |* 83 |                  INDEX UNIQUE SCAN            | PO_HEADERS_U1                 |     1 |       |       |
      |* 84 |                TABLE ACCESS BY INDEX ROWID    | OE_ORDER_LINES_ALL            |     1 |    33 |     3 |
      |* 85 |                 INDEX UNIQUE SCAN             | OE_ORDER_LINES_U1             |     1 |       |     1 |
      |  86 |               TABLE ACCESS BY INDEX ROWID     | OE_ORDER_HEADERS_ALL          |     1 |    51 |     1 |
      |* 87 |                INDEX UNIQUE SCAN              | OE_ORDER_HEADERS_U1           |     1 |       |       |
      |  88 |              TABLE ACCESS BY INDEX ROWID      | HZ_CUST_ACCOUNTS              |     1 |    17 |     2 |
      |* 89 |               INDEX UNIQUE SCAN               | HZ_CUST_ACCOUNTS_U1           |     1 |       |     1 |
      |  90 |             TABLE ACCESS BY INDEX ROWID       | HZ_PARTIES                    |     1 |    29 |     2 |
      |* 91 |              INDEX UNIQUE SCAN                | HZ_PARTIES_U1                 |     1 |       |     1 |
      |* 92 |            TABLE ACCESS BY INDEX ROWID        | HZ_CUST_SITE_USES_ALL         |     1 |    18 |     2 |
      |* 93 |             INDEX UNIQUE SCAN                 | HZ_CUST_SITE_USES_U1          |     1 |       |     1 |
      |* 94 |           TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCT_SITES_ALL        |     1 |    19 |     2 |
      |* 95 |            INDEX UNIQUE SCAN                  | HZ_CUST_ACCT_SITES_U1         |     1 |       |     1 |
      |  96 |          TABLE ACCESS BY INDEX ROWID          | HZ_PARTY_SITES                |     1 |    10 |     2 |
      |* 97 |           INDEX UNIQUE SCAN                   | HZ_PARTY_SITES_U1             |     1 |       |     1 |
      |  98 |         TABLE ACCESS BY INDEX ROWID           | HZ_LOCATIONS                  |     1 |    55 |     2 |
      |* 99 |          INDEX UNIQUE SCAN                    | HZ_LOCATIONS_U1               |     1 |       |     1 |
      |*100 |        INDEX RANGE SCAN                       | HZ_LOC_ASSIGNMENTS_N1         |     1 |     9 |     2 |
      |*101 |       TABLE ACCESS BY INDEX ROWID             | HZ_CUST_SITE_USES_ALL         |     1 |    36 |     2 |
      |*102 |        INDEX UNIQUE SCAN                      | HZ_CUST_SITE_USES_U1          |     1 |       |     1 |
      |*103 |      TABLE ACCESS BY INDEX ROWID              | HZ_CUST_ACCT_SITES_ALL        |     1 |    21 |     2 |
      |*104 |       INDEX UNIQUE SCAN                       | HZ_CUST_ACCT_SITES_U1         |     1 |       |     1 |
      | 105 |     TABLE ACCESS BY INDEX ROWID               | HZ_PARTY_SITES                |     1 |    10 |     2 |
      |*106 |      INDEX UNIQUE SCAN                        | HZ_PARTY_SITES_U1             |     1 |       |     1 |
      | 107 |    TABLE ACCESS BY INDEX ROWID                | HZ_LOCATIONS                  |     1 |    48 |     2 |
      |*108 |     INDEX UNIQUE SCAN                         | HZ_LOCATIONS_U1               |     1 |       |     1 |
      |*109 |   INDEX RANGE SCAN                            | HZ_LOC_ASSIGNMENTS_N1         |     1 |    12 |     2 |
      ---------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("PV"."VENDOR_ID"=:B1)
         4 - access("PV"."VENDOR_ID"=:B1)
         9 - filter("PVSA"."COUNTRY" IS NOT NULL)
        10 - access("PVSA"."VENDOR_ID"=:B1 AND "PVSA"."VENDOR_SITE_CODE"=:B2 AND "PVSA"."ORG_ID"=:B3)
        11 - access("PV"."VENDOR_ID"=:B1)
             filter("PV"."VENDOR_ID"="PVSA"."VENDOR_ID")
        12 - access("FTTP"."TERRITORY_CODE"="PVSA"."COUNTRY" AND "FTTP"."LANGUAGE"=:B1)
        16 - access("PV"."VENDOR_ID"=:B1)
        17 - filter("PV"."SEGMENT1"="B"."DESCRIPTION" AND "B"."DESCRIPTION" IS NOT NULL AND "B"."ATTRIBUTE9" IS NOT
                    NULL AND "B"."ENABLED_FLAG"='Y' AND "B"."TAG"=:B1 AND TO_NUMBER("B"."ATTRIBUTE11")=:B2)
        18 - access("B"."LOOKUP_TYPE"='POC_PICKUP_LOCATIONS' AND "B"."LANGUAGE"=:B1)
             filter("B"."LANGUAGE"=:B1)
        19 - access("FTTP"."TERRITORY_CODE"="B"."ATTRIBUTE9" AND "FTTP"."LANGUAGE"=:B1)
        20 - filter(ROWNUM=1)
        22 - access("MSI"."INVENTORY_ITEM_ID"=:B1)
        23 - filter(ROWNUM=1)
        25 - access("MSI"."INVENTORY_ITEM_ID"=:B1)
        27 - access("FTV"."TERRITORY_CODE"=:B1 AND "FTV"."LANGUAGE"=:B2)
        30 - access("CUST_ACCT"."CUST_ACCOUNT_ID"=:B1)
        32 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
        34 - access("FTT"."TERRITORY_CODE"=:B1 AND "FTT"."LANGUAGE"=:B2)
        36 - access("FTT"."TERRITORY_CODE"=:B1 AND "FTT"."LANGUAGE"=:B2)
        37 - filter(ROWNUM=1)
        38 - filter("OMPH"."PROCESS_TYPE"='DROPSHIP')
        39 - access("OMPH"."REFERENCE_SEQUENCE_ID"=TO_CHAR(:B1))
        41 - access("FTT"."TERRITORY_CODE"=:B1 AND "FTT"."LANGUAGE"=:B2)
        42 - filter(ROWNUM=1)
        43 - filter("OMPH"."PROCESS_TYPE"='DROPSHIP')
        44 - access("OMPH"."REFERENCE_SEQUENCE_ID"=TO_CHAR(:B1))
        47 - filter("OMPH"."PROCESS_TYPE"='DROPSHIP')
        48 - access("OMPH"."REFERENCE_SEQUENCE_ID"=TO_CHAR(:B1))
        50 - access("OMPH"."TRANSACTION_NO"="OMPL"."TRANSACTION_NO"(+))
        69 - access("WOCS"."CARRIER_SERVICE_ID"="WCS"."CARRIER_SERVICE_ID")
        71 - access("WC"."CARRIER_ID"="WCS"."CARRIER_ID" AND "WCS"."MODE_OF_TRANSPORT"=DECODE("SYS_ALIAS_15"."ATTRIBU
                    TE13",'NA','AIR',NULL,'AIR',"SYS_ALIAS_15"."ATTRIBUTE13"))
        73 - access("WC"."FREIGHT_CODE"="SYS_ALIAS_15"."FREIGHT_CARRIER_CODE")
        75 - filter("SYS_ALIAS_15"."FREIGHT_CARRIER_CODE" IS NOT NULL AND "SYS_ALIAS_15"."ASN_TYPE"='ASN' AND
                    "SYS_ALIAS_15"."ATTRIBUTE7" IS NULL)
        76 - filter("SYS_ALIAS_8"."ASN_LINE_FLAG"='Y' AND "SYS_ALIAS_8"."SHIPMENT_LINE_STATUS_CODE" IS NOT NULL)
        77 - access("SYS_ALIAS_15"."SHIPMENT_HEADER_ID"="SYS_ALIAS_8"."SHIPMENT_HEADER_ID")
        78 - filter("SYS_ALIAS_8"."ITEM_ID"="PLA"."ITEM_ID")
        79 - access("SYS_ALIAS_8"."PO_LINE_ID"="PLA"."PO_LINE_ID")
        80 - filter("PLL"."PO_LINE_ID"="PLA"."PO_LINE_ID" AND
                    "PLL"."SHIP_TO_ORGANIZATION_ID"="SYS_ALIAS_8"."TO_ORGANIZATION_ID" AND NVL("PLL"."APPROVED_FLAG",'N')='Y' AND
                    "PLL"."SHIPMENT_TYPE"='STANDARD' AND "WOCS"."ORGANIZATION_ID"="PLL"."SHIP_TO_ORGANIZATION_ID")
        81 - access("SYS_ALIAS_8"."PO_LINE_LOCATION_ID"="PLL"."LINE_LOCATION_ID")
        82 - filter("SYS_ALIAS_5"."TYPE_LOOKUP_CODE"='STANDARD')
        83 - access("PLL"."PO_HEADER_ID"="SYS_ALIAS_5"."PO_HEADER_ID")
             filter("SYS_ALIAS_8"."PO_HEADER_ID"="SYS_ALIAS_5"."PO_HEADER_ID" AND
                    "SYS_ALIAS_5"."PO_HEADER_ID"="PLA"."PO_HEADER_ID")
        84 - filter("OEL"."INVENTORY_ITEM_ID"="SYS_ALIAS_8"."ITEM_ID")
        85 - access("OEL"."LINE_ID"=TO_NUMBER("PLL"."ATTRIBUTE15"))
        87 - access("SYS_ALIAS_9"."HEADER_ID"="OEL"."HEADER_ID")
        89 - access("CUST_ACCT"."CUST_ACCOUNT_ID"="SYS_ALIAS_9"."SOLD_TO_ORG_ID")
        91 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
        92 - filter("HZ_CUST_SITE_USES_ALL"."SITE_USE_CODE"='BILL_TO')
        93 - access("HZ_CUST_SITE_USES_ALL"."SITE_USE_ID"="SYS_ALIAS_9"."INVOICE_TO_ORG_ID")
        94 - filter("ACCT_SITE"."CUST_ACCOUNT_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
        95 - access("ACCT_SITE"."CUST_ACCT_SITE_ID"="HZ_CUST_SITE_USES_ALL"."CUST_ACCT_SITE_ID")
        97 - access("ACCT_SITE"."PARTY_SITE_ID"="PARTY_SITE"."PARTY_SITE_ID")
        99 - access("SYS_ALIAS_13"."LOCATION_ID"="PARTY_SITE"."LOCATION_ID")
       100 - access("SYS_ALIAS_13"."LOCATION_ID"="LOC_ASSIGN"."LOCATION_ID")
             filter(NVL("ACCT_SITE"."ORG_ID",(-99))=NVL("LOC_ASSIGN"."ORG_ID",(-99)))
       101 - filter("SYS_ALIAS_0000"."SITE_USE_CODE"='SHIP_TO')
       102 - access("SYS_ALIAS_0000"."SITE_USE_ID"="SYS_ALIAS_9"."SHIP_TO_ORG_ID")
       103 - filter("SYS_ALIAS_0001"."CUST_ACCOUNT_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
       104 - access("SYS_ALIAS_0001"."CUST_ACCT_SITE_ID"="SYS_ALIAS_0000"."CUST_ACCT_SITE_ID")
       106 - access("SYS_ALIAS_0001"."PARTY_SITE_ID"="SYS_ALIAS_0003"."PARTY_SITE_ID")
       108 - access("SYS_ALIAS_11"."LOCATION_ID"="SYS_ALIAS_0003"."LOCATION_ID")
       109 - access("SYS_ALIAS_11"."LOCATION_ID"="SYS_ALIAS_0002"."LOCATION_ID")
             filter(NVL("SYS_ALIAS_0001"."ORG_ID",(-99))=NVL("SYS_ALIAS_0002"."ORG_ID",(-99)))
      
      Note: cpu costing is off
      
      192 rows selected.
      
       real: 25343
      APPS$rollback;
      
      Rollback complete.
      
       real: 438
      APPS$disconnect
      APPS$spool off
        • 1. Re: Need help in tuning a SQL statement
          638677
          continued...

          All the objects used in the query are ANALYZED and hence the statistics are up-to-date.
          In the query, there are only a few conditions and the rest are just Joins between the tables used.
          From the Explain Plan, NESTED LOOPS and a FULL TABLE SCAN on RCV_SHIPMENT_HEADERS table are expensive operations.
          In RCV_SHIPMENT_HEADERS table, the total no of records are 615770.
          The no of records which i am interested in i.e. which statisfies the conditions in WHERE clause are only 17497.
          I am trying to retrieve only 3% of total records in the table.
          The indexes on the columns used in the WHERE clause are as follows
          INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
          ------------------------------ ------------------------------ ---------------
          RCV_SHIPMENT_HEADERS_N1        SHIP_TO_LOCATION_ID                          1
          RCV_SHIPMENT_HEADERS_N2        SHIPMENT_NUM                                 1
          RCV_SHIPMENT_HEADERS_N4        VENDOR_SITE_ID                               1
          RCV_SHIPMENT_HEADERS_N5        RECEIPT_SOURCE_CODE                          1
          RCV_SHIPMENT_HEADERS_N5        SHIPMENT_NUM                                 2
          RCV_SHIPMENT_HEADERS_N6        ORGANIZATION_ID                              1
          RCV_SHIPMENT_HEADERS_N6        RECEIPT_NUM                                  2
          RCV_SHIPMENT_HEADERS_U1        SHIPMENT_HEADER_ID                           1
          RCV_SHIPMENT_HEADERS_N10       CREATION_DATE                                1
          RCV_SHIPMENT_HEADERS_N3        VENDOR_ID                                    1
          RCV_SHIPMENT_HEADERS_N3        ASN_TYPE                                     2
          RCV_SHIPMENT_HEADERS_N3        CREATION_DATE                                3
          RCV_SHIPMENT_HEADERS_N11       PACKING_SLIP                                 1
          RCV_SHIPMENT_HEADERS_N7        EMPLOYEE_ID                                  1
          RCV_SHIPMENT_HEADERS_N8        RECEIPT_NUM                                  1
          RCV_SHIPMENT_HEADERS_N8        SHIP_TO_ORG_ID                               2
          RCV_SHIPMENT_HEADERS_N9        SYS_NC00085$                                 1
          RCV_SHIPMENT_HEADERS_N99       COMMENTS                                     1
          Inspite of a composite index on ASN_TYPE column, it is not being used and i think it is because the column is in
          2nd position in the index.Please correct me if i am wrong.
          Also, from the documentation, i have read that NESTED loops are inefficient when the join results in more rows
          (typically more than 10000 rows).Here even though the query retrieves more than 20000 records, the optimizer is
          still going for a NESTED LOOP.
          Can you please suggest how can i improve the execution time and also what areas can be improved in the query?

          Regards,
          Sreekanth Munagala
          • 2. Re: Need help in tuning a SQL statement
            user503699
            Sreekanth Munagala wrote:
            Can you please suggest how can i improve the execution time and also what areas can be improved in the query?
            I see you are trying to draw conclusions (about what is wrong with the query execution) based on EXPLAIN PLAN outcome. But the EXPLAIN PLAN output alone is insufficient (and can be inaccurate) to draw any conclusions.
            Your query is completing the execution in 30 minutes. So you should still be able to collect necessary trace details as mentioned in following threads and post them. That way you will get more informed and accurate suggestions (and not guesses).
            When your query takes too long ...
            HOW TO: Post a SQL statement tuning request - template posting
            • 3. Re: Need help in tuning a SQL statement
              638677
              Hi,

              Here are the trace details.
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          0          0           0
              Execute      1      0.00       0.00          0          0          0           0
              Fetch      228     19.89     421.60      44558    2259039          0       22671
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total      230     19.89     421.60      44558    2259039          0       22671
              
              Misses in library cache during parse: 0
              Optimizer goal: CHOOSE
              Parsing user id: 173  
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                 2402  TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                 2402   INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                 2402  TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                 2402   INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                 3777  VIEW  
                 3777   UNION-ALL  
                 3776    NESTED LOOPS  
                 3776     NESTED LOOPS  
                 3776      TABLE ACCESS BY INDEX ROWID PO_VENDOR_SITES_ALL 
                 3776       INDEX UNIQUE SCAN PO_VENDOR_SITES_U2 (object id 45124)
                 3776      INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                 3776     INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                    1    NESTED LOOPS  
                    1     NESTED LOOPS  
                 3778      TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                 3778       INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                    1      TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES 
                 3778       INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 (object id 32884)
                    1     INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                11541  COUNT STOPKEY 
                11541   TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
                11541    INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_U1 (object id 38017)
                11541  COUNT STOPKEY 
                11541   TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
                11541    INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_U1 (object id 38017)
                   84  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                   84   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                 1801  NESTED LOOPS  
                 1801   TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS 
                 1801    INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (object id 81600)
                 1801   TABLE ACCESS BY INDEX ROWID HZ_PARTIES 
                 1801    INDEX UNIQUE SCAN HZ_PARTIES_U1 (object id 172923)
                   15  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                   15   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                   15  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                   15   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                   18  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                   18   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                16819  COUNT STOPKEY 
                16819   TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_HEADERS 
                18369    INDEX RANGE SCAN ONTC_MTC_PROFORMA_HEADERS_U2 (object id 3626691)
                17465  SORT AGGREGATE 
                21607   NESTED LOOPS OUTER 
                16819    TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_HEADERS 
                18369     INDEX RANGE SCAN ONTC_MTC_PROFORMA_HEADERS_U2 (object id 3626691)
                21607    TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_LINES 
                21607     INDEX RANGE SCAN ONTC_MTC_PROFORMA_LINES_PK (object id 3626694)
                22671  NESTED LOOPS  
                22671   NESTED LOOPS  
                22671    NESTED LOOPS  
                22671     NESTED LOOPS  
                22671      NESTED LOOPS  
                22671       NESTED LOOPS  
                22671        NESTED LOOPS  
                22671         NESTED LOOPS  
                22671          NESTED LOOPS  
                22671           NESTED LOOPS  
                22671            NESTED LOOPS  
                22671             NESTED LOOPS  
                22671              NESTED LOOPS  
                22671               NESTED LOOPS  
                22708                NESTED LOOPS  
                22708                 NESTED LOOPS  
               103695                  NESTED LOOPS  
               103695                   NESTED LOOPS  
                79913                    HASH JOIN  
                  620                     TABLE ACCESS FULL WSH_ORG_CARRIER_SERVICES 
                17500                     HASH JOIN  
                  180                      TABLE ACCESS FULL WSH_CARRIER_SERVICES 
                17497                      HASH JOIN  
                  106                       TABLE ACCESS FULL WSH_CARRIERS 
                17497                       TABLE ACCESS FULL RCV_SHIPMENT_HEADERS 
               103695                    TABLE ACCESS BY INDEX ROWID RCV_SHIPMENT_LINES 
               103695                     INDEX RANGE SCAN RCV_SHIPMENT_LINES_U2 (object id 45223)
               103695                   TABLE ACCESS BY INDEX ROWID PO_LINES_ALL 
               103695                    INDEX UNIQUE SCAN PO_LINES_U1 (object id 45106)
                22708                  TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL 
               103695                   INDEX UNIQUE SCAN PO_LINE_LOCATIONS_U1 (object id 45143)
                22708                 TABLE ACCESS BY INDEX ROWID PO_HEADERS_ALL 
                22708                  INDEX UNIQUE SCAN PO_HEADERS_U1 (object id 45052)
                22671                TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL 
                22672                 INDEX UNIQUE SCAN OE_ORDER_LINES_U1 (object id 42102)
                22671               TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL 
                22671                INDEX UNIQUE SCAN OE_ORDER_HEADERS_U1 (object id 41952)
                22671              TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS 
                22671               INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (object id 81600)
                22671             TABLE ACCESS BY INDEX ROWID HZ_PARTIES 
                22671              INDEX UNIQUE SCAN HZ_PARTIES_U1 (object id 172923)
                22671            TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL 
                22671             INDEX UNIQUE SCAN HZ_CUST_SITE_USES_U1 (object id 172912)
                22671           TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL 
                22671            INDEX UNIQUE SCAN HZ_CUST_ACCT_SITES_U1 (object id 172915)
                22671          TABLE ACCESS BY INDEX ROWID HZ_PARTY_SITES 
                22671           INDEX UNIQUE SCAN HZ_PARTY_SITES_U1 (object id 172921)
                22671         TABLE ACCESS BY INDEX ROWID HZ_LOCATIONS 
                22671          INDEX UNIQUE SCAN HZ_LOCATIONS_U1 (object id 172918)
                22671        INDEX RANGE SCAN HZ_LOC_ASSIGNMENTS_N1 (object id 82023)
                22671       TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL 
                22671        INDEX UNIQUE SCAN HZ_CUST_SITE_USES_U1 (object id 172912)
                22671      TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL 
                22671       INDEX UNIQUE SCAN HZ_CUST_ACCT_SITES_U1 (object id 172915)
                22671     TABLE ACCESS BY INDEX ROWID HZ_PARTY_SITES 
                22671      INDEX UNIQUE SCAN HZ_PARTY_SITES_U1 (object id 172921)
                22671    TABLE ACCESS BY INDEX ROWID HZ_LOCATIONS 
                22671     INDEX UNIQUE SCAN HZ_LOCATIONS_U1 (object id 172918)
                22671   INDEX RANGE SCAN HZ_LOC_ASSIGNMENTS_N1 (object id 82023)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                SQL*Net message to client                     228        0.00          0.00
                SQL*Net more data to client                 11289        7.48        374.83
                db file scattered read                       2408        0.05          1.60
                SQL*Net message from client                   228       11.38        406.34
                db file sequential read                     25769        0.47         28.17
                latch free                                      1        0.01          0.01
              ********************************************************************************
              Can you please suggest how can i improve the execution time and what areas can be improved?

              Regards,
              Sreekanth Munagala
              • 4. Re: Need help in tuning a SQL statement
                user503699
                Sreekanth Munagala wrote:
                Can you please suggest how can i improve the execution time and what areas can be improved?
                How did you generate trace and TkProf report? If I remember correctly, a TkProf report in 9.2.0.8 version would display
                the resource consumption details against each step in the Row Source Operation (which is not there in your report).
                Since in 9i, the default optimizer mode is CHOOSE, a query might use RULE based optimizer in the absense of statistics.
                I know you have mentioned earlier that you have analyzed all tables and indexes used by the query.
                Can you tell what command did you use to analyze your tables and indexes?
                In addition to providing above details, I would suggest that you should trace your query execution as follows (unless
                if you have generated above trace using same command)
                ALTER SESSION SET optimizer_mode = ALL_ROWS ;
                ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' ;
                <execute your query>
                exit ;
                and generate TkProf report using
                TKPROF <trace file> <output file>
                It would also help if you can share the output of
                show parameter optimizer
                p.s. BTW, a basic questions.
                1) Why are you fetching 22671 rows to the client application?
                2) In your original post, you mentioned that this query takes 30 minutes to execute but the TkProf shows that your query is executed in just over 7 minutes. So which one is correct?

                Edited by: user503699 on Sep 9, 2010 2:57 AM
                • 5. Re: Need help in tuning a SQL statement
                  638677
                  Hi Narendra,

                  I have generated the TkProf report using the commands given by you.
                  But it is still not showing the resource consumption details against each step in the Row Source Operation.
                  I have analyzed the tables using the below command
                  ANALYZE TABLE <tablename> COMPUTE STATISTICS
                  Also when i issue the command SHOW PARAMETER OPTIMIZER, it says unknown option.
                  But i have executed the below query to get optimizer parameters.
                  APPS$column name format a30
                  APPS$column type format 12
                  Illegal FORMAT string "12"
                  APPS$column type format a12
                  APPS$column value format a10
                  APPS$SELECT   NAME name_col_plus_show_param,
                    2           DECODE (TYPE,
                    3                   1, 'boolean',
                    4                   2, 'string',
                    5                   3, 'integer',
                    6                   4, 'file',
                    7                   5, 'number',
                    8                   6, 'big integer',
                    9                   'unknown'
                   10                  ) TYPE,
                   11           value
                   12      FROM v$parameter
                   13     WHERE UPPER (NAME) LIKE UPPER ('%optimizer%')
                   14  ORDER BY name_col_plus_show_param, ROWNUM;
                  
                  NAME_COL_PLUS_SHOW_PARAM                                         TYPE         VALUE                                                                                                                     
                  ---------------------------------------------------------------- ------------ ----------                                                                                                                
                  optimizer_dynamic_sampling                                       integer      1                                                                                                                         
                  optimizer_features_enable                                        string       9.2.0                                                                                                                     
                  optimizer_index_caching                                          integer      0                                                                                                                         
                  optimizer_index_cost_adj                                         integer      100                                                                                                                       
                  optimizer_max_permutations                                       integer      2000                                                                                                                      
                  optimizer_mode                                                   string       CHOOSE                                                                                                                    
                  call     count       cpu    elapsed       disk      query    current        rows
                  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                  Parse        1      0.57       0.56          0          0          0           0
                  Execute      1      0.00       0.00          0          0          0           0
                  Fetch      228     48.37    1866.89     132255    2326998          0       22671
                  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                  total      230     48.94    1867.45     132255    2326998          0       22671
                  
                  Misses in library cache during parse: 1
                  Optimizer goal: ALL_ROWS
                  Parsing user id: 173  
                  
                  Rows     Row Source Operation
                  -------  ---------------------------------------------------
                     2402  TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                     2402   INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                     2402  TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                     2402   INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                     3777  VIEW  
                     3777   UNION-ALL  
                     3776    NESTED LOOPS  
                     3776     NESTED LOOPS  
                     3776      TABLE ACCESS BY INDEX ROWID PO_VENDOR_SITES_ALL 
                     3776       INDEX UNIQUE SCAN PO_VENDOR_SITES_U2 (object id 45124)
                     3776      INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                     3776     INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                        1    NESTED LOOPS  
                        1     NESTED LOOPS  
                     3778      TABLE ACCESS BY INDEX ROWID PO_VENDORS 
                     3778       INDEX UNIQUE SCAN PO_VENDORS_U1 (object id 45068)
                        1      TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES 
                     3778       INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 (object id 32884)
                        1     INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                    11541  COUNT STOPKEY 
                    11541   TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
                    11541    INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_U1 (object id 38017)
                    11541  COUNT STOPKEY 
                    11541   TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
                    11541    INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_U1 (object id 38017)
                       84  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                       84   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                     1801  NESTED LOOPS  
                     1801   TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS 
                     1801    INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (object id 81600)
                     1801   TABLE ACCESS BY INDEX ROWID HZ_PARTIES 
                     1801    INDEX UNIQUE SCAN HZ_PARTIES_U1 (object id 172923)
                       15  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                       15   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                       15  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                       15   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                       18  TABLE ACCESS BY INDEX ROWID FND_TERRITORIES_TL 
                       18   INDEX UNIQUE SCAN FND_TERRITORIES_TL_U1 (object id 33342)
                    16819  COUNT STOPKEY 
                    16819   TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_HEADERS 
                    18369    INDEX RANGE SCAN ONTC_MTC_PROFORMA_HEADERS_U2 (object id 3626691)
                    17465  SORT AGGREGATE 
                    21607   NESTED LOOPS OUTER 
                    16819    TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_HEADERS 
                    18369     INDEX RANGE SCAN ONTC_MTC_PROFORMA_HEADERS_U2 (object id 3626691)
                    21607    TABLE ACCESS BY INDEX ROWID ONTC_MTC_PROFORMA_LINES 
                    21607     INDEX RANGE SCAN ONTC_MTC_PROFORMA_LINES_PK (object id 3626694)
                    22671  NESTED LOOPS  
                    22671   NESTED LOOPS  
                    22671    NESTED LOOPS  
                    22671     NESTED LOOPS  
                    22671      NESTED LOOPS  
                    22671       NESTED LOOPS  
                    22671        NESTED LOOPS  
                    22671         NESTED LOOPS  
                    22671          NESTED LOOPS  
                    22671           NESTED LOOPS  
                    22671            NESTED LOOPS  
                    22671             NESTED LOOPS  
                    22671              NESTED LOOPS  
                    22671               NESTED LOOPS  
                    22708                NESTED LOOPS  
                    22708                 NESTED LOOPS  
                   103695                  NESTED LOOPS  
                   103695                   NESTED LOOPS  
                    79913                    HASH JOIN  
                      620                     TABLE ACCESS FULL WSH_ORG_CARRIER_SERVICES 
                    17500                     HASH JOIN  
                      180                      TABLE ACCESS FULL WSH_CARRIER_SERVICES 
                    17497                      HASH JOIN  
                      106                       TABLE ACCESS FULL WSH_CARRIERS 
                    17497                       TABLE ACCESS FULL RCV_SHIPMENT_HEADERS 
                   103695                    TABLE ACCESS BY INDEX ROWID RCV_SHIPMENT_LINES 
                   103695                     INDEX RANGE SCAN RCV_SHIPMENT_LINES_U2 (object id 45223)
                   103695                   TABLE ACCESS BY INDEX ROWID PO_LINES_ALL 
                   103695                    INDEX UNIQUE SCAN PO_LINES_U1 (object id 45106)
                    22708                  TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL 
                   103695                   INDEX UNIQUE SCAN PO_LINE_LOCATIONS_U1 (object id 45143)
                    22708                 TABLE ACCESS BY INDEX ROWID PO_HEADERS_ALL 
                    22708                  INDEX UNIQUE SCAN PO_HEADERS_U1 (object id 45052)
                    22671                TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL 
                    22672                 INDEX UNIQUE SCAN OE_ORDER_LINES_U1 (object id 42102)
                    22671               TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL 
                    22671                INDEX UNIQUE SCAN OE_ORDER_HEADERS_U1 (object id 41952)
                    22671              TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS 
                    22671               INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (object id 81600)
                    22671             TABLE ACCESS BY INDEX ROWID HZ_PARTIES 
                    22671              INDEX UNIQUE SCAN HZ_PARTIES_U1 (object id 172923)
                    22671            TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL 
                    22671             INDEX UNIQUE SCAN HZ_CUST_SITE_USES_U1 (object id 172912)
                    22671           TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL 
                    22671            INDEX UNIQUE SCAN HZ_CUST_ACCT_SITES_U1 (object id 172915)
                    22671          TABLE ACCESS BY INDEX ROWID HZ_PARTY_SITES 
                    22671           INDEX UNIQUE SCAN HZ_PARTY_SITES_U1 (object id 172921)
                    22671         TABLE ACCESS BY INDEX ROWID HZ_LOCATIONS 
                    22671          INDEX UNIQUE SCAN HZ_LOCATIONS_U1 (object id 172918)
                    22671        INDEX RANGE SCAN HZ_LOC_ASSIGNMENTS_N1 (object id 82023)
                    22671       TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL 
                    22671        INDEX UNIQUE SCAN HZ_CUST_SITE_USES_U1 (object id 172912)
                    22671      TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL 
                    22671       INDEX UNIQUE SCAN HZ_CUST_ACCT_SITES_U1 (object id 172915)
                    22671     TABLE ACCESS BY INDEX ROWID HZ_PARTY_SITES 
                    22671      INDEX UNIQUE SCAN HZ_PARTY_SITES_U1 (object id 172921)
                    22671    TABLE ACCESS BY INDEX ROWID HZ_LOCATIONS 
                    22671     INDEX UNIQUE SCAN HZ_LOCATIONS_U1 (object id 172918)
                    22671   INDEX RANGE SCAN HZ_LOC_ASSIGNMENTS_N1 (object id 82023)
                  
                  
                  Elapsed times include waiting on following events:
                    Event waited on                             Times   Max. Wait  Total Waited
                    ----------------------------------------   Waited  ----------  ------------
                    SQL*Net message to client                     228        0.00          0.00
                    SQL*Net more data to client                 11289      232.41       1595.87
                    db file sequential read                    113423        0.21        226.54
                    db file scattered read                       2413        0.21          9.06
                    SQL*Net message from client                   228       64.62       1348.85
                    latch free                                     37        0.06          0.56
                  ********************************************************************************
                  Q)Why are you fetching 22671 rows to the client application?
                  A)This query is called from a front end application which displays a report to the user.User can give filter criteria from front-end and all the records that satisfy the criteria will be displayed.But when user doesn't give any filter criteria, we should display all the records.
                  Q)In your original post, you mentioned that this query takes 30 minutes to execute but the TkProf shows that your query is executed in just over 7 minutes. So which one is correct?
                  A)It is actually taking 30 mins.Please see in the trace file posted above.

                  Please let me know if you have any other doubts.

                  Regards,
                  Sreekanth

                  Edited by: Sreekanth Munagala on Sep 9, 2010 4:09 AM

                  Edited by: Sreekanth Munagala on Sep 9, 2010 4:11 AM
                  • 6. Re: Need help in tuning a SQL statement
                    jihuyao
                    It may be more benefitial to use pll as driving table. what are the two counts below?

                    select count(*) from pll;
                    select count(*) from pll where
                    NVL(pll.approved_flag, 'N') = 'Y'
                    AND pll.shipment_type = 'STANDARD';
                    • 7. Re: Need help in tuning a SQL statement
                      user503699
                      Sreekanth Munagala wrote:
                      Hi Narendra,

                      I have generated the TkProf report using the commands given by you.
                      But it is still not showing the resource consumption details against each step in the Row Source Operation.
                      I have analyzed the tables using the below command
                      ANALYZE TABLE <tablename> COMPUTE STATISTICS
                      You should be using DBMS_STATS.GATHER_TABLE_STATS and DBMS_STATS.GATHER_INDEX_STATS
                      in order to gather statistics and not ANALYZE.
                      Q)Why are you fetching 22671 rows to the client application?
                      A)This query is called from a front end application which displays a report to the user.User can give filter criteria from front-end and all the records that satisfy the criteria will be displayed.But when user doesn't give any filter criteria, we should display all the records.
                      You may want to revalidate this requirement. I am not sure if user REALLY wants to see a report containing 22617 rows.
                      Q)In your original post, you mentioned that this query takes 30 minutes to execute but the TkProf shows that your query is executed in just over 7 minutes. So which one is correct?
                      A)It is actually taking 30 mins.Please see in the trace file posted above.
                      Yes. I missed some important wait event details from the report. Apologies.
                      Please let me know if you have any other doubts.
                      The execution with ALL_ROWS optimizer mode is consuming more resources and taking more time than the one with CHOOSE mode.
                      This probably means the execution with CHOOSE optimizer mode used RULE based optimizer.
                      This DOES NOT mean that cost-based optimizer is bad but it might be the result of incomplete and/or incorrect object statistics. As mentioned
                      above, you may want to use DBMS_STATS to collect statistics for objects used in this query (and as a standard going forward).
                      One important detail that I failed to notice is following wait event details (from your original TkProf)
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        SQL*Net message to client                     228        0.00          0.00
                        SQL*Net more data to client                 11289        7.48        374.83
                        db file scattered read                       2408        0.05          1.60
                        SQL*Net message from client                   228       11.38        406.34
                        db file sequential read                     25769        0.47         28.17
                        latch free                                      1        0.01          0.01
                      ********************************************************************************
                      The presense of "SQL*Net more data to client" wait event indicates that your resultset size is larger than what is called Session Data Unit (SDU), which
                      is unit of data transferred between server and client. Also, if I am not mistaken, the presense of this event has also forced the time spent on
                      "SQL*Net message from client" wait event.
                      I believe your arraysize is 100, which is OK but looking at your query it appears your resultset size is quite large (because of so many columns selected
                      and I suspect most of the character columns are quite large in size, by definition).
                      You may want to check with your DBA to see if SDU size can be changed to accommodate this kind of large resultset.
                      • 8. Re: Need help in tuning a SQL statement
                        user503699
                        user503699 wrote:
                        You should be using DBMS_STATS.GATHER_TABLE_STATS and DBMS_STATS.GATHER_INDEX_STATS
                        in order to gather statistics and not ANALYZE.
                        Another point that I missed is you appear to be using "Oracle Applications" database. In that case, I believe oracle has provided a package named FND_STATS in order to collect statistics on objects. So you should be using that instead to collect statistics on related objects.
                        • 9. Re: Need help in tuning a SQL statement
                          PavelE.
                          Hi

                          Could you post result of this select:
                          select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_tab_histograms where table_name='RCV_SHIPMENT_HEADERS';
                          Best regards, Pavel.
                          • 10. Re: Need help in tuning a SQL statement
                            638677
                            Hi Jihuyao,

                            Here is the count of the queries
                            APPS$select count(*) from po_line_locations_all pll;
                            
                             COUNT(*)                                                                                                                                                                                               
                            ---------                                                                                                                                                                                               
                               513474                                                                                                                                                                                               
                            
                            APPS$SELECT COUNT(*)
                              2  FROM po_line_locations_all pll
                              3  WHERE NVL(pll.approved_flag, 'N') = 'Y'
                              4  AND pll.shipment_type = 'STANDARD';
                            
                             COUNT(*)                                                                                                                                                                                               
                            ---------                                                                                                                                                                                               
                               128758  
                            Also, can you please tell how will it be beneficial by choosing pll as driving table?

                            Regards,
                            Sreekanth Munagala
                            • 11. Re: Need help in tuning a SQL statement
                              638677
                              Hi Narendra,

                              Narendra wrote:
                              Another point that I missed is you appear to be using "Oracle Applications" database. In that case, I believe oracle has provided a package named FND_STATS in order to collect statistics on objects. So you should be using that instead to collect statistics on related objects.

                              Yes.You were right in saying that we are using "Oracle Applications" database.I have checked the FND_STATS package and there is an overloaded procedure GATHER_TABLE_STATS to gather table statistics.The procedure accepts the following parameters and i am not sure to pass which values to the parameters except table_name and owner_name.
                              procedure GATHER_TABLE_STATS(ownname in varchar2,
                                                           tabname  in varchar2,
                                                           percent  in number default null,
                                                           degree in number default null,
                                                           partname in varchar2 default null,
                                                           backup_flag in varchar2 default 'NOBACKUP',
                                                           cascade  in boolean default true,
                                                           granularity  in varchar2 default 'DEFAULT',
                                                            hmode in varchar2 default 'LASTRUN',
                                                            invalidate    in varchar2 default 'Y'
                                                          );
                              
                              procedure GATHER_TABLE_STATS(errbuf OUT NOCOPY  varchar2,
                                                           retcode OUT NOCOPY  varchar2,
                                                           ownname in varchar2,
                                                           tabname  in varchar2,
                                                           percent  in number default null,
                                                           degree in number default null,
                                                           partname in varchar2 default null,
                                                           backup_flag in varchar2 default 'NOBACKUP',
                                                           granularity  in varchar2 default 'DEFAULT',
                                                            hmode in varchar2 default 'LASTRUN',
                                                            invalidate    in varchar2 default 'Y'
                                                           );
                              Which version of the procedure should i use and is it ok if i give only table_name and owner_name and leave the remainining parameters to take DEFAULT values?

                              Also, can you please tell or point to any documentation to see the differences between ANALYZE, DBMS_STATS and FND_STATS?

                              You may want to revalidate this requirement. I am not sure if user REALLY wants to see a report containing 22617 rows.

                              Yes, i will check with business regarding this.

                              Yes. I missed some important wait event details from the report. Apologies.

                              I did not understand this.In the initial TKPROF output, the elapsed time was 421.60 sec and as you said earlier, query took only 7 mins to complete.
                              But in the second TKPROF output the elapsed time was 1867.45 sec and this is equal to 31 mins.Can you please tell what details you have missed in getting the total time taken?

                              I believe your arraysize is 100, which is OK but looking at your query it appears your resultset size is quite large (because of so many columns selected
                              and I suspect most of the character columns are quite large in size, by definition).
                              You may want to check with your DBA to see if SDU size can be changed to accommodate this kind of large resultset.

                              Yes, the arraysize is 100.I will check with DBA regarding this.

                              Regards,
                              Sreekanth Munagala.

                              Edited by: Sreekanth Munagala on Sep 13, 2010 3:10 AM
                              • 12. Re: Need help in tuning a SQL statement
                                user503699
                                Sreekanth Munagala wrote:
                                Which version of the procedure should i use and is it ok if i give only table_name and owner_name and leave the remainining parameters to take DEFAULT values?
                                You may want to check with your DBA about which version you should use. The procedure definitions
                                appear to suggest that one of the procedure is used by a concurrent program (which may be already
                                scheduled to run at specific time intervals)
                                Also, can you please tell or point to any documentation to see the differences between ANALYZE, DBMS_STATS and FND_STATS?
                                I don't know if there exists any specific section of Oracle Database documentation which states difference
                                between ANALYZE and DBMS_STATS. But I am sure that from 9i onwards, one is expected to use DBMS_STATS to collect statistics on tables/indexes. FND_STATS is specific to Oracle Applications and you may be able to find details about the same in some Oracle Applications Technical Documentation.
                                Yes. I missed some important wait event details from the report. Apologies.

                                I did not understand this.In the initial TKPROF output, the elapsed time was 421.60 sec and as you said earlier, query took only 7 mins to complete.
                                But in the second TKPROF output the elapsed time was 1867.45 sec and this is equal to 31 mins.If Elapsed time is not the total time taken by the query, can you please tell how to calculate the total time taken including the wait event details?
                                In TkProf, there is an "elapsed time" reported for a query execution, which does not include time spent on sending data over network to the client ("SQL*Net Message to Client" event). In both the TkProf reports that you have provided, there is large amount of time reported against these events. When you mention "query takes 30 minutes to execute", you are probably measuring the time at the client side to fetch all the results of the query. But it is possible that database was able to process your query very quickly but majority of the time (which you see as "execution time") was spent on transferring the data from server to the client.
                                Depending upon how much time and interest you have, you may want to either continue investigating the query with CHOOSE optimizer mode (which is probably using RULE based optimizer and is giving you a better response time at the moment) or dig further to see if you can use cost-based optimizer in addition to tuning your query.
                                Hope this helps.
                                • 13. Re: Need help in tuning a SQL statement
                                  638677
                                  Hi Narendra,

                                  In TkProf, there is an "elapsed time" reported for a query execution, which does not include time spent on sending data over network to the client ("SQL*Net Message to Client" event).

                                  Can you please tell what exactly is "elapsed time"? In the second TKPROF elapsed time is 31 mins. What exactly did oracle do in this 31 mins?

                                  But it is possible that database was able to process your query very quickly but majority of the time (which you see as "execution time") was spent on transferring the data from server to the client.

                                  Is there a way to know
                                  a)In what time did the database process the query?
                                  b)How much time was spent on transferring over the network?(Through Wait events information shown in TKPROF output?)

                                  Depending upon how much time and interest you have, you may want to either continue investigating the query with CHOOSE optimizer mode (which is probably using RULE based optimizer and is giving you a better response time at the moment) or dig further to see if you can use cost-based optimizer in addition to tuning your query.

                                  I will ANALYZE all the tables using FND_STATS and see the performance.

                                  Regards,
                                  Sreekanth Munagala.
                                  • 14. Re: Need help in tuning a SQL statement
                                    3004
                                    Get a clarity at what you are asking. The answer resides in your question itself.

                                    Thota,
                                    Certified Oracle Professional
                                    http://www.infotechse.info/jobs/idevaffiliate.php?id=5536
                                    1 2 3 Previous Next