OraFAQ Forum: Performance Tuning » Tuning Stored Procedure that updates table of size 1 gb

Home » RDBMS Server » Performance Tuning » Tuning Stored Procedure that updates table of size 1 gb

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning Stored Procedure that updates table of size 1 gb [message #65115]

Tue, 04 May 2004 19:23

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

Report message to a moderator

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

Frank Naude
Messages: 4596
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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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 ....

Report message to a moderator

Previous Topic:	shared memory proble
Next Topic:	update query in to stored procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 30 05:57:49 CDT 2025