Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: April 18, 2019 - 11:49 am UTC

Last updated: August 25, 2022 - 2:58 am UTC

Version: 18.4

Viewed 10K+ times! This question is

You Asked

Hello, Ask Tom Team.

I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and there is a high cluster wait (gc buffer busy). I'm using ASSM tablespace and have tested different values in cache (started with 2,000 and now it's on 15,000 on both sequences). The issue is still there.

I was reading about Scalable Sequences to remove contention.

What can I do to solve this out?

Thanks in advanced.

Regards,

and Connor said...

"gc buffer busy" is one session waiting for another to finish working on a buffer in the cache. RAC can exacerbate these because the cache is global (ie, across nodes) so I might be waiting on a session in another instance.

Generally the way to tackle this is to reduce contention for buffers. Sequence contention normally does appear as buffer busy. The most likely culprits are:

- indexes, in particular those that increase (eg sequence number or timestamp). All the session compete for the "leading" block in order to insert rows.

- "looping" style execution plans, ie, tightly wound nested loops going after common blocks

I'd start with your indexes - look at hash partitioned indexes as a solution to this.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up

Geraldo Peralta, April 29, 2019 - 4:04 pm UTC

You mean to use hash partitioned indexes to solve this out?

If so, what would be the adverse effect?

gc remaster waits on merge statement

John, August 14, 2022 - 3:48 am UTC

Greetings!

We encountered an issue with a merge statement hanging on wait event - gc remaster.

This is Exadata environment and the database is running Oracle version 19.11 . GI version 21.3.

The merge process was running on node 3. We found that the master of the table used to merge into was running on node 1.
We reassigned the services to run on node 1 but the merge statement still hung on event gc remaster.

As a last resort, we bounced the instance-3 to resolve the issue.

Could you please give some details about - gc remaster waits.

1) What causes this wait.
2) How to resolve this 'gc remaster wait' without bouncing the instance
3) Any recommended Oracle GC parameters which will help to eliminate this wait.


Thanks!
Connor McDonald
August 15, 2022 - 5:30 am UTC

Were *all* objects involved in the MERGE on node1?

gc remaster waits

John, August 16, 2022 - 3:18 am UTC

Hi Connor,

>> Were *all* objects involved in the MERGE on node1?

I believe the only the table which was used in 'merge into ' was on node1.
When the session was waiting on "gc remaster waits" ,, v$session.row_wait_obj# was constantly pointing to the table used in merge into <table>.


Thanks!
Connor McDonald
August 25, 2022 - 2:58 am UTC

1) What causes this wait.

The wait is when we decide that an object that is mastered on instance "X" is being repeatedly accessed from instance "Y", and so we want to now shift the mastering of the object to "Y" for subsequent performance benefit.

2) How to resolve this 'gc remaster wait' without bouncing the instance

I've seen instances (ie, occasions not database instances) where a MERGE results in multiple passes through the source tables being read. Is it possible to run your MERGE with a level 8 trace enabled? ie

exec dbms_monitor.session_trace_enable(waits=>true)

It would be interesting to see those low level waits to see volume and duration.

3) Any recommended Oracle GC parameters which will help to eliminate this wait.

DRM can be disabled or parameters set to ensure it does not happen, but generally this is not something you want to do because DRM is a good thing. There are some hidden parameters that can be set, but you'd need to log a call with Support to get their endorsement first.

There are also means to manually remaster an object (Support can advise you how to do this) which means a quick workaround might be to remaster all objects involved to the same instance.

gc remaster waits

A reader, August 24, 2022 - 4:02 pm UTC

Hi Connor
Just wondering if you got any update on this.

Thanks for your time.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.