Analysisng statspack for hard hitting queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Analysisng statspack for hard hitting queries

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Analysisng statspack for hard hitting queries

    Hi

    I have a statspack report (attached here)
    and i am confused on which queries should i be looking into that would give me the maximum performance benefit.

    I feel we should be looking into the insert statement

    regards
    Hrishy
    Last edited by hrishy; 05-30-2007 at 01:55 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    the ones that cause the users the most pain

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Davey

    Thank you for taking your time out and having a look at this.

    I am working remotely and the development team has asked me to look into this as they want a expert opinion from the DB side .

    regards
    Hrishy

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    the only thing that matters is what the users thing, if you tune a statement to run in half the time but the users are still annoyed by something else you havent solved anything, but lookk at your hard parses, 112 per second!

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Davey

    Once again thanks for your valuable advice

    I have noted down a few things but when it comes to statspack more experienced eyes the better

    Instance Level

    1)Hard Parses are high
    2)Rollback per transaction is high
    3)Execute to Parse is low

    Wait Events
    enq: HW - contention
    enq: TX - row lock contention
    enq: TX - index contention
    db file sequential read

    are all appearing.
    I guess the contention on enques is due to the fact that session are holding locks which other sessions wants and are not commiting.

    TX - index contention occurs when many sessions try to insert but they need to wait due to non availibilty of ITL can be alleviated to some extent by increasing PCTFREE on the table

    log file parallel write need to check if all the logfiles are on the same disks and the sma econtroller

    Logbuffer size is only 14Mb which is small


    Code:
    SELECT  /*YANTRA*/   LT_RECIPE_ALIAS.* FROM LT_RECIPE_ALIAS LT_
    RECIPE_ALIAS     WHERE LT_RECIPE_ALIAS.RECIPE_KEY=  :1
     
    SELECT  /*YANTRA*/   LT_RECIPE.* FROM LT_RECIPE LT_RECIPE     W
    HERE LT_RECIPE.IMAGE_KEY=  :1
    
    SELECT  /*YANTRA*/   LT_IMAGE_ALIAS.* FROM LT_IMAGE_ALIAS LT_IM
    AGE_ALIAS     WHERE LT_IMAGE_ALIAS.IMAGE_KEY=  :1
    
    INSERT   /*YANTRA*/   INTO YFS_AUDIT(AUDIT_KEY,AUDIT_TRAN_KEY,AU
    DIT_CONTEXT_ID,REFERENCE_1,REFERENCE_2,REFERENCE_3,REFERENCE_4,R
    EFERENCE_5,REFERENCE_6,TABLE_NAME,TABLE_KEY,AUDIT_XML,OPERATION,
    LOCKID,CREATETS,MODIFYTS,CREATEUSERID,MODIFYUSERID,CREATEPROGID,
    MODIFYPROGID) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13
    Is there anything else ?
    are my observations right ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width