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

Vimalshu
Vimalshu Member Posts: 43 Red Ribbon
edited Mar 25, 2020 1:29AM in Real Application Clusters
«1

Answers

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon
    edited Mar 22, 2020 11:34PM

    I have observed below statement causing  gc buffer busy acquire wait events.

    SELECT PHONE_ID, PHONE_NUMBER, UPPER(PHONE_TYPE) PHONE_TYPE, EXTENSION, PHONE_COUNTRY_CODE, COUNTRY_DIALING_CODE, VALID_YN FROM NAME_PHONE WHERE NAME_ID = 30618380 AND PHONE_ROLE = 'EMAIL' AND PHONE_TYPE = CASE WHEN NULL IS NULL OR PHONE_ROLE <> 'EMAIL' THEN PHONE_TYPE ELSE NULL END AND INACTIVE_DATE IS NULL ORDER BY DECODE(PRIMARY_YN, 'Y', 0, NVL(DISPLAY_SEQ, 9999));

      PHONE_ID PHONE_NUMBER PHONE_TYPE EXTENSION            PHO COUNTRY_DIALING_CODE V

    ---------- ------------------------------ -------------------- -------------------- --- -------------------- -

      30910115 [email protected] EMAIL

    execution_plan.png

    Statistics

    ----------------------------------------------------------

             12  recursive calls

              0  db block gets

              6  consistent gets

              0  physical reads

              0  redo size

           1024  bytes sent via SQL*Net to client

            608  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              1  sorts (memory)

              0  sorts (disk)

              1  rows processed

    Details about NAME_PHONE TABLE:

    SQL> select count(*) from NAME_PHONE;

      COUNT(*)

    ----------

      14715951

    SQL> select sum(bytes/1024/1024/1024) “SIZE_IN_GB” from dba_segments where segment_name='NAME_PHONE';

    SIZE_IN_GB

    -------------------------

    2.1875

    INDEX_DETAILS:

    INDEX_DETAILS.png

    As per awr report ,It seems problem is in index NAME_PHONE_UNIQUE. I read blog https://orainternals.wordpress.com/2010/09/27/gc-buffer-busy-waits/

    And as per this blog if problem is due to select statement then need to tune it.

    cid:image009.png@01D5FDD7.994E7730

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Mar 23, 2020 12:08PM

    The 'gc buffer busy acquire' wait event occurs when a session is trying to access a block. But that session is waiting for another session to finish opening that block. The other session is on the same instance as the waiting session. If the other session were on a different instance, the wait event would be 'gc buffer busy release'. In either case, the solutions are the same.

    For starters, make sure your RAC nodes are not CPU bound. High rates of CPU can cause GC operations to slow down.

    As always, tuning the SQL statement is a good idea. The faster the SQL statement runs, the less you will see this wait event.

    Lastly, a high number of occurrences of this wait event may mean you need a larger Buffer Cache so that the blocks stay in the cache longer.

    Cheers,

    Brian

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon
    edited Mar 23, 2020 1:59PM

    Hi Brian,

    Thanks for your reply.

    RAC node are not cpu bound and memory(sga) is 144GB. Which is quite sufficient.

    SQL> show parameter cpu;

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    cpu_count                            integer     48

    As per awr report ,Above sql statement is responsible for gc buffer busy acquire.But When I ran this query then it is running very fast.

    Shall I proceed with hash index partitioning of the index NAME_PHONE_UNIQUE.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Mar 23, 2020 2:32PM

    You can try partitioning, but that won't stop the block from being aged out of the cache. If the table is small, you might get better results from leveraging the KEEP buffer cache. If you are seeing this wait event have a high number of occurrences, then I'd wonder why the block gets aged out of the cache. Or maybe its different blocks. You'd need to see the P1 and P2 values for this wait event to know if it is the same block or different blocks.

    memory(sga) is 144GB. 

    Do you use SGA_TARGET or MEMORY_TARGET? If so, while you may have devoted a lot to the SGA, Oracle is free to manage its components. How big is the Buffer Cache?

    Cheers,

    Brian

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon
    edited Mar 24, 2020 4:40AM

    Hi Brian,

    We have set sga_target 144Gb.So we did not set individual memory components.As of now default buffer cache size is 119 Gb ,I have attached screenshot below.Can you please let me know how do you conclude that it is because of buffer cache size and block is getting age out.Did you get hint   from awr report,Can you please point me on which section you saw.

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon
    edited Mar 24, 2020 4:37AM
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Mar 24, 2020 11:09AM
    Can you please let me know how do you conclude that it is because of buffer cache size and block is getting age out.

    Well you know the buffer is being aged out of the cache because of the presence of this wait event. Two sessions are trying to read that data block and one session is waiting for the other to complete the read, hence this event.

    But as I said before, you'd need to see the P1 and P2 values for this wait event to know if it is the same block or different blocks. IIRC, P1 is the file# and P2 is the block# for this event. If those are consistently the same, then its the same block. If they differ, then you have different blocks at play. You can query DBA_SEGMENTS to find out which segment(s) are part of this contention.

    To know if the Buffer Cache is adequately sized, I'd go back to the basics. Query V$SGA_TARGET_ADVICE. Do your estimated physical reads go down as you increase the size of the SGA?

    Cheers,

    Brian

  • Vimalshu
    Vimalshu Member Posts: 43 Red Ribbon
    edited Mar 24, 2020 2:10PM

    Hi Brian,

    Wait event is due to different block and below objects(snippets from awr) are part of contention.

    TOP_DB_OBJECTS.png

    There is not significant change in estimated physical read by increase of size of sga.

    sga_advisory.png

    Can you also please look into below statistics,

    Global_cache_buffer_busy.png

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Mar 24, 2020 2:34PM

    Looks like a couple of different segments. I'll fall back to a suggestion I made earlier...you might want to investigate using the KEEP cache for those tables and indexes.

    Cheers,
    Brian

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Mar 24, 2020 2:40PM

    One other thing....this is from the original post to start this thread:

    gc buffer busy acquire1,294,57436.3K28.01ms19.4Cluster

    This wait event is the top non-CPU item on the list. But look at the average wait time...28.01ms which is not that big. What drives this up is the total number of occurrences, over 1.2 million times this event is seen.

    Leveraging the KEEP cache for those hot objects will stop them from aging out of the cache. You need to size the keep cache to hold the table and its indexes.

    From your V$SGA_TARGET_ADVICE output, you could increase your SGA by a factor of 1.375 and you may see some benefit. And after you've been on that increased SGA size, V$SGA_TARGET_ADVICE may show you something else. It can be an iterative process.

    But I still think the KEEP cache might be a way to solve your issue.

    Cheers,

    Brian