This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Aug 15, 2013 7:31 AM by Nikolay Savvinov RSS

help me on this Query tunning !!

967023 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    >|   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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    The stats are accurate.

  • 6. Re: help me on this Query tunning !!
    967023 Newbie
    Currently Being Moderated

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

  • 7. Re: help me on this Query tunning !!
    sb92075 Guru
    Currently Being Moderated

    HINT should never be used in Production database.

  • 8. Re: help me on this Query tunning !!
    967023 Newbie
    Currently Being Moderated

    No - I am testing in UAT enviornment.

  • 9. Re: help me on this Query tunning !!
    967023 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 12. Re: help me on this Query tunning !!
    sb92075 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points