Oracle英文版培训课件之Performance Tuning：les-08Tuning the Shared Pool

TuningtheSharedPoolObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DiagnosesharedpoolproblemsSizethesharedpoolSizethereservedareaKeepobjectsinthesharedpoolSharedPoolArchitectureMajorcomponentsofthesharedpoolare:LibrarycacheDatadictionarycacheUserglobalarea(UGA)forsharedserversessionsDatabasebuffercacheRedologbuffer

SharedpoolLibrarycacheDatadictionarycache

SharedareasLargepool

UGASharedPoolOperationThesharedpoolismanagedbyanLRUalgorithm.Newobjectsrequirememoryallocations.Re-creatableobjectsareagedoutofthecache.Objectsaremadeupofchunksofmemory.Amemoryallocationisawholechunk.Achunkiscontiguous.TheLibraryCacheStorescomplexobjectmetadataassociatedwithcursorsStoresSQLstatementsandPL/SQLblocksthataretobesharedbyusersPreventsstatementreparsingSQLAREASQLandPL/SQLStorageCursorcontextareafor

SELECTstatement2Cursorcontextareafor

SELECTstatement1SharedpoolSELECTstatement2SELECTstatement1SELECTstatement1CursorUsageandParsingParseprocedure:1. Findandexecuteanopencursor.2. Findaclosedcursorinthesessioncache.3. Searchthehashchains(softparse).4. Constructthecursor(hardparse).OpencursorsClosedcursorsSessionmemory(UGA)Sharedpool(SGA)CursorhandlesHashchains1234ImportantSharedPoolLatcheslatch:sharedpoolprotectsmemoryallocationsinthesharedpool.latch:librarycachelocatesmatchingSQLinthesharedpool.MutexAmutualexclusionobjectallows:SharingofaresourcewithoutcorruptionSharedaccessforreadsExclusiveaccessforupdateEachobjecttohaveitsownmutexBenefitsofMutexPerformanceisimprovedwithmutexes.Mutexes:AresmallerandfasterHavelesspotentialforcontentionReplacelatchesandpinsMutexViewsandStatisticsMutexviews:V$MUTEX_SLEEPV$MUTEX_SLEEP_HISTORYMutexwaitevents:cursor:mutexXcursor:mutexScursor:pinXcursor:pinScursor:pinSwaitonXMutex-ProtectedOperationsFromonward,mutexprotects:SelectsfromV$SQLSTATSearchesofchildcursorlistsStatspack/AWRIndicatorsStatspackandAWRreportsincludeindicators:LoadProfileInstanceEfficienciesTopWaitEventsTimeModelLoadProfileLoadProfilePerSecondPerTransaction~~~~~~~~~~~~------------------------------Usercalls:4.2921.78Parses:188.09954.20Hardparses:80.00405.85%BlockschangedperRead:0.18RecursiveCall%:99.78Rollbackpertransaction%:9.76RowsperSort:8.47InstanceEfficienciesInstanceEfficiencyPercentages~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~BufferNowait%:100.00RedoNoWait%:100.00BufferHit%:99.87In-memorySort%:100.00LibraryHit%:87.90SoftParse%:57.47ExecutetoParse%:22.50LatchHit%:100.00ParseCPUtoParseElapsd%:83.16%Non-ParseCPU:17.88SharedPoolStatisticsBeginEnd------------MemoryUsage%:92.7592.97%SQLwithexecutions>1:72.6272.28%MemoryforSQLw/exec>1:92.3592.17TopWaitsTop5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)Time----------------------------------------------------CPUtime19098.0latch:librarycache76119.8latch:sharedpool49116.4dbfilesequentialread1,34300.2logfileparallelwrite5405.1-------------------------------------------------------TimeModelTimeModelSystemStats->Orderedby%ofDBtimedesc,Statisticname %ofStatisticTime(s)DBtime------------------------------------------------------sqlexecuteelapsedtime399.595.6DBCPU269.364.4parsetimeelapsed126.830.3hardparseelapsedtime111.926.8PL/SQLcompilationelapsedtime6.61.6PL/SQLexecutionelapsedtime5.91.4connectionmanagementcallelapsed4.91.2failedparseelapsedtime4.01.0hardparse(sharingcriteria)elaps2.3.5sequenceloadelapsedtime0.5.1hardparse(bindmismatch)elapsed0.2.0repeatedbindelapsedtime0.1.0DBtime417.9LibraryCacheActivityLibraryCacheActivityDB/Inst:ORCL/orclSnaps:61-71->"PctMisses"shouldbeverylowGetPctPinPctInvali-NamespaceRequestsMissRequestsMissReloadsdations-----------------------------------------------------------BODY3327.345014.0460CLUSTER490.02003.060INDEX5100.035,3000.000SQLAREA18,88096.989,90436.54960TerminologyGets:(Parse)Thenumberoflookupsforobjects

ofthenamespacePins:(Execution)ThenumberofreadsorexecutionsoftheobjectsofthenamespaceReloads:(Parse)Thenumberoflibrarycachemissesontheexecutionstep,therebycausinganimplicitreparsingoftheSQLstatementInvalidations:ThenumberoftimesanobjectismodifiedbyaDDL,causingalldependentobjectstobemarkedinvalidV$SGASTATV$SGAINFOV$LIBRARYCACHEV$LIBRARY_CACHE_MEMORYV$SHARED_POOL_RESERVEDV$SQLSTATSV$SQLV$SQLAREAV$SQLTEXTV$DB_OBJECT_CACHEDiagnosticTools

forTuningtheSharedPoolParametersaffectingthecomponents:SHARED_POOL_SIZE,OPEN_CURSORSSESSION_CACHED_CURSORS,CURSOR_SPACE_FOR_TIMECURSOR_SHARING,SHARED_POOL_RESERVED_SIZEsp_m_n.lstreport.txtDatadictionary

cacheSharedpoolLibrarycacheSharedSQLandPL/SQLViewsLibraryCacheReloadsReloadsshouldbelessthan1%ofthepins:Ifthereloads-to-pinsratioisgreaterthan1%,increasethevalueoftheSHARED_POOL_SIZEparameter.SQL>SELECTSUM(pins)"Executions", 2SUM(reloads)"CacheMisses",3SUM(reloads)/SUM(pins)4FROMV$LIBRARYCACHE;ExecutesPROC1—>1stpin,1loadExecutesPROC1

—>2ndpin,noreloadExecutesPROC1

—>3rdpin,noreloadExecutesPROC1

—>4thpin,noreloadFourpinsandnoreloadsInvalidationsThenumberoftimesobjectsofthenamespacewere

markedinvalid,causingreloads:SQL>SELECTCOUNT(*)FROMhr.employees;SQL>SELECTnamespace,pins,reloads,2invalidations3FROMV$LIBRARYCACHE;SQL>executeDBMS_STATS.GATHER_TABLE_STATS->('HR','EMPLOYEES');SQL>SELECTCOUNT(*)FROMhr.employees;SQL>SELECTnamespace,pins,reloads,2invalidations3FROMV$LIBRARYCACHE;AvoidHardParsesInanOLTPsystem,reducemissesbykeepinghardparsingtoaminimum:Makesurethatuserscansharestatements.Preventfrequentlyusedstatementsfrombeingagedoutbyallocatingenoughspace.Avoidinvalidationsthatinducereparsing.AreCursorsBeingShared?CheckGETHITRATIOinV$LIBRARYCACHE:Determinewhichstatementscouldbeshared:SQL>SELECTplan_hash_value,count(*)2FROMV$SQL3GROUPBYplan_hash_valueORDERBY2DESCSQL>SELECTsql_text,executions2FROMV$SQLAREA3WHEREplan_hash_value=NNNNNNNNNSQL>SELECTgethitratio2FROMV$LIBRARYCACHE3WHEREnamespace='SQLAREA';SharingCursorsValuesforCURSOR_SHARINGare:EXACTSIMILARFORCEAvoidingSoftParsesReducingsoftparsesreduceslibrarycachelatchcontention.Keepsoftparsingtoaminimumby:SettingSESSION_CACHED_CURSORSSettingHOLD_CURSORintheapplicationprecompilerSettingCURSOR_SPACE_FOR_TIMEAvoidingFragmentationAvoidfragmentationby:Upgradingto10.2.0.xKeepingfrequentlyrequiredlargeobjectsReservingspaceforlargeobjectsEliminatinglargeanonymousPL/SQLblocksEnablingtheuseoflargepoolSizingtheSharedPoolUseAutomaticSharedMemoryManagement.UsetheSharedPoolAdvisorandconfirmusingotherdiagnosticswhendatahasoperationalhistory.Use40percentoftheSGAsizetostart,whenthereisnohistory.Monitorandadjustasneeded.Donotincreasethesizewhenfreememoryisavailable.SQL>SELECT*FROMV$SGASTAT2WHERENAME='freememory'3ANDPOOL='sharedpool';SharedPoolAdvisorySQL>SELECTshared_pool_size_for_estimateAS2pool_size,estd_lc_size,3estd_lc_time_saved4FROMV$SHARED_POOL_ADVICE;POOL_SIZEESTD_LC_SIZEESTD_LC_TIME_SAVED----------------------------------------328786840157868481778685617786864177868721778688017786888177868SharedPoolAdvisorLargeMemoryRequirementsSatisfyrequestsforlargecontiguousmemory.Reservecontiguousmemorywithinthe

sharedpool.SHARED_POOL_SIZESHARED_POOL_RESERVED_SIZEDatadictionary

cacheSharedpoolLibrarycacheSharedSQLandPL/SQLV$SHARED_POOL_RESERVEDReservedareaTuningtheSharedPool

ReservedSpaceIFREQUEST_FAILURESAction>0andincreasingANDRequest_misses>0IncreaseSHARED_POOL_

RESERVED_SIZE>0andincreasingANDFree_memory=>50%ofSHARED_POOL_RESERVED_SIZEIncreaseSHARED_POOL_

SIZE=0ORFree_memory=>50%ofSHARED_POOL_RESERVED_SIZEDecreaseSHARED_POOL_

RESERVED_SIZEKeepingLargeObjectsFindthosePL/SQLobjectsthatarenotkeptinthelibrarycache:Pinlargepackagesinthelibrarycache:SQL>EXECUTEDBMS_SHARED_POOL.KEEP(‘package_name’);

SQL>SELECT*FROMV$DB_OBJECT_CACHE2WHEREsharable_mem>100003AND(type='PACKAGE'ORtype='PACKAGEBODY'OR4type='FUNCTION'ORtype='PROCEDURE')5ANDkept='NO';DataDictionaryCacheThedatadictionarycacheholdsrowimagesofdatadictionaryrows.Datadictionarycache

DatadictionaryStatspack/AWRreportoutput:Iftherearetoomanycachemisses,increasetheSHARED_POOL_SIZEparameter.DictionaryCacheMisses

GetPctFinalCacheRequestsMissUsage-------------------------------------dc_histogram_data5,58726.8411dc_histogram_defs92,5013.7899dc_object_grants24055.026dc_object_ids959,2930.1289dc_objects19,3855.7314UGAandOracleSharedServerSharedpoolPGASharedpool

or

LargepoolStackspacePGAStackspaceUsersessiondataCursorstateUGA

V$STATNAME

V$SESSTAT

V$MYSTATOPEN_CURSORSSESSION_CACHED_CURSORSC