Re: performance issue

In article <1995May5.190235.17123_at_lia.com>, allwyn_at_lia.com (Allwyn Carvalho) wrote:

> I have noticed a major drop in insertion performance on an indexed
> table compared to when the table is not indexed. To quote my
> benchmark numbers, I get around 90 row insertions per second
> when I don't have any indexes on the table, but only around 14
> insertions per second when I have an index.
>
> Granted that these figures depend largely on the hardware/os/table
> specific details, but can anyone tell me whether such a performance
> drop is normal, or can I do something to improve the performance.
>
> For Oracle to be viable I am looking for about 40-50 insertions
> per second with one index on the table. We have about a million
> rows to add in a window of 6-7 hours. I cannot drop the index
> just before doing the insertions and then add it back at the end
> for all kinds of reasons.
>
> I am running Oracle 7.0.16 on a Sparc-10 running Solaris 2.3. The
> table has about 20 columns. The total size of each row is about
> 250 bytes. The column to be indexed is a character field of 20
> bytes. There are about 50 duplicates in the table to the indexed
> column that come at random intervals. When running my benchmark
> I did a COMMIT WORK at the end of every 500 insertions. I can
> gladly provide any additional data needed.
>
> I would appreciate email at my address below in addition to an
> optional posting on the net because our newsfeed is a lot flakier
> than our mailfeed and I would not want to miss any feedback on the
> matter.
>
> Thanks in advance for your replies.
>
> - allwyn
> --
> Allwyn Carvalho | IA Corporation | If it were done when 'tis done
> allwyn_at_ia-us.com | 1900 Powell St | then 'twere well it was done
> Tel:(510)450-6886 | Emeryville | quickly.
> Fax:(510)450-7099 | CA 94608 | -- Shakespeare

What do the I/Os look like, in terms of SQLDBA's opinion versus the server's? As well where are the rollback segments located as opposed to the data files for the different tablespaces? What's your SGA sizing look like?

We've seen disk and memory bottlenecks again and again as the performance killer. Granted the bigger the CPU, the better, but we have a client with a dual processor Alpha whose performance sucked, largely a result of memory depletion and disk contention. Adding memory and stremalining disk I/O increased performance 300%.

E

-- 
...the decline of the American Civilization...stay tuned

--the opinions expressed herein are my own (probably)--