RE: Re: Physical I/O and databases other than oracle
thanks guys. Im only familiar with the oracle database, so I dont know if I can believe what I read when dealing with other databases. so I wanted to check. while we are on the topic of other databases. What kind of wait interface's do other databases provide? How robust are they? > > From: DENNIS WILLIAMS <[EMAIL PROTECTED]> > Date: 2003/10/06 Mon AM 09:34:25 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Re: Physical I/O and databases other than oracle > > Cary - Thanks so much for providing the historical perspective on this > issue. Perhaps you could confirm or refute a theory of mine. My theory is > that in the early days of databases, most of the guidelines for database > tuning came from benchmarks. This was an activity that received a lot of > top-notch resources, and produced objective, numerical results. But in some > cases what worked well for a benchmark with just a few programs might be > misleading for an operating production system. Can you confirm or refute > this idea? >And thanks for setting your methods down in your book. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Sunday, October 05, 2003 3:14 PM > To: Multiple recipients of list ORACLE-L > > > I would expect that any vendor with a product whose bottleneck is the > same for all implementations would have been long dead by now. The > answer is probably that any product's "the bottleneck" will vary hugely > from one configuration and implementation to the next. > > It has always been this way with Oracle as well (where "always" is > defined as "at least since I joined Oracle in 1989"). The idea that "the > bottleneck" in Oracle was ever "always physical I/O" is *very* false. > It's just that many of the popular measurement tools we used back in the > 1980s and 90s were capable only of revealing I/O bottlenecks. But a very > common Oracle bottleneck in 1990 was CPU consumed by excessive LIO > processing and excessive parsing. This is not a new truth, just a new > awareness. > > By the way, the BCHR was never any more useful than it is today. It was, > of course, a much larger component of the "tuner's portfolio" than > today. Actually, don't misunderstand: the BCHR *is* useful, and it > always has been. When it's really close to 100%, it's an almost total > guarantee that you have some really serious performance problems. This > statement has been true since at least Oracle V5... > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > [EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 12:15 PM > To: Multiple recipients of list ORACLE-L > > my email states that in oracle this isnt true. HOWEVER, what about other > databases? > > > > From: Mladen Gogala <[EMAIL PROTECTED]> > > Date: 2003/10/02 Thu PM 12:34:33 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: Physical I/O and databases other than oracle > > > > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > > Im reading an academic book on databases and it states that > Physical I/O > > > > > Eh? > > > What IS the primary bottleneck in tuning Oracle? > > > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your > job > > is done. Database with 99.9% BCHR must be OK. > > > > > > > > > > Note: > > This message is for the named person's use only. It may contain > confidential, proprietary or legally privileged information. No > confidentiality or privilege is waived or lost by any mistransmission. > If you receive this message in error, please immediately delete it and > all copies of it from your system, destroy any hard copies of it and > notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the > intended recipient. Wang Trading LLC and any of its subsidiaries each > reserve the right to monitor all e-mail communications through its > networks. > > Any views expressed in this message are those of the individual > sender, except where the message states otherwise and the sender is > authorized to state them to be the views of any such entity. > > > > -- > > Please
RE: Re: Physical I/O and databases other than oracle
Cary - Thanks so much for providing the historical perspective on this issue. Perhaps you could confirm or refute a theory of mine. My theory is that in the early days of databases, most of the guidelines for database tuning came from benchmarks. This was an activity that received a lot of top-notch resources, and produced objective, numerical results. But in some cases what worked well for a benchmark with just a few programs might be misleading for an operating production system. Can you confirm or refute this idea? And thanks for setting your methods down in your book. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, October 05, 2003 3:14 PM To: Multiple recipients of list ORACLE-L I would expect that any vendor with a product whose bottleneck is the same for all implementations would have been long dead by now. The answer is probably that any product's "the bottleneck" will vary hugely from one configuration and implementation to the next. It has always been this way with Oracle as well (where "always" is defined as "at least since I joined Oracle in 1989"). The idea that "the bottleneck" in Oracle was ever "always physical I/O" is *very* false. It's just that many of the popular measurement tools we used back in the 1980s and 90s were capable only of revealing I/O bottlenecks. But a very common Oracle bottleneck in 1990 was CPU consumed by excessive LIO processing and excessive parsing. This is not a new truth, just a new awareness. By the way, the BCHR was never any more useful than it is today. It was, of course, a much larger component of the "tuner's portfolio" than today. Actually, don't misunderstand: the BCHR *is* useful, and it always has been. When it's really close to 100%, it's an almost total guarantee that you have some really serious performance problems. This statement has been true since at least Oracle V5... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 12:15 PM To: Multiple recipients of list ORACLE-L my email states that in oracle this isnt true. HOWEVER, what about other databases? > > From: Mladen Gogala <[EMAIL PROTECTED]> > Date: 2003/10/02 Thu PM 12:34:33 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Physical I/O and databases other than oracle > > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > Im reading an academic book on databases and it states that Physical I/O > > > Eh? > > What IS the primary bottleneck in tuning Oracle? > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job > is done. Database with 99.9% BCHR must be OK. > > > > > Note: > This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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 -
RE: Re: Physical I/O and databases other than oracle
I would expect that any vendor with a product whose bottleneck is the same for all implementations would have been long dead by now. The answer is probably that any product's "the bottleneck" will vary hugely from one configuration and implementation to the next. It has always been this way with Oracle as well (where "always" is defined as "at least since I joined Oracle in 1989"). The idea that "the bottleneck" in Oracle was ever "always physical I/O" is *very* false. It's just that many of the popular measurement tools we used back in the 1980s and 90s were capable only of revealing I/O bottlenecks. But a very common Oracle bottleneck in 1990 was CPU consumed by excessive LIO processing and excessive parsing. This is not a new truth, just a new awareness. By the way, the BCHR was never any more useful than it is today. It was, of course, a much larger component of the "tuner's portfolio" than today. Actually, don't misunderstand: the BCHR *is* useful, and it always has been. When it's really close to 100%, it's an almost total guarantee that you have some really serious performance problems. This statement has been true since at least Oracle V5... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 12:15 PM To: Multiple recipients of list ORACLE-L my email states that in oracle this isnt true. HOWEVER, what about other databases? > > From: Mladen Gogala <[EMAIL PROTECTED]> > Date: 2003/10/02 Thu PM 12:34:33 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Physical I/O and databases other than oracle > > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > Im reading an academic book on databases and it states that Physical I/O > > > Eh? > > What IS the primary bottleneck in tuning Oracle? > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job > is done. Database with 99.9% BCHR must be OK. > > > > > Note: > This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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). -- 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: Physical I/O and databases other than oracle
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, October 03, 2003 3:15 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Physical I/O and databases other than oracle > > > my email states that in oracle this isnt true. HOWEVER, what > about other databases? At the risk of trivialising things, there are basically no differences with other databases. It's a question of how quickly the DBA community for a given database has woken up to the fact that BCHR is a rarely-useful measure. For instance, the DB2 people worked this out a long time ago (though they still focus heavily on buffer pool tuning, as many of them genuinely have physical i/o issues with the huge batch jobs they run). As for the SQL Server people, the light is only just starting to dawn. 90% or more of the performance posts to the sql2k list (see www.sswug.com) start with "My windoze box has 2GB of RAM, and my instance has 99.999% hit ratio, but performance is still a dog. Help me add more memory". Gentle attempts at suggesting they attempt to find the real culprit, rather than guess, are howled down in the stamped of the hit ratio mob. Ciao for now Fuzzy (It's good to be back) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Physical I/O and databases other than oracle
i guess my question wasnt clear. What Im getting at is do other databases have wait interfaces? Is there architecture such that Physical I/Os are a serious concern. Far more than other bottlenecks. Or is this book just garbage... > > From: Daniel Fink <[EMAIL PROTECTED]> > Date: 2003/10/02 Thu PM 01:09:36 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Physical I/O and databases other than oracle > > This is not an issue of answering the question, but pointing out that the question > is not correct. > > Why do databases exist (aside to make Larry money)? To 'permanently' store data. As > this storage must survive a system failure, we choose to place the data on a > non-volatile medium (disk, paper, stone tablets). In order to save and retrieve this > data, we need to perform some sort of physical action (write/read). Let's take the > telephone system as an example. If we need to look up a phone number, we open the > phone book and do a search for the right entry. This can be rather time consuming, > especially if we don't know the person's last name or business name. Let's assume we > do know the last name, we can get to the proper entry > fairly quickly, but it still takes some time. Once we have retrieved the information > into our memory, we can dial the phone fairly quickly. In this case, the bottleneck > is indeed physical i/o. Of course, if we get a busy signal, we can access our memory > to redial, completely bypassing the phone book. However, if we are using an old > rotary dial phone and we have to make a long distance call, it may take longer to > dial the phone than look up the number. > > The real question should be "What is the primary bottleneck in presenting data to > the user?" In my recent projects, I have found that the database is rarely the > bottleneck. I have seen bad code, cpu-starvation, spooling to RAID-5 devices, > cartesian products. > > Did I mention that I once got an A on an essay test because I argued that the > question asked was itself invalid? > > Dan > > Mladen Gogala wrote: > > > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > > Im reading an academic book on databases and it states that Physical I/O > > > > > Eh? > > > What IS the primary bottleneck in tuning Oracle? > > > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job > > is done. Database with 99.9% BCHR must be OK. > > > > Note: > > This message is for the named person's use only. It may contain confidential, > > proprietary or legally privileged information. No confidentiality or privilege is > > waived or lost by any mistransmission. If you receive this message in error, > > please immediately delete it and all copies of it from your system, destroy any > > hard copies of it and notify the sender. You must not, directly or indirectly, > > use, disclose, distribute, print, or copy any part of this message if you are not > > the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve > > the right to monitor all e-mail communications through its networks. > > Any views expressed in this message are those of the individual sender, except > > where the message states otherwise and the sender is authorized to state them to > > be the views of any such entity. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Mladen Gogala > > 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).
RE: Physical I/O and databases other than oracle
Title: RE: Physical I/O and databases other than oracle So to look good, I should unplug all the CPU boards except one or two to end up with CPU limitation :) Regards, Waleed -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Physical I/O and databases other than oracle According to one recently published source*, in a well-tuned database system, the server should be CPU-limited. The reasoning here is that in a perfectly tuned system, the other bottlenecks of I/O, network, etc. have been eliminated, so the system is then limited by the speed and number of CPUs. This is an ideal system, of course, and we all know that it is common to have less than ideal numbers of disks or I/O controllers to spread the load. * "The Art and Science of Oracle Performance Tuning", Christopher Lawson, 2003, p.184. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com "the most trusted source for STORAGE MANAGEMENT SERVICES" The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you.
Re: Physical I/O and databases other than oracle
I do accept your suggestion but I've just received Cary's book
and I'm enjoying myself very much. I do humbly apologize for any
confusion. To make is perfectly clear to anyone, BCHR is no longer
very relevant indicator. My last sentence ("database with BCHR 99.9%
must be OK") was formulated in that way as an allusion to Cary Milsap's
known article "Why 99% Database Buffer Cache Hit Ratio is NOT Ok".
I was just poking little harmless fun at the "silver bullet" approach.
On Thu, 2003-10-02 at 12:49, Thomas Day wrote:
> How about a tongue-in-cheek smiley (>) to indicate that the following
> advice is offered ironically and is not to be followed. Sort of like the
> hidden parameter _SET_RUN_FASTER=TRUE. We are always getting new members
> and they may not be aware of previous threads.
>
>
>
>
>
> Mladen Gogala
>
>ORACLE-L <[EMAIL PROTECTED]>
> @wangtrading.com cc:
>
> >Subject: Re: Physical I/O and
> databases other than oracle
> Sent by:
>
> ml-errors
>
>
>
>
>
> 10/02/2003 12:34
>
> PM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> On Thu, 2003-10-02 at 11:44, Garry Gillies wrote:
> > > Im reading an academic book on databases and it states that Physical
> I/O
>
> > Eh?
> > What IS the primary bottleneck in tuning Oracle?
>
> Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job
> is done. Database with 99.9% BCHR must be OK.
>
>
>
>
> Note:
> This message is for the named person's use only. It may contain
> confidential, proprietary or legally privileged information. No
> confidentiality or privilege is waived or lost by any mistransmission. If
> you receive this message in error, please immediately delete it and all
> copies of it from your system, destroy any hard copies of it and notify the
> sender. You must not, directly or indirectly, use, disclose, distribute,
> print, or copy any part of this message if you are not the intended
> recipient. Wang Trading LLC and any of its subsidiaries each reserve the
> right to monitor all e-mail communications through its networks.
> Any views expressed in this message are those of the individual sender,
> except where the message states otherwise and the sender is authorized to
> state them to be the views of any such entity.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> 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 m
Re: Re: Physical I/O and databases other than oracle
my email states that in oracle this isnt true. HOWEVER, what about other databases? > > From: Mladen Gogala <[EMAIL PROTECTED]> > Date: 2003/10/02 Thu PM 12:34:33 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Physical I/O and databases other than oracle > > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > Im reading an academic book on databases and it states that Physical I/O > > > Eh? > > What IS the primary bottleneck in tuning Oracle? > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job > is done. Database with 99.9% BCHR must be OK. > > > > > Note: > This message is for the named person's use only. It may contain confidential, > proprietary or legally privileged information. No confidentiality or privilege is > waived or lost by any mistransmission. If you receive this message in error, please > immediately delete it and all copies of it from your system, destroy any hard copies > of it and notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to > monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where > the message states otherwise and the sender is authorized to state them to be the > views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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).
Re: Physical I/O and databases other than oracle
This is not an issue of answering the question, but pointing out that the question is not correct. Why do databases exist (aside to make Larry money)? To 'permanently' store data. As this storage must survive a system failure, we choose to place the data on a non-volatile medium (disk, paper, stone tablets). In order to save and retrieve this data, we need to perform some sort of physical action (write/read). Let's take the telephone system as an example. If we need to look up a phone number, we open the phone book and do a search for the right entry. This can be rather time consuming, especially if we don't know the person's last name or business name. Let's assume we do know the last name, we can get to the proper entry fairly quickly, but it still takes some time. Once we have retrieved the information into our memory, we can dial the phone fairly quickly. In this case, the bottleneck is indeed physical i/o. Of course, if we get a busy signal, we can access our memory to redial, completely bypassing the phone book. However, if we are using an old rotary dial phone and we have to make a long distance call, it may take longer to dial the phone than look up the number. The real question should be "What is the primary bottleneck in presenting data to the user?" In my recent projects, I have found that the database is rarely the bottleneck. I have seen bad code, cpu-starvation, spooling to RAID-5 devices, cartesian products. Did I mention that I once got an A on an essay test because I argued that the question asked was itself invalid? Dan Mladen Gogala wrote: > On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > > Im reading an academic book on databases and it states that Physical I/O > > > Eh? > > What IS the primary bottleneck in tuning Oracle? > > Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job > is done. Database with 99.9% BCHR must be OK. > > Note: > This message is for the named person's use only. It may contain confidential, > proprietary or legally privileged information. No confidentiality or privilege is > waived or lost by any mistransmission. If you receive this message in error, please > immediately delete it and all copies of it from your system, destroy any hard copies > of it and notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to > monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where > the message states otherwise and the sender is authorized to state them to be the > views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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 n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Physical I/O and databases other than oracle
How about a tongue-in-cheek smiley (>) to indicate that the following advice is offered ironically and is not to be followed. Sort of like the hidden parameter _SET_RUN_FASTER=TRUE. We are always getting new members and they may not be aware of previous threads. Mladen Gogala @wangtrading.com cc: >Subject: Re: Physical I/O and databases other than oracle Sent by: ml-errors 10/02/2003 12:34 PM Please respond to ORACLE-L On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > Im reading an academic book on databases and it states that Physical I/O > Eh? > What IS the primary bottleneck in tuning Oracle? Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job is done. Database with 99.9% BCHR must be OK. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Thomas Day 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: Physical I/O and databases other than oracle
On Thu, 2003-10-02 at 11:44, Garry Gillies wrote: > > Im reading an academic book on databases and it states that Physical I/O > Eh? > What IS the primary bottleneck in tuning Oracle? Cache hit ratio. You tune the buffer cache hit ratio (BCHR) and your job is done. Database with 99.9% BCHR must be OK. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Physical I/O and databases other than oracle
Title: RE: Physical I/O and databases other than oracle According to one recently published source*, in a well-tuned database system, the server should be CPU-limited. The reasoning here is that in a perfectly tuned system, the other bottlenecks of I/O, network, etc. have been eliminated, so the system is then limited by the speed and number of CPUs. This is an ideal system, of course, and we all know that it is common to have less than ideal numbers of disks or I/O controllers to spread the load. * "The Art and Science of Oracle Performance Tuning", Christopher Lawson, 2003, p.184. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com "the most trusted source for STORAGE MANAGEMENT SERVICES" The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you.
Re: Physical I/O and databases other than oracle
> Im reading an academic book on databases and it states that Physical I/O is often the > primary bottleneck in tuning. Its not the case in Oracle. Is this statement correct > with sybase, sql server, or DB2? or maybe mysql? Eh? What IS the primary bottleneck in tuning Oracle? I go along with Jonathan Lewis in his book Practical Oracle 8i "Although it is possible to create a few problems with network traffic, excess CPU usage and process contention, ultimately the only significant threat to a database system is physical I/O". (page 38) CONFIDENTIAL: The information contained in this email (including any attachments) is confidential, subject to copyright and for the use of the intended recipient only. If you are not the intended recipient please delete this message after notifying the sender. Unauthorised retention, alteration or distribution of this email is forbidden and may be actionable. Attachments are opened at your own risk and you are advised to scan incoming email for viruses before opening any attached files. We give no guarantee that any communication is virus-free and accept no responsibility for virus contamination or other system loss or damage of any kind. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Garry Gillies 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).
