ORACLE存储结构文档格式.docx
《ORACLE存储结构文档格式.docx》由会员分享，可在线阅读，更多相关《ORACLE存储结构文档格式.docx（16页珍藏版）》请在冰点文库上搜索。
IntroductiontoOracleMemoryStructures
Oracleusesmemorytostoreinformationsuchasthefollowing:
∙Programcodebeingexecuted
∙Informationaboutaconnectedsession,evenifitisnotcurrentlyactive
∙Informationneededduringprogramexecution（forexample,thecurrentstateofaqueryfromwhichrowsarebeingfetched）
∙InformationthatissharedandcommunicatedamongOracleprocesses（forexample,lockinginformation）
∙Cacheddatathatisalsopermanentlystoredonperipheralmemory（forexample,datablocksandredologentries）
ThebasicmemorystructuresassociatedwithOracleinclude:
∙SoftwareCodeAreas
∙SystemGlobalArea（SGA）:
oThedatabasebuffercache
oTheredologbuffer
oThesharedpool
∙ProgramGlobalAreas（PGA）:
oThestackareas
oThedataareas
SystemGlobalArea（SGA）
Asystemglobalarea（SGA）isagroupofsharedmemorystructuresthatcontaindataandcontrolinformationforoneOracledatabaseinstance.Ifmultipleusersareconcurrentlyconnectedtothesameinstance,thenthedataintheinstance'
sSGAissharedamongtheusers.Consequently,theSGAissometimesreferredtoasthesharedglobalarea.
AnSGAandOracleprocessesconstituteanOracleinstance.OracleautomaticallyallocatesmemoryforanSGAwhenyoustartaninstanceandtheoperatingsystemreclaimsthememorywhenyoushutdowntheinstance.EachinstancehasitsownSGA.
TheSGAisread-write;
allusersconnectedtoamultiple-processdatabaseinstancemayreadinformationcontainedwithintheinstance'
sSGA,andseveralprocesseswritetotheSGAduringexecutionofOracle.
TheSGAcontainsthefollowingdatastructures:
∙Thedatabasebuffercache
∙Theredologbuffer
∙Thesharedpool
∙Thelargepool（optional）
∙Thedatadictionarycache
∙Othermiscellaneousinformation
PartoftheSGAcontainsgeneralinformationaboutthestateofthedatabaseandtheinstance,whichthebackgroundprocessesneedtoaccess;
thisiscalledthefixedSGA.Nouserdataisstoredhere.TheSGAalsoincludesinformationcommunicatedbetweenprocesses,suchaslockinginformation.
Ifthesystemusesmulti-threadedserverarchitecture,thentherequestandresponsequeuesandsomecontentsoftheprogramglobalareas,areintheSGA.
TheDatabaseBufferCache
ThedatabasebuffercacheistheportionoftheSGAthatholdscopiesofdatablocksreadfromdatafiles.Alluserprocessesconcurrentlyconnectedtotheinstanceshareaccesstothedatabasebuffercache.
ThedatabasebuffercacheandthesharedSQLcachearelogicallysegmentedintomultiplesets.Thisorganizationintomultiplesetsreducescontentiononmultiprocessorsystems.
OrganizationoftheDatabaseBufferCache
Thebuffersinthecacheareorganizedintwolists:
thewritelistandtheleastrecentlyused（LRU）list.Thewritelistholdsdirtybuffers,whichcontaindatathathasbeenmodifiedbuthasnotyetbeenwrittentodisk.Theleastrecentlyused（LRU）listholdsfreebuffers,pinnedbuffers,anddirtybuffersthathavenotyetbeenmovedtothewritelist.Freebuffersdonotcontainanyusefuldataandareavailableforuse.Pinnedbuffersarecurrentlybeingaccessed.
WhenanOracleprocessaccessesabuffer,theprocessmovesthebuffertothemostrecentlyused（MRU）endoftheLRUlist.AsmorebuffersarecontinuallymovedtotheMRUendoftheLRUlist,dirtybuffersagetowardstheLRUendoftheLRUlist.
ThefirsttimeanOracleuserprocessrequiresaparticularpieceofdata,itsearchesforthedatainthedatabasebuffercache.Iftheprocessfindsthedataalreadyinthecache（acachehit）,itcanreadthedatadirectlyfrommemory.Iftheprocesscannotfindthedatainthecache（acachemiss）,itmustcopythedatablockfromadatafileondiskintoabufferinthecachebeforeaccessingthedata.Accessingdatathroughacachehitisfasterthandataaccessthroughacachemiss.
Beforereadingadatablockintothecache,theprocessmustfirstfindafreebuffer.TheprocesssearchestheLRUlist,startingattheleastrecentlyusedendofthelist.Theprocesssearcheseitheruntilitfindsafreebufferoruntilithassearchedthethresholdlimitofbuffers.
IftheuserprocessfindsadirtybufferasitsearchestheLRUlist,itmovesthatbuffertothewritelistandcontinuestosearch.Whentheprocessfindsafreebuffer,itreadsthedatablockfromdiskintothebufferandmovesthebuffertotheMRUendoftheLRUlist.
IfanOracleuserprocesssearchesthethresholdlimitofbufferswithoutfindingafreebuffer,theprocessstopssearchingtheLRUlistandsignalstheDBW0backgroundprocesstowritesomeofthedirtybufferstodisk.
TheLRUAlgorithmandFullTableScans
Whentheuserprocessisperformingafulltablescan,itreadstheblocksofthetableintobuffersandputsthemontheLRUend（insteadoftheMRUend）oftheLRUlist.Thisisbecauseafullyscannedtableusuallyisneededonlybriefly,sotheblocksshouldbemovedoutquicklytoleavemorefrequentlyusedblocksinthecache.
Youcancontrolthisdefaultbehaviorofblocksinvolvedintablescansonatable-by-tablebasis.TospecifythatblocksofthetablearetobeplacedattheMRUendofthelistduringafulltablescan,usetheCACHEclausewhencreatingoralteringatableorcluster.YoumaywanttospecifythisbehaviorforsmalllookuptablesorlargestatichistoricaltablestoavoidI/Oonsubsequentaccessesofthetable.
SizeoftheDatabaseBufferCache
TheinitializationparameterDB_BLOCK_BUFFERSspecifiesthenumberof
buffersinthedatabasebuffercache.EachbufferinthecacheisthesizeofoneOracledatablock（whichisspecifiedbytheinitializationparameterDB_BLOCK_SIZE）;
therefore,eachdatabasebufferinthecachecanholdasingledatablockreadfromadatafile.
Thecachehasalimitedsize,sonotallthedataondiskcanfitinthecache.Whenthecacheisfull,subsequentcachemissescauseOracletowritedirtydataalreadyinthecachetodisktomakeroomforthenewdata.（Ifabufferisnotdirty,itdoesnotneedtobewrittentodiskbeforeanewblockcanbereadintothebuffer.）Subsequentaccesstoanydatathatwaswrittentodiskresultsinadditionalcachemisses.
Thesizeofthecacheaffectsthelikelihoodthatarequestfordatawillresultinacachehit.Ifthecacheislarge,itismorelikelytocontainthedatathatisrequested.Increasingthesizeofacacheincreasesthepercentageofdatarequeststhatresultincachehits.
MultipleBufferPools
Youcanconfigurethedatabasebuffercachewithseparatebufferpoolsthateitherkeepdatainthebuffercacheormakethebuffersavailablefornewdataimmediatelyafterusingthedatablocks.Particularschemaobjects（tables,clusters,indexes,andpartitions）canthenbeassignedtotheappropriatebufferpooltocontrolthewaytheirdatablocksageoutofthecache.
∙TheKEEPbufferpoolretainstheschemaobject'
sdatablocksinmemory.
∙TheRECYCLEbufferpooleliminatesdatablocksfrommemoryassoonastheyarenolongerneeded.
∙TheDEFAULTbufferpoolcontainsdatablocksfromschemaobjectsthatarenotassignedtoanybufferpool,aswellasschemaobjectsthatareexplicitlyassignedtotheDEFAULTpool.
TheinitializationparametersthatconfiguretheKEEPandRECYCLEbufferpoolsareBUFFER_POOL_KEEPandBUFFER_POOL_RECYCLE.
TheRedoLogBuffer
TheredologbufferisacircularbufferintheSGAthatholdsinformationaboutchangesmadetothedatabase.Thisinformationisstoredinredoentries.Redoentriescontaintheinformationnecessarytoreconstruct,orredo,changesmadetothedatabasebyINSERT,UPDATE,DELETE,CREATE,ALTER,orDROPoperations.Redoentriesareusedfordatabaserecovery,ifnecessary.
RedoentriesarecopiedbyOracleserverprocessesfromtheuser'
smemoryspacetotheredologbufferintheSGA.Theredoentriestakeupcontinuous,sequentialspaceinthebuffer.ThebackgroundprocessLGWRwritestheredologbuffertotheactiveonlineredologfile（orgroupoffiles）ondisk.
TheinitializationparameterLOG_BUFFERdeterminesthesize（inbytes）oftheredologbuffer.Ingeneral,largervaluesreducelogfileI/O,particularlyiftransactionsarelongornumerous.Thedefaultsettingisfourtimesthemaximumdatablocksizeforthehostoperatingsystem.
TheSharedPool
ThesharedpoolportionoftheSGAcontainsthreemajorareas:
librarycache,dictionary
cache,andcontrolstructures.Figure
7-1showsthecontentsofthesharedpool.
ThetotalsizeofthesharedpoolisdeterminedbytheinitializationparameterSHARED_POOL_SIZE.Thedefaultvalueofthisparameteris3,500,000bytes.Increasingthevalueofthisparameterincreasestheamountofmemoryreservedforthesharedpool,andthereforeincreasesthespacereservedforsharedSQLareas.
Figure7-1ContentsoftheSharedPool
LibraryCache
ThelibrarycacheincludesthesharedSQLareas,privateSQLareas,PL/SQLproceduresandpackages,andcontrolstructuressuchaslocksandlibrarycachehandles.
SharedSQLareasmustbeavailabletomultipleusers,sothelibrarycacheiscontainedinthesharedpoolwithintheSGA.Thesizeofthelibrarycache（alongwiththesizeofthedatadictionarycache）islimitedbythesizeofthesharedpool.
SharedSQLAreasandPrivateSQLAreas
OraclerepresentseachSQLstatementitexecuteswithasharedSQLareaandaprivateSQLarea.OraclerecognizeswhentwousersareexecutingthesameSQLstatementandreusesthesharedSQLareaforthoseusers.However,eachusermus
升级会员 