Welcome to OracleGiants.com!     

Oracle 10g DBA & Business Intelligence Resources.

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.

 

Use Xattr CloudFiler™ to dedup, encrypt and move your RMAN backups to the cloud.

Recent Posts

This site recommends Burleson Consulting for DBA Support & Consulting.

We are currently paying for accepted Oracle 10g DBA & Business Intelligence articles, scripts, tips & tricks. Send yours to the editor and we will review within three weeks.

Learn more

Editorial submissions & questions: editor@oraclegiants.com

 

 

This site is moderated by an:
Oracle Certified Professional
Oracle ACE

 

  Classic Car Online - The #1 spot for classic and muscle car enthusiats
2010 OracleGiants.com