Hi Steve. I also see your point, of course, and it's often a fine line to tread. However, if making something well tuned were a simple case of plugging in numbers, it would already be configured that way. Of course, some defaults are plain silly! The reality is, and the point behind my objections, that calling this 'low hanging fruit' deceives the reader into not using his/her brain. For example, it's not high-ROI to tune a system (as I believe this system was tuned) to run for a *short period* like greased lightning. Here's my nutshell description of 'Tuning': Tuning is about avoiding unnecessary work. Think about it for a second. If work has to be done, you can't rob Peter to pay Paul... The work has to be done in the end. It's just a case of minimizing the cost of getting the necessary work done. But your point stands. Chattr +A, for example is a no-brainer (but if anyone has other information, I'd love to hear it). I think there are two actions that are needed to achieve what you're looking for: always-wins, in a list (such as chattr), and a methodology (sorry, I hate that word too) for the other stuff. For sure, there is a no-brainer decision process for this that's just as simple as the always-wins. The danger is unsubstatiated advice. Like not using raw devices - I hope nobody follows that advice without looking very hard at the whys and whats! Glad you liked the book. It would be nice to make a 9i version, but the publisher is 'disappointed with the sales' and isn't interested. Regards
James > > Thanks James. > > OK, now I see where you're coming from... like the esteemed > published author that you are, you're wanting a complete > treatise on Oracle database tuning. > :-) But my sense was that the article was merely addressing > quick and dirty tuning options or "low hanging fruit" as the > author put it. Given that the audience was a Linux forum and > not an Oracle DBA forum I believe these tuning efforts were > intended to be for a "generic" Oracle/Linux implementation. > Here's a quote from the author, "Remember, I said that we'd > start by looking at some very high ROI approaches. That means > we're looking for items so easy and apparent in terms of > applicability and impact that we only need observe the > runtime differences in the TPC to see if we're on the right > track." The author stated that the default Oracle parameters > are useless and started with them as a baseline and gave us > benchmarks to prove his point. That's all... nothing more > nothing less. > > Do we need to look at wait events BEFORE changing some > default init.ora settings, increasing db_block_size, or using > locally managed tablespaces? Is "chattr +A *.dbf" something > we should do on all Linux/Oracle databases using ext3 > filesystems? Isn't there a general concensus that Linux > kernel 2.4.x performs better? I appreciated seeing benchmarks > on the kernel versions. I guess my point is that the default > Oracle/Linux configurations are insufficient and there's > probably a GOOD list of recommended default settings that are > appropriate as a good starting point for >90% of all typical > Linux/Oracle implementations. Has anyone published such a list? > > After starting with a good default installation then it would > be nice if we had a public domain benchmarking database with > data and TPC benchmarking code. Then someone could walk > through a complete tuning effort with all the > "instrumentation" as a teaching exercise on how to identify > and remove bottlenecks. With something like this we could > actually run the tests ourselves without having to take > someone's word for it. Not only that, since nothing remains > the same, it could be a good mechanism for quickly testing > performance impacts with Oracle and Linux software upgrades > and revisions. Sounds like the makings of a new book. ;-) > > > Steve Orr > Bozeman, MT > > BTW, I REALLY like your book. > > > -----Original Message----- > Sent: Tuesday, March 12, 2002 11:00 AM > To: Multiple recipients of list ORACLE-L > > Hi Steve, > > > > > The author is also misinformed about the workings of the > > log buffer, > > > and > > > the interaction of Oracle blocksize with the filesystem > block size. > > > > I didn't see any discussion of this in the article. ? > > This is two separate issues: a) log buffer - this was > discussed insofar as 'open it up really big', and b) block > sizes - this was implied from his not understanding the > reasons for the larger block sizes not making a bigger difference. > > > > > Could you briefly explain your understanding of the workings > > of the log buffer and the interaction of Oracle blocksize and > > filesystem block size? > > > > Yes, no problem: > Log buffer: The log buffer flushes under certain conditions; > an explicit > commit, buffer 1/3 full, or every 3 seconds if one of the former > conditions has not occurred. In the case of the online > transaction, the > former case will have always been true for a TPC-C > transaction. For the > load, either the former or secondary condition will have always been > true, depending upon the loading code. At no point in the article did > the author suggest a rationale for any of the tuning actions, and this > was a classic example. A useful datapoint at this stage would > have been > how long the sessions were waiting for space in the buffer compared to > log file switching time, compared to physical I/O time. I > would suggest > that a far more significant gain in performance would have been gained > from much bigger log files, and a relatively small log buffer. > > Oracle blocksize vs FS blocksize: The author noted a large improvement > going from 2KB to 4KB blocks. He fails to mention that the > block size of > the filesystem (assuming default ext2) is 4KB. This is far more > significant, both from the standpoint of 2KB->4KB and 4KB->8KB Oracle > blocksizes. In the 2->4 case, the system was issuing twice as many > system calls under 2KB blocks for the same number of physical reads as > the $KB case. In the 4->8 case, the system was having to > break open each > I/O call into two physical reads/writes, because the filesystem block > size is the only unit of I/O. So, if the filesystem had a blocksize of > 8KB, the gain at 8KB would have been more significant. All this > discussion completely ignores the other efficiency gains with larger > blocks, especially inside Oracle. Without seeing any Oracle > wait events > (again), it's all pure speculation as to where the real bottlenecks > moved to! > > Really, the thing that went against the grain was the 'tuning in a > vacuum' approach of the author. It's so unnecessary to do > when there is > so much instrumentation available. > Regards > > James > > -- > James Morle > Scale Abilities, Ltd > http://www.scaleabilities.co.uk > Author of "Scaling Oracle8i - Building Highly Scalable OLTP System > Architectures" > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Orr, Steve > 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: James Morle 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).
