Oracle

Mysterious Cluster Wait Event

There are many reasons that can lead a session to wait for a cluster event. Recently I saw in one of the AWR reports than one sql statement spends 80% of elapsed time for cluster wait. The sql was a simple update statement of a table. Table has less than 100 rows in it. Same SQL was running on both nodes of the cluster. I leaned that this sql is necessary for the application to check the connection If the database is up or not so there is no way to escape it and make it work on the same cluster. The blocks ( just 6 blocks ) were moving from one cache to another.

UPDATE APP_GATE
SET LAST_ONLINE_TIME = sysdate
WHERE APP_GATE_CODE = :gate_code;

We learned that most of the time the rows that are updated from one node are not reached from the other node. And we decided to put every row on a different block. This way the blocks will not be transferred frequently.

ALTER TABLE APP_GATE MOVE PCTFREE 90 TABLESPACE DATA_TS ;

Increasing the free percent in the blocks guarantees to have less rows in each block. After distributing the rows on more blocks SQL started not to wait on cluster wait events. And the query speed up by 0.02 seconds : ) Considering the total executions in a day, SQL now waits total of 17 min less every day.

select snap_id,
       round(buffer_gets_delta / executions_delta) buffer,
       round(elapsed_time_delta / executions_delta) elapsed,
       round(clwait_delta / executions_delta) cluster_wait,
       plan_hash_value
from dba_hist_sqlstat
where sql_id = '1vumcddpljq9t'
and executions_delta > 0
order by snap_id desc;
SNAP_ID BUFFER ELAPSED CLUSTER_WAIT PLAN_HASH_VALUE
36161 5 5327 4597 691550476
36160 5 3154 2448 691550476
36159 5 1052 265 691550476
36158 5 943 256 691550476
36157 6 23769 21480 691550476
36156 6 28919 25979 691550476
36155 6 32159 29204 691550476
36154 6 29356 26471 691550476
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s