Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
And I think it's important to realise that ratios are useless as a starting point in the tuning process on any system, not only Oracle. Most OS'es and databases use not instrumented correctly to deal with response time measurements (makes you wonder: If response TIME is what matters, how can you then not measure exactly that - time?) - so in the other worlds (Unix, VMS, Windows, SQL Server, MySQL, DB2) people still beleive in the ratios because they have nothing better. Mogens Anjo Kolk wrote: BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
Yep. Simple example: Even though it seems to be sometimes a little on the 'random' side, the ELAPSED_TIME column on V$SQL in v9 is an absolute god send... Cheers Connor --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: And I think it's important to realise that ratios are useless as a starting point in the tuning process on any system, not only Oracle. Most OS'es and databases use not instrumented correctly to deal with response time measurements (makes you wonder: If response TIME is what matters, how can you then not measure exactly that - time?) - so in the other worlds (Unix, VMS, Windows, SQL Server, MySQL, DB2) people still beleive in the ratios because they have nothing better. Mogens Anjo Kolk wrote: BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
I think Dave Ensor, at the recent UKOUG conference, called it his portable tuning kit: select elapsed_time, cpu_time from v$sql order by elapsed_time; Mogens Connor McDonald wrote: Yep. Simple example: Even though it seems to be sometimes a little on the 'random' side, the ELAPSED_TIME column on V$SQL in v9 is an absolute god send... Cheers Connor --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: And I think it's important to realise that ratios are useless as a starting point in the tuning process on any system, not only Oracle. Most OS'es and databases use not instrumented correctly to deal with response time measurements (makes you wonder: If response TIME is what matters, how can you then not measure exactly that - time?) - so in the other worlds (Unix, VMS, Windows, SQL Server, MySQL, DB2) people still beleive in the ratios because they have nothing better. Mogens Anjo Kolk wrote: BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Correct way to accuse BCHR tuning method (Was: Hit ratio)
Hi Yong I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. The thing is most people who have used this demonstration, use it for 2 reasons 1. It is immediately recognised and empathised with by the audience and 2. It is easily explained. How does it work, by doing pointless io, hmmm I wonder if that is a problem with my real system. You are of course in principle correct that one could use the same technique against other ratios, my suggestion would be that this might also be appropriate for similar reasons. My CPU is 75% utilized on my web server, I wonder does this mean it is well specified or that I am doing pointless cpu work? If people are merely saying the bchr is useless, here is a script that will create one for you then I would agree with you, but in my experience the argument is more cogent than that. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
#1. what is the difference in the 4 BCHR's in 9i? #2. How do you determine whether your buffer cache is sized properly? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 9:34 AM Hi Yong I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. The thing is most people who have used this demonstration, use it for 2 reasons 1. It is immediately recognised and empathised with by the audience and 2. It is easily explained. How does it work, by doing pointless io, hmmm I wonder if that is a problem with my real system. You are of course in principle correct that one could use the same technique against other ratios, my suggestion would be that this might also be appropriate for similar reasons. My CPU is 75% utilized on my web server, I wonder does this mean it is well specified or that I am doing pointless cpu work? If people are merely saying the bchr is useless, here is a script that will create one for you then I would agree with you, but in my experience the argument is more cogent than that. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
#1. what is the difference in the 4 BCHR's in 9i? What do you mean by that? Are you refferring to different buffer pool stats here? #2. How do you determine whether your buffer cache is sized properly? Your application meets it's defined operational response time and throughput constraints. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 9:34 AM Hi Yong I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. The thing is most people who have used this demonstration, use it for 2 reasons 1. It is immediately recognised and empathised with by the audience and 2. It is easily explained. How does it work, by doing pointless io, hmmm I wonder if that is a problem with my real system. You are of course in principle correct that one could use the same technique against other ratios, my suggestion would be that this might also be appropriate for similar reasons. My CPU is 75% utilized on my web server, I wonder does this mean it is well specified or that I am doing pointless cpu work? If people are merely saying the bchr is useless, here is a script that will create one for you then I would agree with you, but in my experience the argument is more cogent than that. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Correct way to accuse BCHR tuning method (Was: Hit ratio)
#2. How do you determine whether your buffer cache is sized properly? Your application meets it's defined operational response time and throughput constraints. Actually that is an interesting answer, since it doesn't answer the question asked 'How do I know if my buffer cache is sized properly' but a much better question which is something like 'how do I know if my application is tuned sufficiently?', or to borrow Gaja's CTD idea 'How do I know when to stop tuning?'. I don't know if other nations have hi-fi buffs (though I guess so) who obsess about the quality of the interconnects, the balance of the turntable, the merits of the pre-amp etc etc. I imagine them as conducting 'tuning' enquiries along the lines of 'how do I know if my speaker cables are performing optimally?' when a much much better question would be 'Why am I listening to Lionel Richie anyway?'. Asking the right questions is a key skill for DBAs it seems to me. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
Performance 'problems' are dependent on what the marketing department gets in the SLA. So if your marketing guys negotiate very strict response time requirements and you dont meet them, then you have a performance problem. How do I know if my buffer cache is having any effect on that? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 4:24 PM #2. How do you determine whether your buffer cache is sized properly? Your application meets it's defined operational response time and throughput constraints. Actually that is an interesting answer, since it doesn't answer the question asked 'How do I know if my buffer cache is sized properly' but a much better question which is something like 'how do I know if my application is tuned sufficiently?', or to borrow Gaja's CTD idea 'How do I know when to stop tuning?'. I don't know if other nations have hi-fi buffs (though I guess so) who obsess about the quality of the interconnects, the balance of the turntable, the merits of the pre-amp etc etc. I imagine them as conducting 'tuning' enquiries along the lines of 'how do I know if my speaker cables are performing optimally?' when a much much better question would be 'Why am I listening to Lionel Richie anyway?'. Asking the right questions is a key skill for DBAs it seems to me. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Correct way to accuse BCHR tuning method (Was: Hit ratio)
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: 27 December 2003 21:59 To: Multiple recipients of list ORACLE-L Subject: Re: Correct way to accuse BCHR tuning method (Was: Hit ratio) Performance 'problems' are dependent on what the marketing department gets in the SLA. So if your marketing guys negotiate very strict response time requirements and you dont meet them, then you have a performance problem. How do I know if my buffer cache is having any effect on that? Build a profile of what the app is doing. If it is spending a significant proportion of its time waiting on physical IO then *maybe* your cache is badly sized (most likely you are doing too much IO though). Even here though (and in my experience others may have different views) your first target will be to reduce the IO before you throw memory at the problem. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
#2. How do you determine whether your buffer cache is sized properly? Your application meets it's defined operational response time and throughput constraints. Actually that is an interesting answer, since it doesn't answer the question asked 'How do I know if my buffer cache is sized properly' but a much better Well, IF Your application meets it's defined operational response time and throughput constraints THEN your buffer cache is sized properly (as everything else) AND one stop worrying about it. Anyway, don't take my posts in threads containing BCHR's too seriously ;) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Correct way to accuse BCHR tuning method (Was: Hit ratio)
[This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Correct way to accuse BCHR tuning method (Was: Hit ratio)
Yong, The point I want to make is that the buffer cache hit ratio actually *is* useful, in the following way. If its value is greater than about 99%, then I can practically guarantee that there is some high-LIO SQL inside the application whose repair will greatly improve system performance. Connor's script is a neat means of jarring someone out of the belief that a good hit ratio necessarily indicates good performance. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 11:09 AM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Correct way to accuse BCHR tuning method (Was: Hit ratio)
BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hit Ratio
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only
if you carefully comtemplate it will you see that there's no relevance of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event times. If
you need very long db file reads, create a big table and keep scanning it. If
you need long enqueue waits, create a table and insert a row. Create 10 or 100
sessions (depending on your patience) and delete from that table and wait. The
fact that you can get arbitary wait times does not reduce the efficacy of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very insufficient
used alone, to tune the database. The reason is that they don't contain enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as scan
rate, CPU usage and netstat output more often, hit ratios in some sar output
are still occasionally used. Most ratios could still be distored by a rogue
user repeatedly doing, say, find / for inodes or find / -exec grep SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage patterns
invalidates your numbers even if you're using a well respected tuning method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%. What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary Millsap
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Yong:
I have not seen all the threads on this. So there are chances some body
might have
covered this/I may be missing some interesting things..But the issue is,
tuning or measuring the
database performance ONLY with Hit Ratios. By high hit ratios Damagement
will tend to understand , that percentage of data is read from the
cache/memory
and try to add memory till the get closer to 100..
I think what we need to understand is the interpretation of Hit ratio. 90%
HIT
ratio does not mean 90% of the data is read from the disk. It just tells a
block
or buffer which was read in to cache is RE-READ 9 times before it goes to
disk.
I have seen many sites with oversized buffer cache/shared pool targetting
100%
hit ratio and suffering huge latch contention. I have been to a site
recently where a FLUSH
shared pool took nearly 5 minutes and checkpoint took close to a minute,
with 99.99%CHR.
But simulating high wait times by yout tricks for a particular session may
bump the wait times
You may probably generate high times for enqueue or any of the IO
events. But when you use 10046 or V$session_wait for a particular session,
the bumped numbers will not be affecting the diagnosability of your
problem.
But if you want to start questioning, you can question the timing details of
the
wait events. Oracle uses gettimeofday () to get the time of the wait events
and
if you alter the system time couple of times, that may give some odd numbers
to the entire timing data. But the bottomline is , Hit ratios are
beautiful numbers
but, you can not relate the pattern to the performance. May be you can
compare
the hit ratio when the system is good/bad and figure out there is a change
in IO
pattern between those interval.. IMHO and YMMV.
Regards,
K Gopalakrishnan
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 8:59 PM
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor.
Only
if you carefully comtemplate it will you see that there's no relevance of
the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning it.
If
you need long enqueue waits, create a table and insert a row. Create 10 or
100
sessions (depending on your patience) and delete from that table and wait.
The
fact that you can get arbitary wait times does not reduce the efficacy of
wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as
scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command
Re: RE: Hit Ratio
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary Millsap
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: RE: Hit Ratio
I hope not, but I think so.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's
objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve
Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create
10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't
contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not
condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement
scripts.
Especially when your bonus depends on it, this is a good time to
perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http
RE: RE: Hit Ratio
Cary's being diplomatic, as well as engaging in some wishful thinking.
:)
They appear to be quite prevalent. There are other microcosms of
Oracle users that you will find from time to time that realize
how Oracle works, and how to go about fixing performance problems.
If though you consider the widespread use of texts that propagate
ancient tuning advice, you must come to the conclusion that it is
still in the mainstream.
Jared
On Tue, 2003-12-23 at 10:54, Cary Millsap wrote:
I hope not, but I think so.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's
objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve
Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create
10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't
contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not
condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement
scripts.
Especially when your bonus depends on it, this is a good time to
perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
RE: RE: Hit Ratio
Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. LIO's are inherently cheaper than PIO's simply because you have to complete the LIO before asking for a PIO. And no you can't work with data that has not been allocated a space in the buffer pool, but that does not mean that a high hit ratio is a good thing either. The problem with a high hit ratio consequently high LIO's is that the process is simply looking at the same bits of data over and over again in a senseless waste of CPU. The goal of any SQL tuning should be to get the process to complete in the shortest elapsed time as possible irrespective of the CPU, LIO, or PIO necessary to get the job done. That being the case a SQL statement should, ideally, be written to look at any single piece of data once and only once which would result in a lower LIO's higher PIO's. That being said, it's also the case that the ideal SQL statement has not yet been written, including those I author. But at least I try. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Cary's being diplomatic, as well as engaging in some wishful thinking. :) They appear to be quite prevalent. There are other microcosms of Oracle users that you will find from time to time that realize how Oracle works, and how to go about fixing performance problems. If though you consider the widespread use of texts that propagate ancient tuning advice, you must come to the conclusion that it is still in the mainstream. Jared On Tue, 2003-12-23 at 10:54, Cary Millsap wrote: I hope not, but I think so. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:29 PM To: Multiple recipients of list ORACLE-L are there really that many people who use hit ratio? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue AM 11:49:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio Yong, Connor's script is not a joke, it's a proof by counterexample that the advice You SQL is tuned if and only if it has a high hit ratio is rubbish. The buffer cache hit ratio is a tool. Used properly, nobody's objecting. It's proper use? To answer the question, What percentage of LIO calls can be satisfied without an OS read call? The correct point that many on this list make over and over again, is that this is often the wrong question to be asking. (And actually, the conventional BCHR=(L-P)/L formula doesn't answer that question very well anyway; see Steve Adams's site for more detail.) It's not the ratio that needs condemning, it's the advice about how to use the ratio. The ratio just happens to be the emblem on the flag. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Tuesday, December 23, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi, Carel-Jan and Rich, Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only if you carefully comtemplate it will you see that there's no relevance of the fact that you can get any hit ratio to the fact that hit ratios are insufficient in performance tuning. It would be equally easy to write scripts to bump up some wait event times. If you need very long db file reads, create a big table and keep scanning it. If you need long enqueue waits, create a table and insert a row. Create 10 or 100 sessions (depending on your patience) and delete from that table and wait. The fact that you can get arbitary wait times does not reduce the efficacy of wait event interface as a performance tuning tool. Buffer cache or library cache hit ratios are not sufficient, very insufficient used alone, to tune the database. The reason is that they don't contain enough information to tune the system with. This is the only reason we should not solely rely on them; in fact, not using them at all doesn't hurt much. The reason is not that we can get any value we want by playing pranks. Hit ratios are still used in other performance tuning and not condemned. Although in UNIX performance tuning one looks at absolute numbers such as scan rate, CPU usage and netstat output more often, hit ratios in some sar output are still
RE: Hit Ratio
One place I can think of (the only place?) where BCHR may be useful,
is as a lower bound for the OPTIMIZER_INDEX_CACHING parameter. This
parameter pretty much asks the question that Cary posed in his email.
I say that the BCHR should be the lower bound for this value because
the question that OPTIMIZER_INDEX_CACHING asks is slightly different
in that it's asking specifically about index blocks. BCHR is across
all block types, and, in some cases, on some databases, the answer to
to those slightky different questions may vary greatly.
What it comes down to is that BCHR is an isolated datapoint. You can't
use it in isolation to determine anything.
This has been done to death. I'll shut up now. If there's any doubt,
read Cary's papers.
-Mark
-Original Message-
From: Cary Millsap [mailto:[EMAIL PROTECTED]
Sent: Tue 12/23/2003 11:49 AM
To: Multiple recipients of list ORACLE-L
Cc:
Subject:RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
RE: RE: Hit Ratio
At 12:04 23-12-03 -0800, you wrote: Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. She? Cary, you didn't tell us about this surgery ;-) Dick, last time I saw Cary (October) he was very masculin. I bet this hasn't changed during the last two months. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Hit Ratio
SORRY!! Regrettably e-mail does not provide the required info, namely a picture. The only other Cary I know is female, in every sense of the word. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 3:45 PM To: Multiple recipients of list ORACLE-L At 12:04 23-12-03 -0800, you wrote: Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. She? Cary, you didn't tell us about this surgery ;-) Dick, last time I saw Cary (October) he was very masculin. I bet this hasn't changed during the last two months. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Hit Ratio
immature ...and all this time I thought Cary was a man! :) ...now all the talk of showers and Cary's cleanliness from Mogens makes sense!! ...(I'm 99.99% sure Cary IS a man...at least he looked it at IOUG a few years back) :) /immature merry xmas everyone! -Original Message- Sent: Tuesday, December 23, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. LIO's are inherently cheaper than PIO's simply because you have to complete the LIO before asking for a PIO. And no you can't work with data that has not been allocated a space in the buffer pool, but that does not mean that a high hit ratio is a good thing either. The problem with a high hit ratio consequently high LIO's is that the process is simply looking at the same bits of data over and over again in a senseless waste of CPU. The goal of any SQL tuning should be to get the process to complete in the shortest elapsed time as possible irrespective of the CPU, LIO, or PIO necessary to get the job done. That being the case a SQL statement should, ideally, be written to look at any single piece of data once and only once which would result in a lower LIO's higher PIO's. That being said, it's also the case that the ideal SQL statement has not yet been written, including those I author. But at least I try. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Cary's being diplomatic, as well as engaging in some wishful thinking. :) They appear to be quite prevalent. There are other microcosms of Oracle users that you will find from time to time that realize how Oracle works, and how to go about fixing performance problems. If though you consider the widespread use of texts that propagate ancient tuning advice, you must come to the conclusion that it is still in the mainstream. Jared On Tue, 2003-12-23 at 10:54, Cary Millsap wrote: I hope not, but I think so. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:29 PM To: Multiple recipients of list ORACLE-L are there really that many people who use hit ratio? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue AM 11:49:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio Yong, Connor's script is not a joke, it's a proof by counterexample that the advice You SQL is tuned if and only if it has a high hit ratio is rubbish. The buffer cache hit ratio is a tool. Used properly, nobody's objecting. It's proper use? To answer the question, What percentage of LIO calls can be satisfied without an OS read call? The correct point that many on this list make over and over again, is that this is often the wrong question to be asking. (And actually, the conventional BCHR=(L-P)/L formula doesn't answer that question very well anyway; see Steve Adams's site for more detail.) It's not the ratio that needs condemning, it's the advice about how to use the ratio. The ratio just happens to be the emblem on the flag. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Tuesday, December 23, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi, Carel-Jan and Rich, Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only if you carefully comtemplate it will you see that there's no relevance of the fact that you can get any hit ratio to the fact that hit ratios are insufficient in performance tuning. It would be equally easy to write scripts to bump up some wait event times. If you need very long db file reads, create a big table and keep scanning it. If you need long enqueue waits, create a table and insert a row. Create 10 or 100 sessions (depending on your patience) and delete from that table and wait. The fact that you can get arbitary wait times does not reduce the efficacy of wait event interface as a performance tuning tool. Buffer cache or library cache hit ratios are not sufficient, very insufficient used alone, to tune the database. The reason is that they don't contain enough information to tune the system with. This is the only reason we should not solely rely on them; in fact
Re: RE: Hit Ratio
Why do people still talk about THE buffer cache hit ratio ? There are lots of them. The one you can get from v$sysstat, the ones you can get from v$buffer_pool_statistics, and the ones you can get from v$segstat. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 6:29 PM are there really that many people who use hit ratio? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Hit Ratio
Exactly. select * from v$statname where name like '%gets%' is simple evidence of that. Cheers Connor --- Jonathan Lewis [EMAIL PROTECTED] wrote: Why do people still talk about THE buffer cache hit ratio ? There are lots of them. The one you can get from v$sysstat, the ones you can get from v$buffer_pool_statistics, and the ones you can get from v$segstat. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 6:29 PM are there really that many people who use hit ratio? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Well, I guess I could have added a :) after my request on how to fix the hit ratio, but it wouldn't be nearly as much fun. On Sun, 2003-12-21 at 03:29, Mogens Nrgaard wrote: Ah yes, you could introduce heuristically (spelling?!) skewed hit ratios. As Dave Ensor explained at UKOUG, the word heuristic in Oracle's optimizer code can be translated directly into constant. So add a number here or there until it fits. Mogens Jonathan Lewis wrote: Easy, A new formula for the hit ratio Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 1:19 AM Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?UTF-8?B?TW9nZW5zIE7DuHJnYWFyZA==?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hit Ratio
My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Hit Ratio
i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
(U) RE: RE: Hit Ratio
CLASSIFICATION: UNCLASSIFIED Rich, Burn any source that talks about hit ratio's. What exactly is running slow in your system and at what times ? Talk directly to the user that is experiencing the slowdown and ask them to repeat the behavior. Set a 10046 trace and go find the slowdown while the user is executing the application. Use the wait interface to determine what the culprit is. Lots of good books out on this stuff now. Is any batch job running at this time ? -Original Message- Sent: Monday, December 22, 2003 11:49 AM To: Multiple recipients of list ORACLE-L i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 10:14 AM To: Multiple recipients of list ORACLE-L As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Classification: UNCLASSIFIED -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hit Ratio
At 11:14 22-12-03 -0800, you wrote: My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of Cary's book), and download one of the fabulous BCHR enhancement scripts. Especially when your bonus depends on it, this is a good time to perform some BCHR tuning. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Hit Ratio
Erm...sorry. I said :) when I should've said ;). Joke. Pun. Tongue-in-cheek. Yer built too low. The fast ones keep going over your head. Gotta keep your eye on the ball. Eye. Ball. That's a joke there, son. Again, sorry. We're in no change mode until after the Holidaze and Foghorn Leghorn's gotta hold of me brain. Back to the Battle With The Vendor over not closing cursors... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 22, 2003 1:49 PM To: Multiple recipients of list ORACLE-L i dont think many people are using bchr anymore. I think its been talked down to death. only place I hear about it is offshore. people still using the old niemic book. his new took all that stuff out. or am i wrong? From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/12/22 Mon PM 02:14:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio My BCHR is currently 96.62%. In the past, it was normally over 99%. What should I do? I'll be waiting for Mladen's reply... :) Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Hit Ratio
Rich, you mean due to 'no change mode' you can't even change your hit ratio ... too bad. Happy holidays everyone !! Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 22, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Erm...sorry. I said :) when I should've said ;). Joke. Pun. Tongue-in-cheek. Yer built too low. The fast ones keep going over your head. Gotta keep your eye on the ball. Eye. Ball. That's a joke there, son. Again, sorry. We're in no change mode until after the Holidaze and Foghorn Leghorn's gotta hold of me brain. Back to the Battle With The Vendor over not closing cursors... ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Easy, A new formula for the hit ratio Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 1:19 AM Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
- Original Message - Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. Fair enough. I wonder what I need to tune to fix this? stop creating those tables? D Seriously: aren't you getting the I/O as mostly writes during the create? Therefore it should be very little off the buffers, no? Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Ah yes, you could introduce heuristically (spelling?!) skewed hit ratios. As Dave Ensor explained at UKOUG, the word heuristic in Oracle's optimizer code can be translated directly into constant. So add a number here or there until it fits. Mogens Jonathan Lewis wrote: Easy, A new formula for the hit ratio Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 1:19 AM Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?UTF-8?B?TW9nZW5zIE7DuHJnYWFyZA==?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
¦D Unreal, eh? that's SAP for you... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Just thought I would share my hit ratio with y'all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared On Sat, 2003-12-20 at 03:29, Nuno Souto wrote: D Unreal, eh? that's SAP for you... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Just thought I would share my hit ratio with y'all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Hit Ratio
Just thought I would share my hit ratio with y'all. LOGICAL I/O PHYSICAL I/O BLOCK HIT USERNAME SID OS USERPROCESS BLOCKS BLOCKS CHANGES RATIO -- -- - --- --- JKSTILL15 jkstill 24548 252 18 1 92.86 SAP_MV 7 jkstill 24073 925,9381,074,497 50,011 -16.04 2 rows selected. ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
HIT RATIO
Hi One of my database showing hit ratio low (=75% approx).I increased the parameters.After last reboot hit ratio was looking great.Last reboot was last week.I am wondering again this ratio is 73% approx. How to fix this problem except Init.ora prameter change? Thx -Seema _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HIT RATIO
Set up a dbms_job to run the command select 'x' from dual; every 10 seconds or so. I think you'll find that your hit ratio increases quite nicely. Oh, the only potential downside is that your overall system performance might go down, way down! But your ratios are great! It is kind of like taking your car into the mechanic and complaining it only goes 20 miles per hour. He runs some tests and says, Everything is fine, there are not any performance problems because your gas mileage is 30 miles per gallon. The two are not related. Seriously, don't focus on the ratio. Check out the great papers on www.hotsos.com, www.orapub.com and www.evdbt.com. And buy a copy of Oracle Performance Tuning 101 from Oracle Press. Focus on wait events, overall performance times and reducing i/o. Dan Fink -Original Message- Sent: Tuesday, November 19, 2002 12:05 PM To: Multiple recipients of list ORACLE-L Hi One of my database showing hit ratio low (=75% approx).I increased the parameters.After last reboot hit ratio was looking great.Last reboot was last week.I am wondering again this ratio is 73% approx. How to fix this problem except Init.ora prameter change? Thx -Seema _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: HIT RATIO
Why do care about this? Are people complaining? Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 8:04 PM Hi One of my database showing hit ratio low (=75% approx).I increased the parameters.After last reboot hit ratio was looking great.Last reboot was last week.I am wondering again this ratio is 73% approx. How to fix this problem except Init.ora prameter change? Thx -Seema _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HIT RATIO
What problem? Seriously, the buffer cache hit ratio is a pretty meaningless value (for details on why, see Cary Millsap's papers on www.hotsos.com). Are your users complaining? Are you seeing behavior on the system (disk contention, etc.) that makes you expect that your users will be complaining soon? -- Michael Brown Glen Raven, Inc./Info Services Senior Oracle DBA 1831 N. Park Ave Phone: (336)586-1146Glen Raven, NC 27217 Fax: (336)586-1382 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 19, 2002 2:05 PM To: Multiple recipients of list ORACLE-L Hi One of my database showing hit ratio low (=75% approx).I increased the parameters.After last reboot hit ratio was looking great.Last reboot was last week.I am wondering again this ratio is 73% approx. How to fix this problem except Init.ora prameter change? Thx -Seema _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). BEGIN:VCARD VERSION:2.1 N:Brown;Michael FN:Michael L Brown (E-mail) ORG:Glen Raven;Information Services TITLE:Sr. Database Administrator TEL;WORK;VOICE:336-586-1146 TEL;PAGER;VOICE:336-222-3912 TEL;WORK;FAX:336-586-1172 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;Information Services=0D=0A1831 N. Park Ave;Glen Raven;NC;27217;USA LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Information Services=0D=0A1831 N. Park Ave=0D=0AGlen Raven, NC 27217=0D=0AUS= A EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20011016T192723Z END:VCARD
RE: HIT RATIO
With Connor McDonald's script in the DBA tool kit, no one should be complaining about a bad BCHR. Ever :) ;-) Get the script from his web site : http://www.oracledba.co.uk/ - Kirti -Original Message- Sent: Tuesday, November 19, 2002 3:24 PM To: Multiple recipients of list ORACLE-L What problem? Seriously, the buffer cache hit ratio is a pretty meaningless value (for details on why, see Cary Millsap's papers on www.hotsos.com). Are your users complaining? Are you seeing behavior on the system (disk contention, etc.) that makes you expect that your users will be complaining soon? -- Michael Brown Glen Raven, Inc./Info Services Senior Oracle DBA 1831 N. Park Ave Phone: (336)586-1146Glen Raven, NC 27217 Fax: (336)586-1382 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 19, 2002 2:05 PM To: Multiple recipients of list ORACLE-L Hi One of my database showing hit ratio low (=75% approx).I increased the parameters.After last reboot hit ratio was looking great.Last reboot was last week.I am wondering again this ratio is 73% approx. How to fix this problem except Init.ora prameter change? Thx -Seema _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: low buffer hit ratio
Gurelei wrote: Hi. I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, fileds from STATS table FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and c.racf = 'RLEWI01' and b.Racf = a.Racf and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB') Group by b.Name, a.Racf, c.Manager Order by b.Name Here is the plan under oracle 7.3.3 rbo: 1.0 SELECT STATEMENT (, , ) 2.1 SORT GROUP BY (, , ) 3.1 NESTED LOOPS (, , ) 4.1 NESTED LOOPS (, , ) 5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) 5.2 TABLE ACCESS BY ROWID STATS (, , ) 6.1 INDEX RANGE SCAN STATS_FK2 (, , ) 4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) The largest table is STATS - 1.6 mil rows. STATS_FK2 is an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads? thanks Gene Gene, First reducing the number of disk reads doesn't necessarily means that it will run faster. Except in the case of fast index scans, index blocks are usually read one-at-a-time, while in a table scan you will read batches of several blocks. If you have for instance a large number of db_file_sequential_reads, then you may find that your query will perform better with a lesser index usage. It's then a matter of knowing where the data you want is. If it happens to be physically clustered, fine (I am using 'clustered' in the general sense here, not referring to Oracle clusters); if it scattered all over the place your query is likely to be painful to run ... Not knowing your data it's difficult to be specific but here are some general guide-lines: - How many rows does your query return ? If it's a huge number I would feel more comfortable with table scans than index accesses in the plan. - You seem to feed three things into your query, racf (any relationship to the IBM product?), product (your query doesn't say from which table it comes) and the date (BTW implicit conversions like here always make me feel nervous). Which one is the most selective ? Try to have your query start with the corresponding table (I am a big fan of /*+ ORDERED */). If you have paid for the partition option,this is something to consider too for your STATS table - a good way to cluster data. A brute scan of the suitable partition is usually extremely efficient. - If you have to join tables with not-so-significant criteria, USE_HASH deserves consideration. Try different things. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: low buffer hit ratio
--- Stephane Faroult [EMAIL PROTECTED] wrote: - How many rows does your query return ? If it's a huge number I would feel more comfortable with table scans than index accesses in the plan. It returns 8 rows in about 2-2.5 seconds. - You seem to feed three things into your query, racf (any relationship to the IBM product?), product (your query doesn't say from which table it comes) and the date (BTW implicit conversions like here always make me feel nervous). Which one is the most selective ? Try to have your query start with the corresponding table (I am a big fan of /*+ ORDERED */). I did something like that. I have prevented Oracle from using the index STATS_FK2 (on a manager field) by comcatinating ||'' to the manager field name. It forced Oracle to use the index on the date field (more selective) and reduced the time to 0.5 sec and I think increased the bhr to 95%. Thanks for your help Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: low buffer hit ratio
Dennis: OEM calculates the BHR for all the queries. I presume it is being calculated using the same formula but only using the disk reads and buffer gets for the single query. I started with this query because it has the lowest bhr in the database. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Gurelei - What do you mean by a low buffer hit ratio for a SQL statement? BHR is measured for the system, not for an individual query. Why have you singled this query out for attention? Are there complaints about its performance? Does it hit more blocks that other queries on your system? Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
low buffer hit ratio
Hi. I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, fileds from STATS table FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and c.racf = 'RLEWI01' and b.Racf = a.Racf and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB') Group by b.Name, a.Racf, c.Manager Order by b.Name Here is the plan under oracle 7.3.3 rbo: 1.0 SELECT STATEMENT (, , ) 2.1 SORT GROUP BY (, , ) 3.1 NESTED LOOPS (, , ) 4.1 NESTED LOOPS (, , ) 5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) 5.2 TABLE ACCESS BY ROWID STATS (, , ) 6.1 INDEX RANGE SCAN STATS_FK2 (, , ) 4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) The largest table is STATS - 1.6 mil rows. STATS_FK2 is an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads? thanks Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: low buffer hit ratio
Gurelei - What do you mean by a low buffer hit ratio for a SQL statement? BHR is measured for the system, not for an individual query. Why have you singled this query out for attention? Are there complaints about its performance? Does it hit more blocks that other queries on your system? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Hi. I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, fileds from STATS table FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and c.racf = 'RLEWI01' and b.Racf = a.Racf and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB') Group by b.Name, a.Racf, c.Manager Order by b.Name Here is the plan under oracle 7.3.3 rbo: 1.0 SELECT STATEMENT (, , ) 2.1 SORT GROUP BY (, , ) 3.1 NESTED LOOPS (, , ) 4.1 NESTED LOOPS (, , ) 5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) 5.2 TABLE ACCESS BY ROWID STATS (, , ) 6.1 INDEX RANGE SCAN STATS_FK2 (, , ) 4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) The largest table is STATS - 1.6 mil rows. STATS_FK2 is an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads? thanks Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cache hit ratio?
Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cache hit ratio?
Seema Singh wrote: Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema Use Connor's script. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache hit ratio?
If u r not kidding then attend hotsos clinic. r u sure u r not kidding can u catch the session which is not performing as it should from the end users perspective? get the top 5 wait events for that session. -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Subject: Cache hit ratio? Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar A. Ghosalkar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Cache hit ratio?
There is a script available (search the archives) that will set the ratio to anything you want... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 10/09/02 02:13 PMcc: Please respond toSubject: Cache hit ratio? ORACLE-L Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache hit ratio?
Seema 1. How low is too low? Basically, different applications will tend to have different BHR values (Buffer Hit Ratio). 2. BHR varies over time, and will depend a lot on the lengths of time you sample it, and what type of processing was running. 3. A low BHR can lead you to a problem. Other indicators like waits can also lead you to the same problems. But the point is that you need to investigate what the problem actually is, rather than just increase the buffer cache size. 4. Changing the db_block_buffer size may increase the BHR. Sometimes it doesn't affect the BHR at all. 5. Get a copy of the book Oracle Performance Tuning 101 and you will learn more about Oracle tuning and why you don't just want to focus on the BHR and increasing the buffer cache size. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 09, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache hit ratio?
And then again, maybe the cheapest path to your data is a full table scan. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 03:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cache hit ratio? Seema 1. How low is too low? Basically, different applications will tend to have different BHR values (Buffer Hit Ratio). 2. BHR varies over time, and will depend a lot on the lengths of time you sample it, and what type of processing was running. 3. A low BHR can lead you to a problem. Other indicators like waits can also lead you to the same problems. But the point is that you need to investigate what the problem actually is, rather than just increase the buffer cache size. 4. Changing the db_block_buffer size may increase the BHR. Sometimes it doesn't affect the BHR at all. 5. Get a copy of the book Oracle Performance Tuning 101 and you will learn more about Oracle tuning and why you don't just want to focus on the BHR and increasing the buffer cache size. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 09, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache hit ratio?
...and a high cache hit ratio (99%) is usually an indication of a serious problem as well. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- WILLIAMS Sent: Wednesday, October 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Seema 1. How low is too low? Basically, different applications will tend to have different BHR values (Buffer Hit Ratio). 2. BHR varies over time, and will depend a lot on the lengths of time you sample it, and what type of processing was running. 3. A low BHR can lead you to a problem. Other indicators like waits can also lead you to the same problems. But the point is that you need to investigate what the problem actually is, rather than just increase the buffer cache size. 4. Changing the db_block_buffer size may increase the BHR. Sometimes it doesn't affect the BHR at all. 5. Get a copy of the book Oracle Performance Tuning 101 and you will learn more about Oracle tuning and why you don't just want to focus on the BHR and increasing the buffer cache size. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 09, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache hit ratio?
However, it is not at all difficult to get a high BHR with Connor's script (...if one *must* see those 'five 9s'...) at http://www.oracledba.co.uk/. Click on Tuning and the on 'Custom Hit Ratio'. While there, browse the site for some really useful things than fixing the BHR. - Kirti -Original Message- Sent: Wednesday, October 09, 2002 9:19 PM To: Multiple recipients of list ORACLE-L ...and a high cache hit ratio (99%) is usually an indication of a serious problem as well. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- WILLIAMS Sent: Wednesday, October 09, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Seema 1. How low is too low? Basically, different applications will tend to have different BHR values (Buffer Hit Ratio). 2. BHR varies over time, and will depend a lot on the lengths of time you sample it, and what type of processing was running. 3. A low BHR can lead you to a problem. Other indicators like waits can also lead you to the same problems. But the point is that you need to investigate what the problem actually is, rather than just increase the buffer cache size. 4. Changing the db_block_buffer size may increase the BHR. Sometimes it doesn't affect the BHR at all. 5. Get a copy of the book Oracle Performance Tuning 101 and you will learn more about Oracle tuning and why you don't just want to focus on the BHR and increasing the buffer cache size. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 09, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Hi One of my database Cache Hit Ratio is low.I adjusted db_block_buffer and I have noticed after week again cache hit ratio became low. Can u please suggest what other things I can do to fix this problem? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based t
If you will search Metalink you will see that this bug is generic and not specific to a certain system. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 16, 2002 7:58 PM Guys, the 2429929 apply for Sun Solaris platform also? thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based train
Title: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based training) You should call your sales persons and start applying pressure for them to provide a correction to 9ir2. I am doing this right now. Yechiel AdarMehish - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 10, 2002 4:23 AM Subject: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based train FWIW There is a document (id 283852) on Metalink updated on 03-SEP-2002 created by "CENTER OF EXPERTISE" named THE COE PERFORMANCE METHOD A PERFORMANCE METHODOLOGY FOR ENTERPRISE-WIDE INFORMATION SYSTEMS which shows that Oracle Corporation turned from "ratio" to "wait interface" approach. The bad news is that in version 9.2.0.1 view V$SESSION_EVENT broken (bug 2429929), also described in Notes 208066.1. Bug fixed in 10i. This bug affects OEM, BSTAT/ESTAT, STATSPACK, etc. Did anybody adjust scripts which join V$SESSION with V$SESSION_EVENT? Should we use something like s.sid=e.sid+1 or s.sid-1=e.sid? Alex. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Looks like Cache Hit Ratios may finally go away !!!
Oracle and Cache Hit Ratio based training
FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
I can see it now... DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... -Original Message- Sent: Monday, September 09, 2002 12:13 PM To: Multiple recipients of list ORACLE-L FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
Hi all, I have a large data load to run into a partitioned table. With indexes the load takes over 24 hours, without on the order of 3 hours, obviously, I don't want to mess with the indexes until after the job completes. So I try to drop to local indexes and get ORA-14076. Fair enough. I go to metastink and see note 107976.1...exactly what I want. So I try to use it...and get ORA-01502. Has anybody done something like this and found there's an extra caveat from the note? Every other note oracle has seems to point back to the original one I am using. I'm on Solaris 8/Oracle 9.0.1.3. Any help would be appreciated. Here's the output I'm getting... SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 USABLE I_BUY_PR_PCE_TYPE_HIST_2 USABLE I_BUY_PR_PCE_TYPE_HIST_1 USABLE PK_BUY_PRICE_PCE_TYPE_HISTORY USABLE SQL ALTER TABLE buy_price_piece_type_history MODIFY PARTITION type13 2 UNUSABLE LOCAL INDEXES; Table altered. SQL select index_name, status from dba_ind_partitions where partition_name = 'TYPE13'; INDEX_NAME STATUS -- I_BUY_PR_PCE_TYPE_HIST_3 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_2 UNUSABLE I_BUY_PR_PCE_TYPE_HIST_1 UNUSABLE PK_BUY_PRICE_PCE_TYPE_HISTORY UNUSABLE SQL ALTER SESSION SET skip_unusable_indexes = true; Session altered. SQL insert into buy_price_piece_type_history partition (type13) select * from hold_type13; insert into buy_price_piece_type_history partition (type13) select * from hold_type13 * ERROR at line 1: ORA-01502: index 'RLADMIN.PK_BUY_PRICE_PCE_TYPE_HISTORY' or partition of such index is in unusable state SQL TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle and Cache Hit Ratio based training
Performance Tuning 102 dear, Performance Tuning 102 --- Fink, Dan [EMAIL PROTECTED] wrote: I can see it now... DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... -Original Message- Sent: Monday, September 09, 2002 12:13 PM To: Multiple recipients of list ORACLE-L FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based train
Title: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based training) FWIW There is a document (id 283852) on Metalink updated on 03-SEP-2002 created by CENTER OF EXPERTISE named THE COE PERFORMANCE METHOD A PERFORMANCE METHODOLOGY FOR ENTERPRISE-WIDE INFORMATION SYSTEMS which shows that Oracle Corporation turned from ratio to wait interface approach. The bad news is that in version 9.2.0.1 view V$SESSION_EVENT broken (bug 2429929), also described in Notes 208066.1. Bug fixed in 10i. This bug affects OEM, BSTAT/ESTAT, STATSPACK, etc. Did anybody adjust scripts which join V$SESSION with V$SESSION_EVENT? Should we use something like s.sid=e.sid+1 or s.sid-1=e.sid? Alex. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] Looks like Cache Hit Ratios may finally go away !!!
Re: Live Webcast: The death of the buffer cache hit ratio
Hi, You missed it, it seems that you were an hour late. You can download it from www.precise.com. When the archives appear there? It's still in pre-register state. Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Live Webcast: The death of the buffer cache hit ratio
already registered. Henry - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 02, 2002 6:33 PM Ok, everybody, let's show up in support of Anjo. This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven't had time to read it yet today. Jared - SearchEBusiness invites you to this Vendor Connection Webcast: * TOPIC: The death of the buffer cache hit ratio WHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT) SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and renowned Oracle performance author SPONSOR: Precise Software PRE-REGISTER FOR THIS WEBCAST TODAY: http://searchEBusiness.com/webcasts * -- About this Webcast -- Join Anjo Kolk, Chief Oracle Technologist at Precise Software and renowned Oracle performance author, for his views on Oracle performance tuning. Anjo will discuss how conventional tuning approaches rely too heavily on checking the buffer cache hit ratio. Many DBAs do their best to get a 99% or better hit ratio but discover that the performance of their database isn't really improving when the hit ratio gets better. Anjo will discuss these points and others: * If the hit ratio was tuned to 99% or better, why is end user response time still bad? * If tuning the hit ratio is not the answer to end-user response time, what is? * Why does a hit ratio do exactly the opposite of what you would think? * Logical I/Os -- The myths dispelled. * How to calculate the cost of a logical I/O. * How to use the response time model in Oracle to find performance problems. If database performance is important to you, click here to pre-register for this Webcast: http://searchEBusiness.com/webcasts * -- Sponsored By: Precise Software Solutions -* Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood, MA, delivers Precise i3, a comprehensive solution for Application Performance Management that focuses directly on the end-user experience and delivers a rapid return on technology investments. Precise i3 proactively detects and corrects the root causes of performance degradation before they affect response times. Visit us at http://www.precise.com. * -- Invite a colleague -- If you think this event topic would be interesting to a colleague, invite them to this Vendor Connection Webcast. All you need to do is forward this e-mail. Unsubscribe from 'Updates on upcoming online events' - Simply Reply to this Email with REMOVE within the Body or Subject or - Go to: http://searchEBusiness.techtarget.com/register - Log in to edit your profile. - Click on the link to Edit email subscriptions. - Uncheck the box next to the newsletter you wish to unsubscribe from. - When finished, click Save Changes to My Profile. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Live Webcast: The death of the buffer cache hit ratio
Thanks Jared, Drinks are on me ;-) [EMAIL PROTECTED] wrote: Ok, everybody, let's show up in support of Anjo.This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven'thad time to read it yet today.Jared-SearchEBusiness invites you to this Vendor Connection Webcast:*TOPIC: The death of the buffer cache hit ratioWHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT)SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and renowned Oracle performance authorSPONSOR: Precise SoftwarePRE-REGISTER FOR THIS WEBCAST TODAY: http://searchEBusiness.com/webcasts *-- About this Webcast --- ---Join Anjo Kolk, Chief Oracle Technologist at Precise Software andrenowned Oracle performance author, for his views on Oracleperformance tuning. Anjo will discuss how conventional tuningapproaches rely too heavily on checking the buffer cache hit ratio.Many DBAs do their best to get a 99% or better hit ratio but discoverthat the performance of their database isn't really improving whenthe hit ratio gets better. Anjo will discuss these points and others:* If the hit ratio was tuned to 99% or better, why is end user response time still bad?* If tuning the hit ratio is not the answer to end-user response time, what is? * Why does a hit ratio do exactly the opposite of what you would think?* Logical I/Os -- The myths dispelled.* How to calculate the cost of a logical I/O.* How to use the response time model in Oracle to find performance problems.If database performance is important to you, c lick here topre-register for this Webcast: http://searchEBusiness.com/webcasts *-- Sponsored By: Precise Software Solutions-*Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood,MA, delivers Precise i3, a comprehensive solution for ApplicationPerformance Management that focuses directly on the end-userexperience and delivers a rapid return on technology investments.Precise i3 proactively detects and corrects the root causes ofperformance degradation before they affect response times. Visit usat http://www.precise.com. *--- --- Invite a colleague --If you think this event topic would be interesting to a colleague,invite them to this Vendor Connection Webcast. All you need to do isforward this e-mail.Unsubscribe from 'Updates on upcoming online events' - Simply Reply to this Email with REMOVE within the Body or Subject or - Go to: http://searchEBusiness.techtarget.com/register - Log in to edit your profile. - Click on the link to Edit email subscriptions. - Uncheck the box next to the newsletter you wishto unsubscribe from. - When finished, click "Save Changes to My Profile."
Live Webcast: The death of the buffer cache hit ratio
Ok, everybody, let's show up in support of Anjo. This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven't had time to read it yet today. Jared - SearchEBusiness invites you to this Vendor Connection Webcast: * TOPIC: The death of the buffer cache hit ratio WHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT) SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and renowned Oracle performance author SPONSOR: Precise Software PRE-REGISTER FOR THIS WEBCAST TODAY: http://searchEBusiness.com/webcasts * -- About this Webcast -- Join Anjo Kolk, Chief Oracle Technologist at Precise Software and renowned Oracle performance author, for his views on Oracle performance tuning. Anjo will discuss how conventional tuning approaches rely too heavily on checking the buffer cache hit ratio. Many DBAs do their best to get a 99% or better hit ratio but discover that the performance of their database isn't really improving when the hit ratio gets better. Anjo will discuss these points and others: * If the hit ratio was tuned to 99% or better, why is end user response time still bad? * If tuning the hit ratio is not the answer to end-user response time, what is? * Why does a hit ratio do exactly the opposite of what you would think? * Logical I/Os -- The myths dispelled. * How to calculate the cost of a logical I/O. * How to use the response time model in Oracle to find performance problems. If database performance is important to you, click here to pre-register for this Webcast: http://searchEBusiness.com/webcasts * -- Sponsored By: Precise Software Solutions -* Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood, MA, delivers Precise i3, a comprehensive solution for Application Performance Management that focuses directly on the end-user experience and delivers a rapid return on technology investments. Precise i3 proactively detects and corrects the root causes of performance degradation before they affect response times. Visit us at http://www.precise.com. * -- Invite a colleague -- If you think this event topic would be interesting to a colleague, invite them to this Vendor Connection Webcast. All you need to do is forward this e-mail. Unsubscribe from 'Updates on upcoming online events' - Simply Reply to this Email with REMOVE within the Body or Subject or - Go to: http://searchEBusiness.techtarget.com/register - Log in to edit your profile. - Click on the link to Edit email subscriptions. - Uncheck the box next to the newsletter you wish to unsubscribe from. - When finished, click Save Changes to My Profile. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Live Webcast: The death of the buffer cache hit ratio
Title: RE: Live Webcast: The death of the buffer cache hit ratio -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Ok, everybody, let's show up in support of Anjo. This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven't had time to read it yet today. But he's the competition! :)
Re: Live Webcast: The death of the buffer cache hit ratio
Well no Jacques, here's your chance to see what the competition is up to. :) Jared On Friday 02 August 2002 15:59, Jacques Kilchoer wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Ok, everybody, let's show up in support of Anjo. This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven't had time to read it yet today. But he's the competition! :) Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Buf Hit Ratio
I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL list 1 select A.value + B.value logical_reads, 2 C.value phys_reads, 3 D.value phy_writes, 4 (A.value+B.value)-C.value log_minus_phys, 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 Buffer Hit Ratio 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Buf Hit Ratio
Given that a 1 GHz CPU can do about 100,000 logical I/Os per second, and the largest machine is currently (I think) only 128 CPUs, I can only guess three possibilities: Your instance has been up for the last 500 years The statistics numbers don't match the ones that the Oracle code is supposed to match. You are running a 64-bit version of Oracle, and the code the exposes v$sysstat (or rather the x$ underlying it) is not quite in-line with the actual memory content. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 April 2002 16:15 |I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? | |SQL list | 1 select A.value + B.value logical_reads, | 2 C.value phys_reads, | 3 D.value phy_writes, | 4 (A.value+B.value)-C.value log_minus_phys, | 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) | 6 Buffer Hit Ratio | 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D | 8 where A.statistic# = 38 | 9 AND B.statistic# = 39 | 10 AND C.statistic# = 40 | 11* AND D.statistic# = 44 |SQL / | | logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio | --- - | 18,446,744,070,414,253,130 18,446,744,069,433,707,559 2,043,488 980,545,5710 | |Here are some other stats; | |DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES | -- -- --- | PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS | -- - |08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 | 18,446,744,047,946,114,866966,679 1,032,014,671 |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Buf Hit Ratio
Glenn - V$SYSSTAT holds cumulative values since the instance was started. You need to measure it over a period of time. Note the values, then note them again in one hour, and measure the difference. Or use Oracle's utility STATSPACK or the older utlbstat/utlestat. Even better, get Oracle Performance Tuning 101 and learn why these ratio aren't the best way to tune your database. http://www.amazon.com/exec/obidos/ASIN/0072131454/qid=1018280809/sr=8-1/ref= sr_8_7_1/002-7587220-4526465 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:39 AM To: Multiple recipients of list ORACLE-L I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL list 1 select A.value + B.value logical_reads, 2 C.value phys_reads, 3 D.value phy_writes, 4 (A.value+B.value)-C.value log_minus_phys, 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 Buffer Hit Ratio 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fav. Urban Legend - Cache Hit Ratio
A tool to generate the hit ration of your choice can be downloaded from my site... eg SQL exec choose_a_hit_ratio(98); Current ratio is: 90.5 Another 1141299 consistent gets needed...Done Current ratio is: 98.02 hth connor --- bill thater [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: If you buffer cache hit ratio is 90% you're experiencing poor performance and you're a DBA wimp. :-) hi, i'm bill and i'm a compulsive database tuner.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Computer and car salesmen differ in that the latter know when they are lying. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Thanks a lot , Srini ! I have this feeling too. Unfortunately , bouncing the instance is not an option at the moment (it's up since March , 2001). The other problem is , when i'll restart the instance , the statistics will disappear , so i never know my hit ratio ;-( -Original Message- Sent: Tuesday, August 07, 2001 9:47 PM To: Multiple recipients of list ORACLE-L Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc Deshpande, Kirti [EMAIL PROTECTED]@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject: negative value for buffer cache hit ratio Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit ratio 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hi Christopher !
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from x$kcbwds
7 where inst_id = userenv('Instance') )
8 /
MISS_RATE
-
-3.90%
Strange , isn't it ?
The actual results are :
SQL select total_waits misses
2from sys.v_$system_event
3 where event = 'db file sequential read' ;
MISSES
--
111086623
SQL col HITS for 999,999,999,999,999,999
SQL select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
Why is it negative ?
Do U think it's a buffer overflow or something ?
Thanks a lot !!!
By the way , i forgot what should i run to create those sys.x_$* synonyms
, i.e. sys.x_$kcbwds.
I had to connect as SYS and use x$kcbwds instead.
Thanks again !!!
-Original Message-
Sent: Tuesday, August 07, 2001 8:14 PM
To: Multiple recipients of list ORACLE-L
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hello Jonathan !
Thanks for reply !
First , i love your site and may i recommend it to all the listers :
http://www.jlcomp.demon.co.uk/#Index
Second, yes , U are right :
SQL col HITS for 999,999,999,999,999,999
SQL select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
It's 8.0.5. The DB has been up since March,2001
-Original Message-
Sent: Tuesday, August 07, 2001 10:16 PM
To: Multiple recipients of list ORACLE-L
It is possible that after 4 months your stats
have wrapped around the ( ? 64 bit ?) limit
value for your platform. Check the actual
values from v$sysstat to see if some of them
have gone negative or appear to be
'counting backwards'.
Jonathan Lewis
Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 07 August 2001 19:52
|
|Dear gurus !
|I have a negative value for buffer cache hit ratio in my DB which is
up for
|some 4 months .
|Any ideas why ?
|Thanks in advance.
|
|SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
| 2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
| 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer
cache hit
|ratio
| 4 FROM v$sysstat a;
|
|buffer cache hit ratio
|--
| -52.99284
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Andrey Bronfin
| INET: [EMAIL PROTECTED]
|
|Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California-- Public Internet access / Mailing
Lists
|
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez [EMAIL PROTECTED] wrote: Hi, My misses is MISS_RATE - 0.68% Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000. Is there a 8.1.7 Standard Edition Version ? Ramon Estevez [EMAIL PROTECTED] = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: negative value for buffer cache hit ratio
Interesting. In 8.1.7 32-bit oracle binary, select * from v$type_size where type = 'UB4' tells me it's 4 bytes. So it's 32 bit and the maximum is 4 billion. Glad to see Jonathan on this forum. Yong Huang [EMAIL PROTECTED] you wrote: From: K Gopalakrishnan Welcome Jonathan, I think most of the counter are limited by ub4maxval and that makes the negative hit ratio. Welcome again ! --- Jonathan Lewis wrote: It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel From: Ramon Estevez [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: negative value for buffer cache hit ratio Date: Wed, 08 Aug 2001 06:27:11 -0800 Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez [EMAIL PROTECTED] wrote: Hi, My misses is MISS_RATE - 0.68% Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000. Is there a 8.1.7 Standard Edition Version ? Ramon Estevez [EMAIL PROTECTED] = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You
RE: negative value for buffer cache hit ratio
Thanks Rachel good point. :-) Ramón Estévez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Rachel Carmichael Enviado el: Wednesday, 08 August, 2001 12:25 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel From: Ramon Estevez [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: negative value for buffer cache hit ratio Date: Wed, 08 Aug 2001 06:27:11 -0800 Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez [EMAIL PROTECTED] wrote: Hi, My misses is MISS_RATE - 0.68% Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000. Is there a 8.1.7 Standard Edition Version ? Ramon Estevez [EMAIL PROTECTED] = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: negative value for buffer cache hit ratio
Infact one of our production database (8.0.x) has a buffer cache hit ratio of 2% for the last 2 years. All others are about 90%+. Strange thing is that users are so happy with the performance of this database with 2% hitratio, they do not want us to increase the buffer cache!! Rama Rachel Carmichael wrote: Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel From: Ramon Estevez [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: negative value for buffer cache hit ratio Date: Wed, 08 Aug 2001 06:27:11 -0800 Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez [EMAIL PROTECTED] wrote: Hi, My misses is MISS_RATE - 0.68% Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000. Is there a 8.1.7 Standard Edition Version ? Ramon Estevez [EMAIL PROTECTED] = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat
negative value for buffer cache hit ratio
Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit ratio 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit
ratio
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject: negative value for buffer cache hit ratio Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit ratio 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
1.- Go to this link of Steve Adam's site: http://www.ixora.com.au/scripts/cache.htm You gonna find a script to estimate the miss rate of the buffer cache based on the assumptions that certain operations are not well compute by statistics as direct path operations. 2.- From 8.X you should rely on v$buffer_pool_statistics from the script $ORACLE_HOME/rdbms/admin/catperf.sql accounting on the several pools you could have. 3.- Remember that even on 8.X, when the database has been opened for a long time and have had a lot of transactions, the value of statistics gets so big that reaches the maximum value and then begins from 0 again. This usually happens with 7.X. Regards. --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject:negative value for buffer cache hit ratio Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit ratio 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Ran this query under 8i(HP-UX 11)
SQL show user
USER is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL show user
user is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache
hit
ratio
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc Deshpande, Kirti [EMAIL PROTECTED]@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject: negative value for buffer cache hit ratio Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit ratio 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Nothing is wrong in your database. You don't have the required X_$ views created. Change X_$ to X$ or create X_$ views as select * from X$ views. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: negative value for buffer cache hit ratio
It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 07 August 2001 19:52 | |Dear gurus ! |I have a negative value for buffer cache hit ratio in my DB which is up for |some 4 months . |Any ideas why ? |Thanks in advance. | |SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / | 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + | 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache hit |ratio | 4 FROM v$sysstat a; | |buffer cache hit ratio |-- | -52.99284 | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Andrey Bronfin | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL show user
USER is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL show user
user is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache
hit
ratio
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command
Re: negative value for buffer cache hit ratio
Welcome Jonathan, I think most of the counter are limited by ub4maxval and that makes the negative hit ratio. Welcome again ! --- Jonathan Lewis [EMAIL PROTECTED] wrote: It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL show user
USER is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL show user
user is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache
hit
ratio
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
RE: negative value for buffer cache hit ratio
Hi,
My misses is
MISS_RATE
-
0.68%
Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000.
Is there a 8.1.7 Standard Edition Version ?
Ramon Estevez
[EMAIL PROTECTED]
---
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL show user
USER is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL show user
user is SYS
SQL select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 buffer cache
hit
ratio
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San
RE: negative value for buffer cache hit ratio
Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez [EMAIL PROTECTED] wrote: Hi, My misses is MISS_RATE - 0.68% Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000. Is there a 8.1.7 Standard Edition Version ? Ramon Estevez [EMAIL PROTECTED] = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Low INDEX get hit ratio from v$librarycache
I get worrying figure for one of our production systems for INDEX hits from shared pool. I have seen others complaining about this as well. What is the importance of this value anyway? What hit ratio can be taken as acceptable? NAMESPACE % gethitratio % pinhitratio RELOADS INVALIDATIONS PINS SQL AREA95 100 677 5238402955680 TABLE/PROCEDURE 99 100 17 0 803751 BODY100 100 0 0 157694 TRIGGER 100 100 0 0 164470 INDEX 14 14 0 0 42 CLUSTER 100 99 0 0 1219 OBJECT 100 100 0 0 0 PIPE100 100 0 0 0 Thanks Kumanan Balasundaram Database Administrator, IT QXL ricardo plc www.qxl.com P: +44 (0)208 962 7409 QXL ricardo plc Registered Office Landmark House, Hammersmith Bridge Road, London W6 9DP Registered in England No 3430894 VAT number - GB 701 8915 43 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumanan Balasundaram INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Buffer Hit Ratio 10% on UNIX HP/UX 11.0
Hi list, I have a curious problem with my buffer hit ratio on Oracle 8.0.5, Unix HP UX 11.0. When I run the following select, I get a hit Rati about 8 %. select sum(decode(NAME, 'consistent gets',VALUE, 0)) ConsistentGets, sum(decode(NAME, 'db block gets',VALUE, 0))DBBlockGets, sum(decode(NAME, 'physical reads',VALUE, 0)) PhysicalReads, round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) HitRatio from sys.v_$sysstat; Output: CONSISTENTGETS DBBLOCKGETS PHYSICALREADS HITRATIO 37104235 166477 35109886 5,8 I know the application which works with this instance. I know that hit ratio have to be higher, cause application is tuned and there is not much data in the database. In other environments (Windows NT) I get buffer hit ratios about 90 - 99 %. So my question: Is there something on OS-level (HP UX 11.0) I have to configure to get higher buffer hit ratios? Here is the environment: Oracle 8.0.5 on HP UX 11.0 512 RAM INIT.ORA parameters: compatible 8.0.0 cpu_count1 db_block_size4096 db_block_buffers 5 shared_pool_size 7000 hash_area_size 131072 sort_area_size 131072 large_pool_size 0 log_buffer 163840 TIA Volker Schön E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schoen Volker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Buffer Hit Ratio 10% on UNIX HP/UX 11.0
For one thing, which you did not mention, when was the last time this instance was restarted? It is normal to see low cache hit ratios shortly after a startup. Second did someone do something, like select * from the largest table, that could have caused the cache to completely flush? Also do you have something going on that could cause a lot of full table scane, like bad statistics? There are a lot of other user level things like this that can cause problems. I have DB's that stay up for a year at a time don't have low cache hit ratios without good cause. Dick Goulet -- Reply Separator -- Author: Schoen Volker [EMAIL PROTECTED] Date: 5/28/01 4:15 AM Hi list, I have a curious problem with my buffer hit ratio on Oracle 8.0.5, Unix HP UX 11.0. When I run the following select, I get a hit Rati about 8 %. select sum(decode(NAME, 'consistent gets',VALUE, 0)) ConsistentGets, sum(decode(NAME, 'db block gets',VALUE, 0))DBBlockGets, sum(decode(NAME, 'physical reads',VALUE, 0)) PhysicalReads, round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) HitRatio from sys.v_$sysstat; Output: CONSISTENTGETS DBBLOCKGETS PHYSICALREADS HITRATIO 37104235 166477 35109886 5,8 I know the application which works with this instance. I know that hit ratio have to be higher, cause application is tuned and there is not much data in the database. In other environments (Windows NT) I get buffer hit ratios about 90 - 99 %. So my question: Is there something on OS-level (HP UX 11.0) I have to configure to get higher buffer hit ratios? Here is the environment: Oracle 8.0.5 on HP UX 11.0 512 RAM INIT.ORA parameters: compatible 8.0.0 cpu_count1 db_block_size4096 db_block_buffers 5 shared_pool_size 7000 hash_area_size 131072 sort_area_size 131072 large_pool_size 0 log_buffer 163840 TIA Volker Schön E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schoen Volker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Hit Ratio fallen through the floor at 31% since a db crash ye
How about hiring a consultant? -Original Message- Sent: Thursday, February 08, 2001 4:41 PM To: Multiple recipients of list ORACLE-L It had only been up for a few weeks. It could be the buffer cache. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, February 08, 2001 1:03 PM To: Multiple recipients of list ORACLE-L ye Just wondering...how long was the instance up before the crash? Could it be that your buffer cache just hasn't built up to where it was before the crash? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, February 08, 2001 13:02 To: Multiple recipients of list ORACLE-L We are running 8.1.5 on Solaris. We can't seem to find the cause - everything is up and running fine, just extremely slow. What should we be looking at? TIA, Barb This message has been scanned for viruses with Trend Micro's Interscan VirusWall. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
