ORACLE存储结构Word文件下载.docx

ORACLE存储结构Word文件下载.docx

《ORACLE存储结构Word文件下载.docx》由会员分享，可在线阅读，更多相关《ORACLE存储结构Word文件下载.docx（12页珍藏版）》请在冰豆网上搜索。

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