Re: PL/SQL insert performance

From: Joel Garry <joelga_at_pebble.ml.org>
Date: Fri, 08 May 1998 00:24:03 GMT
Message-ID: <6itjdu$6b4$1@pebble.ml.org>

In article <6isks8$upv$1_at_nnrp1.dejanews.com>,  <brendan_o'brien_at_wrightexpress.com> wrote:
>Here's a puzzler for the more experienced developers/dba's among you:
>
>SCENARIO: We have a large transaction table, about 30 cols, about 27 million
>records. This is a non-volatile table with virtually no updates or deletes
>against it. Records are inserted by a very straightforward stored procedure
>once daily. The procedure grabs all records from a scratch table (approx.
>30,000), does some minor data scrubbing, then inserts into the production
>table. Commit occurs every 2500 records.
>
>The production table has an 11 column PK, and 2 additional non-unique indexes
>of 10 and 3 columns respectively. The indexes are each spread across multiple
>extents (between 3 and 19 extents per index), while the data is spread across
>33 extents. Extent size = 125M.
>
>PROBLEM: As the table grows in size, the daily insert procedure is taking a
>rapidly increasing amount of time to complete. Execution time has gone from 1
>hour a couple of months ago (approx 5 million existing rows back then) to over
>8 hours today.
>
>POSSIBLE CULPRITS: As the table is non-volatile, I doubt stongly that
>dropping and re-creating indexes will improve performance as virtually no

Yes, but are the indexes being added in ascending order? If not, you are probably getting them unbalanced, so oracle is spending more and more time rebalancing them.

You may find dropping the indexes, doing the inserts then recreating them much faster.

>updates or deletes are ever done, therefore leaving the indexes virtually free
>of any 'dead' space (sorry if my terminology is off here... I'm a developer
>not a DBA). The DBA's swear the storage is optimal and not the problem, and I
>tend to believe them (the reading I've done on storage management suggests
>that reorganizing data for contiguous storage will have little effect on
>performance, especially on inserts). I have read something that suggested the
>culprit may be the non-unique indexes, and the solution lying in the
>optimization of something called 'AIP's to better handle the row-sizes for my
>tables. The procedure that does the inserts has not changed and is about as
>simple as it gets.
>
>That's all I can think of to mention. I've got developers and dba's pointing
>at one another. Any help is greatly appreciated. If you would, please email
>any replies to:
>brendan_o'brien_at_wrightexpress.com
>
>Peace.
>-B.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

--
These opinions are my own and not necessarily those of Information Quest

jgarry@eiq.com                           http://www.informationquest.com

http://ourworld.compuserve.com/homepages/joel_garry "See your DBA?" I AM the @#%*& DBA! Received on Thu May 07 1998 - 19:24:03 CDT