1 2 3 Previous Next 33 Replies Latest reply: Aug 15, 2013 9:31 AM by Nikolay Savvinov RSS

    help me on this Query tunning !!

    967023

      Hello Guru's

       

      Please help me in this query tunning - taking huge time.

       

      SELECT /* ZOZO */ DISTINCT SHPM.DOMAIN_NAME,

             CP.CORPORATION_NAME CUSTOMER_NAME,

             SHPM.SHIPMENT_GID SHIPMENT_GID,

             SHIPMENT_XID BUY_SHIPMENT_ID,

             F_GET_ORDER_RELEASE_GID('B',SHPM.SHIPMENT_GID,0) ORDER_RELEASE_ID,

             DECODE(F_GET_POD_RECEIVED_DATE(SHPM.SHIPMENT_GID),'01-JAN-50','',(TO_CHAR(F_GET_POD_RECEIVED_DATE(SHPM.SHIPMENT_GID),'MM/DD/YYYY'))) RECEIVED_DATE,

             TRUNC(F_TZ_OFFSET(SHPM.SOURCE_LOCATION_GID,SHPM.START_TIME)) BUY_SHIPMENT_START_DATE,

             Glog_util.remove_domain(SHPM.SERVPROV_GID) SCAC,

             LOC.LOCATION_NAME CARRIER_NAME,

             Glog_util.remove_domain(SS.Status_value_gid ) CARRIER_REPORTED_STATUS,

             F_GET_REFNUM_STRING('SHIPMENT',SHPM.SHIPMENT_GID,'MBOL_NUMBER_CLEANSED')MBOL_NUMBER,

             round(DECODE(SHPM.PERSPECTIVE,'B',SHPM.TOTAL_ACTUAL_COST,'0'),'2') BUY_SHIPMENT_COST,

             SHPM.T_ACTUAL_COST_CURRENCY_GID BUY_SHIPMENT_CURRENCY,

             GLOGUPS.F_GET_INVOICE_VOUCHER_BILL('INVOICE_VOUCHER',SHPM.SHIPMENT_GID) SHIPMENT_VOUCHER_NUMBER

      FROM SHIPMENT SHPM,

           SHIPMENT_STATUS SS,

           SHIPMENT_STATUS SS2,

           SHIPMENT_STATUS SS3,

           CORPORATION CP,

           LOCATION  LOC

      WHERE SS.SHIPMENT_GID = SHPM.SHIPMENT_GID

      AND SS2.SHIPMENT_GID = SHPM.SHIPMENT_GID

      AND SS3.SHIPMENT_GID = SHPM.SHIPMENT_GID

      AND SHPM.SERVPROV_GID = LOC.LOCATION_GID

      AND SS.status_type_gid = SS.domain_name||'.SECURE RESOURCES'

      AND SS.status_value_gid in (SS.domain_name ||'.SECURE RESOURCES_PICKUP NOTIFICATION', SS.domain_name ||'.SECURE RESOURCES_ACCEPTED')

      AND  ((SS2.STATUS_TYPE_GID IN (SS2.domain_name || '.CARRIER_REPORTED')

            AND      SS2.status_value_gid =  (SS2.domain_name || '.CARRIER_REPORTED_DELIVERED'))

            OR   SS3.STATUS_TYPE_GID = (SS3.domain_name || '.UPS_FINANCE_POD')

             AND      SS3.status_value_gid = (SS3.domain_name || '.UPS_FINANCE_POD_FORCED'))

      AND (SHPM.DOMAIN_NAME = CP.DOMAIN_NAME AND CP.IS_DOMAIN_MASTER = 'Y')

      AND SHPM.PERSPECTIVE = 'B'

      And shpm.domain_name like 'UPS/CP/DFP/%'

      And 1=1

      And 1=1

      and f_get_sell_id_string(ss.shipment_gid) is null

      order by CP.CORPORATION_NAME, SHIPMENT_XID;

       

       


      Elapsed: 00:34:47.08

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2120761244

      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                  |                    |     1 |   571 |  1325   (1)| 00:00:16 |
      |   1 |  SORT UNIQUE                      |                    |     1 |   571 |  1324   (1)| 00:00:16 |
      |   2 |   NESTED LOOPS                    |                    |     1 |   571 |  1323   (1)| 00:00:16 |
      |   3 |    NESTED LOOPS                   |                    |     1 |   512 |  1322   (1)| 00:00:16 |
      |   4 |     NESTED LOOPS                  |                    |     1 |   395 |  1320   (1)| 00:00:16 |
      |   5 |      NESTED LOOPS                 |                    |     1 |   278 |  1319   (1)| 00:00:16 |
      |*  6 |       HASH JOIN                   |                    |   109 | 17549 |  1100   (1)| 00:00:14 |
      |   7 |        TABLE ACCESS BY INDEX ROWID| CORPORATION        |     4 |   132 |     2   (0)| 00:00:01 |
      |*  8 |         INDEX RANGE SCAN          | IDX$$_22680001     |     4 |       |     1   (0)| 00:00:01 |
      |   9 |        TABLE ACCESS BY INDEX ROWID| SHIPMENT           |  4077 |   509K|  1098   (1)| 00:00:14 |
      |* 10 |         INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |  4077 |       |    17  (24)| 00:00:01 |
      |* 11 |       TABLE ACCESS BY INDEX ROWID | SHIPMENT_STATUS    |     1 |   117 |     2   (0)| 00:00:01 |
      |* 12 |        INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |     2 |       |     2   (0)| 00:00:01 |
      |  13 |      TABLE ACCESS BY INDEX ROWID  | SHIPMENT_STATUS    |    37 |  4329 |     2   (0)| 00:00:01 |
      |* 14 |       INDEX RANGE SCAN            | PK_SHIPMENT_STATUS |    37 |       |     2   (0)| 00:00:01 |
      |* 15 |     TABLE ACCESS BY INDEX ROWID   | SHIPMENT_STATUS    |     1 |   117 |     2   (0)| 00:00:01 |
      |* 16 |      INDEX RANGE SCAN             | PK_SHIPMENT_STATUS |    37 |       |     2   (0)| 00:00:01 |
      |* 17 |    INDEX RANGE SCAN               | IND_LOCID_LOCNAME  |     1 |    59 |     1   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

         6 - access("SHPM"."DOMAIN_NAME"="CP"."DOMAIN_NAME")
         8 - access("CP"."IS_DOMAIN_MASTER"='Y' AND "CP"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%')
             filter("CP"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%')
        10 - access("SHPM"."PERSPECTIVE"='B' AND "SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%')
             filter("SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%')
        11 - filter("SS"."STATUS_TYPE_GID"="SS"."DOMAIN_NAME"||'.SECURE RESOURCES' AND
                    ("SS"."STATUS_VALUE_GID"="SS"."DOMAIN_NAME"||'.SECURE RESOURCES_PICKUP NOTIFICATION' OR
                    "SS"."STATUS_VALUE_GID"="SS"."DOMAIN_NAME"||'.SECURE RESOURCES_ACCEPTED'))
        12 - access("SS"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
             filter("F_GET_SELL_ID_STRING"("SS"."SHIPMENT_GID") IS NULL)
        14 - access("SS2"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
        15 - filter("SS2"."STATUS_TYPE_GID"="SS2"."DOMAIN_NAME"||'.CARRIER_REPORTED' AND
                    "SS2"."STATUS_VALUE_GID"="SS2"."DOMAIN_NAME"||'.CARRIER_REPORTED_DELIVERED' OR
                    "SS3"."STATUS_TYPE_GID"="SS3"."DOMAIN_NAME"||'.UPS_FINANCE_POD' AND
                    "SS3"."STATUS_VALUE_GID"="SS3"."DOMAIN_NAME"||'.UPS_FINANCE_POD_FORCED')
        16 - access("SS3"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
        17 - access("SHPM"."SERVPROV_GID"="LOC"."LOCATION_GID")


      Statistics
      ----------------------------------------------------------
         26136871  recursive calls
                0  db block gets
        312371932  consistent gets
           281730  physical reads
             1504  redo size
           284866  bytes sent via SQL*Net to client
             1690  bytes received via SQL*Net from client
              108  SQL*Net roundtrips to/from client
           131053  sorts (memory)
                0  sorts (disk)
             1591  rows processed

       

       

      Count of the tables are

      1303 ,CORPORATION

      176249 ,LOCATION

      1174538 ,SHIPMENT

      43096186 ,SHIPMENT_STATUS

       

      The stats are upto date - I used sql tunning advisor - It asked me to create few index on these  - But still i did'nt find any changes in Elapsed time.

       

      Recommendation (estimated benefit: 97.95%)
        ------------------------------------------
          create index GLOGOWNER.IDX$$_22680001 on
          GLOGOWNER.CORPORATION("IS_DOMAIN_MASTER","DOMAIN_NAME");

       

          create index GLOGOWNER.IDX$$_22680002 on
          GLOGOWNER.SHIPMENT_STATUS("DOMAIN_NAME","SHIPMENT_GID","STATUS_VALUE_GID");

        • 1. Re: help me on this Query tunning !!
          sb92075

          How do you explain the discrepancy between the EXPLAIN PLAN having 00:16 second completion time & SQL actual having 34:47; which is a LARGE  difference?

           

          Was EXPLAIN PLAN produced on the same database as the SQL was run against?

          • 2. Re: help me on this Query tunning !!
            967023

            Hi Sb,

             

            Yes i ran in the same database.

             

            I used below  method to generate explain plan

             

            SET LINESIZE 130

            SET PAGESIZE 0

            set autotrace traceonly

            set timing on

            <Here i ran the query>

            • 3. Re: help me on this Query tunning !!
              sb92075

              >|   9 |        TABLE ACCESS BY INDEX ROWID| SHIPMENT           |  4077

               

              does query really  return 4077 rows from SHIPMENT  table?

               

              In other words are statistics accurate for all tables & indexes?

              • 4. Re: help me on this Query tunning !!
                nagarw31

                Your select statement contains  DISTINCT  and that is the one reason for  huge time..

                 

                You  remove DISTINCT first and check how much time will it takes..

                And then apply hint accordingly.

                • 5. Re: help me on this Query tunning !!
                  967023

                  The stats are accurate.

                  • 6. Re: help me on this Query tunning !!
                    967023

                    Hi - Which hint do you need to assign..?

                    • 7. Re: help me on this Query tunning !!
                      sb92075

                      HINT should never be used in Production database.

                      • 8. Re: help me on this Query tunning !!
                        967023

                        No - I am testing in UAT enviornment.

                        • 9. Re: help me on this Query tunning !!
                          967023

                          Can anyone help me please - Where this query is hitting ..? - After giving recommandation from sql advisior still elapsed time is same.


                          • 10. Re: help me on this Query tunning !!
                            sb92075

                            967023 wrote:

                             

                            No - I am testing in UAT enviornment.

                            UAT exists to replicate & duplicate production, so that when code is promoted to Production you will know how it performs!

                            • 11. Re: help me on this Query tunning !!
                              967023

                              I agree SB - Do you any reommandation what need to done for the above query..?

                              • 12. Re: help me on this Query tunning !!
                                sb92075

                                How do you explain the discrepancy between the EXPLAIN PLAN having 00:16 second completion time & SQL actual having 34:47; which is a LARGE  difference?

                                • 13. Re: help me on this Query tunning !!
                                  967023

                                  I was in assumption the time 00:16 seconds is estimated time - Where 34:47 is actual elapsed time went though to finish the query. - Correct me If was wrong..?

                                   

                                  Also in what circumstances that difference will exist..?

                                  • 14. Re: help me on this Query tunning !!
                                    Hemant K Chitale

                                    Run the query with a "gather_plan_statistics"  hint

                                    --- ie

                                    SELECT /*+ gather_plan_statistics */ DISTINCT SHPM.DOMAIN_NAME,  .............

                                     

                                    Then, immediately thereafter, run an XPLAN DISPLAY CURSOR to show the execution statistics :

                                     

                                    select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

                                     

                                     

                                    This will tell you how much time each portion of the execution plan actually took.

                                     

                                     

                                    Hemant K Chitale

                                     

                                     

                                    Here is an example that shows that Oracle expected to fetch 70 rows but fetched 0 rows (in your case, the converse is more likely -- the explain plan expects few rows but the actual execution fetches many more rows at one or more steps)

                                     

                                    SQL> select /*+ gather_plan_statistics */
                                      2  count(*) from hkc_test_1
                                      3  where group_name = 'HEMANT'
                                      4  /

                                      COUNT(*)
                                    ----------
                                             0

                                    1 row selected.

                                    SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

                                    PLAN_TABLE_OUTPUT
                                    ----------------------------------------------------------------------------------------------------------

                                    SQL_ID  cu9q3k8muvn28, child number 0
                                    -------------------------------------
                                    select /*+ gather_plan_statistics */ count(*) from hkc_test_1 where
                                    group_name = 'HEMANT'

                                    Plan hash value: 3163584011

                                    ----------------------------------------------------------------------------------------------------
                                    | Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                                    ----------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT   |            |      1 |        |      1 |00:00:02.41 |    6267 |   6263 |
                                    |   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:02.41 |    6267 |   6263 |
                                    |*  2 |   TABLE ACCESS FULL| HKC_TEST_1 |      1 |     70 |      0 |00:00:02.41 |    6267 |   6263 |
                                    ----------------------------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                       2 - filter("GROUP_NAME"='HEMANT')

                                    Note
                                    -----
                                       - dynamic sampling used for this statement (level=2)


                                    24 rows selected.

                                    SQL>

                                    1 2 3 Previous Next