Index Contention / Sequence Caching
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 15 Mar 2014 02:10:15 +0000
Hi all,
I'm having a slight disagreement with my developers, and hoping you guys can
help me out (either confirm what I think or tell me I'm flat-out wrong, I can
take either, I just need to get this fixed).
One of our applications is suffering pretty severe waits on a particular
insert. From querying the waits from gv$active_session_history for *one* second:
14-MAR-14 11.11.00.041 AM 826usupm5pkwu gc buffer busy
13557923 13
14-MAR-14 11.11.00.041 AM 826usupm5pkwu enq: TX - index contention
13557923 19
14-MAR-14 11.11.00.142 AM 826usupm5pkwu gc buffer busy
13557923 49
14-MAR-14 11.11.00.142 AM 826usupm5pkwu enq: TX - index contention
13557923 53
14-MAR-14 11.11.00.041 AM 826usupm5pkwu enq: TX - index contention
-1 67
14-MAR-14 11.11.00.483 AM 826usupm5pkwu gc buffer busy
13557923 109
To me, this spells classic hot block on object 13557923 - which happens to be
the PK for a table where we had a lot of waits happening. My idea to resolve is
to partition that index to split out the contention - instead of all sessions
trying to grab that last block, the sessions will have X blocks to work with,
depending on the degree of partitioning.
Frankly, I didn't quite understand development's response (which may indicate a
lack in my knowledge, or may indicate that they're not talking Oracle terms).
They want to increase the caching for the sequence that they use to populate
the PK from 20 to 1000, because:
"The fact that the biggest waits were index contention and gc buffer busy waits
indicates that the contention is mostly between RAC nodes trying to lock the
same index page. Increasing cache from 20 to 1000 (assuming that the default
page is 8K and a PK column size is 8 bytes) will cause multiple nodes to work
with their own index pages. There still will be contention between sessions
running on the same node due to high number of CPUs (48) on each node. But in a
short term increasing sequence cache might decrease contention up to three
times (it's a 3 node RAC)"
I'm fairly certain that the gc buffer busy waits were because sessions trying
to grab that index block were spread among multiple nodes, but I really don't
understand how increasing the sequence cache value should help that - no matter
what's cached in memory, the same block is going to be the hot spot, no?
Short version: 1) am I crazy? 2) is the developer who said that crazy? 3) what
the heck is object "-1" 4) if you were confronted with waits like that - any
immediate thoughts for resolving other than partitioning that PK index?
Oracle 10.2.0.4 on Windows 2003 x64, in case that matters.
Thanks!
stephan
Stephan Uzzell
Other related posts:
