Re: Surprising Performance Changes with Oracle (Long Post)

From: Robert Klemme <>
Date: Sun, 06 Sep 2009 17:14:20 +0200
Message-ID: <>

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.

> 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


remember.guy do |as, often| as.you_can - without end
Received on Sun Sep 06 2009 - 10:14:20 CDT

Original text of this message