Cluster Wait events are high
Cluster wait events are caused by multiple nodes in the cluster needing access to the same set of data. These must be tracked down on an individual basis. Usually there are multiple statements and tables that have contention problems.
Ideally, one node would only access one subset of data in order to reduce these conflicts. If possible, different types of operations should be done on each node. For example OLAP access could be targeted to node 1 while OLTP access could be targeted to node 2.
Usually there are multiple queries attempting update the same sets of data from different nodes. In order to determine what queries is producing the contention across the nodes, do the following:
1. Determine your beginning and ending snapshots, we will use 1000 and 2000 in the example below.
2. Find the event id:
select event_id, event, count(1) cnt from dba_hist_active_sess_history
where snap_id between 1000 and 2000 and wait_class_id=3871361733 and event in (’gc cr block busy’,’gc cr multi block request’)
group by event_id, event
order by 3;
3. Select sql_id, count(1) from dba_hist_active_sess_history where snap_id between 1000 and 2000 and event_id in (results from (2) above);
4. Select sql_text from dba_hist_sqltext where sqlid in .;
The steps above will tell you the SQL statements that cause the data contention.
