Database Reference
In-Depth Information
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, current_obj#,
current_file#, current_block#, con_id
HAVING COUNT(*) >5
)
WHERE rownum <101
)
SELECT * FROM
(SELECT a.inst_id, a.con_id, owner, object_name, object_type, current_file#,
current_block#, cnt
FROM ash_gc a, cdb_objects o
WHERE (a.current_obj# =o.object_id (+) and a.con_id=o.con_id(+) )
AND a.current_obj# >=1
UNION
SELECT inst_id, a.con_id, '', '', 'Undo Header/Undo block' ,
current_file#, current_block#, cnt
FROM ash_gc a
WHERE a.current_obj#=0
UNION
SELECT inst_id,a.con_id, '', '', 'Undo Block' ,
current_file#, current_block#, cnt
FROM ash_gc a
WHERE a.current_obj#=-1
)
ORDER BY 7 DESC
/
In summary, event waits for gc buffer busy acquire/release is a symptom of another problem, and you need to
investigate the layers underneath to identify the root cause.
DRM
As discussed earlier in this chapter, the mastership of gc resources is distributed among all active instances. If a
resource is locally mastered, then the lock on that resource can be acquired using the affinity-locking scheme. With
the affinity-locking scheme, the FG process can query GRD in the local instance, identify current locks, and acquire
locks typically without any involvement of the background processes. This affinity-locking scheme improves the
performance of the application, as the waits for grants are eliminated or reduced.
What if an object obj1 is accessed heavily by an instance? In that scenario, wouldn't it be better to remaster that
object to that instance so that every block of that object obj1 is locally mastered? With locally mastered locks, the
application can perform better, since waits for grants will be minimal. That's the idea behind the DRM feature. For
example, a tkprof output file of a batch program accessing few tables aggressively is shown as follows. Over 1,050 s was
spent waiting for grants. These waits for grants can be avoided if those heavily accessed objects are locally remastered.
