Re: Questions about SYSTEM Statistics

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>Answers in-line
>(or see OTN for a recent article (of mine))
>http://otn.oracle.com/pub/articles/lewis_cbo.html

Which I (re-)read just before posting my question. Very good, we need more of that stuff. May I mention
http://www.nocoug.org/download/2003-08/how_cbo_works.ppt too?

>> Is there any source for adequate or typical values that I can SET
>> directly, trying to improve the way the CBO calculates costs?
>
> You could stop the database and test the hardware I/O
> response time using the program at www.iozone.com
> But you need a sensible figure for the MBRC - which
> Oracle gets by looking at v$filestat over the snapshot
> period - so you could copy the idea.

I've got an armada of such programs ready to go (including iozone), I have already done some simple measurements using bonnie+. On the Solaris side, I use the SE Tools (the famous virtual Adrian tools) to monitor hardisk activity, from which I can directly read how many blocks per I/O are being read. On the Linux side, I use plain iostat, did not find a fancier thing. In general, service times are reported in the range 5-10 ms for FibreChannel SCSI 15K RPM harddisks. But, since we can't afford using only this kind of expensive harddisks, a second enclosure in the same storage holds a bunch of S-ATA disks in a RAID 5 configuration. Now, since not all DB files can be put in the fast harddisks, I have to use these slow S-ATA disks too, which gives a total different set of values for the aux_stats$ table! Is it possible to assign different sets of values on a DB file basis? I think not, so this setup is suffering from deficiencies due to the use of very different hardware.

Of course, the most comfortable way to see this is using the Performance Tuning Pack of the OEM. The problem is, that Oracle can't really tell where data is coming from (even when the file systems are mounted directio, i.e. unbuffered in one of the two mentioned machines). The central storage (SAN) has a big cache. In order to discriminate what is being read from Oracle buffers, from the storage cache or ultimately from the harddisks themselves, one has to monitor activity inside Oracle, at the OS level and on the storage subsystem itself (which cost license fees of EMC).

> I would use the CPU figure - as I suspect it's based on an
> internal calibration operation, so if it's wrong it's going to
> be wrong self-consistently.

Some known relationship between real CPU cycle frequency and the one measured by Oracle?

And what if I got 4 CPUs (more complicated: 8 due to hyperthreading)? Should I then multiple the value times the number of CPUs?

> If you gather stats at an unlucky point in time, when
> a rogue processing is causing bizarre tablescan response
> then the MBRC and mreadtim values can be silly, and
> it makes sense to adjust them

I tend to believe that the gathering period should be long enough to be representative of most situations.

>> Why is it necessary to gather system stats according to different
>> activity levels of the server if no matter how much activity is taking
>> place any single block access will take the same time? I mean that the
>> hardware won't get slower, any multiblock read operation will last
>> exactly the same time at any point in time... or not?
>>
>
> Depends how variable your workload is - for example:
> A real, physical, tablescan may be the quickest way to get
> the answer for (say) a 1 in 400 row query when no-one else
> is using the system. If 25 other people are using the system
> and doing the SAME thing, then they may all get the best
> response time if they all did an indexed access path.
> (Oracle is moving from Adam Smith to John Nash)
> In general, though, I suspect that a neutral calibration of
> i/o times, combined with a reasonable MBRC will be the
> best for most systems.

Sounds reasonable. I am biased because in our environment we have only a few users, and they put a heavy load on the DB due to DW type of operations.

Thanks a lot, Jonathan.

Bye
Rick Denoire Received on Sun Jun 20 2004 - 14:52:29 CDT