RE: Oracle on Linux articles

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: James Morle
  INET: James.Morle_at_scaleabilities.co.uk

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).