Re: Slow performance on index creation

"Brian M. Biggs" <bbiggs_at_cincom.com> wrote:
>We are trying to create some very large indexes on our HP machine, and
>performance is EXTREMELY slow. The machine is a relatively small
>machine, but the numbers we are getting back from Oracle seem very
>poor even for this machine.
>
>We increased the db_block_buffers to 5000, multi_block_read_count to 32
>and sort_area_size to about 60MB. One of the indexes being created is
>about 34MB, 1 million rows, and only on one 20-byte CHAR column. The
>process creating the index is the only account on the system. There is
>only one index tablespace, and it is entirely on one physical disk. The
>data tablespace is on a separate physical disk, and the redo logs,
>rollback, system, etc. are on a third physical disk.
>
>The index sort seems to all fit in memory (TEMP tablespace size is not
>decreased), but the writes to disk are taking forever, and seem very
>slow. In SQL*DBA, the Request Rate on the File I/O Monitor screen is
>only anywhere from 7-40 writes/s, sometimes peaking at 50, for the index
>tablespace. What unit of measurement is that statistic in? Does this
>seem like a low value when the monitor is updating at its default rate
>(5 seconds)?
>
>Our redo logs are only 512K. I know this is small, but I didn't think
>it would have this much of an effect on performance. Could this be some
>of the reason for our lousy numbers? Is Oracle spending a lot of time
>doing log switches and such?
>

• snip -

Thomas Kyte hit the nail on the head with the redo logs, but also...

We are running db_file_multiblock_read_count of 512. We are running RAID on a big machine, but you may want to experiment with sizes > 32.

We did several tests creating indexes and found performance degredation when using a sort_area_size bigger than 10M. We also saw no improvement increasing it past 1M. Our machine has 2G of RAM and we had < 4 connections at a time on the machine.

We saw a dramatic improvement by watching the alert.log and increasing redo log size until we saw no more 'Checkpoint not complete...'.

We have 10 online redo logs, each one is 50M.

We can create a single column index on a million row table in around 3-4 minutes. Keep in mind, we have a DEC Alpha 7740 with 100G of RAID-5 storage.

Will. Received on Mon Mar 25 1996 - 00:00:00 CET