buffer cache hit ratio. LIO vs PIO - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 40

Thread: buffer cache hit ratio. LIO vs PIO

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Buffer cache hit ratio.....

    Originally posted by slimdave
    just run some badly designed sql over and over again, thus increasing LIO's and herdly touching PIO's. That tells you all you need to know about how meaningful the ratio is.
    Why not tell Hit Ratio as CRAP

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #12
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Re: Re: Buffer cache hit ratio.....

    Originally posted by abhaysk
    Why not tell Hit Ratio as CRAP
    But it's not! It's just one of many "tools" that you can use to judge if your db is healthy - you need to understand its usefulness and its limitations in the context of your db.

  3. #13
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Buffer cache hit ratio.....

    Originally posted by Alchemy
    When I was being taught Oracle 9i(OCP) our Instructor made the point that if buffer cache hit ratio should be at the acceptable level of 95%, and if it fell below 95% to add more ram(tuning). All but 1 article that I have read on the subject, and outside books(2) that I have said the same thing, "when buffer cache hit ratio falls below 95% throw more ram(tuning) at it until it rises back up to 95%.
    A generalisation and therefore false! HOWEVER in some proportion of cases, it might be a cost-effective solution: memory is cheap and consultants are expensive.

    The proper solution is expensive because it requires someone to think about it:

    a) Is there a problem?
    A low hit ratio in itself is NOT a problem. (A few times per day, I have hit ratios around 20% for a few minutes when a user selects customers according to some ad-hoc profile. We made a conscious decision NOT to add a vast numbers of indexes to cover all eventualities, so most of these selections are FTS. Getting a customised client list in two minutes is very acceptable to the end-user - it would take 24hrs if he asked me to do it).

    b) Why has the ratio changed?
    c) Does it matter?
    d) Should I fix it?
    e) Can I fix it?
    These may take weeks to answer. But these answers are the only valid ones.

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: Re: Buffer cache hit ratio.....

    Originally posted by DaPi
    But it's not! It's just one of many "tools" that you can use to judge if your db is healthy - you need to understand its usefulness and its limitations in the context of your db.

    Ok, lets see how use full might be that * HIT Ratio *..

    Case 1 :

    Ratio > 95+

    * Some argue that u gotta bad sqls where in ur LIOs shooting up so much that u cannot see the PIOs to be significant..

    * But it can as well be some thing like ALL SQL perfectly tuned and ther isnt any crap requirement by which LIOs will have shoot up ... and yes now u can say HIT is perfect and u can be happy..

    So with this u really cannot make anything unless u know what SQL are running and r they tuned and so many with respect to SQL.

    Case 2 :

    Ratio < 75-

    * Some may argue that u have a balance between PIO/LIO and assume all SQL tuned one.

    * Contradictory to it will be, there are very simple requirements (SQLs) and its codeded in such a fashion its worst ( BAD sqls ) and ofen reading data from files...


    So with this as well u really cannot make anything unless u know what SQL are running and r they tuned and so many with respect to SQL.


    So, why bother on HIT Ratio and just start looking for BAD SQLs and every one will be happy !

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Re: Re: Re: Re: Buffer cache hit ratio.....

    Originally posted by abhaysk
    So, why bother on HIT Ratio and just start looking for BAD SQLs and every one will be happy !
    Ah, but WHEN do you look for BAD SQL? Possible answers:
    a) when someone complains
    b) when the hit ratio changes unexpectedly

    To my mind it's like a doctor using a thermometer: if the patient's temperature is normal, it doesn't necessarily mean he/she is in good health; if it is abnormal, better find out why.

    On p11 of Millsap's article, he describes the hit ratio as "unreliable" - by his definition the doctor's thermometer is "unreliable" too. Would you expect doctors to stop using thermometers?

    What would you think of a doctor who:
    a) never used a thermometer?
    b) never used anything but a thermometer?
    Last edited by DaPi; 01-29-2004 at 04:45 AM.

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: Re: Re: Re: Buffer cache hit ratio.....

    Originally posted by DaPi
    Ah, but WHEN do you look for BAD SQL? Possible answers:
    a) when someone complains
    b) when the hit ratio changes unexpectedly

    To my mind it's like a doctor using a thermometer: if the patient's temperature is normal, it doesn't necessarily mean he/she is in good health; if it is abnormal, better find out why.

    On p11 of Millsap's article, he describes the hit ratio as "unreliable" - by his definition the doctor's thermometer is "unreliable" too. Would you expect doctors to stop using thermometers?

    Well the things (Ratio Vs Thermometer) u r trying relate will be meaningless...

    Lets consider one by one cases..

    1) Thermo shows too high value than the normal 98 F Vs Ratio Showing more than 98+

    Conlusion from Thermo : Person is suerly not in good health.
    Conclusion from Ration : Can u make?? ( coz it can be any may be good if all sqls running are good and no stupid requirements and also may not be good if bad sqls running )

    2) Thermo shows normal value i.e 98 F Vs Ratio Showing 90 to 93.

    Conlusion from Thermo : Person may or may not be in good health.
    Conclusion from Ration : Can u make?? (here the ans being same).

    3) Thermo shows too less value than the normal 98 F Vs Ratio Showing too less than 80 may be between (50 to 70)

    Conlusion from Thermo : Person is suerly not in good health.
    Conclusion from Ration : Can u make?? (here the ans being same).


    Conclusion from above 3 : 2 out 3 cases for Thermo, u can be sure that something is wrong, but 0 out of 3 cases u are not sure that some thing is wrong/right ( Plainly by seeing Ratio ).

    So can u realy corelate?

    Yes ofcourse the second point u made ( drastic change in Hit Ratio is a very valid point, but with also u cannot be sure )..


    as far as the quote

    What would you think of a doctor who:
    a) never used a thermometer?
    b) never used anything but a thermometer?
    is concerned, both are CRAP !!

    I see u r big fan of * Hit Ratio *

    Abhay.
    Last edited by abhaysk; 01-29-2004 at 05:01 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #17
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Abhay, I think your comparison of hit ratio with temperature falls down because you know what is a "normal" temperature (actually my "normal" is 97.7 not 98.4 ! !) - for a db you need to find out what is "normal" for that db.
    I see u r big fan of * Hit Ratio *
    No not a big fan. Sometimes it's useful, so I don't call it crap.

  8. #18
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    Abhay, I think your comparison of hit ratio with temperature falls down because you know what is a "normal" temperature (actually my "normal" is 97.7 not 98.4 ! !) - for a db you need to find out what is "normal" for that db.
    Well David you are simply not willing to agree that Hit Ratio is meaningless...

    Coz u know who u can decide a NORMAL temp for a person, u also know how to find normal Hit Ratio of DB ( and also know that large flucation of it is possible and not necessaryly a problem but that is not the case with thermo )...



    ( i can say it be used very very rarely that to for an OLTP type system only & size of DB within TB & no no for DW/DSS )
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #19
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Can someone please tell me why BAD SQL (poorly tuned) causes more LIO than PIO?

    I always thought that whether or not a query used the Buffer Cache or Disk depended on whether or not the blocks were in memory. So why would a poorly written statement do this more than a well tuned one? Surely its a matter of the blocks ageing out of the buffers ...?

    I (obviously) have little experience in this aspect of the job. In 4 years we've never had a user complain about poor performance so I've had little scope to justify (to management) any time spent tuning the DB. So - I'm trying to learn it theoretically and came upon some contradictory 'advice' (my original post).

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by JMac
    [B]Can someone please tell me why BAD SQL (poorly tuned) causes more LIO than PIO?
    The amount of LIO's practically defines what is good and what is bad SQL (relatively).

    Try the following ...

    Code:
    set autotrace on
    
    select owner,object_name from dba_objects
    where timestamp =
    (select max(timestamp) from dba_objects)
    /
    
    select owner,object_name
    from
    (
    select owner,object_name,timestamp,
    max(timestamp) over () max_timestamp
    from dba_objects
    )
    where timestamp=max_timestamp
    /
    Try these on your system ... same result, different LIO's.

    Now if this were a user query then the blocks would either be in memory or not, so the PIO's would be the same. On my little test system I got 41420 consistent gets for the first methodology and 20710 for the second -- exactly half. The cache hit ratio for second methodology is therefore half of the ratio for the first methodology.

    You see? Better SQL = lower cache hit ratio. High cache hit ratio = potentially bad SQL.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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