Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    5

    Unanswered: High buffer gets vs performance

    We have noticed that one of INSERT statement has been taking longer time than earlier to get executed. DBA's has done some profiling and provided me
    with some stats for a three hour window ( INSERT statement execition was longer this 3 hr window)
    Below are readings for 3 SQL statements. We noticed that the first two select queries got higher number of buffer gets.
    These 2 SQL statements were not unexpected to run during that window. So, i wanted to figure these 2 select statements had an adverse affects on the INSERT statement.

    Please note that the database is oracle 10g.

    My Questions:
    1. 126631 value for buffer gets/execution is really too high??
    2. Can this impact the performance of the other SQL statements (like Insert statements) running on the same database on different tables?
    Because we noticed during this period one of the INSERT statement was taking more time. The log file sync wait event (18ms) was listed in the TOP 5 events during this period



    Select SQL1

    Buffer Gets: 371,031,088
    Executions: 2,930
    Gets per Exec: 126,631.77
    %Total: 34.61
    CPU Time (s): 1416.82
    Elapsed Time (s): 1526.85
    SQL Module: JDBC Connect Client



    Select SQL2

    Buffer Gets: 370,418,828
    Executions: 2,932
    Gets per Exec: 126,336.57
    %Total: 34.56
    CPU Time (s): 1800.82
    Elapsed Time (s): 1802.95
    SQL Module: JDBC Connect Client



    INSERT SQL1

    Buffer Gets: 5,957,708
    Executions: 786,108
    Gets per Exec: 7.58
    %Total: 0.56
    CPU Time (s): 495.41
    Elapsed Time (s): 498.38
    SQL Module: JDBC Connect Client


    Your suggestions are greatly appreciated.

    Thanks
    Last edited by kkp1238; 10-23-12 at 18:36.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >INSERT SQL1
    >Buffer Gets: 5,957,708
    >Executions: 786,108

    Above was executed about 400 TIMES! more than the other SQL
    I suspect a row by row (aka slow by slow) INSERT inside a LOOP
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    5
    Thanks for your response Anacedent. I do agree about the number of the insert operations being high which is normal.

    But what i'm trying to find out is, if the SELECT statements with 126631 buffer gets/execution can impact the performance of the database and hence the INSERT operation was taking longer time than usual during that 3 hr window.

    Thanks

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    in general, if you have a run-away query that is sucking up resources from everything else on the box, yes, it can have a negative effect on other processes running on the same machine. Is the machine running at high CPU, or disk activity when those select statements are run? Can those insert statements be changed to a bulk load, or is this an order entry system, and that can not be bundled? Can the select statements be tuned better?

  5. #5
    Join Date
    Oct 2012
    Posts
    5
    MCrowley,

    Thanks for your prompt response. Yes, i'm working towards redesigning the application to perform batch inserts. I will be able to reduce the commit operations by 15 times at least.

    However, the question that i was asked is to why the INSERT operation took so look during that window which is unusual at least in this environment. So, i'm trying to dig out the facts about what happened during that window.

    log_file_sync_wait time for each commit was 18ms during that window which is normally 2-5ms on any other day. The only unusual thing i noticed was those two SELECT statements which is issued by a users front-end application. I know that there were many users sitting and monitoring this run.

    CPU was reported to be normal during that time.

    Do you think avoiding those SELECT operations during that time could have led to better performance on the INSERT operation.

    I'm not a DBA so please excuse any misinterpretations.

    Thanks

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are the redo logs, and the tablespace(s) that the select statements go after on the same physical drives?

  7. #7
    Join Date
    Oct 2012
    Posts
    5
    Yes they are on the same physical drives.

    Thanks

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >( INSERT statement execition was longer this 3 hr window)

    not according the the posted details below

    >INSERT SQL1
    >
    >Buffer Gets: 5,957,708
    >Executions: 786,108
    >Gets per Exec: 7.58
    >%Total: 0.56
    >CPU Time (s): 495.41
    >Elapsed Time (s): 498.38
    >SQL Module: JDBC Connect Client

    INSERT was on the system less than 9 minutes!
    Code:
    SQL> select 500/60 from dual;
    
        500/60
    ----------
    8.33333333
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Next time this happens, check the disk queues on that drive. I think you will see them fairly high compared to a 'normal" run.

  10. #10
    Join Date
    Oct 2012
    Posts
    5
    MCrowley,

    Sure. I will watch out for it. I'm trying recreate the scenario.
    Appreciate all your siggestions.

    Thanks a lot!!!

Posting Permissions

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