Database Software

I have situation here to discuss. In a 3-node RAC setup which is Logical standby DB; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-

EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS
CPU time 5,802 34.9
RFS ping 15 5,118 33,671 30.8 Other
Log file sequential read 234,831 5,036 21 30.3 System I/O

Sql*Net more data from
client 24,171 1,087 45 6.5 Network

Db file sequential read 130,939 453 3 2.7 User I/O

Findings:-
On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.

Environment :- (Oracle- 10.2.0.4.0, O/S - AIX .3)

1)other node awr shows "log file sync" - is it due to oversized log buffer?
2)Network wait events can be reduced by tweaking SDU & TDU values based on MDU.
3) Why ARCH processes taking much to archives filled redo logs; is it issue with slow disk I/O?

Regards

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXPDB 4123595889 XXX2p2 2 10.2.0.4.0 YES sipd207

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1053 04-Apr-11 18:00:02 59 7.4
End Snap: 1055 04-Apr-11 20:00:35 56 7.5
Elapsed: 120.55 (mins)
DB Time: 233.08 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 3,728M 3,728M Std Block Size: 8K
Shared Pool Size: 4,080M 4,080M Log Buffer: 14,332K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 245,392.33 10,042.66
Logical reads: 9,080.80 371.63
Block changes: 1,518.12 62.13
Physical reads: 7.50 0.31
Physical writes: 44.00 1.80
User calls: 36.44 1.49
Parses: 25.84 1.06
Hard parses: 0.59 0.02
Sorts: 12.06 0.49
Logons: 0.05 0.00
Executes: 295.91 12.11
Transactions: 24.43

% Blocks changed per Read: 16.72 Recursive Call %: 94.18
Rollback per transaction %: 4.15 Rows per Sort: 53.31

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.92 In-memory Sort %: 100.00
Library Hit %: 99.83 Soft Parse %: 97.71
Execute to Parse %: 91.27 Latch Hit %: 99.79
Parse CPU to Parse Elapsd %: 15.69 % Non-Parse CPU: 99.95

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 83.60 84.67
% SQL with executions>1: 97.49 97.19
% Memory for SQL w/exec>1: 97.10 96.67

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 4,503 32.2
RFS ping 168 4,275 25449 30.6 Other
log file sequential read 183,537 4,173 23 29.8 System I/O
SQL*Net more data from client 21,371 1,009 47 7.2 Network
RFS write 25,438 343 13 2.5 System I/O
-------------------------------------------------------------
RAC Statistics DB/Inst: UDAS2PDB/udas2p2 Snaps: 1053-1055

Begin End
----- -----
Number of Instances: 3 3


Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 0.78 0.03
Global Cache blocks served: 1.18 0.05
GCS/GES messages received: 131.69 5.39
GCS/GES messages sent: 139.26 5.70
DBWR Fusion writes: 0.06 0.00
Estd Interconnect traffic (KB) 68.60


Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 99.91
Buffer access - remote cache %: 0.01
Buffer access - disk %: 0.08


Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 0.5

Avg global cache cr block receive time (ms): 0.9
Avg global cache current block receive time (ms): 1.0

Avg global cache cr block build time (ms): 0.0
Avg global cache cr block send time (ms): 0.1
Global cache log flushes for cr blocks served %: 2.9
Avg global cache cr block flush time (ms): 4.6

Avg global cache current block pin time (ms): 0.0
Avg global cache current block send time (ms): 0.1
Global cache log flushes for current blocks served %: 0.1
Avg global cache current block flush time (ms): 5.0

Global Cache and Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 0.1
Avg message sent queue time on ksxp (ms): 0.6
Avg message received queue time (ms): 0.0
Avg GCS message process time (ms): 0.0
Avg GES message process time (ms): 0.1

% of direct sent messages: 31.57
% of indirect sent messages: 5.17
% of flow controlled messages: 63.26
-------------------------------------------------------------

Time Model Statistics DB/Inst: UDAS2PDB/udas2p2 Snaps: 1053-1055
-> Total time in database user-calls (DB Time): 13984.6s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 7,270.6 52.0
DB CPU 4,503.1 32.2
parse time elapsed 506.7 3.6
hard parse elapsed time 497.8 3.6
sequence load elapsed time 152.4 1.1
failed parse elapsed time 19.5 .1
repeated bind elapsed time 3.4 .0
PL/SQL execution elapsed time 0.7 .0
hard parse (sharing criteria) elapsed time 0.3 .0
connection management call elapsed time 0.3 .0
hard parse (bind mismatch) elapsed time 0.0 .0
DB time 13,984.6 N/A
background elapsed time 869.1 N/A
background cpu time 276.6 N/A
-------------------------------------------------------------

Wait Class DB/Inst: UDAS2PDB/udas2p2 Snaps: 1053-1055
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
System I/O 529,934 .0 4,980 9 3.0
Other 582,349 37.4 4,611 8 3.3
Network 279,858 .0 1,009 4 1.6
User I/O 54,899 .0 317 6 0.3
Concurrency 136,907 .1 58 0 0.8
Cluster 60,300 .0 41 1 0.3
Commit 80 .0 10 130 0.0
Application 6,707 .0 3 0 0.0
Configuration 17,528 98.5 1 0 0.1
-------------------------------------------------------------

Wait Events DB/Inst: UDAS2PDB/udas2p2 Snaps: 1053-1055
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
RFS ping 168 .0 4,275 25449 0.0
log file sequential read 183,537 .0 4,173 23 1.0
SQL*Net more data from clien 21,371 .0 1,009 47 0.1
RFS write 25,438 .0 343 13 0.1
db file sequential read 54,680 .0 316 6 0.3
DFS lock handle 97,149 .0 214 2 0.5
log file parallel write 104,808 .0 157 2 0.6
db file parallel write 143,905 .0 149 1 0.8
RFS random i/o 25,438 .0 86 3 0.1
RFS dispatch 25,610 .0 56 2 0.1
control file sequential read 39,309 .0 55 1 0.2
row cache lock 130,665 .0 47 0 0.7
gc current grant 2-way 35,498 .0 23 1 0.2
wait for scn ack 50,872 .0 20 0 0.3
enq: WL - contention 6,156 .0 14 2 0.0
gc cr grant 2-way 16,917 .0 11 1 0.1
log file sync 80 .0 10 130 0.0
Log archive I/O 3,986 .0 9 2 0.0
control file parallel write 3,493 .0 8 2 0.0
latch free 2,356 .0 6 2 0.0
ksxr poll remote instances 278,473 49.4 6 0 1.6
enq: XR - database force log 2,890 .0 4 1 0.0
enq: TX - index contention 325 .0 3 11 0.0
buffer busy waits 4,371 .0 3 1 0.0
gc current block 2-way 3,002 .0 3 1 0.0
LGWR wait for redo copy 9,601 .2 2 0 0.1
SQL*Net break/reset to clien 6,438 .0 2 0 0.0
latch: ges resource hash lis 23,223 .0 2 0 0.1
enq: WF - contention 32 6.3 2 62 0.0
enq: FB - contention 660 .0 2 2 0.0
enq: PS - contention 1,088 .0 2 1 0.0
library cache lock 869 .0 1 2 0.0
enq: CF - contention 671 .1 1 2 0.0
gc current grant busy 1,488 .0 1 1 0.0
gc current multi block reque 1,072 .0 1 1 0.0
reliable message 618 .0 1 2 0.0
CGS wait for IPC msg 62,402 100.0 1 0 0.4
gc current block 3-way 998 .0 1 1 0.0
name-service call wait 18 .0 1 57 0.0
cursor: pin S wait on X 78 100.0 1 11 0.0
os thread startup 16 .0 1 53 0.0
enq: RO - fast object reuse 193 .0 1 3 0.0
IPC send completion sync 652 99.2 1 1 0.0
local write wait 194 .0 1 3 0.0
gc cr block 2-way 534 .0 0 1 0.0
log file switch completion 17 .0 0 20 0.0
SQL*Net message to client 258,483 .0 0 0 1.5
undo segment extension 17,282 99.9 0 0 0.1
gc cr block 3-way 286 .7 0 1 0.0
enq: TM - contention 76 .0 0 4 0.0
PX Deq: reap credit 15,246 95.6 0 0 0.1
kksfbc child completion 5 100.0 0 49 0.0
enq: TT - contention 141 .0 0 2 0.0
enq: HW - contention 203 .0 0 1 0.0
RFS create 2 .0 0 115 0.0
rdbms ipc reply 339 .0 0 1 0.0
PX Deq Credit: send blkd 452 20.1 0 0 0.0
gcs log flush sync 128 32.8 0 2 0.0
latch: cache buffers chains 128 .0 0 1 0.0
library cache pin 441 .0 0 0 0.0
Wait Events DB/Inst: UDAS2PDB/udas2p2 Snaps: 1053-1055
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)