This discussion is archived
9 Replies Latest reply: Aug 10, 2013 3:03 AM by Martin Preiss RSS

Query tunning

967023 Newbie
Currently Being Moderated

Hi Gurus,

 

My Database Version

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

Can you please help me for tuning of this query ?

 

SELECT 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,

     CORPORATION CP,

     LOCATION  LOC

WHERE SS.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 (SHPM.DOMAIN_NAME = CP.DOMAIN_NAME AND CP.IS_DOMAIN_MASTER = 'Y')

AND SHPM.PERSPECTIVE = 'B'

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

And 1=1

and shpm.servprov_Gid = 'UPS.CNWY'

and f_get_sell_id_string(ss.shipment_gid) is null

order by CP.CORPORATION_NAME, SHIPMENT_XID;

 

{noformat}

PLAN_TABLE_OUTPUT

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

Plan hash value: 605685422

 

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

| Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                 |                    |     1 |   337 |  1131   (1)| 00:00:14 |

|   1 |  SORT UNIQUE                     |                    |     1 |   337 |  1130   (1)| 00:00:14 |

|   2 |   NESTED LOOPS                   |                    |       |       |            |          |

|   3 |    NESTED LOOPS                  |                    |     1 |   337 |  1129   (1)| 00:00:14 |

|*  4 |     HASH JOIN                    |                    |     4 |   880 |  1121   (1)| 00:00:14 |

|   5 |      NESTED LOOPS                |                    |    18 |  3366 |  1106   (1)| 00:00:14 |

 

PLAN_TABLE_OUTPUT

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

|*  6 |       INDEX RANGE SCAN           | IND_LOCID_LOCNAME  |     1 |    59 |     2   (0)| 00:00:01 |

|*  7 |       TABLE ACCESS BY INDEX ROWID| SHIPMENT           |    18 |  2304 |  1105   (1)| 00:00:14 |

|*  8 |        INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |  4103 |       |    17  (24)| 00:00:01 |

|*  9 |      TABLE ACCESS FULL           | CORPORATION        |     4 |   132 |    14   (0)| 00:00:01 |

|* 10 |     INDEX RANGE SCAN             | PK_SHIPMENT_STATUS |     2 |       |     2   (0)| 00:00:01 |

|* 11 |    TABLE ACCESS BY INDEX ROWID   | SHIPMENT_STATUS    |     1 |   117 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

 

PLAN_TABLE_OUTPUT

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

   4 - access("SHPM"."DOMAIN_NAME"="CP"."DOMAIN_NAME")

   6 - access("LOC"."LOCATION_GID"='UPS.CNWY')

   7 - filter("SHPM"."SERVPROV_GID"='UPS.CNWY')

   8 - access("SHPM"."PERSPECTIVE"='B' AND "SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

       filter("SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

   9 - filter("CP"."IS_DOMAIN_MASTER"='Y' AND "CP"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

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

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

  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'))

 

33 rows selected

{noformat}

 

Based on this thread : When your query takes too long ...

I tried to put the plan in readable format - Don't know it can be readable or not - If not plz let me know which codes i need to pre-fix

  • 1. Re: Query tunning
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    1) it's not confluence (I wish it was!) so {noformat} tags don't help. Code formatting has been seriously messed up during recent forum upgrade so for now you have to resort to workarounds, e.g. highlight the code snippet or plan, go to "advanced editor' and change the font to 'Courier New' (or switch to HTML and use <pre> tags). Fortunately, the plan you posted is still readable when copy-pasted into notepad, so it's not that big an issue

    2) in most cases, explain plan doesn't provide enough information to identify an issue correctly. At very least, you should add elapsed time and autotrace output so that we could cross-check optimizer estimates against the reality

    3) if we do trust optimizer's estimates, then the main problem with the query is inefficient index skip scan at step 8, because it returns 4103 rows which result in hundreds of single-block reads, most of which are useless because only 18 rows would be kept. If the index contained SERVPROV_GID column, this table access wouldn't have been necessary

    4) like I said, the reasoning in 3) only holds if we do trust optimizer estimates. I'd be careful about that, because if the optimizer is right, then the total cost of the query is equivalent to 1131 single-block reads, which isn't many (assuming 90% cache hit ratio and 5 ms per one single-block read access we get less than a second elapsed time -- if that was the case, that query probably wouldn't worry you too much)

     

    Best regards,

    Nikolay

  • 2. Re: Query tunning
    967023 Newbie
    Currently Being Moderated

    Thank you Nikolay - You are amazing I follow your blog as well.

     

     

    Here is attching autotrace with optimizer estimate - As you said in point number 3) i.e., "If the index contained SERVPROV_GID column, this table access wouldn't have been necessary" - I didn't understand the meaning of it - Can you please explain - We have indexes in the coloumn SERVPROV_GID.

     

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

    | Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

    |   0 | SELECT STATEMENT                 |                    |     1 |   337 |  1131   (1)| 00:00:14 |

    |   1 |  SORT UNIQUE                     |                    |     1 |   337 |  1130   (1)| 00:00:14 |

    |   2 |   NESTED LOOPS                   |                    |       |       |            |          |

    |   3 |    NESTED LOOPS                  |                    |     1 |   337 |  1129   (1)| 00:00:14 |

    |*  4 |     HASH JOIN                    |                    |     4 |   880 |  1121   (1)| 00:00:14 |

    |   5 |      NESTED LOOPS                |                    |    18 |  3366 |  1106   (1)| 00:00:14 |

    |*  6 |       INDEX RANGE SCAN           | IND_LOCID_LOCNAME  |     1 |    59 |     2   (0)| 00:00:01 |

    |*  7 |       TABLE ACCESS BY INDEX ROWID| SHIPMENT           |    18 |  2304 |  1105   (1)| 00:00:14 |

    |*  8 |        INDEX SKIP SCAN           | IND_SHIP_DOM_ICON  |  4103 |       |    17  (24)| 00:00:01 |

    |*  9 |      TABLE ACCESS FULL           | CORPORATION        |     4 |   132 |    14   (0)| 00:00:01 |

    |* 10 |     INDEX RANGE SCAN             | PK_SHIPMENT_STATUS |     2 |       |     2   (0)| 00:00:01 |

    |* 11 |    TABLE ACCESS BY INDEX ROWID   | SHIPMENT_STATUS    |     1 |   117 |     2   (0)| 00:00:01 |

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

     

    Predicate Information (identified by operation id):

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

     

       4 - access("SHPM"."DOMAIN_NAME"="CP"."DOMAIN_NAME")

       6 - access("LOC"."LOCATION_GID"='UPS.CNWY')

       7 - filter("SHPM"."SERVPROV_GID"='UPS.CNWY')

       8 - access("SHPM"."PERSPECTIVE"='B' AND "SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

           filter("SHPM"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

       9 - filter("CP"."IS_DOMAIN_MASTER"='Y' AND "CP"."DOMAIN_NAME" LIKE 'UPS/CP/TMP/%')

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

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

      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'))

     

     

    Statistics

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

        1024915  recursive calls

              1  db block gets

       12508062  consistent gets

              0  physical reads

              0  redo size

           9707  bytes sent via SQL*Net to client

            557  bytes received via SQL*Net from client

              5  SQL*Net roundtrips to/from client

            100  sorts (memory)

              0  sorts (disk)

             49  rows processed

  • 3. Re: Query tunning
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    thanks for following my blog, I'm glad you're finding it helpful.

     

    regarding your questions.

     

    1) you may have indexes involving SERVPROV_GID column, but index IND_SHIP_DOM_ICON doesn't contain contain it. Because it doesn't, it cannot check any conditions involving that column. And therefore, Oracle has to go to the SHIPMENT table, and check this condition. This is expensive. If the index contained that column, it could check the condition without accessing the table, and then do the table access for a much smaller number of rows

    2) however the autotrace dump you posted clearly indicates that you have problems far more serious than the ones shown in the plan. Query execution caused 12M consistent gets, which is 12,000 times more than predicted by the optimizer. Additionally, you have over 1M recursive calls, which suggests massive performance overhead due to PL/SQL subroutine processing or something like that.

     

    Because of that, explain plan is essentially useless for understanding your performance problem, and we need a more suitable diagnostic tool. Are you licensed to use the Diagnostic and Tuning pack? If so, then SQL monitor is perhaps the easiest way. If not, then you can use regular SQL trace or dbms_xplan.display_cursor, whichever you like better.

     

    Best regards,

    Nikolay

  • 4. Re: Query tunning
    user12011367 Newbie
    Currently Being Moderated

    Use the gather_plan_statistics hint and then execute a "select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))"  to get more information about the execution of you SQL.

    =>  HOW TO: Post a SQL statement tuning request - template posting

  • 5. Re: Query tunning
    Iordan Iotzov Expert
    Currently Being Moderated

    The Oracle optimizer is more likely to generate a good plan if it can accurately estimate the selectivity, i.e. how many records would be eliminated, of all involved predicates. (It is assumed the table/index stats are correct.)


    Getting an accurate estimate about some of the predicates in the query is difficult, so the Oracle optimizer is forced to make a guess. When the guess turns incorrect (it is a guess after all!) then the Oracle CBO is likely to generate a bad plan.


    If this case, costing the following predicates might be a challenge for the CBO:


    ->  f_get_sell_id_string(ss.shipment_gid) is null
    ->  SS.status_type_gid = SS.domain_name||'.SECURE RESOURCES'
    ->  shpm.domain_name like 'UPS/CP/TMP/%'  - histograms might help

     

    You can get more info, and some potential workarounds, from my presentation/white paper:

    http://iiotzov.files.wordpress.com/2013/03/confidence_of_cardinality_presentationiotzov.pptx
    http://iiotzov.files.wordpress.com/2013/03/confidence_of_cardinality_whitepaperiotzov.docx

     

    Iordan Iotzov

  • 6. Re: Query tunning
    967023 Newbie
    Currently Being Moderated

    Thank you all for your valuable replies

     

    Yes I have licensed Diagnostic Tuning pack - I ran DBMS_SQLTUNE advisior - It gave me some recommendation for above SQL - Like it ask me to create few indexes on the table - But i want to know how effective is this recommendations..? - Can we consider this recommendations..?

     

    Please let me know if any other process in Oracle 11g which will give good recommendations to above SQL.

  • 7. Re: Query tunning
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    we have no idea what these recommendations are. And we have no idea what the actual performance problem here is, because you haven't provided the requested diagnostic information.

     

    So how could we possibly answer your question?

     

    Best regards,

    Nikolay

  • 8. Re: Query tunning
    967023 Newbie
    Currently Being Moderated

    Ohh sorry - The Recommandation it gave as to create the below indexes

     

    Recommendation (estimated benefit: 97.31%)

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

      - Consider running the Access Advisor to improve the physical schema design

        or creating the recommended index.

        create index GLOGOWNER.IDX$$_22060001 on

        GLOGOWNER.CORPORATION("IS_DOMAIN_MASTER","DOMAIN_NAME");

     

      - Consider running the Access Advisor to improve the physical schema design

        or creating the recommended index.

        create index GLOGOWNER.IDX$$_22060002 on

        GLOGOWNER.SHIPMENT_STATUS("DOMAIN_NAME","SHIPMENT_GID","STATUS_VALUE_GID");

     

      - Consider running the Access Advisor to improve the physical schema design

        or creating the recommended index.  If you choose to create the

        recommended index, consider dropping the index

        "GLOGOWNER"."IND_SHIP_SERVPROV_GID" because it is a prefix of the

        recommended index.

        create index GLOGOWNER.IDX$$_22060003 on

        GLOGOWNER.SHIPMENT("SERVPROV_GID","PERSPECTIVE","DOMAIN_NAME");


    Also Can you please tell me why there is huge number of Recursive calls..?

  • 9. Re: Query tunning
    Martin Preiss Expert
    Currently Being Moderated

    before I would create additional indexes I would try to find out if there are massive errors in the estimated cardinalities. As suggested already by others I would use the gather_plan_statistics hint to check the differences between estimated and actual cardinalities.

Legend

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