Re: Surprising Performance Changes with Oracle (Long Post)

From: Charles Hooper <>
Date: Sun, 6 Sep 2009 08:55:46 -0700 (PDT)
Message-ID: <>

On Sep 6, 11:14 am, Robert Klemme <> wrote:
> On 05.09.2009 22:26, Charles Hooper wrote:
> > * Direct I/O and Asynch I/O, which seem to be frequently recommended
> > to improve performance, do not always improve performance, and may in
> > fact drastically affect performance.
> Wouldn't you have to increase SGA target when switching to direct IO for
> a fair comparison?  Jonathan pointed out that without direct IO you are
> actually increasing disk buffer size (Oracle's SGA + OS level cache).
> So, assuming a dedicated machine (so not much OS buffer needed for other
> tasks), the direct IO test would have to run with the SGA increased so
> you get a similar amount of memory used for caching blocks.

That is a good question. The machine in this setup had 12GB of memory. The SGA_TARGET was set to 8GB, the PGA_AGGREGATE_TARGET was set to 1.8GB, and the KEEP buffer pool was set to 6GB. Since the actual table which the test table mimics will likely be infrequently queried in full, such as a report which attempts to indicate the change in the size measured for the left side of the cylinder wall over the production lifetime of the part, it probably would not be a good idea to optimize the instance and operating system performance for this one query. Other data from other tables would likely be occupying the KEEP buffer pool, which means that in production the available RAM for caching of data blocks at the operating system level might be quite limited. It might be interesting to test what happens when the KEEP buffer pool is fully utilized - will Linux start swapping other memory out to disk to buffer the Oracle blocks in the file system cache? What happens when a 512MB (or larger) redo log needs to be archived, will it hinder the effects of the operating system level caching of Oracle blocks? It might come down to how closely the test environment is able to mimic the production environment.

> > So, should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower
> > number to (quoting from a posting on the Internet) “immediately tune
> > all of the SQL in your database to favor index scans over full-table
> > scans”?  :-)
> I am by far not as expert as Jonathan but I have a bad gut feeling about
> such a "global optimization".  It is not very targeted and I have seen
> too many cases where an optimization which looked good on first sight
> hat bad effects overall...
> Charles, thank you for sharing this!
> Kind regards
>         robert

This test case is a light-weight example from the book "Expert Oracle Practices: Oracle Database Administration from the Oak Table". The test case took a different twist while testing on

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Sep 06 2009 - 10:55:46 CDT

Original text of this message