Copyright©OracleCorporation,2001.Allrightsreserved.OracleArchitecturalComponents1-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•OutlinetheOraclearchitectureanditsmaincomponents•ListthestructuresinvolvedinconnectingausertoanOracleinstance1-3Copyright©OracleCorporation,2001.Allrightsreserved.OverviewofPrimaryComponentsInstanceSGARedologbuffercacheSharedpoolDataDict.cacheLibrarycacheDBWRSMONPMONCKPTLGWROthersUserprocessServerprocessPGAControlfilesDatafilesArchivedlogfilesParameterfilePasswordfileRedologfilesDatabaseDatabasebuffercache1-4Copyright©OracleCorporation,2001.Allrightsreserved.OracleServerAnOracleserver:•Isadatabasemanagementdatabasemanagementsystemthatprovidesansystemthatprovidesanopen,comprehensive,open,comprehensive,integratedapproachtointegratedapproachtoinformationmanagementinformationmanagement•ConsistsofanOracleonsistsofanOracleinstanceandanOracleinstanceandanOracledatabasedatabaseOracleServer1-5Copyright©OracleCorporation,2001.Allrightsreserved.OracleInstanceAnOracleinstance:•IsameanstoaccessanOracledatabase•Alwaysopensoneandonlyonedatabase•ConsistsofmemoryandprocessstructuresBackgroundstructuresMemorystructuresSGARedologbuffercacheDatabasebuffercacheSharedpoolDBWRSMONPMONCKPTLGWROthersDataDictionarycacheLibrarycacheInstance1-6Copyright©OracleCorporation,2001.Allrightsreserved.EstablishingaConnectionandCreatingaSessionConnectingtoanOracleinstanceconsistsofestablishingauserconnectionandcreatingasession.ConnectionestablishedSessioncreatedDatabaseuserUserprocessOracleserverServerprocess1-7Copyright©OracleCorporation,2001.Allrightsreserved.OracleDatabaseAnOracledatabase:•Isacollectionofdatathatistreatedasaunit•ConsistsofthreefiletypesControlfilesDatafilesArchivedlogfilesParameterfilePasswordfileRedologfilesOracleDatabase1-8Copyright©OracleCorporation,2001.Allrightsreserved.PhysicalStructureThephysicalstructureofanOracledatabaseisdeterminedbytheoperatingsystemfilesthatprovidetheactualphysicalstoragefordatabaseinformation.•Controlfiles•Datafiles•RedologfilesControlfilesDatafiles(includesdatadictionary)HeaderOnlineredologfiles)1-9Copyright©OracleCorporation,2001.Allrightsreserved.MemoryStructureOracle’smemorystructureconsistsoftwomemoryareasknownas:•SystemGlobalArea(SGA):Allocatedatinstancestartup,andisafundamentalcomponentofanOracleInstance•ProgramGlobalArea(PGA):Allocatedwhentheserverprocessisstarted1-10Copyright©OracleCorporation,2001.Allrightsreserved.SystemGlobalArea(SGA)•TheSGAconsistsofseveralmemorystructures:–Sharedpool–Databasebuffercache–Redologbuffer•TherearetwooptionalmemorystructuresthatcanbeconfiguredwithintheSGA:–Largepool–Javapool1-11Copyright©OracleCorporation,2001.Allrightsreserved.SystemGlobalArea(SGA)•SGAisdynamicandsizedusingSGA_MAX_SIZE.•SGAmemoryallocatedandtrackedingranulesbySGAcomponents–Contiguousvirtualmemoryallocation–SizebasedonSGA_MAX_SIZE1-12Copyright©OracleCorporation,2001.Allrightsreserved.SharedPoolThesharedpoolisusedtostorethemostrecentlyexecutedSQLstatementsandthemostrecentlyuseddatadefinitions.•Itconsistsoftwokeyperformance-relatedmemorystructures:–Librarycache–Datadictionarycache•SizedbytheparameterSHARED_POOL_SIZE.SharedpoolDatadictionarycacheLibrarycacheALTERSYSTEMSETSHARED_POOL_SIZE=64M;1-13Copyright©OracleCorporation,2001.Allrightsreserved.LibraryCacheThelibrarycachestoresinformationaboutthemostrecentlyusedSQLandPL/SQLstatements.Thelibrarycache:•Enablesthesharingofcommonlyusedstatements•Ismanagedbyaleastrecentlyused(LRU)algorithm•Consistsoftwostructures:–SharedSQLarea–SharedPL/SQLarea•Hasitssizedeterminedbythesharedpoolsizing1-14Copyright©OracleCorporation,2001.Allrightsreserved.DataDictionaryCacheThedatadictionarycacheisacollectionofthemostrecentlyuseddefinitionsinthedatabase.•Itincludesinformationaboutdatabasefiles,tables,indexes,columns,users,privileges,andotherdatabaseobjects.•Duringtheparsephase,theserverprocesslooksatthedatadictionaryforinformationtoresolveobjectnamesandvalidateaccess.•Cachingthedatadictionaryinformationintomemoryimprovesresponsetimeonqueries.•Sizeisdeterminedbythesharedpoolsizing.1-15Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseBufferCacheThedatabasebuffercachestorescopiesofdatablocksthathavebeenretrievedfromthedatafiles.•Itenablesgreatperformancegainswhenyouobtainandupdatedata.•Itismanagedthroughaleastrecentlyused(LRU)algorithm.•DB_BLOCK_SIZEdeterminestheprimaryblocksize.Databasebuffercache1-16Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseBufferCacheDB_CACHE_SIZE•DatabasebuffercachecanbedynamicallyresizedtogroworshrinkusingALTERSYSTEM.•DB_CACHE_ADVICEcanbesettogatherstatisticsforpredictingdifferentcachesizebehavior.ALTERSYSTEMSETDB_CACHE_SIZE=96M;1-17Copyright©OracleCorporation,2001.Allrightsreserved.RedoLogBufferCacheTheredologbuffercacherecordsallchangesmadetothedatabasedatablocks.•Itsprimarypurposeisrecovery.•Changesrecordedwithinarecalledredoentries.•Redoentriescontaininformationtoreconstructorredochanges.•SizeisdefinedbyLOG_BUFF