Re: CPU waiting for... what? (mistery)

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

>
>question 1:
> What is your database block size - for the moment
> I'll assume 8K

Yes, 8K. As a side note: We are in the process to get a new DB server (Intel based, 4x Xeon MP), and when the DB will be setup in this new server, I will change bocksize to 16K - this is a very seldom opportunity to change block size! Most of the jobs that run in this DB take hours to finish (Data Mining applications). Preparing for this transition I am very concerned about matching sizes of all types of blocks involved - there are more than one would think.

> Is this a parallel index build - if so, what degree of
> parallelism ? (I think I have to assume it is, because
> I can't see how you appear to get 8 hours 48 minutes
> of CPU time in 4 hours 54 minutes of real time. Or
> have I misinterpreted the meaning of the 'time' figures)

The degree of most tables is set to default, and a max. of 2x CPUs parallel processes will be raised. When this rebuild was running, at times, but only at times, I could observe things running in parallel.

>Point 1:
> Note that your device cache is about half the
> size of the Oracle buffer cache. If the system
> is working hard, it won't help much.

When the cache is switched off, the service time (srv_t column of the iostat command) gets about tenfold. Don't know how this actually translates to total execution time.

Thank you for your explanation about the steps Oracle uses in this operation, although I can't follow you in all details; don't know anything about strips.

>Two critical issues to pick up on -
> There is a lot more I/O than you might expect
> A VERY large fraction of it is single block I/O.

You convinced me, but I still think that something is badly mistuned in this machine. Since Oracle is the main application running here, putting an eye on Oracle operations seems reasonable.

>It doesn't necessarily matter how fast your devices are
>if the O/S if flinging lots of very small write requests at
>it, and then waiting for a response.

Exactly, especially if seems to wait too long for the response.

One particular hint I got concerning the suspected I/O problem is the additional "delay" column that appears in the iostat-like output of Adrain Cockcroft's SE Tool (using zoom.se, for example). There is no such column when using the corresponding iostat operation system command and I could not find any interpretation for this value elsewhere, but this is the only parameter showing values completely out of bounds. Quite often I see values of aprox. 5000 ms (=5 sec) for "delay", and I have seen values of about 25000 ms !! (The SE Tool monitoring the system reacts with an alarm).

My next steps will be to really understand all types of I/O Oracle does. There are a lot terms that are confusing me most of the time: consistent read, logical read, physical read, scattered read, sequential read, block read.. etc. Just assuming that one should trivially understand these concepts based on the names used (like for example: "A block read is an operation where a block is requested to be read") is quite naive. I need to know when and why I/O operations occur in which context and having which consequences. For example, when will Oracle ignore or obey the setting of db_file_multiblock_read_count? And in which situations will which operations benefit from blocks lying physically consecutively? Does a scan go along a logical or a physical path? When should I use the cache option for an object? When should I force Oracle to bypass or use the buffer cache? Which kind of I/O are typically used with specific kind of joins? How do I interpret I/O from steps in the execution plan? Should I reorganize data of a table in a way that the records are lying exactly in the physical order that a specific query would demand them? Should I artificially group records into blocks according to a specific pattern?

So you see I have alot to learn. You gave me one more kick.

Bye
Rick Denoire Received on Sun Apr 13 2003 - 10:08:50 CDT