OraFAQ Forum: Server Administration » Performance of the server

Home » RDBMS Server » Server Administration » Performance of the server

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

Performance of the server [message #51778]

Thu, 13 June 2002 01:54

Kumar Devendra
Messages: 4
Registered: June 2002

Junior Member

I have written a simple procedure like this :--->

create or replace
procedure update_publisher
is
cursor p is select pub_code from publishers;
i number(8);
begin
i := 0;
for prec in p
loop
i := i + 1;
update publishers set pub_code = i
where pub_code = prec.pub_code;
update main_book_details set pub_code = i
where pub_code = prec.pub_code;
commit;
end loop;
end;

There are 33000 records in publishers table and around 100000(one lakh) records in main_book_details table. But this procedure is taking more and more time (nearly eight hours) for updation of this much of records.Even insert statement is executng very slow for large no of records.Is there any parameter setting problem?How to remove this problem ?Please send the solution.

Report message to a moderator

Re: Performance of the server [message #51783 is a reply to message #51778]

Thu, 13 June 2002 05:57

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

   Seems you are issuing COMMIT for every single record
   for each loop. consider changing that
   try to issue a commit for every 1000 records or
   2000 records. u can use a mod function to do that.

Report message to a moderator

Previous Topic:	Re: KCF: write/open error
Next Topic:	Need an Information.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 20:27:07 CDT 2024