FAQ
Hello List,

First of all, I am not using TABLESPACES with ASSM feature. I saw a
lot of buffer busy waits on couple of tables which being used for
heavy inserts. I have increased the freelist of those 5 online tables
from 1(default) to 5, as I have 5 CPU on the server.
I was expecting 'buffer busy waits' to be reduced on these segments,
but, to my surprise, what I have seen is a drastic increments in the
value for these segment, buffer busy waits. Is increasing freelist
wont help in this manner?

--=20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia

---------------------------------------------------------------------------=

"It is your atittude, not your aptitude that determins your altitude."

Search Discussions

  • K Gopalakrishnan at May 10, 2005 at 9:15 am
    Jaffar:

    What is the most common reason code (p3=3D?) you are noticing? Tracing
    the sessions with 10046^8 will give you more information about the
    type of the block and the reason code for buffer busy wait..

    (btw the waits are 'buffer busy waits' WAIT
    or 'buffer busy' WAIT?) If it is 'buffer busy' WAITs then the issue
    may not be with freelists.

    --=20
    Best Regards,
    K Gopalakrishnan=20
    Co-Author: Oracle Wait Interface, Oracle Press 2004
    http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
  • The Human Fly at May 10, 2005 at 9:33 am
    Gopal,

    Actually, I was digging at v$segment_statistic level. The following
    query has been used,

    select object_name,statistic_name,value from v$segment_statistics
    where statistic_name like '%buffer busy%'
    and value > 100
    order by value desc

    and the output is

    OBJECT_NAME STATISTIC_NAME VALUE
    ------------------------------ ------------------------------ ----------
    C_INTIMATION buffer busy waits 8032
    CIS_AUDIT_TRAILH buffer busy waits 3739
    TRANSACTION_LOG buffer busy waits 2185
    FM_AUDIT_FORM buffer busy waits 2032
    C_CUSTOMER buffer busy waits 1768
    FM_OLTP_LOG buffer busy waits 1476
    RB_RESTRAINTS buffer busy waits 730
    RB_TRAN_HIST buffer busy waits 653
    FM_ERROR_LOG buffer busy waits 532

    I have added freelist as 5 to some of the tables, like
    c_intimation,c_transaction_log,c_customer,fm_audit_form
    these tables are oltp tables and every transactions entry goes to
    these tables. We have around 45 - 55 transaction per second.

    Planning to move those tables to a tablespace with ASSM feature.

    Any further idea?
    On 5/10/05, K Gopalakrishnan wrote:
    Jaffar:
    =20
    What is the most common reason code (p3=3D?) you are noticing? Tracing
    the sessions with 10046^8 will give you more information about the
    type of the block and the reason code for buffer busy wait..
    =20
    (btw the waits are 'buffer busy waits' WAIT
    or 'buffer busy' WAIT?) If it is 'buffer busy' WAITs then the issue
    may not be with freelists.
    =20
    --
    Best Regards,
    K Gopalakrishnan
    Co-Author: Oracle Wait Interface, Oracle Press 2004
    http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
    =20
    --=20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---------------------------------------------------------------------------=

    "It is your atittude, not your aptitude that determins your altitude."
  • K Gopalakrishnan at May 10, 2005 at 9:47 am
    Jaffar:

    You are waiting on the 'buffer busy waits' WAIT, not 'buffer busy'
    WAIT. That is the good news.

    But you should not just increase the freelists by looking
    V$SEGMENT_STATS. What happes if there is a contention for some set of
    blocks (not for the segement header blocks) like login tables or
    master tables. Increasing freelists will not help in this case. You
    need to tweak the block level storage parameters (like PCTFREE,
    MINIMIZE NUMBER OF ROWS PER BLOCK) etc.

    To start with just attach 10046^8 for couple of sessions and look in
    to the p3 values. If you still want to dig deeper, there is a detailed
    discussion abot BBW in OWI, Chapter 6.

    Or run the follwoing query (from chapter 8), and this will tell you
    the list of places (system calls) causing buffer busy waits.

    select wh.kcbwhdes "module", sw.why0 "calls", sw.why2
    "waits", sw.other_wait "caused waits"from x$kcbwh wh, =20

    x$kcbsw swwhere wh.indx =3D sw.indx and sw.other_wait > 0order by
    sw.other_wait;

    --=20
    Best Regards,
    K Gopalakrishnan=20
    Co-Author: Oracle Wait Interface, Oracle Press 2004
    http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
  • Alex Gorbachev at May 17, 2005 at 3:46 pm
    As it was mentioned already, you probably don't have this problem. If
    you have around 50 tps, chances are that you perform much more logical
    IOs than few thousands "buffer busy waits".

    You can have a look at v$system_event to see whether "buffer busy
    waits" is significant. Better yet to identify the sessions that have
    performance problem and check v$session_event or even better do 10046
    trace. If you do not see buffer busy waits in top waits for the
    instance and do not have performance issues for specific
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.

    2005/5/10, The Human Fly :
    Gopal,
    =20
    Actually, I was digging at v$segment_statistic level. The following
    query has been used,
    =20
    select object_name,statistic_name,value from v$segment_statistics
    where statistic_name like '%buffer busy%'
    and value > 100
    order by value desc
    =20
    and the output is
    =20
    OBJECT_NAME STATISTIC_NAME VALUE
    ------------------------------ ------------------------------ ----------
    C_INTIMATION buffer busy waits 8032
    CIS_AUDIT_TRAILH buffer busy waits 3739
    TRANSACTION_LOG buffer busy waits 2185
    FM_AUDIT_FORM buffer busy waits 2032
    C_CUSTOMER buffer busy waits 1768
    FM_OLTP_LOG buffer busy waits 1476
    RB_RESTRAINTS buffer busy waits 730
    RB_TRAN_HIST buffer busy waits 653
    FM_ERROR_LOG buffer busy waits 532
    =20
    --=20
    Best regards,
    Alex Gorbachev
  • Cary Millsap at May 17, 2005 at 4:39 pm
    Ah! Credit to Gaja and Kirti for that one...

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Visit www.hotsos.com for curriculum and schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES

    As it was mentioned already, you probably don't have this problem. If
    you have around 50 tps, chances are that you perform much more logical
    IOs than few thousands "buffer busy waits".

    You can have a look at v$system_event to see whether "buffer busy
    waits" is significant. Better yet to identify the sessions that have
    performance problem and check v$session_event or even better do 10046
    trace. If you do not see buffer busy waits in top waits for the
    instance and do not have performance issues for specific
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.

    2005/5/10, The Human Fly :
    Gopal,
    =20
    Actually, I was digging at v$segment_statistic level. The following
    query has been used,
    =20
    select object_name,statistic_name,value from v$segment_statistics
    where statistic_name like '%buffer busy%'
    and value > 100
    order by value desc
    =20
    and the output is
    =20
    OBJECT_NAME STATISTIC_NAME VALUE
    ------------------------------ ------------------------------ ----------
    C_INTIMATION buffer busy waits 8032
    CIS_AUDIT_TRAILH buffer busy waits 3739
    TRANSACTION_LOG buffer busy waits 2185
    FM_AUDIT_FORM buffer busy waits 2032
    C_CUSTOMER buffer busy waits 1768
    FM_OLTP_LOG buffer busy waits 1476
    RB_RESTRAINTS buffer busy waits 730
    RB_TRAN_HIST buffer busy waits 653
    FM_ERROR_LOG buffer busy waits 532
    =20
    --=20
    Best regards,
    Alex Gorbachev
  • Alex Gorbachev at May 17, 2005 at 7:03 pm
    My bad...

    2005/5/17, Cary Millsap :
    Ah! Credit to Gaja and Kirti for that one...
    =20
    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *
    =20
    Visit www.hotsos.com for curriculum and schedule details...
    =20
    =20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES
    =20
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.
    --=20
    Best regards,
    Alex Gorbachev
  • The Human Fly at May 18, 2005 at 2:15 am
    However, I haven't have buffer busy wait as my tops events nor I see
    any session execessively waiting for this event. I have already
    mentioned in my previous replies that I have grabbed these values from
    v$segment_statistics dynamic view.

    The following are today's result for the buffer busy waits.

    TRANSACTION_LOG 211143
    FM_AUDIT_FORM 39906
    C_INTIMATION 37029
    CIS_AUDIT_TRAILH 15001
    OUTWARD_CLEARING_CHEQUES 8310
    C_CUSTOMER 5793
    FM_OLTP_LOG 2727
    INWARD_CLEARING_CHEQUES 2347
    RB_RESTRAINTS 2239
    SYSTEM_PARAMETER_VALUE 1492
    RB_TRAN_HIST 1102

    I have increased default freelist 1 to 5 to those tables and now I can
    clearly see that those tables buffer busy wait event has increased
    madly. This could be due to heavy access on those tables?

    However, I have been reading Cary's book past 1 year and still I
    enjoying reading it. Thank god, now, I started undertanding the
    concept behind method R and the true power of 10046 trace for
    resolving performance issues.
    On 5/18/05, Alex Gorbachev wrote:
    My bad...
    =20
    2005/5/17, Cary Millsap :
    Ah! Credit to Gaja and Kirti for that one...
    =3D20
    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *
    =3D20
    Visit www.hotsos.com for curriculum and schedule details...
    =3D20
    =3D20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists.o=
    rg=3D
    ]
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES
    =3D20
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.
    =20
    --=3D20
    Best regards,
    Alex Gorbachev
    --
    http://www.freelists.org/webpage/oracle-l
    =20
    --=20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---------------------------------------------------------------------------=

    "It is your atittude, not your aptitude that determins your altitude."
  • Mark W. Farnham at May 18, 2005 at 8:20 am
    Did your transactional throughput go up, down, or not change much?

    Asked another way, are these higher waits insignificant side effects of
    un-jambing some other problem?

    It is not possible to know without the times, and even with the times, we
    cannot know whether this is a problem or simply a report of a change in
    statistics unless you tells us whether throughput is now changed, and how.

    Asked yet another way, is some process materially slower than you need it to
    be due to the amount of TIME spent waiting for these waits.

    Abstract attempts to change the values of statistics reports from running
    systems without correlation to a process or task that is running slower than
    required have little (little includes nothing) to do with making things
    actually process faster.

    Collection and observation of some statistics without correlation to problem
    processes can be useful, but that is part of capacity planning and load
    management, not optimization. Even then (unless you're using systems
    statistics as a proxy for company business growth in an attempt to predict
    stock outcomes or something like that) you need to correlate the statistics
    you observe to known scaling limits of your existing environment.

    So, are you trying to fix some process that is going slower than you think
    it should (or more appropriately, slower than your service level
    requirement),
    or are you just trying to manipulate the statistics?

    Now I will not deprecate that activity if you're doing it as a learning
    exercise or even if you're just servicing your CTD. But I would like to know
    if you have a throughput problem you're trying to solve (or not).

    To answer your question, if changes you made ungated pent up demand for
    these tables, sure, you could have driven the load on the tables higher. If
    your throughput went up, then the higher frequency of wait events is a happy
    side effect. If your throughput went down, the times and a correlation with
    the now slower process(es) *might* show that the increase in waits
    represents a problem to solve.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of The Human Fly
    Sent: Wednesday, May 18, 2005 2:11 AM
    To: gorbyx_at_gmail.com
    Cc: Cary.Millsap_at_hotsos.com; Oracle-L Freelists
    Subject: Re: buffer busy waits PUZZLES

    However, I haven't have buffer busy wait as my tops events nor I see
    any session execessively waiting for this event. I have already
    mentioned in my previous replies that I have grabbed these values from
    v$segment_statistics dynamic view.

    The following are today's result for the buffer busy waits.

    TRANSACTION_LOG 211143
    FM_AUDIT_FORM 39906
    C_INTIMATION 37029
    CIS_AUDIT_TRAILH 15001
    OUTWARD_CLEARING_CHEQUES 8310
    C_CUSTOMER 5793
    FM_OLTP_LOG 2727
    INWARD_CLEARING_CHEQUES 2347
    RB_RESTRAINTS 2239
    SYSTEM_PARAMETER_VALUE 1492
    RB_TRAN_HIST 1102

    I have increased default freelist 1 to 5 to those tables and now I can
    clearly see that those tables buffer busy wait event has increased
    madly. This could be due to heavy access on those tables?

    However, I have been reading Cary's book past 1 year and still I
    enjoying reading it. Thank god, now, I started undertanding the
    concept behind method R and the true power of 10046 trace for
    resolving performance issues.
    On 5/18/05, Alex Gorbachev wrote:
    My bad...
    =20
    2005/5/17, Cary Millsap :
    Ah! Credit to Gaja and Kirti for that one...
    =3D20
    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *
    =3D20
    Visit www.hotsos.com for curriculum and schedule details...
    =3D20
    =3D20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists.o=
    rg=3D
    ]
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES
    =3D20
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.
    =20
    --=3D20
    Best regards,
    Alex Gorbachev
    --
    http://www.freelists.org/webpage/oracle-l
    =20
    --=20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---------------------------------------------------------------------------=

    "It is your atittude, not your aptitude that determins your altitude."
  • The Human Fly at May 18, 2005 at 8:32 am
    First of all thnks for your valuable time and suggestions.

    However, there is no change in our transaction rate, they are almost
    the same value at any given time.
    However, I am not doing it for any testing or learning purpose. We are
    facing some transactions time-out in our production database uding
    texudo middleware.
    The tables which I increased the freelist are the one who are
    referring almost in every transaction.
    Server configuration, 9gb of RAM with 5cpu, AIX 5m on D240 storage.
    I dont see 'buffer busy wait' as top 5 timed events in any statspack
    report but very seldomly I can see them in v$session_wait.
    My thinking was, all these tables have only 1 freelist (default
    value), when they are involving heavy concurent insertion, it would be
    a good idea to play with its freelist.
    On 5/18/05, Mark W. Farnham wrote:
    Did your transactional throughput go up, down, or not change much?
    =20
    Asked another way, are these higher waits insignificant side effects of
    un-jambing some other problem?
    =20
    It is not possible to know without the times, and even with the times, we
    cannot know whether this is a problem or simply a report of a change in
    statistics unless you tells us whether throughput is now changed, and how= .
    =20
    Asked yet another way, is some process materially slower than you need it= to
    be due to the amount of TIME spent waiting for these waits.
    =20
    Abstract attempts to change the values of statistics reports from running
    systems without correlation to a process or task that is running slower t= han
    required have little (little includes nothing) to do with making things
    actually process faster.
    =20
    Collection and observation of some statistics without correlation to prob= lem
    processes can be useful, but that is part of capacity planning and load
    management, not optimization. Even then (unless you're using systems
    statistics as a proxy for company business growth in an attempt to predic= t
    stock outcomes or something like that) you need to correlate the statisti= cs
    you observe to known scaling limits of your existing environment.
    =20
    So, are you trying to fix some process that is going slower than you thin= k
    it should (or more appropriately, slower than your service level
    requirement),
    or are you just trying to manipulate the statistics?
    =20
    Now I will not deprecate that activity if you're doing it as a learning
    exercise or even if you're just servicing your CTD. But I would like to k= now
    if you have a throughput problem you're trying to solve (or not).
    =20
    To answer your question, if changes you made ungated pent up demand for
    these tables, sure, you could have driven the load on the tables higher. = If
    your throughput went up, then the higher frequency of wait events is a ha= ppy
    side effect. If your throughput went down, the times and a correlation wi= th
    the now slower process(es) *might* show that the increase in waits
    represents a problem to solve.
    =20
    Regards,
    =20
    mwf
    =20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of The Human Fly
    Sent: Wednesday, May 18, 2005 2:11 AM
    To: gorbyx_at_gmail.com
    Cc: Cary.Millsap_at_hotsos.com; Oracle-L Freelists
    Subject: Re: buffer busy waits PUZZLES
    =20
    However, I haven't have buffer busy wait as my tops events nor I see
    any session execessively waiting for this event. I have already
    mentioned in my previous replies that I have grabbed these values from
    v$segment_statistics dynamic view.
    =20
    The following are today's result for the buffer busy waits.
    =20
    TRANSACTION_LOG 211143
    FM_AUDIT_FORM 39906
    C_INTIMATION 37029
    CIS_AUDIT_TRAILH 15001
    OUTWARD_CLEARING_CHEQUES 8310
    C_CUSTOMER 5793
    FM_OLTP_LOG 2727
    INWARD_CLEARING_CHEQUES 2347
    RB_RESTRAINTS 2239
    SYSTEM_PARAMETER_VALUE 1492
    RB_TRAN_HIST 1102
    =20
    I have increased default freelist 1 to 5 to those tables and now I can
    clearly see that those tables buffer busy wait event has increased
    madly. This could be due to heavy access on those tables?
    =20
    However, I have been reading Cary's book past 1 year and still I
    enjoying reading it. Thank god, now, I started undertanding the
    concept behind method R and the true power of 10046 trace for
    resolving performance issues.
    =20
    On 5/18/05, Alex Gorbachev wrote:
    My bad...
    =3D20
    2005/5/17, Cary Millsap :
    Ah! Credit to Gaja and Kirti for that one...
    =3D3D20
    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *
    =3D3D20
    Visit www.hotsos.com for curriculum and schedule details...
    =3D3D20
    =3D3D20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists=
    .o=3D
    rg=3D3D
    ]
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES
    =3D3D20
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.
    =3D20
    --=3D3D20
    Best regards,
    Alex Gorbachev
    --
    http://www.freelists.org/webpage/oracle-l
    =3D20
    =20
    --=3D20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia
    -------------------------------------------------------------------------= --=3D
    -------------
    "It is your atittude, not your aptitude that determins your altitude."
    --
    http://www.freelists.org/webpage/oracle-l
    =20
    =20
    --=20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---------------------------------------------------------------------------=

    "It is your atittude, not your aptitude that determins your altitude."
  • Alex Gorbachev at May 18, 2005 at 6:08 pm
    See inline.

    2005/5/18, The Human Fly :
    However, I am not doing it for any testing or learning purpose. We are
    facing some transactions time-out in our production database uding
    texudo middleware.
    What I would do is to isolate these "some" transactions in terms of
    time and Oracle session (and/or application binaries, transaction
    type).
    It's probably not very difficult to narrow down to the certain
    application binary and/or transaction type. If timeouts happen on
    different transactions and/or Tuxedo binaries than you would probably
    want to concentrate on the most critical ones and most impacted.
    With timeframe it might be tricky... You might be tempted to seek for
    some correlation with other events or with some storage definitions,
    schema objects, etc. (like in this case). However, in my experiance I
    have been most of the time much better off tracing correct session
    with 10046 at the right time. Sometimes, in order to get to the "right
    time", I had to enable 10046 for significant time and then cut only
    very tiny part from gigs of traces (based on tim values and timeouts
    that we observed on application tier).
    The best thing about it is that often problem is identified to be
    extenal to your database saving hours and days of valuable time trying
    to tune a well operating component.
    The tables which I increased the freelist are the one who are
    referring almost in every transaction.
    Think about it - how would it help you with _SOME_ timeouts?
    I dont see 'buffer busy wait' as top 5 timed events in any statspack
    report but very seldomly I can see them in v$session_wait.
    Again, chances are that you will not see your problem in Statspack
    reports at all! If I understand you correclty, the system is MOSTLY ok
    but SOME transactions experience timeouts. Solution is to limit scope
    of your statistic correctly (time/sessions/transactions).
    My thinking was, all these tables have only 1 freelist (default
    value), when they are involving heavy concurent insertion, it would be
    a good idea to play with its freelist.
    If you get through Carry's book again, you'll find examples how
    actions of this kind can give you even more timeouts (real service
    impact and not just increase/decrease of buffer busy waits).

    --=20
    Best regards,
    Alex Gorbachev
  • Terry Sutton at May 18, 2005 at 11:15 pm
    Some of the others have given you good advice about isolating your problem
    sessions to see if the buffer busy waits are related to the sessions timing
    out. I'd like to add a couple points to what they've said.

    It has been mentioned that you are only listing number of buffer busy waits,
    not time waited. Whether you're looking at an individual session or
    systemwide stats, the number of waits is not very important (though, at a
    query level, it can sometimes help with diagnosis). What is important is
    the amount of time spent waiting. For example if the total amount of time
    your system has spent on buffer busy waits is 100 seconds, and you've had
    hundreds of transactions timing out after waiting 60 seconds each, then you
    can figure that the buffer busy waits aren't your big problem. Time waited
    is what you need to be measuring.

    It has also been mentioned, but you don't seem to have addressed it- you
    need to determine the reason code of the buffer busy wait (the P3 value in
    v$session_wait). There are about 10 different reason codes (see Metalink
    doc #34405.1) and they point to different causes of the buffer busy wait.
    While sometimes a buffer busy wait can due to a freelist problem (though
    I've never come across that as the problem in a production system), there
    are several other causes which have nothing to do with freelists. If you
    find that buffer busy waits are a significant problem (in time waited), then
    find out the reason code and what it represents.

    --Terry

    Original Message -----
    From: "The Human Fly"
    To: "Mark W. Farnham"
    Cc: "Oracle-L Freelists"
    Sent: Wednesday, May 18, 2005 5:27 AM
    Subject: Re: buffer busy waits PUZZLES - is there actually a problem?

    First of all thnks for your valuable time and suggestions.

    However, there is no change in our transaction rate, they are almost
    the same value at any given time.
    However, I am not doing it for any testing or learning purpose. We are
    facing some transactions time-out in our production database uding
    texudo middleware.
    The tables which I increased the freelist are the one who are
    referring almost in every transaction.
    Server configuration, 9gb of RAM with 5cpu, AIX 5m on D240 storage.
    I dont see 'buffer busy wait' as top 5 timed events in any statspack
    report but very seldomly I can see them in v$session_wait.
    My thinking was, all these tables have only 1 freelist (default
    value), when they are involving heavy concurent insertion, it would be
    a good idea to play with its freelist.
    On 5/18/05, Mark W. Farnham wrote:
    Did your transactional throughput go up, down, or not change much?
    =20
    Asked another way, are these higher waits insignificant side effects of
    un-jambing some other problem?
    =20
    It is not possible to know without the times, and even with the times, we
    cannot know whether this is a problem or simply a report of a change in
    statistics unless you tells us whether throughput is now changed, and how= .
    =20
    Asked yet another way, is some process materially slower than you need it= to
    be due to the amount of TIME spent waiting for these waits.
    =20
    Abstract attempts to change the values of statistics reports from running
    systems without correlation to a process or task that is running slower t= han
    required have little (little includes nothing) to do with making things
    actually process faster.
    =20
    Collection and observation of some statistics without correlation to prob= lem
    processes can be useful, but that is part of capacity planning and load
    management, not optimization. Even then (unless you're using systems
    statistics as a proxy for company business growth in an attempt to predic= t
    stock outcomes or something like that) you need to correlate the statisti= cs
    you observe to known scaling limits of your existing environment.
    =20
    So, are you trying to fix some process that is going slower than you thin= k
    it should (or more appropriately, slower than your service level
    requirement),
    or are you just trying to manipulate the statistics?
    =20
    Now I will not deprecate that activity if you're doing it as a learning
    exercise or even if you're just servicing your CTD. But I would like to k= now
    if you have a throughput problem you're trying to solve (or not).
    =20
    To answer your question, if changes you made ungated pent up demand for
    these tables, sure, you could have driven the load on the tables higher. = If
    your throughput went up, then the higher frequency of wait events is a ha= ppy
    side effect. If your throughput went down, the times and a correlation wi= th
    the now slower process(es) *might* show that the increase in waits
    represents a problem to solve.
    =20
    Regards,
    =20
    mwf
    =20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of The Human Fly
    Sent: Wednesday, May 18, 2005 2:11 AM
    To: gorbyx_at_gmail.com
    Cc: Cary.Millsap_at_hotsos.com; Oracle-L Freelists
    Subject: Re: buffer busy waits PUZZLES
    =20
    However, I haven't have buffer busy wait as my tops events nor I see
    any session execessively waiting for this event. I have already
    mentioned in my previous replies that I have grabbed these values from
    v$segment_statistics dynamic view.
    =20
    The following are today's result for the buffer busy waits.
    =20
    TRANSACTION_LOG 211143
    FM_AUDIT_FORM 39906
    C_INTIMATION 37029
    CIS_AUDIT_TRAILH 15001
    OUTWARD_CLEARING_CHEQUES 8310
    C_CUSTOMER 5793
    FM_OLTP_LOG 2727
    INWARD_CLEARING_CHEQUES 2347
    RB_RESTRAINTS 2239
    SYSTEM_PARAMETER_VALUE 1492
    RB_TRAN_HIST 1102
    =20
    I have increased default freelist 1 to 5 to those tables and now I can
    clearly see that those tables buffer busy wait event has increased
    madly. This could be due to heavy access on those tables?
    =20
    However, I have been reading Cary's book past 1 year and still I
    enjoying reading it. Thank god, now, I started undertanding the
    concept behind method R and the true power of 10046 trace for
    resolving performance issues.
    =20
    On 5/18/05, Alex Gorbachev wrote:
    My bad...
    =3D20
    2005/5/17, Cary Millsap :
    Ah! Credit to Gaja and Kirti for that one...
    =3D3D20
    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *
    =3D3D20
    Visit www.hotsos.com for curriculum and schedule details...
    =3D3D20
    =3D3D20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists=
    .o=3D
    rg=3D3D
    ]
    On Behalf Of Alex Gorbachev
    Sent: Tuesday, May 17, 2005 2:42 PM
    To: sjaffarhussain_at_gmail.com
    Cc: K Gopalakrishnan; oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES
    =3D3D20
    sessions/transactions than you you've got CTD ("Compulsive Tuning
    Disorder") speaking Carry's language.
    =3D20
    --=3D3D20
    Best regards,
    Alex Gorbachev
    --
    http://www.freelists.org/webpage/oracle-l
    =3D20
    =20
    --=3D20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia
    -------------------------------------------------------------------------= --=3D
    -------------
    "It is your atittude, not your aptitude that determins your altitude."
    --
    http://www.freelists.org/webpage/oracle-l
    =20
    =20
    --=20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---------------------------------------------------------------------------=

    "It is your atittude, not your aptitude that determins your altitude."
  • Powell, Mark D at May 10, 2005 at 9:54 am
    Just because there is a wait does not mean there is a problem. How many
    IO were there to the objects during the same time period? 8032 waits
    out of 8032 requests is probably a problem but 8032 waits over 8 million
    requests doesn't seem like much of a problem. I do not see how anyone
    can give you sound recommendations when you only provide one statistic.

    IMHO -- Mark D Powell --

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of The Human Fly
    Sent: Tuesday, May 10, 2005 9:29 AM
    To: K Gopalakrishnan
    Cc: oracle-l_at_freelists.org
    Subject: Re: buffer busy waits PUZZLES

    Gopal,

    Actually, I was digging at v$segment_statistic level. The following
    query has been used,

    select object_name,statistic_name,value from v$segment_statistics where
    statistic_name like '%buffer busy%'
    and value > 100
    order by value desc

    and the output is

    OBJECT_NAME STATISTIC_NAME VALUE
    ------------------------------ ------------------------------ ----------
    C_INTIMATION buffer busy waits 8032
    CIS_AUDIT_TRAILH buffer busy waits 3739
    TRANSACTION_LOG buffer busy waits 2185
    FM_AUDIT_FORM buffer busy waits 2032
    C_CUSTOMER buffer busy waits 1768
    FM_OLTP_LOG buffer busy waits 1476
    RB_RESTRAINTS buffer busy waits 730
    RB_TRAN_HIST buffer busy waits 653
    FM_ERROR_LOG buffer busy waits 532

    I have added freelist as 5 to some of the tables, like
    c_intimation,c_transaction_log,c_customer,fm_audit_form
    these tables are oltp tables and every transactions entry goes to these
    tables. We have around 45 - 55 transaction per second.

    Planning to move those tables to a tablespace with ASSM feature.

    Any further idea?
    On 5/10/05, K Gopalakrishnan wrote:
    Jaffar:
    =3D20
    What is the most common reason code (p3=3D3D?) you are noticing? = Tracing
    the sessions with 10046^8 will give you more information about the =20
    type of the block and the reason code for buffer busy wait..
    =3D20
    (btw the waits are 'buffer busy waits' WAIT or 'buffer busy' WAIT?) =20
    If it is 'buffer busy' WAITs then the issue may not be with freelists.
    =3D20
    --
    Best Regards,
    K Gopalakrishnan
    Co-Author: Oracle Wait Interface, Oracle Press 2004 =20
    http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
    =3D20
    --=3D20
    Best Regards,
    Jaffar, OCP DBA
    Banque Saudi Fransi
    Saudi Arabia

    ---=3D

    "It is your atittude, not your aptitude that determins your altitude."

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 10, '05 at 8:45a
activeMay 18, '05 at 11:15p
posts13
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase