This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Aug 15, 2013 7:31 AM by Nikolay Savvinov Go to original post RSS
  • 15. Re: help me on this Query tunning !!
    967023 Newbie
    Currently Being Moderated

    Hi Hemanth

     

    This is it was shown.. - Is this wrong.?

     

    23 rows selected.

    Elapsed: 00:00:00.09

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3713220770

    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    21   (0)| 00:00:01 |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |  8168 | 16336 |    21   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
            918  recursive calls
              0  db block gets
            383  consistent gets
             38  physical reads
              0  redo size
           1862  bytes sent via SQL*Net to client
            535  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
             31  sorts (memory)
              0  sorts (disk)
             23  rows processed

  • 16. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Were you running an Explain Plan instead of actually executing the statement ?  OR did you have AUTOTRACE ON ? You need to execute the SQL (without AUTOTRACE) and then run dbms_xplan.display_cursor

     

     

    Hemant K Chitale

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

    Hi Hemanth,

     

    I think now the dbms_xplan came correctly.

     

    Here it is

     

    SQL> select * from   table( dbms_xplan.display_cursor ( '3wq0764mm0qam' , null , 'RUNSTATS_LAST' ) )
      2  ;
    SQL_ID  3wq0764mm0qam, child number 0
    -------------------------------------
    SELECT /*+ gather_plan_statistics */  DISTINCT SHPM.DOMAIN_NAME,
    CP.CORPORATION_NAME CUSTOMER_NAME,        SHPM.SHIPMENT_GID
    SHIPMENT_GID,        SHIPMENT_XID BUY_SHIPMENT_ID,
    F_GET_ORDER_RELEASE_GID(:"SYS_B_00",SHPM.SHIPMENT_GID,:"SYS_B_01")
    ORDER_RELEASE_ID,        DECODE(F_GET_POD_RECEIVED_DATE(SHPM.SHIPMENT_GI
    D),:"SYS_B_02",:"SYS_B_03",(TO_CHAR(F_GET_POD_RECEIVED_DATE(SHPM.SHIPMEN
    T_GID),:"SYS_B_04"))) RECEIVED_DATE,
    TRUNC(F_TZ_OFFSET(SHPM.SOURCE_LOCATION_GID,SHPM.START_TIME))
    BUY_SHIPMENT_START_DATE,        Glog_util.remove_domain(SHPM.SERVPROV_GI
    D) SCAC,        LOC.LOCATION_NAME CARRIER_NAME,
    Glog_util.remove_domain(SS.Status_value_gid ) CARRIER_REPORTED_STATUS,
          F_GET_REFNUM_STRING(:"SYS_B_05",SHPM.SHIPMENT_GID,:"SYS_B_06")MBOL
    _NUMBER,        round(DECODE(SHPM.PERSPECTIVE,:"SYS_B_07",SHPM.TOTAL_ACT
    UAL_COST,:"SYS_B_08"),:"SYS_B_09") BUY_SHIPMENT_COST,
    SHPM.T_ACTUAL_COST_CURRENCY_GID BUY_SHIPMENT_CURRENCY,
    GLOGUPS.F_GET_INVOICE_VOUCHE

    Plan hash value: 2588913212

    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                    |      1 |        |   1591 |00:07:42.05 |     312M|    168K|
    |   1 |  SORT UNIQUE                       |                    |      1 |      1 |   1591 |00:07:42.05 |     312M|    168K|
    |*  2 |   FILTER                           |                    |      1 |        |  59034 |00:31:57.70 |     301M|    163K|
    |   3 |    NESTED LOOPS                    |                    |      1 |      1 |  59034 |00:31:57.67 |     301M|    163K|
    |   4 |     NESTED LOOPS                   |                    |      1 |      1 |  59034 |00:31:57.32 |     301M|    163K|
    |   5 |      NESTED LOOPS                  |                    |      1 |      1 |    114K|00:31:46.39 |     299M|    162K|
    |   6 |       NESTED LOOPS                 |                    |      1 |      1 |   3047 |00:31:46.06 |     299M|    162K|
    |*  7 |        HASH JOIN                   |                    |      1 |    109 |    128K|00:00:02.02 |   97295 |  31578 |
    |   8 |         TABLE ACCESS BY INDEX ROWID| CORPORATION        |      1 |      4 |     61 |00:00:00.01 |      43 |      4 |
    |*  9 |          INDEX RANGE SCAN          | IDX$$_22680001     |      1 |      4 |     61 |00:00:00.01 |       2 |      1 |
    |  10 |         TABLE ACCESS BY INDEX ROWID| SHIPMENT           |      1 |   4077 |    129K|00:00:01.80 |   97252 |  31574 |
    |* 11 |          INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |      1 |   4077 |    129K|00:00:00.44 |     643 |    640 |
    |* 12 |        TABLE ACCESS BY INDEX ROWID | SHIPMENT_STATUS    |    128K|      1 |   3047 |00:31:48.28 |     299M|    130K|
    |* 13 |         INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |    128K|      2 |    127K|00:31:47.38 |     299M|    122K|
    |  14 |       TABLE ACCESS BY INDEX ROWID  | SHIPMENT_STATUS    |   3047 |     37 |    114K|00:00:00.29 |   69701 |      0 |
    |* 15 |        INDEX RANGE SCAN            | PK_SHIPMENT_STATUS |   3047 |     37 |    114K|00:00:00.07 |   11200 |      0 |
    |* 16 |      TABLE ACCESS BY INDEX ROWID   | SHIPMENT_STATUS    |    114K|      1 |  59034 |00:00:10.81 |    2318K|   1063 |
    |* 17 |       INDEX RANGE SCAN             | PK_SHIPMENT_STATUS |    114K|     37 |   4296K|00:00:02.10 |     164K|    397 |
    |* 18 |     INDEX RANGE SCAN               | IND_LOCID_LOCNAME  |  59034 |      1 |  59034 |00:00:00.27 |   15509 |      2 |
    ----------------------------------------------------------------------------------------------------------------------------

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

       2 - filter((:SYS_B_23=:SYS_B_24 AND :SYS_B_21=:SYS_B_22))
       7 - access("SHPM"."DOMAIN_NAME"="CP"."DOMAIN_NAME")
       9 - access("CP"."IS_DOMAIN_MASTER"=:SYS_B_18 AND "CP"."DOMAIN_NAME" LIKE :SYS_B_20)
           filter("CP"."DOMAIN_NAME" LIKE :SYS_B_20)
      11 - access("SHPM"."PERSPECTIVE"=:SYS_B_19 AND "SHPM"."DOMAIN_NAME" LIKE :SYS_B_20)
           filter("SHPM"."DOMAIN_NAME" LIKE :SYS_B_20)
      12 - filter(("SS"."STATUS_TYPE_GID"="SS"."DOMAIN_NAME"||:SYS_B_11 AND
                  ("SS"."STATUS_VALUE_GID"="SS"."DOMAIN_NAME"||:SYS_B_12 OR "SS"."STATUS_VALUE_GID"="SS"."DOMAIN_NAME"||:SYS_B_13)))
      13 - access("SS"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
           filter("F_GET_SELL_ID_STRING"("SS"."SHIPMENT_GID") IS NULL)
      15 - access("SS2"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
      16 - filter((("SS2"."STATUS_TYPE_GID"="SS2"."DOMAIN_NAME"||:SYS_B_14 AND
                  "SS2"."STATUS_VALUE_GID"="SS2"."DOMAIN_NAME"||:SYS_B_15) OR ("SS3"."STATUS_TYPE_GID"="SS3"."DOMAIN_NAME"||:SYS_B_16
                  AND "SS3"."STATUS_VALUE_GID"="SS3"."DOMAIN_NAME"||:SYS_B_17)))
      17 - access("SS3"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")
      18 - access("SHPM"."SERVPROV_GID"="LOC"."LOCATION_GID")

     

    Can you please guide me where this query is mostly hiting ..?

  • 18. Re: help me on this Query tunning !!
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    sb92075 wrote:

     

    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?

     

     

     

    Timings shown by EXPLAIN PLAN rarely have anything to do with the reality.

     

    Best regards,

    Nikolay

  • 19. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    This step :

     

    |* 13 |         INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |    128K|      2 |    127K|00:31:47.38 |     299M

     

    gets executed 128thousand times and takes a total execution time of 31minutes47seconds.

     

    Why is it executed 128thousand times ?

     

    It is driven by this step :

    |*  7 |        HASH JOIN                   |                    |      1 |    109 |    128K|00:00:02.02 |   97295

    which returns 128thousand rows instead of the expected 109 rows.

     

    Why does step 7 return 128thousand rows ?

    Steps 10 and 11 return 129thousand rows (in one single pass) instead of the expectd 4077 rows.

    |  10 |         TABLE ACCESS BY INDEX ROWID| SHIPMENT           |      1 |   4077 |    129K|00:00:01.80 |   97252 |  31574 |

    |* 11 |          INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |      1 |   4077 |    129K|00:00:00.44 |     643 | 

     

     

    So you could start evaluating the predicates for steps 10 and 11

     

    Hemant K Chitale

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

    Steps 10 and 11 return 129thousand rows (in one single pass) instead of the expectd 4077 rows.

    |  10 |         TABLE ACCESS BY INDEX ROWID| SHIPMENT           |      1 |   4077 |    129K|00:00:01.80 |   97252 |  31574 |

    |* 11 |          INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |      1 |   4077 |    129K|00:00:00.44 |     643 | 

     

    I am poor in performance tunning - Can you please help me

    Is there any missing index on the above ..? - because on the index IND_SHIP_DOM_ICON these were the columsn I see - Is there any flaw in query ..?

    INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION STATUS   TABLE_NAME                     INDEX_TYPE

    =================================================================================================================================

    IND_SHIP_DOM_ICON              DOMAIN_NAME                                  3 VALID    SHIPMENT                       NORMAL

    IND_SHIP_DOM_ICON              PERSPECTIVE                                  1 VALID    SHIPMENT                       NORMAL

    IND_SHIP_DOM_ICON              USER_DEFINED1_ICON_GID                       2 VALID    SHIPMENT                       NORMAL

     

    Also can you please explain - On what reason the below will exists..? - Is this because of lack of indexs or any other..?

     

    |* 13 |         INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |    128K|      2 |    127K|00:31:47.38 |     299M

     

    gets executed 128thousand times and takes a total execution time of 31minutes47seconds.

     

    Why is it executed 128thousand times ?

     

    It is driven by this step :

    |*  7 |        HASH JOIN                   |                    |      1 |    109 |    128K|00:00:02.02 |   97295

    which returns 128thousand rows instead of the expected 109 rows.

  • 21. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The index defined is on (PERSPECTIVE, USER_DEFINED1_ICON_GID, DOMAIN)  but the query predicates are

    ("SHPM"."PERSPECTIVE"='B' AND "SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%')  --- USER_DEFINED1_ICON_GID is not a predicate

    which makes the index not so good a choice.  How many distinct values of PERSPECT and DOMAIN_NAME do you have ?

     

    Hemant K Chitale

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

    For PERSPECT TABLE there are 2 distinct valuses - Where as for DOMAIN_NAME it was 145 values.

    So if we create a compsite index on SHPM"."PERSPECTIVE,SHPM"."PERSPECTIVE) will it be benefical..?

  • 23. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    If the number of rows for predicate ""SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/DFP/%'"  is not very high, then you can create an index on

    PERSPECTIVE, DOMAIN_NAME

     

     

    Hemant K Chitale


  • 24. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The step

    10 |         TABLE ACCESS BY INDEX ROWID| SHIPMENT           |      1 |   4077 |    129K|00:00:01.80 |   97252 |  31574

    shows 129thousand rows being returned for this combination.

     

     

    Hemant K Chitale

  • 25. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You should also look at trying to avoid the hash join that causes this :

    |* 12 |        TABLE ACCESS BY INDEX ROWID | SHIPMENT_STATUS    |    128K|      1 |   3047 |00:31:48.28 |     299M|    130K|

    |* 13 |         INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |    128K|      2 |    127K|00:31:47.38 |     299M|   

    The index is read 128thousand times but only 3,047 rows qualify in the table.   Check if this index is appropriate.

     

     

    Hemant K Chitale

     

     

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

    On the PK_SHIPMENT_STATUS Index these are two coloumns there

     

    PK_SHIPMENT_STATUS             SHIPMENT_GID                                 1 VALID    SHIPMENT_STATUS                NORMAL

    PK_SHIPMENT_STATUS             STATUS_TYPE_GID                              2 VALID    SHIPMENT_STATUS                NORMAL

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

    After creating the index on PERSPECTIVE,DOMAIN_NAME - My query elapsed time doesn't chanage anything..?
    When i rewrite the query with And shpm.domain_name like 'UPS/CP/DFP/WEM' - The query is taking only 2 Mins of elapsed time

     

    If the same query give like shpm.domain_name like 'UPS/CP/DFP/%' - it is taking 35 mins elapsed time - Even if have indexes.

     

    Do we need any specific index on like operation - Can you please guide me?

  • 28. Re: help me on this Query tunning !!
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    take a close look at line 13 of the plan:

     

    |* 13 |         INDEX RANGE SCAN           | PK_SHIPMENT_STATUS |    128K|      2 |    127K|00:31:47.38 |     299M|    122K|

    ...

      13 - access("SS"."SHIPMENT_GID"="SHPM"."SHIPMENT_GID")

           filter("F_GET_SELL_ID_STRING"("SS"."SHIPMENT_GID") IS NULL)


    All this looks very odd. This is an index range scan against a primary key index, not an index unique scan, so apparently, the primary key includes columns other than SHIPMENT_GID. However, the number of rows returned is close to (and less than) the number of starts for this operation, which suggests that this column alone is almost as good as the entire primary key (i.e. SS.SHIPMENT_GID = const would always or almost always return 1 row).


    But the operation causes 299M logical reads, roughly 2,350 per operation. Why would Oracle need to scan over 2 thousand blocks in an index just to find one row?


    My suspicion is that all these reads are not coming from the index scan itself, but rather from predicate filtering:

           filter("F_GET_SELL_ID_STRING"("SS"."SHIPMENT_GID") IS NULL)


    which contains a call to some user defined function F_GET_SELL_ID_STRING. I think it's this string that is responsible for most of the I/O (and thus most of the response time) here.


    Check this by running SELECT F_GET_SELL_ID_STRING(<some realistic value of shipment_gid>) FROM DUAL with autotrace on. Chances are, that it's this function that needs tuning,

    more than anything else in the statement itself.


    Best regards,

    Nikolay

  • 29. Re: help me on this Query tunning !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The inclusion of the predicate :  domain_name like 'UPS/CP/DFP/%    causes 129thousand rows at that step.  Since this predicate is what you require,  you can't avoid this.

     

    Then I suggest looking at the  usage of PK_SHIPMENT_STATUS in step 13.

     

    Hemant K Chitale


Legend

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