Monday, July 25, 2011

Undo Block on RAC

In Today, I saw "enq: US - contention" waited, after user updated and didn't commit immediately on OLTP system.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: US - contention 5,483,916 363,654 66 79.1 Other
latch: row cache objects 1,057,136 37,099 35 8.1 Concurrenc
latch: ges resource hash list 2,665,567 16,912 6 3.7 Other
cursor: pin S wait on X 1,016,179 11,620 11 2.5 Concurrenc
I interest some idea "Undo Block Considerations" on Oracle RAC Presentation. Why I interest? N/A
If index blocks are read frequently from active transactions from multiple instances. You may see excessive undo block shipment and contention. When you "SELECT" data, It needs to read consistent read version, if it belongs to more than one instance, there is need to combine local and remote undo information for CR. If the amount of index blocks are changed by multiple instances and the duration of the transactions, undo block shipment may become a bottleneck. This happens in applications that read recently inserted data very frequently, but commit infrequently.

Idea to reduce it (RAC)!!!,
- Shorter transactions: it can reduce the likelihood that any given index block in the cache contains uncommitted data.
- Increasing sequence cache size: it can reduce interinstance concurrent access to index leaf blocks.
*** Some Idea!!! check Interconnect Network, use Gigabit Ethernet ... (don't cross CABLE)***
If you... What is your idea to reduce it (enq: US - contention) on RAC?

No comments: