buffer busy waits - enqueue
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: buffer busy waits - enqueue

  1. #1
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103

    Red face buffer busy waits - enqueue

    Hai Everyone.
    We are using Oracle9i RAC. The setup is as follows:

    Shared Storage: 1 - EMC Clarion CX-300 (RAID 10)
    RAC Nodes: 2 - Dell Poweredge 2650 Servers (2 CPU, 8GB RAM)
    Private Interconnect: Gigabit Ethenet Cards


    We are doing Load Testing on our application. We are using a load of about 100 - 500 hits/sec, I mean the application load, acheived with the help of a Tool.

    When I pull the statspack report, in the "Top 5 Timed Events" I'm getting high amount of waits on 'buffer busy waits' and 'enqueue'.
    I'm using segment space management as AUTO and even I have increased the INITRANS to 3 on the tables. I would like to know, how can I remove these wait events.


    regards,
    Dileep.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Dileep

    Can you please upload your statspack report.

    regards
    Hrishy

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    What type of enqueue waits?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  4. #4
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Dear Hrishy,
    I'm uploading the Statspack report from both the RAC nodes in the form of a zip file.


    regards,
    Dileep T.V.R.
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    I see you are using 9.2.0.5. I dont mean to sound like Oracle support folks but 9.2.0.6 has some optimizer fixes... just fyi. we just went thru a major siebel upgrade....
    "High Salaries = Happiness = Project Success."

  6. #6
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    I also noticed somethings are sp85_86.lst

    1. Begin/end snap differences are like more then 2hrs.
    2. your enqueue is high so fix that 1st but the wait isnt high at all.
    3. buffer busy waits can be resolved. Think about using different buffer pools
    4. you have latching problems...
    5.high undo waits.
    6. free buffer requested is high..allocate more buffers
    7. High reads and waits on BIDRXDV1
    8. What ever is in this datafile..needs to be segregated.. tablespace BIDRX_INDEX -->/ocfs-index/bidrx_index01.dbf
    9. consider faster disk or spllitng hot tables into different datafiles or accross sep spindles..
    10. bump up shared_pool_size
    11, your userentity table has 1 Million reads. Maybe you can stick it in the keep pool or maybe partition the data
    12. Usage PGA aggregate target instead of sort area size.
    "High Salaries = Happiness = Project Success."

  7. #7
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Dear jlakhani,
    Thanks for your info. I have planned to increase the size of db_cache_size and shared_pool also. I planned for version upgrade from Oracle 9.2.0.5.0 to Oracle 9.2.0.6.0. I was able to upgrade it successfully on our Test Server. The database was fine and working normally. But, We are not able to do any DML from our web application. We are using JAVA/JSP/SERVLETS. There is some problem with the drivers. I'm not able to get the JDBC Drivers for Oracle 9.2.0.6.0 version. I tried with version JDBC drivers of other versions like 9.2.0.5.0 and 10g versions also. But they too are not working properly with version Oracle 9.2.0.6.0. I mean selections are fine but no DML. We are getting "Protocol Voilation Exception" error and the application is not able to connect to database.

    How can we reduce buffer busy waits and enqueue?


    regards,
    Dileep.

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Dileep

    I can see that your snap time is
    Elapsed: 155.93 (mins)

    well that makes the statspack report pretty useless.Just take it in a interval of 10-15 minutes max when you are running the load to make it meaningful.

    regards
    Hrishy

  9. #9
    Join Date
    Jun 2005
    Posts
    31
    Hi tvrdileep!

    jlakhani suggests to optimize IO (tablespace BIDRXDV1)

    (1) Before optimizing IO you should identify IF you really NEED that much IO !

    The TOP-IO-statement causing 54% of all pysical reads is

    CPU Elapsd

    Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

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

    203,513 28 7,268.3 54.7 15.18 111.55 1413706332

    select MODULENAME as Servlet_Name,count(MODULENAME) as NO_OF_EXE
    , round(max(TIMETAKENINMILLIS )) as Max_Time_Taken_In_Millisecon
    d, round(min(TIMETAKENINMILLIS )) as Min_Time_Taken_In_Milliseco
    nd, round(avg(TIMETAKENINMILLIS )) as Averate_In_Millisecond fro
    m ResponseTime group by MODULENAME order by MODULENAME


    28 executions in 155 minutes is about once per 5 minutes.

    And this statement looks like a performance statistic for servelets.

    Please consider to turn that monitor off for a short time and check if your buffer busy waits disappear.

    Afterwards you might consider to archive your table "ResponseTime" so that the actual records required by your performance monitor are so small that they are fully cached.

    (2) Your statspack report shows many SQ Enqueue Waits.

    There exists a document on Metalink which describes this SQ Enqueue in context with "Buffer Busy Waits.

    http://metalink.oracle.com/metalink/...&p_id=155971.1

    Please note that 12% of your parse-time is just caused by fetching new Sequence-Range:

    % Total

    Parse Calls Executions Parses Hash Value

    ------------ ------------ -------- ----------
    11,728 11,728 12.41 3716207873

    update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
    order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
    1



    Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
    ------------ --------------- ---------------- ----------- ---------- ----------

    SELECT SEQ_DRUGAUTOBIDLOG.NEXTVAL FROM DUAL

    2,324,471 2,324,880 1.0 0.00 0.00 52363000


    SELECT SEQ_DRUGAUTOBIDLOG.NEXTVAL FROM DUAL

    3,995 3,995 1.0 0.00 0.00 1745196126

    SELECT SEQ_SPPCRPTDET.NEXTVAL FROM DUAL


    This are about those 12,000 accesses to seq$ to assign 2,3 Mio new values
    (nearly all for the Insert-statement

    CPU per Elap per

    Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

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

    2,325,011 2,324,992 1.0 0.00 0.01 3941894919

    INSERT INTO DRUGAUTOBIDLOG ( DABLID, DRUGAUCTIONID, BIDDATE, DRU
    GID, PHARMACYID, REASON ) VALUES ( :B4, :B3, SYSTIMESTAMP, :B2,
    :B1, 'No rule defined to calculate drug price' )


    In total you are assigning 2,3 Mio new Sequence-Values with 12,000 access to SEQ$,
    that means your Sequence-Cache size seems to be about 200.

    Consider to increase the cache-size.
    (2.3 Mio inserts in 155 min is about 250 per second)

    Good Luck,

    Telco_DBA
    http://www.Mercury-Consulting-Ltd.com/

  10. #10
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai Hrishy,
    I have uploaded the statspack reports from the two RAC nodes. This report was taken after doing a load test for about 25 minutes. This report is different from my other reports.


    regards,
    Dileep T.V.R.
    Attached Files Attached Files

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