Scribd
Upload
52 views45 pages

Diagnosing RAC Performance Issues

This document summarizes the steps taken to diagnose and resolve a sudden slow down issue on a two node Real Application Clusters (RAC) environment. The troubleshooting process involved systematically measuring performance at the operating system, database, and session levels. Key findings included an unbalanced workload on one node, long-running sessions with wait events, and network interconnect issues resolved by replacing a switch. Measuring performance using tools like AWR, ASH, and OS monitoring was essential to pinpointing and addressing the root cause of the slow down impacting business applications.

Uploaded by

behanchod
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
52 views45 pages

Diagnosing RAC Performance Issues

This document summarizes the steps taken to diagnose and resolve a sudden slow down issue on a two node Real Application Clusters (RAC) environment. The troubleshooting process involved systematically measuring performance at the operating system, database, and session levels. Key findings included an unbalanced workload on one node, long-running sessions with wait events, and network interconnect issues resolved by replacing a switch. Measuring performance using tools like AWR, ASH, and OS monitoring was essential to pinpointing and addressing the root cause of the slow down impacting business applications.

Uploaded by

behanchod
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
  • Performance Scenario
  • Introduction
  • Scenario
  • Setup
  • Troubleshooting Principle
  • Measured the OS stack
  • Checked the DB environment
  • Checked instance-wide DB performance
  • Checked session-level DB performance
  • Drilled down on the network interconnect
  • Conclusion
  • References and Tools

PerformanceScenario:

Diagnosingandresolvingsudden
slowdownontwonodeRAC
Introduction
KarlArao,OCPDBA,RHCT
SeniorConsultantatSQL*Wizard
RACuserfor3years
1st environmentonVMware
Iheart performance
Dontliketoguesswhentroubleshooting
Scenario
OneThursday
aclientcalled

TherewasaSUDDEN
slowdown
onALL oftheapplications

abigimpacttotheBusiness
Anditsrunningon

RAC RAC
nochangesonthe
RACnodesandontheapplications
Someof10gPerformanceFeatures
OEMPerformancePage
ADDM
SQLTuningadvisor
AWR(DBA_HIST_)
ASH
TimeModel(totaltimeforalldbcalls)
WaitClass(12waitclass)
Metrics(v$performancemetricdeltas)
Services
Setup
ServerandStorage:SunFire X4200(2CPU,
12GBmemory)withLUNs onEMCCX300
OS:RHEL4.3ES
Databaseandclusterware:Oracle10.2.0.3
DatabaseFiles,FlashRecoveryArea,OCR,and
VotingdiskarelocatedonOCFS2filesystems
Application:FormsandReports(6iandalso
lower)
TroubleshootingPrinciple

Systematic/Layeredapproach..
Understand..
ThenFix..

Letsgetiton!
[Link]
Monitoredthefollowing
cpu (vmstat,top,mpstat)
io (iostat)
memory(vmstat,meminfo)
network(netstat)
processinfo(top,ps)
CPUonserver1

CPUonserver2
Datafiles onserver1

Datafiles onserver2
OCR&votingdiskonserver1

OCR&votingdiskonserver2
Archivelogs onserver1

Archivelogs onserver2
FlashRecoveryAreaonserver1

FlashRecoveryAreaonserver2
Memoryonserver1

Memoryonserver2
[Link]
Comparedmypast&currentRDAofthe
database
Queryonsomev$views..aqueryonv$session
showedthatserver1hasmoreconnections
(89%ofthetotalusers)
[Link]
Thiscouldbebecauseof:
1) Theclientshavinglowerversions(<Sql*Plus8.1
orOCI8,seeNote97926.1)thatmaynotsupport
TAF(FAILOVER_MODE)andLoadBalancing
(LOAD_BALANCE)
OR
2)TheyareusingTNSentriesexplicitlyconnecting
toserver1
[Link]
UsersdonthaveFAILOVERcapabilities
[Link]
Checkedtheapplicationmoduleusageonserver1
[Link]
HowboutIgraphitinexcel?Willthedatabemore
meaningful?
..YES [Link] module
[Link]
DBperformance
GraphedtheASHdata..
..sufferingfromgc cr blocklost andgc cr multiblockrequest from7amto4pm
[Link]
DBperformance

ResearchedonMetalink forknownissues..
FoundDocID:563566.1gc lostblocks
diagnostics
Wasabletopinpointthepeakperiodfromthe
[Link],generatedADDMandAWR
reportonthatpeakperiod..
[Link]
DBperformance
ADDM

ElapsedTime:60min
DBTime:61.83min
AAS:1.03
MaxCPU:2
[Link]
DBperformance
ShouldIfollowtheserecommendationsrightaway?
Nope collectmorefacts,numbers,figures
[Link]
DBperformance
AWR
[Link]
DBperformance
Dowehaveaworkloaddistributionproblem?
Nope evenwithdistributedusers..
Westillhaveperformanceproblem..
[Link]
DBperformance
Thedatabasehastoomanyactivity,wheredo
Istart?Wheretodrilldown?
gv$session_longops &gv$session_wait output
toomanyusers,andrequirerepetitive
monitoring
InthespiritofMethodR
"WORKFIRSTTOREDUCETHEBIGGESTRESPONSETIMECOMPONENTOFA
BUSINESS'MOSTIMPORTANTUSERACTION

WenttotheAccountingDepartment,checked
onthedesktopterminals
[Link]
DBperformance
UsersPC1069(withSID601)andPC918(with
SID483)areontotalhang
[Link]
DBperformance
Checkedonthe
performance/waitcounters
thecurrentSQLs
[Link]
DBperformance
v$session_wait (SID601)
[Link]
DBperformance
v$sesstat (SID601)
[Link]
DBperformance
v$sql,v$sql_plan,v$sql_plan_statistics (SID601)

Runningfor98minutes
Just12.14secondsonCPU
[Link]
DBperformance
v$sesstat (SID483)
[Link]
DBperformance
v$sql,v$sql_plan,v$sql_plan_statistics (SID483)

Runningfor3hours
Just2.68secondsonCPU
[Link]
DBperformance
AnothergraphofASH
[Link]
interconnect

Generatedacat&egrep commandtolook
forproblemsintheinterconnectfromtheOS
Watchernetstat output
(fromMetalink DocID:563566.1gc lostblocksdiagnostics)
[Link]
interconnect
$catserver1_netstat.dat|egrep i"udpInOverflows|packet receive
errors|fragments dropped|reassembles failed|fragments droppedafter
timeout"
34096fragmentsdroppedaftertimeout
306030packetreassemblesfailed
15packetreceiveerrors
34096fragmentsdroppedaftertimeout
306268packetreassemblesfailed
15packetreceiveerrors
34096fragmentsdroppedaftertimeout
306574packetreassemblesfailed
outputsnipped
[Link]
interconnect
Restartedtheswitch

STILL THEREISAPERFORMANCEPROBLEM
[Link]
interconnect
Replacedtheswitch

THEYGOTFAST
[Link]
interconnect
karao@karl:~/Desktop$[Link] |egrep i"udpInOverflows|packet receive
errors|fragments dropped|reassembles failed|fragments droppedaftertimeout"
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
0packetreceiveerrors
[Link]
interconnect
AnothergraphofASH(Stackedgraph)
[Link]
interconnect
AnothergraphofASH(3dview)
Conclusion

Youdonthavetoguess..

EvenifitsaRACenvironment..

Itjusttakesfacts,numbers,figures
tosolveaperformanceproblem
ReferencesandTools
[Link]
[Link]
[Link]
[Link]
NeilGunther &Tanel Poder MultidimensionalVisualizationofOracle
PerformanceusingBarry007[Link]
[Link]
[Link]
[Link]

Metalink DocID97926.1FailoverIssuesandLimitations[Connecttime
failoverandTAF]
Metalink DocID563566.1gc lostblocksdiagnostics
Metalink DocID301137.1OSWatcherUserGuide
JoinOracleUsers Philippines

Facebook
[Link]

Linkedin
[Link]
Contactmethrough:

karao@[Link]
09192673389
8896999

Performance Scenario:  Diagnosing and resolving sudden  slow down on two node RAC
Introduction… • Karl Arao, OCP‐DBA, RHCT • Senior Consultant at SQL*Wizard • RAC user for 3years • 1st environment 
Scenario One Thursday… a client called… There was a SUDDEN  slow down  on ALL of the applications …a big impact to the Busi
And it’s running on RAC RAC no changes on the  RAC nodes and on the applications
Some of 10g Performance Features • OEM Performance Page • ADDM  • SQL Tuning advisor • AWR (DBA_HIST_) • ASH • Time Model (to
Setup • Server and Storage: SunFire X4200 (2CPU,  12GB memory) with LUNs on EMC CX300 • OS: RHEL 4.3 ES • Database and 
Troubleshooting Principle Systematic/Layered approach.. Understand..  Then Fix..  Lets get it on!
1. Measured the OS stack • Monitored the following – cpu (vmstat, top, mpstat) – io (iostat) – memory (vmstat, meminfo) –
• CPU on server1 • CPU on server2
• Datafiles on server1 • Datafiles on server2

You might also like