database - performance tuning - Insert -

i have 1 unix script in creating table, index , loading date file table using sql loader . , doing near 70 direct update (not using or bulk collect) on table.

at last inserting new table data table. per day it's processing 500 000 records. these update fast.

during inserting data table taking 20 minutes. how can improved?

1. no problem in insert because on same table inserting 500 000 rectors table that's working fine. insert done in less minute.

insert tables () select () tablex; it's taking 20 min 500 000 records tablex- created , loaded , 70 direct update done in same shell script .

checked explain plan cost select alone , insert script both same.

insert tables () select () tabley; above statement executed less second.

• i used parallel hint. cost reduced . , cpu utilisation zero.
• shall create 1 more table tablez load data tablez final table?
• is stats gathering required? daily run program.

when direct path insert using sql loader, records inserted above highwatermark. after load completed , highwatermark moved up, there lots of empty blocks below original/old highwatermark position. if select going full table scan, reading empty blocks too. check see if table has accumulated lots of empty blocks on period of time. may use segment advisor this. based on advisor recommendations, shrink table , free unused space. speed execution. hope helps.