8 Replies Latest reply on Apr 3, 2008 12:21 PM by 628428

    Tuning of SQL query in PO base tables

    616678
      Hi all,

      Is there any way to optimize the below query as its taking too many hours to exectute when I pass the dates parameters for long period like eg. 6months to 1 year. Am I missing something...?

      SELECT poh.org_id,
      rcvt.organization_id,
      mcat.segment1,
           pov.vendor_name,
           poh.vendor_id,
           rcvt.transaction_type,
           rcvt.primary_quantity,
      poh.rate,
      pol.unit_price,     
           NULL,
      pvs.attribute8,
      pvs.address_line1,
           pvs.address_line2,
           pvs.city,
           pvs.state,
           pvs.zip,
           pvs.country
      FROM rcv_transactions rcvt,
      mtl_item_categories icat,
      po_line_locations_all poll,
      po_lines_all pol,
      mtl_system_items_b msi,
      po_headers_all poh,
      po_vendors pov,
      mtl_categories_b mcat,
      po_vendor_sites_all      pvs,
      mtl_categories_b mcatedi
      WHERE pov.vendor_id = poh.vendor_id
      AND poh.po_header_id = pol.po_header_id
      AND msi.inventory_item_id = pol.item_id
      AND pov.vendor_id = pvs.vendor_id
      AND pvs.vendor_site_id = poh.vendor_site_id
      AND msi.organization_id = 117
      AND mcatedi.category_id = pol.category_id
      AND poll.po_line_id = pol.po_line_id
      AND icat.organization_id = msi.organization_id +0
      AND icat.inventory_item_id = msi.inventory_item_id +0
      AND icat.category_set_id = 1
      AND icat.category_id = mcat.category_id
      AND rcvt.po_line_location_id = poll.line_location_id
      AND rcvt.transaction_date BETWEEN p_Start_Date AND p_End_Date
      AND ((rcvt.transaction_type = 'RETURN TO VENDOR') OR (rcvt.transaction_type = 'RECEIVE' ))
      AND ((rcvt.transaction_type = 'RECEIVE') OR (rcvt.transaction_type = 'RETURN TO VENDOR')
      AND nvl(rcvt.reason_id, 110) IN
      (
      SELECT reason_id
      FROM mtl_transaction_reasons
      WHERE disable_date IS NULL AND reason_id NOT IN(20 , 21 , 22 , 23, 24 , 25 , 26 ,27 , 28 , 29 , 50 , 51 , 52 , 53 , 54 , 55 ,100 ,102, 103, 122 ,123 , 124)
      ));

      Thank you all in advance....
        • 1. Re: Tuning of SQL query in PO base tables
          628722
          I would suggest generating the explain plan for this query and see which table are having full table scan etc. Based on theexplain plan we can explore the option of giving hints in the query

          Thanks
          Arun
          • 2. Re: Tuning of SQL query in PO base tables
            616678
            this is the report of explain plan please go through...

            ------------------------------------------------------------

            Statement Id=4203132 Type=
            Cost=2.64022111505165E-308 TimeStamp=02-04-08::16::29:26

            (1) SELECT STATEMENT ALL_ROWS
            Est. Rows: 1 Cost: 9,793
            FILTER
            (27) TABLE TABLE ACCESS BY INDEX ROWID PO.RCV_TRANSACTIONS [Analyzed]
            (27) Blocks: 749,842 Est. Rows: 1 of 16,890,400 Cost: 2
            Tablespace: POD
            (26) NESTED LOOPS
            Est. Rows: 1 Cost: 9,793
            (24) NESTED LOOPS
            Est. Rows: 2 Cost: 9,789
            (21) NESTED LOOPS
            Est. Rows: 1 Cost: 9,788
            (18) NESTED LOOPS
            Est. Rows: 1 Cost: 9,787
            (16) NESTED LOOPS
            Est. Rows: 1 Cost: 9,786
            (14) NESTED LOOPS
            Est. Rows: 5 Cost: 9,785
            (12) NESTED LOOPS
            Est. Rows: 15 Cost: 9,784
            (9) HASH JOIN
            Est. Rows: 14 Cost: 9,777
            (5) VIEW VIEW PO.index$_join$_007
            Est. Rows: 53,570 Cost: 54
            (4) HASH JOIN
            (2) INDEX (UNIQUE) INDEX FAST FULL SCAN PO.PO_VENDORS_U1 [Analyzed]
            Est. Rows: 53,570 Cost: 14
            (3) INDEX (UNIQUE) INDEX FAST FULL SCAN PO.PO_VENDORS_U2 [Analyzed]
            Est. Rows: 53,570 Cost: 38
            (8) HASH JOIN
            Est. Rows: 775,370 Cost: 9,717
            (6) TABLE TABLE ACCESS FULL PO.PO_VENDOR_SITES_ALL [Analyzed]
            (6) Blocks: 5,907 Est. Rows: 120,010 of 120,010 Cost: 1,154
            Tablespace: POD
            (7) TABLE TABLE ACCESS FULL PO.PO_HEADERS_ALL [Analyzed]
            (7) Blocks: 35,077 Est. Rows: 779,450 of 779,450 Cost: 6,872
            Tablespace: POD
            (11) TABLE TABLE ACCESS BY INDEX ROWID PO.PO_LINES_ALL [Analyzed]
            (11) Blocks: 64,217 Est. Rows: 1 of 1,582,010 Cost: 1
            Tablespace: POD
            (10) INDEX (UNIQUE) INDEX RANGE SCAN PO.PO_LINES_U2 [Analyzed]
            Est. Rows: 6 Cost: 1
            (13) INDEX (UNIQUE) INDEX UNIQUE SCAN INV.MTL_SYSTEM_ITEMS_B_U1 [Analyzed]
            Est. Rows: 1 Cost: 1
            (15) INDEX (UNIQUE) INDEX RANGE SCAN INV.MTL_ITEM_CATEGORIES_U1 [Analyzed]
            Est. Rows: 1 Cost: 1
            (17) INDEX (UNIQUE) INDEX UNIQUE SCAN INV.MTL_CATEGORIES_B_U1 [Analyzed]
            Est. Rows: 1 Cost: 1
            (20) TABLE TABLE ACCESS BY INDEX ROWID INV.MTL_CATEGORIES_B [Analyzed]
            (20) Blocks: 79 Est. Rows: 1 of 4,421 Cost: 1
            Tablespace: INVD
            (19) INDEX (UNIQUE) INDEX UNIQUE SCAN INV.MTL_CATEGORIES_B_U1 [Analyzed]
            Est. Rows: 1 Cost: 1
            (23) TABLE TABLE ACCESS BY INDEX ROWID PO.PO_LINE_LOCATIONS_ALL [Analyzed]
            (23) Blocks: 102,297 Est. Rows: 3 of 2,208,500 Cost: 1
            Tablespace: POD
            (22) INDEX INDEX RANGE SCAN PO.PO_LINE_LOCATIONS_N1 [Analyzed]
            Est. Rows: 3 Cost: 1
            (25) INDEX INDEX RANGE SCAN PO.RCV_TRANSACTIONS_N6 [Analyzed]
            Est. Rows: 29 Cost: 1
            (30) FILTER
            (29) TABLE TABLE ACCESS BY INDEX ROWID INV.MTL_TRANSACTION_REASONS [Analyzed]
            (29) Blocks: 6 Est. Rows: 1 of 196 Cost: 1
            Tablespace: INVD
            (28) INDEX (UNIQUE) INDEX UNIQUE SCAN INV.MTL_TRANSACTION_REASONS_U1 [Analyzed]
            Est. Rows: 1 Cost: 1
            • 3. Re: Tuning of SQL query in PO base tables
              628722
              No wonder there is a FULL Table scan on PO.PO_HEADERS_ALL & PO_VENDOR_SITES_ALL. This it self is enuf to kill the query. Let me analyse
              • 4. Re: Tuning of SQL query in PO base tables
                628428
                Hi,
                Please try this:

                SELECT poh.org_id, rcvt.organization_id, mcat.segment1, pov.vendor_name, poh.vendor_id
                , rcvt.transaction_type, rcvt.primary_quantity, poh.rate, pol.unit_price, NULL, pvs.attribute8
                , pvs.address_line1, pvs.address_line2, pvs.city, pvs.state, pvs.zip, pvs.country
                FROM po_headers_all poh
                , po_lines_all pol
                , po_line_locations_all poll
                , po_vendors pov
                , po_vendor_sites_all pvs
                , rcv_transactions rcvt
                , mtl_system_items_b msi
                , mtl_item_categories icat
                , mtl_categories_b mcat
                --, mtl_categories_b mcatedi
                WHERE poh.po_header_id = pol.po_header_id
                AND pol.po_line_id = poll.po_line_id
                AND poh.vendor_id = pov.vendor_id
                AND pov.vendor_id = pvs.vendor_id
                AND pol.item_id = msi.inventory_item_id
                AND rcvt.organization_id = msi.organization_id
                --AND pvs.vendor_site_id = poh.vendor_site_id
                AND msi.organization_id = 117
                --AND mcatedi.category_id = pol.category_id
                AND msi.organization_id = icat.organization_id
                AND msi.inventory_item_id = icat.inventory_item_id
                AND icat.category_set_id = 1
                AND icat.category_id = mcat.category_id
                AND rcvt.po_line_location_id = poll.line_location_id
                AND trunc(rcvt.transaction_date) BETWEEN p_start_date AND p_end_date
                AND ((rcvt.transaction_type = 'RETURN TO VENDOR') OR (rcvt.transaction_type = 'RECEIVE'))
                AND ( (rcvt.transaction_type = 'RECEIVE')
                OR (rcvt.transaction_type = 'RETURN TO VENDOR')
                AND NVL (rcvt.reason_id, 110) IN (
                SELECT reason_id
                FROM mtl_transaction_reasons
                WHERE disable_date IS NULL
                AND reason_id NOT IN
                (20
                , 21
                , 22
                , 23
                , 24
                , 25
                , 26
                , 27
                , 28
                , 29
                , 50
                , 51
                , 52
                , 53
                , 54
                , 55
                , 100
                , 102
                , 103
                , 122
                , 123
                , 124
                ))
                );

                Thanks
                Nagamohan
                • 5. Re: Tuning of SQL query in PO base tables
                  616678
                  Hi Nagamohan,

                  Thank you for your update.
                  Its not giving me any results, I am not able to get records. Tables PO_HEADERS and PO_VENDOR_SITES_ALL are getting scanned FULL as per the Explain Plan, what I suppose to do to prevent? Can I use any hints here.

                  Thank you

                  Message was edited by:
                  user613675
                  • 6. Re: Tuning of SQL query in PO base tables
                    628428
                    This is the explain I have in my instance. Looks like there is a different in indexes.
                    I have no full table scan on any table in the SQL I sent you. There are the changes I have made:

                    Removing the table --, mtl_categories_b mcatedi. It is not necessary to link the categories to PO lines unless you are entering POs without items. That I think you are not (looks like) as you are looking at the transactions line Receive and Retrun to vendor so I assumed these inventory purchases where you are using item. Hence removed that.

                    One more possibility is that you are reciving the expense POs also (accruing on receipt for expese purchases). If you want to add that table and condition (AND mcatedi.category_id = pol.category_id) add that and test.

                    Mistakenly commented out AND pvs.vendor_site_id = poh.vendor_site_id. You can add that back.

                    Added this where condition:
                    AND rcvt.organization_id = msi.organization_id

                    Reason: You are receiving into organization where the item is assigned to. If you remove this where condition it will do full table scan on the tables you mentioned along with the po_vendors_all. This is what making all the difference.

                    Remaining all is your SQL only.

                    SQL
                    ====================================
                    SELECT poh.org_id, rcvt.organization_id, mcat.segment1, pov.vendor_name, poh.vendor_id
                    , rcvt.transaction_type, rcvt.primary_quantity, poh.rate, pol.unit_price, NULL, pvs.attribute8
                    , pvs.address_line1, pvs.address_line2, pvs.city, pvs.state, pvs.zip, pvs.country
                    FROM po_headers_all poh
                    , po_lines_all pol
                    , po_line_locations_all poll
                    , po_vendors pov
                    , po_vendor_sites_all pvs
                    , rcv_transactions rcvt
                    , mtl_system_items_b msi
                    , mtl_item_categories icat
                    , mtl_categories_b mcat
                    , mtl_categories_b mcatedi
                    WHERE poh.po_header_id = pol.po_header_id
                    AND pol.po_line_id = poll.po_line_id
                    AND poh.vendor_id = pov.vendor_id
                    AND pov.vendor_id = pvs.vendor_id
                    AND pol.item_id = msi.inventory_item_id
                    AND rcvt.organization_id = msi.organization_id
                    AND pvs.vendor_site_id = poh.vendor_site_id
                    AND msi.organization_id = 117
                    AND mcatedi.category_id = pol.category_id
                    AND msi.organization_id = icat.organization_id
                    AND msi.inventory_item_id = icat.inventory_item_id
                    AND icat.category_set_id = 1
                    AND icat.category_id = mcat.category_id
                    AND rcvt.po_line_location_id = poll.line_location_id
                    AND trunc(rcvt.transaction_date) BETWEEN ('01-JAN-2008') AND ('31-MAR-2008')
                    AND ((rcvt.transaction_type = 'RETURN TO VENDOR') OR (rcvt.transaction_type = 'RECEIVE'))
                    AND ( (rcvt.transaction_type = 'RECEIVE')
                    OR (rcvt.transaction_type = 'RETURN TO VENDOR')
                    AND NVL (rcvt.reason_id, 110) IN (
                    SELECT reason_id
                    FROM mtl_transaction_reasons
                    WHERE disable_date IS NULL
                    AND reason_id NOT IN
                    (20
                    , 21
                    , 22
                    , 23
                    , 24
                    , 25
                    , 26
                    , 27
                    , 28
                    , 29
                    , 50
                    , 51
                    , 52
                    , 53
                    , 54
                    , 55
                    , 100
                    , 102
                    , 103
                    , 122
                    , 123
                    , 124
                    ))
                    );

                    ==================
                    Plan
                    ======================
                    SELECT STATEMENT CHOOSECost: 388 Bytes: 306 Cardinality: 1                                                             
                         30 FILTER                                                        
                              26 NESTED LOOPS Cost: 388 Bytes: 306 Cardinality: 1                                                   
                                   24 NESTED LOOPS Cost: 388 Bytes: 302 Cardinality: 1                                              
                                        21 NESTED LOOPS Cost: 387 Bytes: 290 Cardinality: 1                                         
                                             18 NESTED LOOPS Cost: 385 Bytes: 150 Cardinality: 1                                    
                                                  15 NESTED LOOPS Cost: 384 Bytes: 122 Cardinality: 1                               
                                                       12 NESTED LOOPS Cost: 383 Bytes: 91 Cardinality: 1                          
                                                            10 NESTED LOOPS Cost: 381 Bytes: 73 Cardinality: 1                     
                                                                 8 NESTED LOOPS Cost: 380 Bytes: 64 Cardinality: 1                
                                                                      5 NESTED LOOPS Cost: 379 Bytes: 41 Cardinality: 1           
                                                                           2 TABLE ACCESS BY INDEX ROWID PO.RCV_TRANSACTIONS Cost: 378 Bytes: 31 Cardinality: 1      
                                                                                1 INDEX RANGE SCAN NON-UNIQUE PO.RCV_TRANSACTIONS_N22 Cost: 11 Cardinality: 3,490
                                                                           4 TABLE ACCESS BY INDEX ROWID PO.PO_LINE_LOCATIONS_ALL Cost: 1 Bytes: 10 Cardinality: 1      
                                                                                3 INDEX UNIQUE SCAN UNIQUE PO.PO_LINE_LOCATIONS_U1 Cardinality: 1
                                                                      7 TABLE ACCESS BY INDEX ROWID PO.PO_LINES_ALL Cost: 1 Bytes: 23 Cardinality: 1           
                                                                           6 INDEX UNIQUE SCAN UNIQUE PO.PO_LINES_U1 Cardinality: 1      
                                                                 9 INDEX UNIQUE SCAN UNIQUE INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Bytes: 9 Cardinality: 1                
                                                            11 INDEX RANGE SCAN UNIQUE INV.MTL_ITEM_CATEGORIES_U1 Cost: 2 Bytes: 18 Cardinality: 1                     
                                                       14 TABLE ACCESS BY INDEX ROWID PO.PO_HEADERS_ALL Cost: 1 Bytes: 31 Cardinality: 1                          
                                                            13 INDEX UNIQUE SCAN UNIQUE PO.PO_HEADERS_U1 Cardinality: 1                     
                                                  17 TABLE ACCESS BY INDEX ROWID PO.PO_VENDORS Cost: 1 Bytes: 28 Cardinality: 1                               
                                                       16 INDEX UNIQUE SCAN UNIQUE PO.PO_VENDORS_U1 Cardinality: 1                          
                                             20 TABLE ACCESS BY INDEX ROWID PO.PO_VENDOR_SITES_ALL Cost: 2 Bytes: 140 Cardinality: 1                                    
                                                  19 INDEX RANGE SCAN UNIQUE PO.PO_VENDOR_SITES_U1 Cost: 1 Cardinality: 1                               
                                        23 TABLE ACCESS BY INDEX ROWID INV.MTL_CATEGORIES_B Cost: 1 Bytes: 12 Cardinality: 1                                         
                                             22 INDEX UNIQUE SCAN UNIQUE INV.MTL_CATEGORIES_B_U1 Cardinality: 1                                    
                                   25 INDEX UNIQUE SCAN UNIQUE INV.MTL_CATEGORIES_B_U1 Bytes: 4 Cardinality: 1                                              
                              29 FILTER                                                   
                                   28 TABLE ACCESS BY INDEX ROWID INV.MTL_TRANSACTION_REASONS Cost: 1 Bytes: 4 Cardinality: 1                                              
                                        27 INDEX UNIQUE SCAN UNIQUE INV.MTL_TRANSACTION_REASONS_U1 Cardinality: 1                                         
                                                  
                    Thanks
                    Nagamohan
                    • 7. Re: Tuning of SQL query in PO base tables
                      616678
                      Hi Nagamohan,

                      Are you able to fetching the data with the query? But when I execute the same query I am getting 'No rows return'. I do not know why is it so..? Can you trace out the causing error please....

                      Thanks a lot Nagamohan...
                      • 8. Re: Tuning of SQL query in PO base tables
                        628428
                        I am getting rows.Are you getting rows with your original query? Can you uncomment the condition rcvt.organization_Id=msi.organization_Id and run the query for a small period of time where you think you have rows?

                        But that will do full table scan, so if you can use one PO that is even better.

                        Thanks
                        Nagamohan