Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve row exclusively - performance (oracle 10g,UNIX)
icon8.gif  Retrieve row exclusively - performance [message #406695] Fri, 05 June 2009 07:09 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
Hi,
I have a table voucher_tab with 5 lac unique voucher (records) and a procedure to exclusively select an unused voucher.


TABLE: VOUCHER_TAB
ID STATUS
11111 N
22222 N
33333 N

PROCEDURE:
create or replace procedure getId(id out number) as
....
SELECT ID into myid from VOUCHER_TAB
where rownum=1 and STATUS='N' FOR UPDATE;
UPDATE VOUCHER_TAB SET STATUS='U' where ID=myid;
COMMIT;
end;


The above procedure takes 10 milliseconds to retrieve an unused voucher.

But when I call this procedure from a multi-threaded application continuously(40 calls per second), the time taken increases. (Reaches 3 seconds in a minute).


Could anyone help me with a solution?
Re: Retrieve row exclusively - performance [message #406697 is a reply to message #406695] Fri, 05 June 2009 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Use just 1 update, get rid of the select.
2/ Do not commit inside the procedure, only the caller knows if and when commit (or rollback) must be done, not the procedure.

Regards
Michel
Re: Retrieve row exclusively - performance [message #406699 is a reply to message #406697] Fri, 05 June 2009 07:22 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Thanks for the reply.
But sorry I am not clear .
1/ Use just 1 update, get rid of the select.
How do I retrieve the ID without select.
Re: Retrieve row exclusively - performance [message #406700 is a reply to message #406699] Fri, 05 June 2009 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the returning clause.

Regards
Michel
Re: Retrieve row exclusively - performance [message #406703 is a reply to message #406700] Fri, 05 June 2009 07:54 Go to previous message
johnbach
Messages: 32
Registered: June 2009
Member
Thanks ,it works.
Now it takes constantly 14 millisecond.
Previous Topic: EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2)
Next Topic: PL/SQL: ORA-00942 Error
Goto Forum:
  


Current Time: Mon Dec 05 13:18:38 CST 2016

Total time taken to generate the page: 0.05726 seconds