improving hardware performance in oracle environments_final

Giving Your Oracle Hardware Performance a Shot in the ArmBoosting Oracle database and application performance can always be a challenge, especially if you have to start parsing code. But choosing the right underlying hardware and improving its performance can have immediate benefits for Oracle environments. BY FRANK OHLHORST

1 2 3 4EDITORS NOTE PICKING SERVER

HARDWARE FOR ORACLE? FIRST KNOW YOUR METRICS

GET OUT THE ELBOW GREASE TO BOOST ORACLE PERFORMANCE

IN-MEMORY PROCESSING GIVES ORACLE A PERFORMANCE JOLT

VIRT

UAL

IZAT

ION

CLO

UD

APPL

ICAT

ION

DEV

ELO

PMEN

T

NET

WO

RKIN

G

STO

RAG

E AR

CHIT

ECTU

RE

DATA

CEN

TER

MAN

AGEM

ENT

BUSI

NES

S IN

TELL

IGEN

CE/A

PPLI

CATI

ON

S

DIS

ASTE

R RE

COVE

RY/C

OM

PLIA

NCE

SECU

RITY

2 G IV ING YOUR ORACLE HARDWARE PERFORMANCE A SHOT IN THE ARM

Home

Editors Note

Picking Server Hardware for Oracle?

First Know Your Metrics

Get Out the Elbow Grease

to Boost Oracle Performance

In-Memory Processing Gives

Oracle a Performance Jolt

OPENER3 lines is max title length.

Style title. Then use hard return to push last line of title to sit on

this baseline.

All pages: text begins on this baseline

OPENER1st text baseline begins here.

To change slug and # txt.

On your page, in this order, deselect slug first, then move just outside of blue box to deselect the #.

This will keep the slug text in front of the number

1EDITORS NOTEBeyond Query Tuning: Improving Oracle Performance With Big Iron

A business relies inlargepartonhowwellitsdatabasesandapplications

operate,whethercustomer-facingorinternal.Thustherearevariousways

toimproveperformance,apopularonebeingdissectingandfixingcodeand

tuningimportantqueries.Butworklikethatoftentakesateamofdevelop-

ersanddatabaseadministrators.Itconsumesalotoftimeandcostsalotof

money.Solution:Justthrowabunchofhardwareattheproblem.Right?

Well,itsnotthateasy.Notallserverhardwareisthesame,justasnotall

storageisthesameandnotallnetworksarethesame.Bulkinguponserver

musclewithnoregardtowhatsinthemachinesandtheirconfigurations

mayyieldsomeimprovements,butyouwontgetthatperformancekick

yourelookingfor.Thatisthesubjectofthisguidesleadarticle,whichde-

tailshowtopickserverhardwareforyourOracleenvironment.

OursecondarticlefocusesonOracleDatabaseperformance.Thebottom

lineisthatyoumustfirstdeterminewherethedatabasebottlenecksareand

thenfindthetoolstohelpopenthemup.

Inthefinalarticle,weexaminetheincreasingrolethatrandom-access

memory(RAM)isplayingindatabaseandapplicationenvironments.Moores

Law,itseems,isstartingtoapplytomemory,andapplicationsanddatabases

canperformmuchfasterwhensittinginRAMandatanincreasinglylower

price.n

Mark Fontecchio

News and Site Editor, SearchOracle.com


Home

Editors Note









this baseline.






2BEST PRACTICESPicking Server Hardware for Oracle? First Know Your Metrics

Years ago, it wassimpletofigureoutwhathardwareyouwouldneedto

runanOracledatabaseeffectively.Butthingschange.Oraclehasevolvedinto

acompanyofferingmanyproductsbeyondjustdatabasesanddatabase-

relatedtools.Andsotheburningquestionformanyhasbecome,HowdoI

planhardwarelifecyclesaroundOraclesvastarrayofoptions?

TodaysOracleofferseverythingfrommiddlewaretoapplicationstoapp

developmenttoolsandthensome.Itisenoughtomakeadatabasemanagers

headspin.Luckily,Oracleoffersagreatdealofguidance.Butthatguidance

canbecontradictoryattimesanddifficulttodiscern,especiallywhenyou

incorporatetechnologiessuchasvirtualizationandcloudservicesintothe

picture.

PerhapsthebeststartingpointistolookasOraclesproductfamiliesand

extrapolatebasicrequirementstobuildasuitablehardwarematrix.That

methodologyworkswellforcreatingafoundationofhardwarerequirements

andcreatingthepredictionsneededforgrowthandincreaseddemand.

Firstandforemostistodeterminewhatperformancemetricisthemost

importantforagivenOracleproduct.Forexample,someOracledatabases

areI/O-intensive,requiringhardwarethatcanprocesstensofthousandsof

transactionsasecond.SomeOraclemiddlewareapplicationsareprocessor-

intensive,creatinghighCPUloadsforagivenprocess.Othermetricstolook

atincludestorageloads,temporaryfilecreation,concurrentconnections,

replicationloadsandprocessingthreads.

Thoseissuesdefinewhathardwaretoselect,howtoappropriatelysize

thathardwareandhowtoplanforthefuture.Assigningabiastotransac-

tions,processingorstoragecomesdowntoacombinationoffactorsthe


Home

Editors Note









this baseline.






2BEST PRACTICESarchitectureoftheapplication,theintendedusageandthemaximumex-

pectedloads.Applyingthosethreefactorstotheintendedenvironmentof-

fersupmanyoptionsforselectinghardware,andthatssimplybecausethe

environmentsthemselvescanvary.

Thoseenvironmentsmayconsistofvirtualizedservers,variousnetwork

operatingsystems,cloud-basedservices,storageareanetworks(SANs)and

soonwhichaddsanotherlayertothehardwarefoundation.Perhapsthe

bestadviceistolookatthecompleteplatform,asumofthevariousparts

thatneedtoworkasawhole.

DIFFERENT EDITIONS, DIFFERENT HARDWARE

ApplyingthatideologytovariousOracleproductsisoneofthebestmeth-

odsofsizinghardware.TaketheOracle11gDatabase,whichcomesinmulti-

pleeditions.TheproductisavailableinEnterpriseEdition,StandardEdition,

StandardEditionOneandExpressEdition.Eacheditionhasdifferinghard-

warerequirements.Forexample,OracleDatabase11gEnterpriseEditionfea-

turesadvancedcapabilitiessuchasclustering,failover,advancedsecurity

andotherenterprise-levelfeaturesthatdrivethebasichardwarerequirement

fromasingle-serverinstallationtoamultiservercluster.Administrators

mustlookattheexpectedloads,traffic,quantityoftransactionsandconnec-

tivitytocomeupwiththeappropriatehardware.Thecalculationsinvolved

couldprobablyfillabookontheirown.

ThingsgetsimplerwhenlookingattheStandardEditionofOracleData-

base11g.Thateditionincorporatesbasicfeatures,whichcanbetranslated

intomeasurableserverloads.Oracleofferssomebasicguidelinesonitsweb-

siteforminimumhardwarerequirements,statingthattheminimumsare

roughly1.5GHzdual-corePentiumprocessors,2GBofphysicalmemoryand

3GBoffreediskspace.Ofcourse,thoseminimumshavetobeaddedtothe

requirementsofthebaseoperatingsystem,suchasWindowsServer,Linux

orUnix.

Thoserequirementsgiveabasicunderstandingforwhatisneededtoruna

freshinstallationofOracleDatabase11gStandardEdition.Next,youshould


Home

Editors Note









this baseline.






2BEST PRACTICESaccountfortotaldatabasesize,growthofthedatabase,throughputneeded

(suchastransactionspersecond,numberofusersandrecordsize)and

anymanagementoverhead.

Ofcourse,itgetsmorecomplicatedwhentakingintoaccountvirtualized

serverssimplybecauseitishardtoestimatetheperformanceofferedbya

virtualmachineunderagivenconfiguration.Tobetterfine-tunetherequire-

ments,avirtualservermanagementsuiteshouldbeincorporatedthatcalcu-

latesperformanceaswellasmonitorsavailabilityandcanrunsynthetictests

togaugeoverallperformance.

BEYOND THE DATABASE

ForotherOracleproducts,manyofthesamerulesapply.Forexample,Or-

acleFusionMiddlewarehasbasicrequirementspublishedonOraclesweb-

site.Itwillbeuptotheadministratororimplementertocalculateaboveand

beyondthebasicrequirementstoeffectivelysizehardwaretomeetcurrent

andfutureneeds.Dependingupontheelementsandoptionschosenthere-

quirementscanvarygreatly.Oracleofferscomprehensiveinformationonits

OracleTechnologyNetworkwebsite.

Itisimportanttonotethatthecombinationofproducts,featuresand

managementtoolswilldictatewhattheminimumrequirementsare,and

mostadministratorswillfindtheirownminimumrequirementsuniquefor

theirparticularenvironments.Perhapsthemostimportantthingtoknowis

thatittakesextensiveresearchtodeterminewhathardwareisbest-suitedfor

particularOracleproductimplementations.Whatsmore,advancedcapabili-

tiessuchasclustering,highavailabilityorgrid-styleimplementationswill

furtherimpactthehardwareselectedandeventhedeploymentofthathard-

ware,includingtheinterconnectsandthebaseinfrastructure.

SOME BASIC RULES FOR PICKING HARDWARE

Therearesomebasicrulestofollowthatcanlaythefoundationforhardware

selection.ThatfoundationwillbebasedonspecificelementssuchasCPU

capabilities,I/Oandoverallthroughput.However,thefirstdecisiontosize


Home

Editors Note









this baseline.






2BEST PRACTICEShardwaredependsonthebaseoperatingsystemandwhethervirtualization

willbeused.

Forexample,virtualizedserverenvironmentsrespondwelltohigh-

performance,multicoreCPUsthatincorporatevirtualizationtechnology.

ThatrequirementshouldtrumpallotherswhenitcomestodeployingOracle

onavirtualizedserver.

OracleproductsthatdemandhighI/Oarebetter-suitedbysystemsthat

canrunmultiplethreads,wide,64-bitdatapathsandhavehigh-speedin-

terconnectsincorporatedintothesystemboard.Thosehigh-speedinter-

connectsmayconsistofFibreChannelconnectivityforSANdeployments,

10GigabitEthernetforhigh-speedcommunicationsbetweeninfrastructure

components,andahigh-performancebackplaneforintelligentrack-mounted

systems.

Hardwareselectionshouldnotbebasedsolelyonserverperformance.

Otherelementssuchasserviceability,integration,expansionandmanage-

mentshouldallbeprimaryconsiderationsforselectingahardwareplatform

onwhichtorunOracle.Thoseconsiderationsplaywellintoreturnoninvest-

ment(ROI)andoverallTCO,whicharecriticalwhenitcomestobudgeting

andmeetingthecoreneedsofthebusiness.Productsthatareeasiertoup-

gradetendtodeliveralowerTCOovertimeandimproveROI.

WhenselectinghardwareforanOracleecosystem,itisimportanttore-

membertheprimaryfactorstoconsider.Thosefactorsnotonlyincludeper-

formanceoftheOracleproductsdeployed,butalsotheinitialcost,theinitial

utilization,anticipatedgrowthandhardwarelifecycles.Allthoseelements

areusedtocalculateTCO,whichhasbecomethefinanciallanguageoftheIT

department.n


Home

Editors Note









this baseline.






3EVALUATIONGet Out the Elbow Grease to Boost Oracle Performance

Oracle databases are oftenthecornerstoneoflargeenterpriseapplica-

tions,andasmostDBAshavediscovered,poordatabaseperformancehasa

rippleeffectacrossITsystems,negativelyimpactingproductivity.Regaining

andimprovingproductivityhasbecomeamajorconcernformostorganiza-

tionsthatarestilltryingtodomorewithlesscreatingbothachallengeand

anopportunityfortheharriedDBA.

Thechallengecomesindetermininghowtosuperchargedatabaseswithout

incurringextensivecosts.Justthrowingmoney(andCPUcycles)attheprob-

lemisnolongereconomicallyviableformostenterprises.Itwilltakeeffort,

notcash,toimproveperformance.ThereinliesanopportunityforDBAs,a

chancetoimprovedatabaseperformancewithoutbreakingthebank.Itcan

onlyhelpDBAsimprovetheirstandinginanITdepartment.

Nevertheless,thereisnomagicwandthatcanimprovedatabaseperfor-

mance.Thereareonlytools,techniquesanddevicesthathavebeenvettedby

tried-and-truemethods.Thetrickistoknowwhenandwhattechniquesde-

livermeasurableperformancegainsandhowtoapplythem.

IDENTIFY GOALS, EVALUATE PERFORMANCE

Thefirststeptoimprovingperformancecomesindiscussinggoalsandplans

withotherITteammembers,includingserveradministrators,datacen-

termanagersandanyoneelseinvolvedwiththeday-to-daymanagementof

servers,storageandinfrastructure.Thisstepprovescriticaltomappingout

theinfrastructureinplaceandtheavailableresources.Insomecases,itmay

justtakeasimplereallocationofresourcestogarnernoticeableperformance

gains.


Home

Editors Note









this baseline.






3EVALUATIONThekeyistoidentifybottlenecksthathamperperformance,especiallybe-

forelookingattheprimaryserverhostinganOracleDatabase.Insomein-

stances,abottlenecksuchasanovertaxedrouter,saturatedIPnetworkor

poorWANconnectivitycanbetheprimaryreasonthatanOracleDatabase

doesnotperformaswellasexpected.Ifthatisthecase,upgradingaserveror

noninfrastructureelementsisawasteoftimeandmoney.

Thetrickistogaugethepotentialperformanceofthedatabaseandthein-

frastructure.Lookattheloadplacedonthedatabaseitselftoseeifthehost-

inghardwareisbeingtaxedtoapointwhereperformancedropsoff.

Luckily,thatsarelativelyeasytaskof

monitoringCPUusage,memoryusage

anddiskactivity,allofwhichcanbeac-

complishedbyusingnativetoolspro-

videdbytheoperatingsystemvendor

aswellasthird-partyservermanage-

mentprogramsandsoftwareprovided

byOracle.

Oracleprovidesseveraltuningtools

tomeasureefficiencyandperformance.Somearefree,whileothersinvolve

additionalcosts.Oraclesfreetoolsareagoodstartingpointtounderstand

howadatabaseisperformingandarearguablythefirst,beststepwhenpre-

paringtotuneadatabase.Therearethird-partytoolsthatmaydothejob

better,butOraclesfreetoolsareOracle-centricandonlylookatOracle-re-

latedperformancemeasurements.Insomecases,however,amoreholistic

approachmaybecalledfortoolsthatgaugeoverallsystem,infrastructureand

databaseperformance.

TOOLS TO GAUGE PERFORMANCE

OneofthefirsttoolstoconsiderisOraclesAutomaticDatabaseDiagnos-

ticMonitor(ADDM),whichcanbebestdescribedasthedatabasesdoc-

tor.ADDMallowsanOracledatabasetoself-diagnoseandadviseonhowto

solveproblems.ADDMlaunchesautomaticallyafteranAutomaticWorkload

Look at the load placed on the database itself to see if the hosting hardware is being taxed to a point where performance drops off.


Home

Editors Note









this baseline.






3EVALUATIONRepositorystatisticcaptureandoffersdetailedworkloadstatistics,soitcan

revealtheperformanceparametersofanOracledatabaseataparticulartime.

Bytimingthereportduringtypicalandhighworkloads,DBAscanmaximize

thevalueofthedata.

However,thetruevalueofthecapturedperformancedataonlycomes

tolightwhenitisproperlytrendedoveraperiodoftime,suchasaweek

ormonth.Toaccomplishthat,DBAswillhavetoturntoOracleStatspack,

whichisasetofperformancemonitoringandreportingutilities.Statspack

canreducethetimeandstepsinvolved

inperformancemeasurementwhileau-

tomatingsomeoftheanalyticaland

captureprocesses.

StatspackincludesanumberofSQL

scriptsthatcanberunagainstthedata-

basetogatherperformanceinformation

andanalyzethatinformationovertime.

However,thereportscanbesomewhat

crypticandrequireDBA-directedexecutionwithcommand-linescripts.

Third-partytoolsthathelpmakethereportsmorepresentableareavailable

fromoraperf.com,statspackanalyzer.comandspviewer.com.

Oraclealsobundlesinasimplerperformancemeasurementutilityinthe

formofTKProf,autilityfoundintheOracleHome/bindirectory.TKProf

formatsSQLTraceoutputintoausableformatthatshowsCPUutilization,

elapsedtimesandseveralotherpiecesofinformationthatcanbeusedto

judgeperformance.

ForthoseDBAslookingforamorecomprehensivesuiteofperformance

measurementandmonitoringtools,itisprettyhardtobeatOracleEnter-

priseManager(OEM)anditsassociatedTuningPack.OEMoffersaGUI-

basedinterfacethatprovidesreal-timeperformanceinformationaswellas

errordetectionandstatisticalmeasurementthatallowsDBAstomonitorand

trendperformance.Whatsmore,toolsareincludedthatcantunedatabases

tomaximizeperformance.

The true value of the captured performance data only comes to light when it is properly trended over a period of time.


Home

Editors Note









this baseline.






3EVALUATIONMostofthetoolsandmethodsabovehaveonethingincommon:Theyare

allgearedtowardimprovingadatabasesperformancebyfine-tuninginher-

entparameters.Thereare,ofcourse,othermethodsthatcanimproveper-

formance,suchashardwarerefreshes,replacementsorupgrades,aswellas

changestooperatingsystemsettings.However,beforemovingtowardthose

improvements,itiscriticaltomakesurethedatabaseitselfisperformingas

wellaspossibleandistunedtoprovidetheleastamountoflatency.

Otherhardwareandsoftwareperformanceimprovementsusuallyfall

undertherealmofsomeoneotherthanaDBA.Thatiswhycommunication

amongITmanagersiscriticalbeforemakingotherchanges.DBAsmustmake

suretheirownhouseisinorderbeforehavingotherscommittohardware

orsoftwarerefreshes.Thatcanultimatelyreducecostsandimproveperfor-

mancefornotjustOracledatabasesbutotherline-of-businessapplications

aswell.n

1 1 G IV ING YOUR ORACLE HARDWARE PERFORMANCE A SHOT IN THE ARM

Home

Editors Note









this baseline.






4PERFORMANCEIn-Memory Processing Gives Oracle a Performance Jolt

As data grows exponentially,ITmanagersarerunningheadfirstintoa

speedbarrierinformationcantbeaccessedfastenoughtomeettheneeds

ofadvancedprocessingalgorithms,suchasthoseusedinbusinessintelli-

genceandbigdataanalytics.

Exacerbatingthespeedproblemisthechangingorganizationalnatureof

data.SQLandrelationaldatabasemanagementsystems(RDBMSes)aresup-

plementedbyunstructureddatastoresandotherelements,suchasaudio,

video,logsandsensordata.Thosechangesandothers,suchastheinclusion

ofsocialmediadata,makeitmoredifficulttoprocesslargeamountsofdata

atthespeedofbusinessandnetresultsthatimpactthebottomline.

Thereishelponthehorizonnewertechnologiesthatcanchangethe

speedthatdataisprocessedandsuperchargetraditionaldatabases,suchas

thoseofferedbyOracleandotherRDBMSvendors.Oneofthoseemerging

technologiesisin-memoryprocessing,whichpromisesapplicationsinstan-

taneousaccesstoneededinformationforreal-timeprocessing.

HOW IN-MEMORY WORKS

In-memoryprocessingcanboostperformancebymovingdatanormally

storedondiskintorandom-accessmemory(RAM),whichisexponentially

fasterthandisk-basedstorage.InmanycasesRAMsitsclosertotheCPU,

whichthendoesnothavetoprocessinformationoverthenetworkorviathe

storageareanetwork.

Whilein-memoryprocessingseemssimilartoRAM-basedcache,itisin-

herentlydifferent.Acachenormallystoresthemostfrequentrequestsmade

tosmallpiecesofdata,suchasadocumentorasinglerecordinadatabase,


Home

Editors Note









this baseline.






4PERFORMANCEboostingperformanceforonlyverysimpleprocessingrequests.In-memory

processingmovesprimarydataelementsneededintoRAMforanalytical

processing.

IntheworldofBI,in-memoryprocessingmakesasignificantdifference.

Normally,BIandanalyticsprocessesmovedataontodiskintheformofta-

blesandmultidimensionalcubesagainstwhichqueriesarerun.Whendatais

loadedintoRAMinsteadofontoharddisks,ITstaffcanspendlessdevelop-

menttimeondatamodeling,queryanalysis,cubebuildingandtabledesign.

AndseeingasRAMcanbehundredsoftimesfasterthandisk,performance

improves.

Thebenefitsofin-memoryprocessingaremanifold,reachingbeyondjust

theobviousargumentthatRAMisfasterthandisk.Forexample,usingdisk-

basedtechnologytoanalyzelargevolumesofdatarequiresaccessingmultiple

tablesstoredonaserversharddisk.Whenthequeriesincreaseincomplex-

ity,performancedegrades.

ThatsbecauseanRDBMSsuchasSQLServer,MySQLorOracleisde-

signedfortransactionalprocessing,meaningthatthedatabasemustsupport

insertions,aggregationsandjoins.WhatsmoreisSQLisdesignedtoeffi-

cientlyfetchcompleterowsofdata.Analyticsworksdifferentlyandnormally

requiresfetchingpartialrowsofdataandperforminganalyticaljoinsacross

multipledatabases.

Improvingqueryperformanceandtherelatedanalyticsrequiresadiffer-

entapproachtoworkingwithdata,hencethecreationofmultidimensional

databasesorcubes,alsocalledmultidimensionalonlineanalyticalprocessing

(MOLAP).Itdoesnotlenditselfwelltoadhocqueriesandotherreal-time

processes,however,creatingadilemmaforpeoplelookingtomaximizeana-

lyticalperformance.

COLUMN-BASED DATABASES

Thatdilemmahasdrivenenterprisestolooktocolumnardatabases,which

storesimilarinformationtogetherandusebettercompressiontechniques.

Theresultingreductioninstoragerequirementsmakesin-memorydatabases


Home

Editors Note









this baseline.






4PERFORMANCEfeasible.Within-memorydatabases,allinformationisinitiallyloadedinto

memory,anditeliminatestheneedfordatabaseoptimizationssuchascre-

atingindexes,aggregatesanddesigningcubesandstarschemas.

In-memorytoolsusealgorithmsthatcompressdatabetterthanharddisks

do.Sowhenusersquerythedata,performancebottlenecksareavoided.With

in-memorytools,dataavailableforanalysiscanbeaccessedinsecondsby

multipleconcurrentusersandofferthepotentialforexcellentanalytics.

TheyalsominimizetheneedforperformancetuningbyITstaff.

Ultimately,in-memoryprocessingshouldproveidealforbusinessesthat

holdspeedasacriticalobjective.Onthedownside,in-memoryprocessingis

manytimesmoreexpensivethantraditional,disk-basedstoragesystemsand

requiresadifferentskillset.Nevertheless,itlooksasthoughMooresLaw

cannowbyappliedtomemory,withpricesgoingdownformorecapacityin

shortperiodsoftime.Asthosepricesfall,in-memoryprocessingwillbe-

comemoreprevalentandeasiertoscale.n


Home

Editors Note









this baseline.






ABOUTTHE

AUTHOR

FRANK OHLHORST isanaward-winningtechnologyjournalist,professionalspeakerandITbusinessconsultantwithmorethan25yearsofexperience.Heservedasanetworkadministra-torandapplicationsprogrammerattheU.S.DepartmentofEnergybeforeform-inghisowncomputerconsultingfirm,whichcanbefoundatohlhorst.net.

Giving Your Oracle Hardware Performance a Shot in the ArmisaSearchOracle.come-publication.

Jason SparapaniManaging Editor, E-Publications

Mark FontecchioNews and Site Editor

Lena WeinerAssociate Site Editor

David EssexExecutive Editor

Linda KouryDirector of Online Design

Mike BolducPublisher

[email protected]

Ed LaplanteDirector of Sales

[email protected]

TechTarget 275 Grove Street, Newton, MA 02466

www.techtarget.com

2012TechTargetInc.Nopartofthispublicationmaybetransmittedorreproducedinanyformorbyanymeanswithoutwrittenpermissionfromthepublisher.TechTargetreprintsareavailablethroughTheYGSGroup.

About TechTarget:TechTargetpublishesmediaforinformationtechnologyprofessionals.Morethan100focusedwebsitesenablequickaccesstoadeepstoreofnews,adviceandanalysisaboutthetech-nologies,productsandprocessescrucialtoyourjob.Ourliveandvirtualeventsgiveyoudirectaccesstoindependentexpertcommentaryandadvice.AtITKnowledgeExchange,oursocialcommunity,youcangetadviceandsharesolutionswithpeersandexperts.

improving hardware performance in oracle environments_final

Documents