Insertion is very, very slow

[ETLMaster]

Hi,
I created an application that inserts a number of records into a certain table. The problem is that the 6 VARCHAR2(500) columns of this table are indexed, and when inserts are done on this table, the tkprof disk access value increases 6 times from ~2000 to ~12000. I dropped these indexes and the disk access decreased to the ~2000 value so it seems that my hypothesis is correct. I also tried increasing the db_cache_size to around 350 MB in the hopes that the disk access would decrease since the DBWR process would not be invoked since the buffer cache would keep on holding the dirty data(and rebuilding of indexes), but I was only able to reduce the total time by about 100 seconds.
Dropping and recreating the indexes might not be an option since the application logic must fall within the global transaction. Can I "hide" dropping and recreating the indexes within an autonomous transaction so that the DML done within the global transaction can still be rolled back?
Any help is appreciated.

Ron

 

[lewisp]

I'm not sure how or if it might help, but have you analyzed the table? Try using DBMS_STATS.GATHER_STATS on your table.

 

[ETLMaster]

Does analyzing the table improve the index rebuilding speed? My understanding is that it only improves selectivity of the table. Anyways, I'll try that idea and see if it improves my situation.

Thanks!

 

[nbateman]

What you are experiencing is I think normal. As a rough guide, when estimating the time it will take to insert n rows, I expect 1 "unit of time" to insert the data, and 1 for each index (including the primary key).
For example, if it takes 1 second to load 100 rows into a table, I expect it to take about 2 seconds with the primary key, 3 if it has one secondary index, etc.

You can probably tune it a bit, but if you really wany to speed it up, reduce the number of indexes.

If it is possible to combine several indexes into one compound one, you will see an improvement.

 

[snayak]

Just my 2 cents , I feel you have to look into the area of freelist for the table.

 

[walkerci]

Ron,

Please provide:

1. Number of rows in table before insert.
2. Average row size in bytes.
3. Number of rows to be inserted.
4. Number and type of indexes on table.
5. Number of extents for table and each index.
6. Tablespace parameters for table and indexes.
a. Local or Dictionary Managed.
b. Initial / Next Extent sizes.
c. Blocksize.
d. LOGGING?
7. network packet size, type of network, tcp no delay setting, number of hops, TTL
8. Using ODBC or native Oracle connection?

Also, type of application OLTP, DW, etc.

I suspect that you may have a extent management issue and/or network related delays.

Charles