Member Avatar for johnbach

I have around 2 million vouchers of some fixed denomination (say 10 and 20)

create table vouchers(pin varchar2(20) primary key ,amount number,status char(1),extra_value number);
create index IDX_AMOUNT_STATUS on vouchers(amount,status);

Typical values

amount,status ,count(status)
10     N       1000000       
10     U        700000       
20     N        300000

For each payment transaction,I fetch one voucher wrt.transaction amount and mark it as used
(So it will not be used for any other transaction)

UPDATE vouchers SET status='U' 
 WHERE amount=:txn_amount AND staus='N' AND rownum=1
 RETURNING pin,extra_value INTO :pin,:extra;

I have 10 parallel threads processing incoming transaction. I am expecting 30 transactions per second.
Initially the update takes 0.002 millisecond but after few hours it reaches 2 seconds.

I even tried with partitioning table also(status N one partition and defalut another partition).

Platform details
Linux RHEL 5
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

Suggestions please.

Edited by johnbach because: Oracle version
  • 1 Contributor
  • 1 Reply
  • 166 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by johnbach
Member Avatar for johnbach

Oracle Advanced Queuing solved the problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.