Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

Re: OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

From: Noons <nsouto_at_optusnet.com.au.nospam>
Date: 21 Jan 2003 13:28:07 GMT
Message-ID: <Xns930B2735A64Bmineminemine@210.49.20.254>


don_at_burleson.cc (Don Burleson) wrote in news:998d28f7.0301201611.792c4759_at_posting.google.com and I quote:

> OK, but why then do almost all Oracle systems show multi-block
> (scattered) reads having average waits that are 10x longer than index
> (sequential) reads?
>
> Anyone have an insight?

Not really. But one thing is for sure: if the file systems in use are not raw, I'd be very reluctant to accept Oracle statistics/wait times on file access as the ultimate in precision.

There are so many variables that may be affecting times and what is returned to Oracle when using a file system, I don't even dare look at the numbers.

Just as an example: NTFS will auto-detect the db_file_multi_block_read stuff on a scatter read and do its own "read-ahead optimisation". So do most Unix advanced file systems. Which may invalidate any numbers you get for that stat, and IMHO also negates the need to use this parameter at all at db level! Other than to affect CBO weightings.

The thing we can't forget here is that file-related performance features and parameters in Oracle are to be used *only* when raw files are used. Because *then* you have full control from Oracle of what actually ends up being sent to the disk controllers.

If you're using a file system, then turn off all that stuff at Oracle level and *use* the file system optimisations *and* its tuning parameters to improve performance. And use the file system stats to monitor the I/O, in preference to the Oracle stats. Following this approach you're much nearer to where things actually make a difference.

Of course, like anything there are always exceptions. There are very tight integrations for example between Veritas file systems and Oracle. Same for EMC. Those are special cases!

> Any link to this hotsos discussion?

Don't recall it. We had a few here on this subject.

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam
Received on Tue Jan 21 2003 - 07:28:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US