Oracle 9i 数据库性能调优技术-les04

Copyright©OracleCorporation,2002.Allrightsreserved.SizingtheSharedPool4-2Copyright©OracleCorporation,2002.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Determinethesizeofanobjectandpinitinthesharedpool•Tunethesharedpoolreservedspace•Describetheuserglobalarea(UGA)andsessionmemoryconsiderations•Measurethelibrarycachehitratio•Listothertuningissuesrelatedtothesharedpool•Measurethedictionarycachehitratio•Setthelargepool4-3Copyright©OracleCorporation,2002.Allrightsreserved.SharedPoolContentsMajorcomponentsofthesharedpoolare:•Librarycache•Datadictionarycache•Userglobalarea(UGA)forsharedserversessionsDatabasebuffercacheRedologbufferSharedpoolLibrarycacheDatadictionarycacheUserglobalareaLargepoolUGA4-4Copyright©OracleCorporation,2002.Allrightsreserved.SharedPool•DefinedbySHARED_POOL_SIZE•Librarycachecontainsstatementtext,parsedcode,andexecutionplan.•Datadictionarycachecontainsdefinitionsfortables,columns,andprivilegesfromthedatadictionarytables.•UGAcontainssessioninformationforOracleSharedServeruserswhenalargepoolisnotconfigured.SharedpoolLibrarycacheDatadictionarycacheUGASharedpoolSharedpool4-5Copyright©OracleCorporation,2002.Allrightsreserved.TheLibraryCache•UsedtostoreSQLstatementsandPL/SQLblockstobesharedbyusers•Managedbyaleastrecentlyused(LRU)algorithm•Usedtopreventstatementsreparsing4-6Copyright©OracleCorporation,2002.Allrightsreserved.SharedSQL,PL/SQLareasTheLibraryCacheContextareaforSELECTstatement2ContextareaforSELECTstatement1SELECTstatement2SELECTstatement1SELECTstatement14-7Copyright©OracleCorporation,2002.Allrightsreserved.ImportantSharedPoolLatches•sharedpool:Protectsmemoryallocationsinthesharedpool•librarycache:LocatesmatchingSQLinthesharedpool4-8Copyright©OracleCorporation,2002.Allrightsreserved.SharedPoolandLibraryCacheLatchesContentionforsharedpoollatchandlibrarycachelatchindicatesoneormoreofthefollowing:•UnsharedSQL•ReparsedsharableSQL•Insufficientlysizedlibrarycache4-9Copyright©OracleCorporation,2002.Allrightsreserved.TuningtheLibraryCacheReducemissesbykeepingparsingtoaminimum:•Makesurethatuserscansharestatements.•Preventstatementsfrombeingagedoutbyallocatingenoughspace.•Avoidinvalidationsthatinducereparsing.4-10Copyright©OracleCorporation,2002.Allrightsreserved.TuningtheLibraryCacheAvoidfragmentationby:•Reservingspaceforlargememoryrequirements•Pinningfrequentlyrequiredlargeobjects•EliminatinglargeanonymousPL/SQLblocks•EnablingtheuseoflargepoolforOracleSharedServerconnections4-11Copyright©OracleCorporation,2002.Allrightsreserved.Terminology•Gets:(Parse)Thenumberoflookupsforobjectsofthenamespace•Pins:(Execution)Thenumberofreadsorexecutionsoftheobjectsofthenamespace•Reloads:(Parse)Thenumberoflibrarycachemissesontheexecutionstep,therebycausinganimplicitreparsingoftheSQLstatement•Invalidations:(Parse)Ifanobjectismodifiedthenallexplainplansthatreferencetheobjectaremarkedinvalidandmustbeparsedagain.4-12Copyright©OracleCorporation,2002.Allrightsreserved.v$sgastatv$librarycachev$sqlv$sqlareav$sqltextv$db_object_cacheDiagnosticToolsforTuningtheLibraryCacheParametersaffectingthecomponents:SHARED_POOL_SIZE,OPEN_CURSORSSESSION_CACHED_CURSORS,CURSOR_SPACE_FOR_TIMECURSOR_SHARING,SHARED_POOL_RESERVED_SIZEsp_m_n.lstreport.txtDatadictionarycacheUGASharedpoolLibrarycacheSharedSQLandPL/SQLViews4-13Copyright©OracleCorporation,2002.Allrightsreserved.AreCursorsBeingShared?•Checkgethitrationinv$librarycache:•Determinewhichstatementsusersarerunning:SQLSELECTsql_text,users_executing,2executions,loads3FROMv$sqlarea;SQLSELECT*FROMv$sqltext2WHEREsql_textLIKE3'SELECT*FROMhr.employeesWHERE%';SQLSELECTgethitratio2FROMv$librarycache3WHEREnamespace='SQLAREA';4-14Copyright©OracleCorporation,2002.Allrightsreserved.SharingCursorsValuesforCURSOR_SHARINGare:•Exact•Similar•Force4-15Copyright©OracleCorporation,2002.Allrightsreserved.Guidelines:LibraryCacheReloads•Reloadsshouldbelessthan1%ofthepins:•Ifthereloads-to-pinsratioisgreaterthan1%,increasethevalueoftheSHARED_POOL_SIZEparameter.SQLSELECTSUM(pins)Executions,2SUM(reloads)CacheMisses,3SUM(reloads)/SUM(pins)4FROMv$librarycache;ExecutesPROC1—1stpin,1loadExecutesPROC1—2ndpin,noreloadExecutesPROC1—3rdpin,noreloadExecutesPROC1—4thpin,noreload4pinsandnoreloads4-17Copyright©OracleCorporation,2002.Allrightsreserved.InvalidationsThenumberoftimesobjectsofthenamespaceweremarkedinvalid,causingreloads:SQLSELECTcount(*)FROMhr.employees;SQLSELECTnamespace,pins,reloads,2invalidations3FROMv$librarycache;SQLexecutedbms_stats.gather_table_stats-('HR','EMPLOYEES');SQLSELECTcount(*)FROMhr.employees;SQLSELECTnamespace,pins,reloads,2invalidations3FROMv$librarycache;4-19Copyright©OracleCorporation,2002.Allrightsreserved.SizingtheLibraryCache•Definetheglobalspacenecessaryforstoredobjects(packages,views,andsoon).•DefinetheamountofmemoryusedbytheusualSQLstatements.•Reservespaceforlargememoryrequirementstoavoidmissesandfragmentation.•Pinfrequentlyusedobjects.•ConvertlargeanonymousPL/SQLblocksintosmallanonymousblockscallingpackagedfunctions.4-20Copyright©O