Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)

> * 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

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/