Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Thu, 16 Nov 2006 17:39:42 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF302002144061@BLRKECMSG11.ad.infosys.com>

Folks, Bernard

My Questions are prefixed by "~~" below

Thanks indeed


From: Polarski, Bernard [mailto:Bernard.Polarski_at_atosorigin.com] Sent: Thursday, November 16, 2006 4:02 PM To: VIVEK_SHARMA; oracle-l_at_freelists.org Subject: RE: cursor: pin S - mutex Wait - in Benchmark

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.

~~ Will Manual Data partitioning thereby causing usage of different
table partitions & hence different table blocks, extents & datafiles help reduce the respective waits -

"cursor: pin S", "row cache lock" & "latch: cache buffers chains"

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.

~~ Some more detail on the following please:-

  1. How is "preallocation segment of table per free list group on different datafile"
  2. Assign group of customer to specific free list group"

Any Sample SQL Commands / Links for the above?

~~ What I know is to 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.

bp                  

Received on Thu Nov 16 2006 - 06:09:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US