Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: cursor: pin S - mutex Wait - in Benchmark

RE: cursor: pin S - mutex Wait - in Benchmark

Date: Thu, 16 Nov 2006 21:03:45 +0530
Message-ID: <>

Bernard, Folks  

Qs 1 - A basic Qs on a specific Case when respective Table is PARTITONED.   Consider a partitioned Table with its different partitions lying in different Tablespaces (hence datafiles)

Assume Transactions are manually grouped & corresponding INSERTs/Updates are made to occur in "different" partitions thru the "Different" RAC Nodes (Data partitioning).

Are multiple freelist GROUPS used by "Default" & have any significance in such a case? (Assuming NO Preallocation of Extents to Freelist Groups is Done)  

Qs 2 - ~~ Will Manual Data partitioning help reduce the respective waits - "cursor: pin S", "row cache lock" & "latch: cache buffers chains"  



My Questions are prefixed by "~~" below  

From: Polarski, Bernard [] Sent: Thursday, November 16, 2006 4:02 PM

2 nodes, 3000 dedicated, that's avg 1500 dedicated with load balancing per node. You must have very serious reason to not use MTS for an OLTP.

For info, I could have 996 concurent active user with MTS on a 2 gig ram box were I could only have 85 dedicated session (test with swingbench).  

~~ I stand corrected. 3000 Concurrent Application users spawn only about
1200 (600 per node) dedicated server sessions. Our Application internally uses the concept of MTS/Shared servers & hence Oracle's MTS is NOT needed.  

Are you sure that the cursor Pin is not linked to query about session info in the data dictionary?  

~~ How can the same be checked?

Data partitioning will not help if your users are all accessing the same table extends, so you will need to partition by divisions of customer rather than by something generic like date. In this respect you can partition by date, subpartitions by (group of customers) and use storage condition of sub partitions to allocate partitions on different datafiles/tbs, this will work.    

Do you use ASSM or manual segment space? For manual, consider also free list group and free list. ie, you can consider preallocation segment of table per free list group on different datafile and assign group of customer to specific free list group so they will not insert/access the same extend following their division category.  

~~ What we do is RE-CREATE the Tables undergoing INSERTs/ UPDATEs as a
partitioned Table with multiple (>=2) Partitions such that 2 groups of Customers exist in different Partitions (& hence different datafiles/tablespaces) & with FREELIST GROUPS = 2(Num of Nodes). Thus application Transactions can be manually grouped customer-wise so that Inserts/Updates from different RAC Nodes happen in different blocks, partitions & datafiles(Manual Data partitioning).  

But wave /bye to your performance for range scan index access (that's include also access by predicate equijoin on non-unique indexes columns).  

~~ Some more detail please? Do you mean the "preallocation of segment of
table per free list group" mentioned above will adversely affect performance?

NOTE - Our OLTP Transactions do access by predicate equijoin on non-unique indexes columns  

Single access lookup and FTS will not be affected. On OLTP this may be a serious issue if your 3000 session issue small range scan access and that's suddenly they need 10 blocks instead of 2 or 3. that's the drawback of destroying clustering factor in the quest for a solutiong to hot blocks. Spreading the inserts row among various blocks using any of the free list or ASM trick will yield you this in return.  


Received on Thu Nov 16 2006 - 09:33:45 CST

Original text of this message