Oracle英文版培训课件之Performance Tuning：les-12Tuning IO

TuningI/OObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DiagnosedatabaseI/OissuesDescribetheStripeAndMirrorEverything(SAME)conceptExplainthebenefitsofasynchronousI/OChooseappropriateI/OsolutionsTuneI/OusingAutomaticStorageManagement(ASM)I/OArchitectureOracleDatabase10gincludesthreestandardstorageoptions:FilesystemRawpartitionsAutomaticStorageManagement(ASM)FileSystemCharacteristicsCertaincharacteristicsarebetterfordatabaseuse:Write-through-cacheabilityWriteacknowledgementSecurityJournalingHighperformanceRawPartitionsRawpartitionshavebeenconsideredtobethehigh-performancesolution.RawreadsandwritesdonotusetheOSbuffercache.RawreadsandwritescanmovelargerbuffersthanfilesystemI/Os.Usingrawpartitionscomplicatesadministration.WriteReadProcessDiskfileI/OModesI/Ocanbewrittentodiskinseveralwaysbyusingdifferentsystemcalls:StandardI/OSynchronousI/OAsynchronousI/OBuffercacheDiskfileWriteFlushProcessBandwidthVersusSizeI/Operformancedependsonbandwidth.Numberofdisks,notsizeNumberofcontrollersBackgroundprocessDiskcontrollersStripeandMirrorEverythingAlldatafilestoaccessallavailablebandwidthAlldatabasefilestobeonthesamelogicaldevicesHighestperformanceconfigurationUsingRAIDRedundantArrayofInexpensiveDevices(RAID)levels:Level0StripedforperformanceNoredundancyLevel1MirroredforsafetyLittleperformancebenefitLevel5Blocklevelredundancy(rebuildalgorithm)ImprovedreadperformanceAdditionalwritecostRAIDCostVersusBenefitsRAIDcostismeasuredinperformanceandreliability.RAID0:FastLossofanydevicedamagesthearray.RAID1:SafeandexpensiveSlightbenefitinhigh-readenvironmentsRAID5FastSafewithlossofanyonedevicePossiblehighwritecostShouldIUseRAID1orRAID5?RAID1(Mirroring)RecommendedbyOracleMostdemandingapplications

ProsBestredundancyBestperformanceLowrecoveryoverheadConsRequireshighercapacityRAID5(Parity)DSSandmoderateOLTPProsRequireslesscapacityConsLessredundancyLessperformanceHighrecoveryoverheadDiagnosticsIndicatorsofI/Oissues:Topwaitsarereadsandwritesplus:BufferbusywaitsWritecompletewaitsDBfileparallelwritesEnqueuewaits

FileI/OStatisticssectionshowshighwaitsandAVGBufferWaittimehigherthanaverageoncertainfiles.Note:Onawell-performingsystem,thetopeventsarelikelytobeCPUtime,dbfilescatteredread,anddbfilesequentialread.DatabaseI/OTuningConfiguringstorageforadatabasedependsonmanyvariables:Whichdatatoputonwhichdisk;complicatedbyvendor-configuredlogicalunits(LUNs)DBapplicationworkloads:OLTP,DSS,batchversusonlineTrade-offsbetweenavailableoptionsOngoingtuning:changesinworkloadsExpandingorcontractingyourdatabaseWhatIsAutomaticStorageManagement?ASM:Isaportableandhigh-

performanceclusterfilesystemManagesOracledatabasefilesDistributesdataacrossdisks

tobalanceloadProvidesintegratedmirroring

acrossdisksSolvesmanystorage

managementchallengesEncapsulatestheSAMEmethodologyASMFile

systemVolume

managerOperatingsystemApplicationDatabaseASM:KeyFeaturesandBenefitsStripesfilesratherthanlogicalvolumesOnlinediskreconfigurationanddynamicrebalancingAdjustablerebalancingspeedProvidesredundancyonafilebasisSupportsonlyOracledatabasefilesDatabaseclusterfilesystemwithperformanceofrawI/OusableonallstorageplatformsAutomaticdatabasefilemanagementNomorehotspots:eliminatesmanualI/Otuning......................................................................................................................................................................................................NotesonlypageHowManyDiskGroupsperDatabaseTwodiskgroupsarerecommended:LeveragemaximumofLUNsBackupforeachotherLowerperformancemaybe

usedforFRA(orinnertracks)Exceptions:AdditionaldiskgroupsfordifferentcapacityorperformancecharacteristicsDifferentILMstoragetiersDataDGFRADGERPDBCRMDBHRDBDatabaseStorageConsolidationSharedstorageacrossseveraldatabasesRACandsingle-instancecanusethesameASMinstance.Benefits:SimplifiedandcentralizedmanagementHigherstorageutilizationHigherperformancePayrollGLPayrollandGL1050GB1050GB10100GB………WhichRAIDConfiguration

forBestAvailability?ASMmirroringHardwareRAID1(mirroring)HardwareRAID5(ParityProtection)BothASMmirroringandhardwareRAIDAnswer:Dependsonbusinessrequirementandbudget(cost,availability,performance,andutilization)ASMleverageshardwareRAID.ASMMirroringGuidelinesBestchoiceforlow-coststorageEnablesExtendedClusteringsolutionsNohardwaremirroring1MB

AUASMStripingGranularityCOARSEFINEWhatTypeofStripingWorksBest?ASMstripingonly(noRAID0)RAID0andASMstripingUseLVMNostripingAnswer:AandBASMandRAIDstripingarecomplementary.ASMStripingOnlyProsDrivesevenlydistributedforData&FRAHigherbandwidthAllowssmallincrementalgrowth(73GB)NodrivecontentionDataDGFRADG1TB2TB1673GB

LUNs3273GB

LUNsRAID1OracleDBsize:1TBStorageconfiguration:

8arrayswith

1273GBdisksperarrayConsNotwellbalancedacross

alldisksLUNsizelimitedtodisksizeHardwareRAIDStripedLUNsProsFastestregionforDataDGBalanceddatadistributionFewerLUNstomanagewhilemaxspindlesDataDGFRADG1TB2TB4250GB

LUNs4500GB

LUNsRAID0+1OracleDBsize:1TBStorageconfiguration:

8arrayswith

1273GBdisksperarrayConsLargeincrementalgrowthDataandFRA“contention”ASMGuidelinesUseexternalRAIDprotectionwhenpossible.Createlogicalunits(LUNs)using:OutsidehalfofdiskdrivesforhighestperformanceSmalldisk,highrpm(forexample,73GB/15krpm)UseLUNswiththesameperformancecharacteristics.UseLUNswiththesamecapacity.Maximizethenumberofspindlesinyourdiskgroup.ASMInstanceInitializationParametersASMinstanceshavestaticmemoryneeds.UsingdefaultSGAsizingparametersshouldbeenoughformostconfigurations:Add500KBtothesharedpoolperadditionaldiskgroupafterthefirstfive.INSTANCE_TYPE=ASMDB_UNIQUE_NAME=+ASMASM_POWER_LIMIT=1ASM_DISKSTRING='/dev/rdsk/*s2','/dev/rdsk/c1*'ASM_DISKGROUPS=dgroupA,dgroupBPROCESSES=25+15*<#DBinstusingASMfortheirstorage>DynamicPerformanceViewsStoragesystemV$ASM_DISKGROUPV$ASM_CLIENTV$ASM_DISKV$ASM_FILEV$ASM_TEMPLATEV$ASM_ALIASV$ASM_OPERATIONDiskgroupADiskgroupBMonitoringLong-RunningOperationsbyUsingV$ASM_OPERATIONEstimatedamountoftime(inminutes)foroperationterminationEST_MINUTESEstimatednumberofallocationunitsmovedperminuteEST_RATEEstimatednumberofremainingallocationunitsEST_WORKNumberofallocationunitsmovedsofarSOFARPowerallocatedtothisoperationACTUALPowerrequestedforthisoperationPOWERStateofoperation:WAITorRUNSTATETypeofoperation:REBALOPERATIONDiskgroupGROUP_NUMBERDescriptionColumnASMInstancePerformanceDiagnosticsSELECTevent,total_waitst_wait,

total_timeoutst_timeout,

time_waitedt_waittm,

average_waita_waittm,wait_classFROMV$SYSTEM_EVENTWHEREwait_class<>'Idle'andtime_waited>0ORDERBY4DESC;EVENTWAITTOUTWAITTAVGCLASS

ASMmount:waitforheartbeat11439438.85Admin…kfk:asyncdiskIO5780377.65SystI/Ologwrite(odd)7329642.33Otherrdbmsipcreply3712597.01Otherlogwrite(even)8219724.58OtherSQL*Netmessagetoclienetworkosthreadstartup90798.77Conc…bufferwritewait106060.31OtherDBFGwaitingforreply1601.04OtherASMPerformancePageAddthefollowingtoSHARED_POOL_SIZE:SELECTd+l+tDB_SPACEFROM(SELECTSUM(bytes)/(1024*1024*1024)d

FROMV$DATAFILE),(SELECTSUM(bytes)/(1024*1024*1024)l

FROMV$LOGFILEa,V$LOGbWHEREa.group#=b.group#),(SELECTSUM(bytes)/(1024*1024*1024)t

FROMV$TEMPFILEWHEREstatus='ONLINE');Addatleast16toPROCESSES.(DB_SPACE/100+2)*#_External_RedOR

(DB_SPACE/50+4)*#_Normal_RedOR

(DB_SPACE/33+6)*#_High_RedDatabaseInstanceParameterChangesASMDiskMetadataRequirementsForemptydiskgroups:Fornormalandhighredundancy:15+(2*#_disks)+(126*#_ASM_insts)Forexternalredundancy:5+(2*#_disks)+(42*#_ASM_insts)Foreachfile:Highredundancy:Add3MBiffilesizeisgreatert