Oracle英文版培訓(xùn)課件之Performance Tuning：les-13Tuning PGA and Temporary Space

TuningPGAandTemporarySpaceObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DiagnosePGAmemoryissuesSizethePGAmemoryDiagnosetemporaryspaceissuesSpecifytemporarytablespaceparametersforefficientoperationSQLMemoryUsageMemory-intensiveSQLoperators:Sort-based(sort,group-by,rollup,window,...)Hash-joinBitmapoperators(mergeandinversion)Conceptofworkarea:Memoryallocatedbyamemory-intensiveoperatortoprocessitsinputdataPerformanceimpactofmemory:Optimal:Inputdatafitsintotheworkarea(cache).One-pass:Performoneextrapassoverinputdata.Multi-pass:Performseveralextrapassesoverinputdata.PerformanceImpactResponseTimeMemory..1-passoptimalM-passSortResponseTime..Hash-JoinM1-passMoptimalM1-passMoptimal1-passoptimalM-passMemoryAutomaticPGAMemoryDynamicallyadaptstheSQLmemoryallocationbasedon:PGAmemoryavailableSQLoperatorneedsSystemworkloadImprovesmanageability:Noneedtoset*_AREA_SIZEparametersDBAsetsamemorytarget:PGA_AGGREGATE_TARGETImprovesperformance:PGAmemoryisreallyreturnedtotheOS.Memoryisallocatedtotheoperationtomaximizethroughput.Maximizeoverallmemoryutilizationbydynamicallyadaptingmemorywithworkloadvariation.Operationadaptsitsmemoryusageduringtheexecution.SQLMemoryManager...HJGBHJWP1LocalSMMHJGBWP5Server-1Server-nSGAGlobal

memory

boundGlobalSMM(CKPT)Aggregate

PGA

auto-targetMemoryBoundComputationevery3sWP2LocalSMMWP3WP4V$SQL_WORKAREA_ACTIVEV$SQL_WORKAREAPGAMemory

Usage

StatisticsPGA_AGGREGATE_TARGETV$PROCESS

V$PROCESS_MEMORY

V$PGASTATWP5WP2WP1V$PGASTATAuto-TargetComputationWP3WP4NotesonlypageConfiguringAutomaticPGAMemoryPGA_AGGREGATE_TARGET:SpecifiesthetargetaggregateamountofPGAmemoryavailabletotheinstanceCanbedynamicallymodifiedattheinstancelevelExamples:100,000KB;2,500MB;50GBDefaultvalue:10MBor20%ofthesizeoftheSGA,whicheverisgreaterWORKAREA_SIZE_POLICY:OptionalCanbedynamicallymodifiedattheinstanceorsessionlevelAllowsyoutofallbacktostaticSQLmemorymanagementforaparticularsessionSettingPGA_AGGREGATE_TARGETInitiallyLeave20%oftheavailablememorytootherapplications.Leave80%ofmemorytotheOracleinstance.ForOLTP:ForDSS:PGA_AGGREGATE_TARGET=(total_mem*80%)*20%PGA_AGGREGATE_TARGET=(total_mem*80%)*50%MonitoringSQLMemoryUsageV$SQL_WORKAREA_ACTIVEV$SQL_WORKAREAV$PGASTATV$SQL_PLANV$SQLV$SQL_WORKAREA_HISTOGRAMSV$PROCESS_MEMORYV$SYSSTATV$TEMPSEG_USAGENotesonlypageMonitoringSQLMemoryUsage:ExamplesSELECTsql_text,sum(onepass_executions)onepass_cnt,sum(multipasses_executions)mpass_cntFROMV$SQLs,V$SQL_WORKAREAwaWHEREs.address=wa.addressGROUPBYsql_textHAVINGsum(onepass_executions+multipasses_executions)>0;SELECTTO_NUMBER(DECODE(sid,65535,NULL,sid))sid,operation_typeOPERATION,TRUNC(expected_size/1024)ESIZE,TRUNC(actual_mem_used/1024)MEM,TRUNC(max_mem_used/1024)MAXMEM,number_passesPASS,TRUNC(tempseg_size/1024)TSIZEFROMV$SQL_WORKAREA_ACTIVEORDERBY1,2;12TuningSQLMemoryUsageDeterminethebestPGA_AGGREGATE_TARGETvaluebyusing:V$PGA_TARGET_ADVICEV$PGA_TARGET_ADVICE_HISTOGRAMMonitorAWRreports/Statspackreportsfor:directpathreadtempdirectpathwritetempPGATargetAdviceStatisticsV$PGA_TARGET_ADVICEpredictshowcachehitpercentagesshowninV$PGASTATevolve.STATISTICS_LEVELmustbesettoatleastTYPICAL.V$PGA_TARGET_ADVICE13%25%50%75%100%120%140%160%180%200%300%400%PGA_TARGET_FACTORESTD_PGA_CACHE_HIT_PERCENTAGECurrent

valueOverallocation

zoneGood

valueESTD_OVERALLOC_COUNT0102030405060708090100PGATargetAdviceHistogramsV$PGA_TARGET_ADVICE_HISTOGRAMpredictshowhistogramsshowninV$SQL_WORKAREA_HISTOGRAMevolve.STATISTICS_LEVELmustbesettoatleastTYPICAL.AutomaticPGAandEnterpriseManagerAutomaticPGAandAWRReportsTemporaryTablespaceManagement:OverviewTemporarydatageneratedbyadatabaseinclude:BitmapmergesHash-joinBitmapindexcreationSortTemporaryLOBsGlobaltemporarytablesDatapersistsforthedurationofatransactionorsession.Highconcurrencyofthespacemanagementoperationiscritical.Mediaandinstancerecoveryisnotrequired.TemporaryTablespace:BestPracticeUsinglocallymanagedtemporarytablespace:Allowshigh-concurrencyspacemanagementAtsteadystate,allspacemetadataiscachedinSGA.OperationsareserializedbytheSGAlatch.Allowsfasterwritestotempfiles.Redogeneratedontemporaryblocksisnotwrittentodisk.MakesREAD

ONLYdatabasespossibleConfiguringTemporaryTablespaceLocallymanagedtemporarytablespacesareuniform-extenttablespaces.1MBto10MBextentsize:ForDSS,OLAPapplicationsinvolvinghugeworkareasLargetemporaryLOBsarepredominant.64KBormultiplelessthan1MB:Smallglobaltemporarytablesarepredominant.OLTPUseV$TEMPSEG_USAGEtomonitorspaceusageandworkloaddistribution.NotesonlypageTemporaryTablespaceGroup:OverviewGroupsmultipletemporarytablespacesCharacteristics:AtleastonetemporarytablespaceSamenamespaceastablespacesCreatedimplicitlyonfirstassignmentNoexplicitdeletionDefaulttablespaceEXAMPLE…Defaulttemporary

tablespacegroupTEMPTablespaceTEMP1TablespaceTEMPnTemporaryTablespaceGroup:BenefitsEnablesausertousemultipletemporarytablespaces:SameuserinmultiplesessionsOneparticularparalleloperationTemporarytablespacegroupTEMPTablespaceTEMP1TablespaceTEMP2TablespaceTEMP3SerialParallelHRHRCreatingTemporaryTablespaceGroupsMaintainingTemporary

TablespaceGroupsDataDictionaryChangesSELECTgroup_name,tablespace_name

FROMDBA_TABLESPACE_GROUPS;MonitoringTemporaryTablespaceUseV$TEMPSEG_USAGEtomonitorspaceusageandworkloaddistribution:

UseV$SORT_SEGMENTtodeterminespaceusagepercentage:SELECTsession_num,username,segtype,