Database Software

Hi,

We have an Update statement running in the RAC environment, usually users connected to the service that run on the first two nodes of the cluster will be issuing the update statement. and these are the following waits related to that particular update statement obtained from the gV$active_session_history view.


Here are the observed things.

The table size is just 170MB and more over the statistics for this particular table are last_analyzed in 2009.



INST_ID EVENT SUM(WAIT_TIME+TIME_WAITED)
---------- -------------------------------------------
1 db file sequential read 8115
1 gc cr block 2-way 9110
1 gc current block 3-way 10970
1 read by other session 18760
1 gc current block busy 30457
1 gc cr block 3-way 54155
1 gc cr block busy 397674
1 gc cr multi block request 483379
1 1145628
1 gc cr block lost 5422238
1 gc buffer busy acquire 13597698
2 gc current block 2-way 1212
2 latch: checkpoint queue latch 18100
2 gc cr block busy 45876
2 gc current grant busy 62472
2 gc current block 3-way 65820
2 gc current block busy 86190
2 latch free 113561
2 gc current grant 2-way 137344
2 gc cr block 3-way 195308
2 gc cr block 2-way 266081
2 buffer busy waits 479797
2 latch: cache buffers chains 883226
2 4784887
2 gc cr block lost 6245743
2 gc buffer busy acquire 14482453

With the above waits in consideration, we are thinking of the performing the following steps.
1. Increase the PCT free for the table.
2. collect the statistics and rebuild the indexes associated with the table with the same PCT free as changedto the table.


Can any one please suggest an alernative strategy that we can follow to resolve this issue. We believe that most of the waits will be reduced once we perform the above two steps. please suggest any other things that we can consider.

DB:11.1.0.7
PF: AIX 5.3
Nodes 4



Thanks,
900486