Home » RDBMS Server » Performance Tuning » Tuning Stored Procedure that updates table of size 1 gb
Tuning Stored Procedure that updates table of size 1 gb [message #65115] Tue, 04 May 2004 19:23 Go to next message
Sunil Bassi
Messages: 28
Registered: June 2003
Junior Member
Hi,I have a stored procedure which takes around 1 hr to complete. The logic used inside the procedure is fairly simple.
declare
cursor c1 is Select * from tab1;
begin
for i in c1
Loop
--ie update the corresponding record in tab2
Update tab2 ( all the fields)
where tab2.pk(primary key col) = i.col;
If Sql%notfound then
--If no such record exists then insert a record in tab2.
insert into tab2;
end if;
End Loop;
end;

Further details are like this

The size of tab1 is around 1GB.
the size of tab2 is around 700 MB.
Db is 9.1.2
Sga is 1GB
db_cache_Size is .5GB
db_multiblock_readcount is 32

Any suggetions to reduce the procedure execution time ?

Regds
Sunil
Re: Tuning Stored Procedure that updates table of size 1 gb [message #65117 is a reply to message #65115] Thu, 06 May 2004 12:11 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Consider implementing PL/SQL's BULK COLLECT and BULK BIND operations. See the example at http://www.orafaq.com/scripts/index.htm#GENPLSQL

Best regards.

Frank
Re: Tuning Stored Procedure that updates table of size 1 gb [message #65124 is a reply to message #65115] Tue, 11 May 2004 05:37 Go to previous messageGo to next message
Carsten Jørgensen
Messages: 3
Registered: April 2004
Junior Member
How can TAB1 be bigger than TAB2 (1GB versus 700 MB)?
It seems that all rows/columns from TAB1 are copied to TAB2.

Anyway - here are some suggestions:

a) If the number of rows to be inserted exceeds the number of rows to updated, consider doing the INSERT first and the UPDATE (in case of duplicate rows condition) afterwards.

b) Consider if you have unnessesary indexes on TAB2.

c) Consider if you have indexes on TAB2 which could be disabled before the update process and rebuilt after the update has taken place. Note that simultanous queries against the TAB2 table will fail if these queries need the index (unless the queries run with ORACLE parameter SKIP_UNUSABLE_INDEXES is set to TRUE -
eg. ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;)

d) Try using the MERGE INTO statement - This statement should be able to replace your entire stored procedure. Have not tried it myself, however.

Best regards
Carsten
Re: Tuning Stored Procedure that updates table of size 1 gb [message #65148 is a reply to message #65117] Tue, 18 May 2004 23:23 Go to previous messageGo to next message
shailesh
Messages: 22
Registered: October 2000
Junior Member
merge statements are indeed very fast i use it to merge 2 million records in a table..

MERGE INTO client_m rcm
USING client_temp ct ON (rcm.clientcd = ct.clientcd)
WHEN MATCHED THEN
UPDATE SET rcm.clientname = ct.clientname
WHEN NOT MATCHED THEN
INSERT VALUES ( ct.clientcd , ct.clientname) ;

you can try something like this..

Also in your existing case, performance can be boosted if you commit after say evey 10,000 records.

Regards
Shailesh
Re: Tuning Stored Procedure that updates table of size 1 gb [message #65336 is a reply to message #65115] Wed, 11 August 2004 02:10 Go to previous message
Sanjay
Messages: 236
Registered: July 2000
Senior Member
suppose i want to write the query into stored proceudre for update

can i append the name of the field into query dynamicaly

e.g.
update (table name ) set @fieldname = 1 where ....
Previous Topic: shared memory proble
Next Topic: update query in to stored procedure
Goto Forum:
  


Current Time: Fri Mar 29 03:38:43 CDT 2024