RE: Tuning for High Volume of Updates of Temporary info into a Small Table ?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Fri, 23 Feb 2007 22:02:53 +0800
Message-id: <037301c75753$4fcaa090$6401a8c0@windows01>

Hi,  

Try a single table hash cluster hashed by some unique field (like SID from v$session).  

Note that this is helpful in cases where vast majority of accesses are based on known IDs.  

You can make the cluster use a separate block for each row to gain maximum concurrency (which means higher buffer cache usage as well) or you can put several hash buckets into one block.  

By specifying the SID itself as the hash function you guarantee that no more than one value will collide to a hash bucket. This enables some optimizations like shared cache buffers chains reads from a block without pinning it etc if looking for by an unique ID from there.  

Also you would have just one single segment, no separate indexes (which mean DML overhead) are needed for SID lookup.  

Syntax:  

create cluster c (

      sid number(10)
)

size 2000
single table
hashkeys 1000
hash is sid;  

create table t (

       session_id number(10) not null,
       username varchar2(10)

)

cluster c(session_id);

If you want to keep this table from aging out, you could put it into a separate buffer pool (a keep pool if it doesn't already accommodata other data) or buffer pool with 2k block size should you want to keep 1 row per block...  

Tanel.  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA
Sent: Friday, February 23, 2007 20:22
To: oracle-l_at_freelists.org
Subject: Tuning for High Volume of Updates of Temporary info into a Small Table ?

Folks  

Our Banking Product has an Application Login Table into which Session Connect information is INSERTED on User Login.

Approx 10,000 Concurrent Users login & hence the Table contains about 10,000 rows.

Info created by 1 session may be used by Another Database Session process. On User Logout the respective Session information Record is DELETED.  

An Application Load of 300 UPDATEs per second happen to the Table's different rows at random (NOT to every row) depending on the respective Application User's Transaction

NOTE - Each Update is updating a unique single row via the unique index.  

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 23 2007 - 08:02:53 CST