Speed up an update statement on a big table

[DanDanAUS]

Dear all,

I have a table that has 1.3 million records.

The full insert of that data using external tables takes 2.3 minutes. Which is really nice time.

But when i try and update the full table takes up to 20 minutes. The update takes place every 30 minutes and its updating at least 1 million records of the full table.

Here is the query:



CREATE OR REPLACE PROCEDURE TestUpdateTMIRecordsStaging
IS
BEGIN

MERGE INTO TMI_PARAMETERS T
USING TMI_PARAMETERS_STAGING L
ON (T.ID = L.ID
AND
T.ORBIT = L.ORBIT)
WHEN MATCHED THEN
UPDATE
SET
T.TIME_MIN = L.TIME_MIN,
T.SEA_SURFACE_TEMP_C = L.SEA_SURFACE_TEMP_C,
T.Z_WINDS_MS = L.Z_WINDS_MS,
T.W_WINDS_MS = L.W_WINDS_MS,
T.WATER_VAPOUR_MM = L.WATER_VAPOUR_MM,
T.CLOUD_LIQUID_WATER_MM = L.CLOUD_LIQUID_WATER_MM,
T.RAIN_RATE_MMHR = L.RAIN_RATE_MMHR
WHEN NOT MATCHED THEN
INSERT(T.TIME_MIN,T.SEA_SURFACE_TEMP_C,T.Z_WINDS_MS,T.W_WINDS_MS,
T.WATER_VAPOUR_MM,T.CLOUD_LIQUID_WATER_MM,T.RAIN_RATE_MMHR)
VALUES(L.TIME_MIN,L.SEA_SURFACE_TEMP_C,L.Z_WINDS_MS,L.W_WINDS_MS,
L.WATER_VAPOUR_MM,L.CLOUD_LIQUID_WATER_MM,L.RAIN_RATE_MMHR);

END;
/

Is there anything that i am doing wrong? Or any advice on how i can speed up this update?

Using Oracle 9i.

Thank you so much for your time.

Looking forward to your response.

Cheers
Dan

 

[Dagon]

Obvious questions:

a) have you analyzed the tables ?
b) have you looked at the explain plan/trace file ?

 

[Dagon]

To give you some idea how trivial 1.3 million rows is in the scheme of things these days, I ran the following on my home PC.

SQL> set timing on
SQL> 
SQL> DROP TABLE TMI_PARAMETERS;

Table dropped.

Elapsed: 00:00:02.98
SQL> 
SQL> DROP TABLE TMI_PARAMETERS_STAGING;

Table dropped.

Elapsed: 00:00:00.01
SQL> 
SQL> create table TMI_PARAMETERS
  2  (id  number, orbit varchar2(20),
  3  TIME_MIN		number,
  4  SEA_SURFACE_TEMP_C number,
  5  Z_WINDS_MS 	number,
  6  W_WINDS_MS 	number,
  7  WATER_VAPOUR_MM	number,
  8  CLOUD_LIQUID_WATER_MM number,
  9  RAIN_RATE_MMHR	   number);

Table created.

Elapsed: 00:00:00.01
SQL> 
SQL> create table TMI_PARAMETERS_STAGING
  2  (id  number, orbit varchar2(20),
  3  TIME_MIN		number,
  4  SEA_SURFACE_TEMP_C number,
  5  Z_WINDS_MS 	number,
  6  W_WINDS_MS 	number,
  7  WATER_VAPOUR_MM	number,
  8  CLOUD_LIQUID_WATER_MM number,
  9  RAIN_RATE_MMHR	   number);

Table created.

Elapsed: 00:00:00.01
SQL> 
SQL> declare
  2  begin
  3    for i in 1..1300000 loop
  4  	 insert into TMI_PARAMETERS
  5  	  (id, orbit, TIME_MIN,
  6  	   SEA_SURFACE_TEMP_C,
  7  	   Z_WINDS_MS,
  8  	   W_WINDS_MS,
  9  	   WATER_VAPOUR_MM,
 10  	   CLOUD_LIQUID_WATER_MM,
 11  	   RAIN_RATE_MMHR)
 12  	  values
 13  	  (i, 'XX'||i, i*2, i*1.5, i*1.4, 1*0.7, i*0.9, i*0.01, i*0.02);
 14  	 if mod(i,1000) = 0 then
 15  	   commit;
 16  	 end if;
 17    end loop;
 18    commit;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:18.53
SQL> 
SQL> declare
  2  begin
  3    for i in 1..1000000 loop
  4  	 insert into TMI_PARAMETERS_STAGING
  5  	  (id, orbit, TIME_MIN,
  6  	   SEA_SURFACE_TEMP_C,
  7  	   Z_WINDS_MS,
  8  	   W_WINDS_MS,
  9  	   WATER_VAPOUR_MM,
 10  	   CLOUD_LIQUID_WATER_MM,
 11  	   RAIN_RATE_MMHR)
 12  	  values
 13  	  (i, 'XX'||i, i*4, i*1.3, i*1.2, 1*0.6, i*0.8, i*0.02, i*0.03);
 14  	 if mod(i,1000) = 0 then
 15  	   commit;
 16  	 end if;
 17    end loop;
 18    commit;
 19   for i in 1300001..1300100 loop
 20  	 insert into TMI_PARAMETERS_STAGING
 21  	  (id, orbit, TIME_MIN,
 22  	   SEA_SURFACE_TEMP_C,
 23  	   Z_WINDS_MS,
 24  	   W_WINDS_MS,
 25  	   WATER_VAPOUR_MM,
 26  	   CLOUD_LIQUID_WATER_MM,
 27  	   RAIN_RATE_MMHR)
 28  	  values
 29  	  (i, 'XX'||i, i*4, i*1.3, i*1.2, 1*0.6, i*0.8, i*0.02, i*0.03);
 30  	 if mod(i,1000) = 0 then
 31  	   commit;
 32  	 end if;
 33    end loop;
 34    commit;
 35  end;
 36  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.37
SQL> 
SQL> MERGE INTO TMI_PARAMETERS T
  2  USING TMI_PARAMETERS_STAGING L
  3  ON (T.ID = L.ID
  4  	 AND
  5  	 T.ORBIT = L.ORBIT)
  6  WHEN MATCHED THEN
  7  	 UPDATE
  8  	 SET
  9  	 T.TIME_MIN = L.TIME_MIN,
 10  	 T.SEA_SURFACE_TEMP_C = L.SEA_SURFACE_TEMP_C,
 11  	 T.Z_WINDS_MS = L.Z_WINDS_MS,
 12  	 T.W_WINDS_MS = L.W_WINDS_MS,
 13  	 T.WATER_VAPOUR_MM = L.WATER_VAPOUR_MM,
 14  	 T.CLOUD_LIQUID_WATER_MM = L.CLOUD_LIQUID_WATER_MM,
 15  	 T.RAIN_RATE_MMHR = L.RAIN_RATE_MMHR
 16  WHEN NOT MATCHED THEN INSERT(T.TIME_MIN,T.SEA_SURFACE_TEMP_C,T.Z_WINDS_MS,T.W_WINDS_MS,T.WATER_VAPOUR_MM,T.CLOUD_LIQUID_WATER_MM,T.RAIN_RATE_MMHR)
 17  VALUES(L.TIME_MIN,L.SEA_SURFACE_TEMP_C,L.Z_WINDS_MS,L.W_WINDS_MS,L.WATER_VAPOUR_MM,L.CLOUD_LIQUID_WATER_MM,L.RAIN_RATE_MMHR)
 18  /

1000100 rows merged.

Elapsed: 00:01:18.35
SQL> 
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL> 
SQL> spool off

End to end (including an inefficient row by row insert of the tables) it took about 4 minutes. The merge of 1 million 100 rows took 1 min 18 seconds. Admittedly, you might have a lot of indexes or constraints, which would add to the time, but 20 minutes to update a million rows seems excessive.

 

[LKBrwnDBA]

Have you checked for migrated/chained rows?
Are your indexes balanced?

Describe your table + indexes.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Dan²,

If you want to get rid of chained rows or unbalanced indexes quickly, you can do the following:

alter table s_emp move;

Table altered.

alter index S_EMP_ID_PK rebuild;

Index altered.

In the above cases, each object was completely reorganised, removing chaining, imbalances and extra leaves.

Let us know if this had an effect.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.

 

[DanDanAUS]

Hmmmm this really gets me thinking.

I am even using external tables with SQL loader driver to insert the data in the database table and takes 2.3 minutes to insert the 1.3 milion records.

I cant get why the insert that Dragon does even with an in efficient way takes only small time.

Only thing that comes to my mind is because this database is a production database and at any time takes absolute pounding by users and multiple datasets that get inserted.


Also when you guys mentioned the indexes on the table. Initially when i tried the update on the table i didnt have any indexes or primary keys on the tables. And even then it took time.

I tried 2 aproaches:

I - aproach
1. Create external table
2. Use external statement in update statement to update the main table
-- no primary key and no indexes

takes about 20 minutes

II - aproach

1. create external table
2. insert data from exeternal table into staging table
3. place primary key on original table
4. place primary key on staging table
5. run the update from staging table to original table

takes about 20 minutes

Any ideas?Suggestions?

 

[Dagon]

I'm using 10g and PL/SQL has been speeded up in 10g compared to 9i, so that may explain the relatively good performance of my "inefficient" inserts.

I retried my test with an external table in the merge, but the results weren't much different (1 min 25 seconds). I suppose a third approach you could try would be:

1) load data into staging database table using sql*loader direct path
2) - no primary key/no indexes (because you are replacing so much of the data they probably won't help).
3) run the update from staging table to original table

That would at least reduce two steps to one.

 

[taupirho]

I'd be interested to see what hapens if you try a correlated update. Ok, so this won't sort out the insert bit that you need to do but lets break it down one part at a time.

For the correlated update both you target and staging table will need the same pkey, then you do something like

UPDATE
(select
l.min_time old1,
t.min_time new1,
l.sea_surface_temp old2,
t.sea_surface_temp new2
etc ...
etc ...
from TMI_PARAMETERS T, TMI_PARAMETERS_STAGING L
where T.ID = L.ID
AND T.ORBIT = L.ORBIT)
SET new1 = old1,new2 = old2 etc ...

Let us know how long this part takes


In order to understand recursion, you must first understand recursion.