Re: Slow performance on index creation

Try increasing SORT_AREA_SIZE

"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?
>
>We also have NOT done anything with parallel index creation, since this
>machine has only one processor, or multiple database writers. Given the
>above configuration, any thoughts on whether those 2 options will help
>us?
>
>Any other ideas on INIT.ORA parameters to change, or Oracle statistics
>to monitor? Need more information on our configuration? Please e-mail
>me directly in addition to posting to the newsgroup.
>
>Regards,
>Brian
>
>--
>Brian M. Biggs mailto:bbiggs_at_cincom.com
>Cincom Systems, Inc. voice: (513) 677-7661
>http://www.cincom.com/
Received on Fri Mar 22 1996 - 00:00:00 CET