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!
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!
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.