RE: Oracle on Linux articles

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: sorr_at_rightnow.com

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: ListGuru_at_fatcity.com (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).