Re: performance issue

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

   Newsgroups: comp.databases.oracle
   Path: lia!usenet
   From: allwyn_at_lia.com (Allwyn Carvalho)    Sender: usenet_at_lia.com (USENET NNTP)    Organization: I A Corporation
   Date: Fri, 5 May 1995 19:02:35 GMT

   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

Thanks for all those who replied to my posting. I learned of several ways to improve my performance, the two most important being:

1. use SQL*Loader instead of Pro*C (use direct path loading preferably), and
2. keep tablespace for data/index/roll back segment on different disk spindles if possible.

Once again, thanks.

• allwyn

--
--

Allwyn Carvalho      |    IA Corporation     |   Please note the change
allwyn_at_ia-us.com     |    1900 Powell St     |   in my email address
Tel:(510)450-6886    |    Emeryville         |   from allwyn_at_lia.com
Fax:(510)450-7099    |    CA 94608           |   to allwyn_at_ia-us.com