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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 13 Apr 2003 10:45:55 +0100
Message-ID: <b7bbjl$ndi$1$8302bc10@news.demon.co.uk>

Rick,

Thinking only about this specific index build, and not the generic state of the machine. I have collated the following from three different posts:

<< Quote >>
The table itself is 3,5 GB in size with 97 Mio rows. The index was 2.8 GB. I used the "time" command:
real: 4h54m
user: 8h13m
sys: 35m

sort_area_size: 4 MB
Buffer cache: 900 MB

CPUs (4x)

alter index <index_name> rebuild
tablespace X storage (initial 32Mnext 32M ) nologging;

battery powered write cache (almost 400 MB, and about 100 MB read cache),

The service time showed by iostat is a few milliseconds

<< end quote>>

question 1:

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

    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)

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.

    With a 3.5 GB table to read, 100MB is wiped out.     With a 2.8GB index write, the same is true of     the 400MB write cache - after the 1st 400MB of     Oracle writes, you are down to uncached disk speed.

    Building a 2.8 GB index with a 4MB S_A_S is a     multipass job.

    For a serial build, the resource usage would     probably be something like the following if     Oracle decided to scan the table rather than     re-using the existing index:

    Read 3.5 GB of table in multi block reads     Sort and write ca. 700 x 4MB strips.     Read back and merge 512 strips using single block reads.

            (Oracle tends to maximise number of strips
            merged by minimising the size of read from
            each strip.  I have assumed one block per read,
            Oracle might choose 2 blocks for 256 reads
            (512 x 8K = 256 * 16K = 4MB S_A_S)

    Write out one 2GB strip
    Read back and merge all remaining strips in single block reads.     Write the whole 2.8GB to temp (this is an Oracle bug that     may still be around for index builds)     Read 2.8GB index from TEMP
    Write entire 2.8GB index to proper location.

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.

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.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html



____UK_______April 22nd

____Denmark__May 21-23rd

____USA_(FL)_May 2nd


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html


____UK_(Manchester)_May

____Estonia___June (provisional)

____Australia_June (provisional)

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Sun Apr 13 2003 - 04:45:55 CDT