-3

I have one UNIX script In this we are creating the table, index and loading the date from file to this table using SQL Loader . And doing near 70 direct update (not using for all or bulk collect) on this table.

At last we are inserting this new table Data to another table. Per day it's processing 500 000 records. all these update are very fast.

During inserting this data into another table taking 20 minutes. How can this be improved?

  1. No problem in insert because on the same table we are inserting 500 000 rectors from another table that's working fine. Insert done in less than a minute.

Insert into tables () select () from tablex; It's taking 20 min for 500 000 records Tablex- created , loaded , 70 direct update done in the same shell script .

Checked the explain plan cost for select alone and with insert script both are same.

Insert into tables () select () from tabley; The above statement executed less than a second.

  • I used parallel hint. Cost is reduced . And cpu utilisation is zero.
  • Shall I create one more table tablez then load the data from tablez to my final table?
  • Is stats gathering is required? This is daily run program.
James Z
  • 12,209
  • 10
  • 24
  • 44
VIDHYA
  • 11
  • 1
  • 1

1 Answers1

0

When we do direct path insert using SQL Loader, the records are inserted above the HighWaterMark. After the load is completed and the HighWaterMark is moved up, there could be lots of empty blocks below the original/old HighWaterMark position. If your SELECT is going for a Full Table Scan, it will be reading all those empty blocks too. Check to see if your table has accumulated lots of empty blocks over the period of time. You may use Segment Advisor for this. Based on advisor recommendations, shrink the table and free unused space. This could speed up the execution. Hope this helps.

ArtBajji
  • 949
  • 6
  • 14
  • Hi Thanks for your response . Im Creating table , one time insert using SQL Loader and many update for business logic implementation . Post this from this table to another table . As im creating the table here i think there wont be any problem with empty block . – VIDHYA Apr 22 '16 at 18:21
  • Got it. Kindly check the below details. 1. What is the size of table X and table Y in GB? 2. Are there any differences in the explain plans of select from table X and table Y? 3. If 1 and 2 above are same, then check if any other process is eating up I/O or CPU during your insert into table X. You may want to use, AWRs or OEM or dynamic performance views, to check, if it is so. Hope this helps. Thanks. – ArtBajji Apr 23 '16 at 05:21