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;