Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> PL/SQL insert performance

PL/SQL insert performance

From: <brendan_o'brien_at_wrightexpress.com>
Date: Thu, 07 May 1998 15:44:08 GMT
Message-ID: <6isks8$upv$1@nnrp1.dejanews.com>


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 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 Received on Thu May 07 1998 - 10:44:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US