Forum Stats

  • 3,853,532 Users
  • 2,264,231 Discussions
  • 7,905,384 Comments

Discussions

Performance issue due to Gc buffer busy acquire & Gc cr block busy in rac database version 12.2.0.1

2»

Answers

  • User_NIZCN
    User_NIZCN Member Posts: 1 Green Ribbon
    edited Jun 15, 2022 5:20PM

    C.3.16 buffer busy waits for Oracle 12.1

    Wait until a buffer becomes available.

    There are four reasons that a session cannot pin a buffer in the buffer cache, and a separate wait event exists for each reason:

    1. "buffer busy waits": A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.
    2. "read by other session": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.
    3. "gc buffer busy acquire": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.
    4. "gc buffer busy release": A session cannot pin the buffer in the buffer cache because another session on another instance is taking the buffer from this cache into its own cache so it can pin it.

    Prior to release 10.1, all four reasons were covered by "buffer busy waits." In release 10.1, the "gc buffer busy" wait event covered both the "gc buffer busy acquire" and "gc buffer busy release" wait events.

    So the db problem is not related to aging the block out of buffer cache. It is there. It just resides in another instance's buffer cache. The session on this instance tries to fetch the block from another instance. The real problem is that there is no affinity of data to instances. Each instance can work with the same data causing buffer fetches over interconnect. Google for "Oracle real world performance 15 index contention" for ideas. Although it talks about inserts the idea of smart key can be applied to SELECTs as well. Your app can INSERT INTO <table>(name_id, ..) VALUES(SYS_CONTEXT('userenv','instanceid')||<name_id_value>,);

    Of course, please, test if the additional overhead from creating smart keys/values is less than gain from RAC wait events.

    Also for now you can check "Interconnect Ping Latency Stats" in AWR to see the latency. If it is within 1ms you are Ok. If more than 1ms look into network issues that cause such latency. Anyway, the real solution is to keep affinity of data to each instance thus avoiding communication between instances completely.

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon

    Thanks for your reply. It's old post. I just resolved this issue by doing global hash partitioning of index.