Oracle RAC 환경 의 UDP 를 통한 캐시퓨전시 성능 문제를 간단히 확인하는 법
RAC구조에서 UDP 버퍼사이즈가 작아서 문제가 생길시 아래 방법을 통해 확인 하고 결과적으로 UDP 버퍼 사이즈를 키워주면 됩니다.
비효율적인 네트워크 설정 확인
– Gigabit Ethernet Interconnect에서는 UDP(User Diagram Protocol) 사용
– 확인 방법 :
SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name
[예제]
oracle@srac1:~$ sqlplus “/as sysdba”
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 19 01:06:42 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
IPC information written to the trace file
SQL> oradebug tracefile_name
/oracle/app/diag/rdbms/soldb/soldb1/trace/soldb1_ora_22881.trc
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@srac1:~$ more /oracle/app/diag/rdbms/soldb/soldb1/trace/soldb1_ora_22881.trc
* TRACE 파일 내용 중 테스트 결과가 찍혀 있다.
WAIT HISTORY
Wait Time Time since Fast reaps Wait Type Return Code
(ms) prev wait(ms) before
——— ————– ———– ——— ———–
wait delta 11 sec (11907 msec) ctx ts 0 sec (2 msec) last ts 0 msec twait secs 5 (blocked 0) curts = 11907
EXTENDED WAIT HISTORY STATS
Time Prev Ncomp Nwait Nloop Nsel Select No Svc Events
(-ms) (ms) (ms) (ms)
———- ——— ——— ——— ——— ——— ——— ———- ———–
current (0)
11908 0 0 0 0 0 0 0 0
user cpu time since last wait 0 sec 0 ticks
system cpu time since last wait 0 sec 0 ticks
locked 2
blocked 0
timed wait receives 0
fast reaps since last wait 0
context timestamp 0x2
SSKGXPT ffff80ffbdfe27d0 flags 0x5 { READPENDING } sockno 4 IP 192.168.55.191 UDP 53806 lerr 0
…
SSKGXPT ffff80ffbdefeda0 flags 0x0 sockno 10 IP 192.168.55.191 UDP 42095 lerr 0
…
– RAC Interconnect 성능과 가장 연관 있는 것은 UDP Buffer Size.
– 오라클 권장 UDP buffer size = 256K
– UDP Buffer Size가 작으면 패킷유실(Packet loss) 현상이 발생. (gc cr/current block lost 발생)
* 패킷 유실 확인
Solaris netstat -s -P udp
UDP udpInDatagrams =250263 udpInErrors = 0
udpOutDatagrams =229050 udpOutErrors = 0
HP_UX> netstat -s -p udp
udp:
57726 incomplete headers
4795 bad checksums
52931 socket overflows