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.
Way cool, some valid points! I appreciate you making this article available, the rest of the site is also high quality. Have a fun.
I like this website because so much useful material on here : D.
I have recently started a web site, the info you provide on this web site has helped me tremendously. Thank you for all of your time & work.
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
Hi there, You’ve done an excellent job. I’ll definitely digg it and personally suggest to my friends. I’m sure they’ll be benefited from this web site.
I got what you intend, thanks for posting.Woh I am lucky to find this website through google.
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!
Hello there! Would you mind if I share your blog with my facebook group? There’s a lot of people that I think would really appreciate your content. Please let me know. Cheers
Hi there, I found your blog via Google while searching for a related topic, your website came up, it looks good. I’ve bookmarked it in my google bookmarks.
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!
Appreciate it for helping out, good information. “Those who restrain desire, do so because theirs is weak enough to be restrained.” by William Blake.
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 .
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!
Great site. Lots of helpful info here. I’m sending it to some friends ans additionally sharing in delicious. And naturally, thanks to your sweat!
You are a very capable person!
Hello.This article was really fascinating, especially since I was investigating for thoughts on this issue last Saturday.
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.
I have been checking out a few of your posts and i can claim pretty clever stuff. I will surely bookmark your blog.
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?
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.
Wow! Thank you! I permanently needed to write on my blog something like that. Can I implement a part of your post to my site?
I am continuously browsing online for articles that can help me. Thx!
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.
What is Gluco6? Gluco6 is a revolutionary dietary supplement designed to help individuals manage their blood sugar levels naturally.
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.
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.
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!
Very interesting points you have mentioned, appreciate it for putting up.
vavada слоты
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.
Keep functioning ,impressive job!
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.
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.
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
I truly appreciate your piece of work, Great post.
I really happy to find this internet site on bing, just what I was looking for : D too bookmarked.
Hey, you used to write wonderful, but the last few posts have been kinda boring?K I miss your tremendous writings. Past few posts are just a little bit out of track! come on!
Some really excellent articles on this site, thank you for contribution. “Such evil deeds could religion prompt.” by Lucretius.
Wohh precisely what I was searching for, regards for putting up.
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!…
Sight Care reviews
Fit spresso reviews
Cellu care reviews
Lottery defeater software review
Sightcare
Fitspresso reviews
Sight Care reviews
Nanodefense pro reviews
I have recently started a blog, the information you offer on this site has helped me greatly. Thank you for all of your time & work.
Lottery defeater reviews
Cellucare review
Cellu care review
Sumatra Slim Belly Tonic review
Lottery defeater system review
Fitspresso reviews
Fitspresso reviews
Lottery defeater software review
Lottery defeater review
Opinipix
Opinipix é golpe
Ikaria Lean Belly Juice reviews
Fitspresso
Fitspresso
Fitspresso reviews
Fitspresso reviews
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.
Fitspresso
Fitspresso review
Fitspresso reviews
Fitspresso review
Fitspresso
Fitspresso review
Fitspresso
I think you have remarked some very interesting details , thanks for the post.
Fitspresso reviews
Weight loss juice
Lottery defeater software reviews
Neotonics gummies reviews
Sight Care reviews
Dentavim reviews
Hello! Do you know if they make any plugins to protect against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any tips?
Dentavim
Lottery defeater software reviews
Sightcare
Pineal XT reviews
Fitspresso review
Kerassentials
Fitspresso review
Fitspresso reviews
I like this blog so much, saved to bookmarks.
Cellucare review
Fitspresso reviews
Java Burn
Fitspresso
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
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
Fitspresso reviews
TONIC GREENS REVIEW
Sight care review
Lottery defeater reviews
Neotonics reviews
Fitspresso
Fitspresso review
LOTTERY SOFTWARE REVIEW
LOTTERY DEFEATER REVIEWS
Nanodefense pro review
Puravive review
THE GROWTH MATRIX REVIEWS
PROVADENT REVIEWS
FITSPRESSO REVIEW
LIPOZEM
Lottery defeater software review
Love Your Article https://fitspresso.shorthandstories.com/
SUGAR DEFENDER REVIEWS
The genius wave system
LOTTERY DEFEATER SOFTWARE REVIEWS
Sight Care reviews
Love Your Article https://fitspresso.shorthandstories.com/
The genius wave system
Love Your Article https://fitspresso.shorthandstories.com/
Lottery defeater system
Loved Your Article https://synogut.shorthandstories.com
Loved Your Article https://synogut.shorthandstories.com
Interesting Read https://lotterydefeatersoftware.shorthandstories.com
Lottery defeater software
sugar defender benefits
The genius wave
Interesting Read https://lotterydefeatersoftware.shorthandstories.com
Dentavim
PLANTSULIN REVIEWS
Puravive
GLUCO FREEDOM REVIEWS
Erecprime reviews
LIPOZEM
sugar defender ingredients
Pronerve reviews
Lottery defeater reviews
ALPHABITES
Dentavim reviews
Tonic Greens
Pronerve review
Billionaire brain wave review
Pineal XT
Provadent review
Lottery defeater system review
Plant sulin
Interesting Read https://worst.today
Liv care Liver support
Flexigenics review
Boostaro reviews
Lottery defeater system
Boostaro review
Fitspresso reviews
Sightcare review
Lipozem
The genius wave
Pura vive reviews
Nanodefense pro
Lipozem reviews
Boostaro
Alpha bites review
Quietum Plus review
nitric boost real reviews
The genius wave software
Alphabites reviews
Lipozem
PROVADENT
SUGAR DEFENDER REVIEW
Alphabites
ALPHA BITES REVIEWS
LIPOZEM REVIEW
nitric boost amazon
Alphabites review
Interesting Read https://finance-daily.shorthandstories.com/how-to-borrow-money-from-cashapp/
nagano reviews
Plantsulin review
Alphabites reviews
Lipozem reviews
Lottery defeater system reviews
LOTTERY DEFETAER REVIEWS
ALPHA BITES REVIEWS
Fitspresso
Alpha bites review
Alpha bites review
LOTTERY DEFEATER
BOOSTARO REVIEW
FITSPRESSO
BOOSTARO REVIEWS
NAGANO LEAN BODY TONIC
ALPHA BITES REVIEWS
Lottery defeater system
Lipozem review
Fitspresso reviews
Alphabites
TURMERIC HACK FOR WEIGHT LOSS
LIPOZEM
PURAVIVE REVIEWS
Lipo Slim reviews
I like this post, enjoyed this one regards for putting up.
Alphabites review
NERVOVIVE REVIEWS
Alphabites
Real wonderful info can be found on web site.
FREE SUGAR PRO REVIEWS
Alphabites reviews
Lipozem reviews
LipoSlim review
Alpha bites reviews
Lipo Slim review
Alphabites reviews
Alphabites reviews
Femi pro reviews
MANNAFLUX
Kerassentials reviews
Alphabites
Boostaro reviews
TONIC GREENS REVIEWS
Dentavim
LEANBIOME REVIEWS
SUGAR DEFENDER REVIEWS
how does Booster XT work
Alpha bites review
Well I truly liked studying it. This subject offered by you is very practical for accurate planning.
Alpha bites review
pokračovat v tom, abyste vedli ostatní.|Byl jsem velmi šťastný, že jsem objevil tuto webovou stránku. Musím vám poděkovat za váš čas
Kan du anbefale andre blogs / websteder / fora, der beskæftiger sig med de samme emner?
how does Booster XT work
NEUROQUIET REVIEWS
Alpha bites reviews
Fitspresso
apreciariam o seu conteúdo. Por favor, me avise.
také jsem si vás poznamenal, abych se podíval na nové věci na vašem blogu.|Hej! Vadilo by vám, kdybych sdílel váš blog s mým facebookem.
Mitolyn reviews
Lipozem
Mitolyn
Mitolyn
MITOLYN
Mitolyn
Mitolyn review
e dizer que gosto muito de ler os vossos blogues.
Ищете, где заказать лента конвейерная? У нас вы найдете оптимальные варианты для ваших нужд. Загляните в наш каталог, чтобы выбрать подходящее решение.
Thanks for sharing your ideas. I might also like to state that video games have been actually evolving. Modern technology and improvements have aided create realistic and enjoyable games. These kind of entertainment games were not actually sensible when the actual concept was being tried out. Just like other styles of electronics, video games also have had to evolve via many generations. This itself is testimony towards fast growth of video games.
It contains fastidious material.|I think the admin of this website is actually working hard in favor of his site,
Booster XT benefit
|Tato stránka má rozhodně všechny informace, které jsem o tomto tématu chtěl a nevěděl jsem, koho se zeptat.|Dobrý den! Tohle je můj 1. komentář tady, takže jsem chtěl jen dát rychlý
Lottery defeater review
Fitspresso
Alphabites review
Obrigado|Olá a todos, os conteúdos existentes nesta
You need to participate in a contest for top-of-the-line blogs on the web. I’ll recommend this site!
Alpha bites review
Nicoya Pura Tea review
meget af det dukker op overalt på internettet uden min aftale.
Cost-free naked hairy pussy photographs collection of rare hairy pussies porn pics with naked mature hairy pussy in addition to naked hairy pussy girls – this link. Sexy hairy pussy pictures galleries for bushy crotch lovers.
horny teens red pubic hair pics
hair sex pics
argentinian hairy milf
Booster XT side effects
Obrigado|Olá a todos, os conteúdos existentes nesta
Mitolyn reviews
Mitolyn
Com tanto conteúdo e artigos, vocês já se depararam com algum problema de plágio?
Mitolyn
Děkuji|Ahoj všem, obsah, který je na této stránce k dispozici.
Mitolyn review
Mitolyn reviews
Tak Hej der til alle, det indhold, der findes på denne
Mitolyn review
Mitolyn review
Mitolyn reviews
que eu mesmo criei ou terceirizei, mas parece que
Tak skal du have!|Olá, creio que este é um excelente blogue. Tropecei nele;
ALHA BITES REVIEW
Lipozem reviews
ALPHA BITES
que eu mesmo criei ou terceirizei, mas parece que
PROSTAZEN REVIEWS
LIPOZEM
Tak skal du have!|Olá, creio que este é um excelente blogue. Tropecei nele;
Mitolyn reviews
Mitolyn review
Fitspresso
Fitspresso review
Kerassentials
MITOLYN
ALPHA BITES
Mitolyn review
Mitolyn reviews
MITOLYN
NAGANO LEAN BODY TONIC
buď vytvořil sám, nebo zadal externí firmě, ale vypadá to.
fortsæt det gode arbejde stipendiater. Med at have så meget indhold og artikler gør du det
Neuroquiet
Mitolyn