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') and in
the
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:
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: 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, not
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: 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: 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).
