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

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"


 

Thanks 

 

________________________________

From: VIVEK_SHARMA 

My Questions are prefixed by "~~" below

 

________________________________

From: Polarski, Bernard [mailto:Bernard.Polarski@xxxxxxxxxxxxxx] 
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.

 

bp

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely for the use of the addressee(s). If you are not the intended
recipient, please notify the sender by e-mail and delete the original
message. Further, you are not to copy, disclose, or distribute this
e-mail or its contents to any other person and any such actions are
unlawful. This e-mail may contain viruses. Infosys has taken every
reasonable precaution to minimize this risk, but is not liable for any
damage you may sustain as a result of any virus in this e-mail. You
should carry out your own virus checks before opening the e-mail or
attachment. Infosys reserves the right to monitor and review the content
of all messages sent to or from this e-mail address. Messages sent to or
from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts: