gc buffer busy acquire .. and release

RAC Wait event gc buffer busy acquire indicates that the session experiencing this event is waiting on another session on the same instance to release the block. Basically the waiting session is not able to pin a block, and will wait on this event until it can pin the block. (gc buffer busy release means the session experiencing the event is waiting on a session from remote RAC instance.)

We had intermittent gc buffer busy acquire waits in the system. I used the below query to look into the details. (I think the same strategy can be used for gc buffer busy release.)

SELECT instance_number, sql_opname,event, p1, p2, p3,  current_obj#,  COUNT (*) cnt
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TIMESTAMP '2016-09-14  14:02:00'
      AND TIMESTAMP '2016-09-14  14:14:00'
  AND wait_class = 'Cluster'
GROUP BY instance_number, event, sql_opname,p1, p2, p3, current_obj#
ORDER BY cnt DESC
 /

INST SQL_OPNAME EVENT                        P1         P2 P3 CURRENT_OBJ#  CNT
---- ---------- ------------------------- ----- ---------- -- ------------ ----
   2 SELECT     gc buffer busy acquire     3368    2979072  1    133868669   19
   2 SELECT     gc buffer busy acquire     3070    2085712  1    121281015   18
   2 SELECT     gc buffer busy acquire     1644    1298853  1    121281059   18
   2 SELECT     gc buffer busy acquire     3714    3564678  1    121281111   17
   2 SELECT     gc buffer busy acquire     3815    1114002  1    121281038   16
   2 SELECT     gc buffer busy acquire     3702    2919988  1    121281111   14
   2 SELECT     gc buffer busy acquire     4185    2813351  1    133868677   14
   2 SELECT     gc buffer busy acquire     3068    1428520  1    103874215   14
   2 SELECT     gc buffer busy acquire     3852     637200  1    121281015   13

gc buffer busy acquire and gc buffer busy release are basically buffer busy waits.
Buffer busy waits are usually caused by:

  • For tables with index on sequence generated column, Inserts will cause the right most leaf block of the index to grow causing contention. Index growth causes index block to split too. Such index leaf block will be hot.
  • Multiple session doing Inserts will content for segment header block in non-ASSM tablespace if freelist/freelist group is low.
  • Sequence with low Cache value could lead to contention for seq$ blocks if the usage(Inserts) is high
  • CPU issue. Low CPU can cause delays during the block pinning process (kind of stuck in between)
  • Interconnect delays also can cause delays during the block pinning process

But here, these statements are Select statements. Please see sql_opname below.

  • For Select statements, buffer busy waits can happen from plan inefficiency, and high concurrent execution can increase the severity.

For gc buffer busy acquire/release, parameter p1 is the file# and p2 is the block# and p3 is the block class.These object ids (also p1,p2) map to different partitions of the same table. Block class of 1 means these are data blocks.

Using a different query I realized that most these issues point to one particular step in the plan:

SELECT sql_id, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, COUNT (*) cnt
FROM dba_hist_active_sess_history h
WHERE  sample_time BETWEEN TIMESTAMP '2016-09-14  14:02:00'
       AND TIMESTAMP '2016-09-14  14:14:00'
  AND wait_class = 'Cluster' and event='gc buffer busy acquire'
GROUP BY sql_id, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
ORDER BY cnt DESC;

SQL_ID        SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID        CNT
------------- ------------------- ---------------- ----------
0bp0244jtyykx           800453358                8         82
dcr0x1ka230k9           800453358                8         77
faz4zbwy58ar3           800453358                8         75
dchgsfds58akn           800453358                8         70
56tx1khv7vmrb           800453358                8         67
cj30tcbaqzjdc           800453358                8         63
d1swp67w795m9           800453358                8         61
91awx3zf97xgz           800453358                8         56
6rzmd2y2fwx44           980965006                8         47
3urxktq0h1u6h           800453358                8         36
8axjuyxs4zss7           800453358                8         33
8xzvgfd6drux2           800453358                8         25
4zu7k9gztappm           800453358                8         17
5cp8s99wrdwtx           800453358                8         16
90vcv0wqmx5ku           800453358                8         14
69hb3jbwwgzcy           800453358                8         11
2g2c9ratbnwwu          2426697778               14          8

Line 8 in the plan with hash value 800453358 is experiencing the wait. Though the sql_ids are different, they are basically same sql that differ only for literals.

Plan hash value: 800453358
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                              |       |       |   139K(100)|          |       |       |
|   1 |  SORT UNIQUE                                      |                              |  4919 |   365K|   139K  (1)| 00:00:22 |       |       |
|   2 |   UNION-ALL                                       |                              |       |       |            |          |       |       |
|   3 |    CONCATENATION                                  |                              |       |       |            |          |       |       |
|   4 |     FILTER                                        |                              |       |       |            |          |       |       |
|   5 |      HASH JOIN OUTER                              |                              |   129K|  9596K| 69683   (1)| 00:00:11 |       |       |
|   6 |       NESTED LOOPS OUTER                          |                              | 23945 |  1613K| 39284   (1)| 00:00:07 |       |       |
|   7 |        NESTED LOOPS                               |                              |  4925 |   298K| 24505   (1)| 00:00:04 |       |       |
|   8 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| FIN_STMT_ALL                 |  9475 |   314K|  5550   (1)| 00:00:01 | ROWID | ROWID |
|   9 |          INDEX RANGE SCAN                         | FIN_STMT_ACT_IX_5             |  9475 |       |   124   (0)| 00:00:01 |       |       |
..

It seems there is some issue with TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED step in the plan. This is new in 12c and not much information is available on it. Oracle documentation says:

The BATCHED access means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.

I want to optimize the plan avoiding the TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED and see what happens. Need to investigate the suitability of the index FIN_STMT_ACT_IX_5. TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED need not be the culprit. Need to check if SQL Tuning Advisor has some recommendations.

I will keep you posted.

98 thoughts on “gc buffer busy acquire .. and release”

  4. Undeniably consider that which you said. Your favourite reason seemed to be on the web the simplest factor to keep in mind of. I say to you, I definitely get irked whilst other folks consider issues that they just don’t recognize about. You controlled to hit the nail upon the top and also defined out the entire thing with no need side-effects , other people could take a signal. Will probably be back to get more. Thanks

    Reply

  7. Hi there, just became aware of your blog through Google, and found that it is truly informative. I am going to watch out for brussels. I will appreciate if you continue this in future. A lot of people will be benefited from your writing. Cheers!

    Reply

  10. I don’t even know how I ended up here, but I thought this post was great. I do not know who you are but certainly you are going to a famous blogger if you are not already 😉 Cheers!

    Reply

  11. Appreciate it for helping out, good information. “Those who restrain desire, do so because theirs is weak enough to be restrained.” by William Blake.

    Reply

  12. Keep up the excellent piece of work, I read few blog posts on this website and I conceive that your web site is rattling interesting and has got bands of wonderful info .

    Reply

  13. Hi there! This post couldn’t be written any better! Reading this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Pretty sure he will have a good read. Thanks for sharing!

    Reply

  17. Hi, Neat post. There’s a problem with your web site in internet explorer, would test this… IE still is the market leader and a big portion of people will miss your great writing because of this problem.

    Reply

  19. I have been absent for some time, but now I remember why I used to love this web site. Thank you, I will try and check back more frequently. How frequently you update your site?

    Reply

  20. I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my view, if all website owners and bloggers made good content as you did, the net will be much more useful than ever before.

    Reply

  23. I am often to blogging and i really appreciate your content. The article has really peaks my interest. I am going to bookmark your site and keep checking for new information.

    Reply

  25. Have you ever thought about creating an e-book or guest authoring on other blogs? I have a blog centered on the same topics you discuss and would really like to have you share some stories/information. I know my subscribers would enjoy your work. If you are even remotely interested, feel free to shoot me an email.

    Reply

  26. You have mentioned very interesting details! ps nice internet site. “Formal education will make you a living self-education will make you a fortune.” by Jim Rohn.

    Reply

  27. This design is spectacular! You definitely know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Wonderful job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!

    Reply

  30. certainly like your web-site however you need to check the spelling on quite a few of your posts. Several of them are rife with spelling issues and I in finding it very bothersome to tell the truth on the other hand I¦ll surely come back again.

    Reply

  32. I together with my pals have already been going through the excellent techniques on your site while then came up with a horrible suspicion I never thanked the site owner for those techniques. Most of the women ended up for that reason joyful to learn them and have in effect seriously been tapping into these things. Appreciate your being really accommodating and then for making a choice on varieties of good information most people are really desperate to be informed on. My personal sincere regret for not saying thanks to earlier.

    Reply

  33. Aw, this was a really nice post. In concept I wish to put in writing like this moreover – taking time and precise effort to make a very good article… but what can I say… I procrastinate alot and in no way appear to get one thing done.

    Reply

  34. An interesting discussion is worth comment. I think that you should write more on this topic, it might not be a taboo subject but generally people are not enough to speak on such topics. To the next. Cheers

    Reply

  40. I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!…

    Reply

  53. Sumatra Slim Belly Tonic review

    Reply

  66. Dentavim is a revolutionary dietary supplement designed to promote oral health by addressing two major concerns: teeth’ cleanliness and gums’ health. Unlike typical oral hygiene products that focus solely on surface treatment, Dentavim dives deeper into the issues often caused by environmental factors, especially particulate matter, which can lead to persistent bad breath and stubborn stains. This product contains a proprietary blend of six potent nutrients derived from natural sources to enhance dental hygiene and overall well-being.

    Reply

  95. Fitspresso is a brand-new natural weight loss aid designed to work on the root cause of excess and unexplained weight gain. The supplement uses an advanced blend of vitamins, minerals, and antioxidants to support healthy weight loss by targeting the fat cells’ circadian rhythm

    Reply

  96. Fitspresso is a brand-new natural weight loss aid designed to work on the root cause of excess and unexplained weight gain. The supplement uses an advanced blend of vitamins, minerals, and antioxidants to support healthy weight loss by targeting the fat cells’ circadian rhythm

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *