Oracle OCP SQL Oracle Architectural Components7729.pptx

《Oracle OCP SQL Oracle Architectural Components7729.pptx》由会员分享，可在线阅读，更多相关《Oracle OCP SQL Oracle Architectural Components7729.pptx（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、ECopyright Oracle Corporation,2001.All rights reserved.Oracle Architectural ComponentsE-2Copyright Oracle Corporation,2001.All rights reserved.ObjectivesAfter completing this appendix,you should be ableto do the following:Describe the Oracle Server architecture and its main componentsList the struct

2、ures involved in connecting a user to an Oracle instanceList the stages in processing:QueriesDML statementsCommitsE-3Copyright Oracle Corporation,2001.All rights reserved.DatabaseRedolog filesControl filesData files OverviewOverviewUserprocessServerprocessPGAArchived log filesParameterfilePasswordfi

3、leInstanceSGARedo logbufferData buffercacheShared poolData dict.cacheLibrarycache PMON DBW0SMON LGWRCKPTOthersE-5Copyright Oracle Corporation,2001.All rights reserved.Oracle Database FilesOracle Database FilesDatabaseRedolog filesControl filesData files Archived log filesParameterfilePasswordfileE-6

4、Copyright Oracle Corporation,2001.All rights reserved.Other Key Physical StructuresOther Key Physical StructuresArchivedlog filesDatabaseArchived log filesParameterfilePasswordfileE-7Copyright Oracle Corporation,2001.All rights reserved.Oracle InstanceOracle InstanceBackground Background processespr

5、ocessesAn Oracle instance:An Oracle instance:Is a means to access an Oracle databaseIs a means to access an Oracle databaseAlways opens one and only one databaseAlways opens one and only one databaseMemory Memory structuresstructuresInstanceSGARedo logbufferData buffercacheShared pool PMON DBW0SMON

6、LGWRCKPTOthersE-9Copyright Oracle Corporation,2001.All rights reserved.Processing a SQL StatementProcessing a SQL StatementConnect to an instance using:Connect to an instance using:The user processThe user processThe server processThe server processThe Oracle Server components that are used The Orac

7、le Server components that are used depend on the type of SQL statement:depend on the type of SQL statement:Queries return rowsQueries return rowsDML statements log changesDML statements log changesCommit ensures transaction recoveryCommit ensures transaction recoverySome Oracle Server components do

8、not Some Oracle Server components do not participate in SQL statement processing.participate in SQL statement processing.E-10Copyright Oracle Corporation,2001.All rights reserved.Connecting to an InstanceConnecting to an InstanceUserServerServerUserClientClientUserServerOracle ServerServerServerAppl

9、ication serverApplication serverBrowserBrowser E-12Copyright Oracle Corporation,2001.All rights reserved.Processing a QueryProcessing a QueryParse:Parse:Search for identical statementSearch for identical statementCheck syntax,object names,and privilegesCheck syntax,object names,and privilegesLock ob

10、jects used during parseLock objects used during parseCreate and store execution planCreate and store execution planExecute:Identify rows selectedExecute:Identify rows selectedFetch:Return rows to user processFetch:Return rows to user processE-13Copyright Oracle Corporation,2001.All rights reserved.T

11、he Shared PoolThe Shared PoolThe library cache contains the SQL statement text,The library cache contains the SQL statement text,parsed code,and execution plan.parsed code,and execution plan.The data dictionary cache contains table,column,The data dictionary cache contains table,column,and other obj

12、ect definitions and privileges.and other object definitions and privileges.The shared pool is sized by The shared pool is sized by SHARED_POOL_SIZESHARED_POOL_SIZE.Data dictionarycacheLibrarycacheShared poolE-15Copyright Oracle Corporation,2001.All rights reserved.Database Buffer CacheDatabase Buffe

13、r CacheStores the most recently used blocksStores the most recently used blocksSize of a buffer based on Size of a buffer based on DB_BLOCK_SIZEDB_BLOCK_SIZENumber of buffers defined by Number of buffers defined by DB_BLOCK_BUFFERSDB_BLOCK_BUFFERS Data buffercacheE-16Copyright Oracle Corporation,200

14、1.All rights reserved.Program Global Area(PGA)Program Global Area(PGA)Not sharedNot sharedWritable only by the server processWritable only by the server processContains:Contains:Sort areaSort areaSession informationSession informationCursor stateCursor stateStack spaceStack spaceServerprocessPGAE-17

15、Copyright Oracle Corporation,2001.All rights reserved.11Processing a DML StatementProcessing a DML Statement234UPDATE employees.UserprocessServerprocessDatabaseRedolog filesControl filesData files SGARedo logbufferData buffercacheShared poolE-19Copyright Oracle Corporation,2001.All rights reserved.R

16、edo Log BufferRedo Log BufferHas its size defined by Has its size defined by LOG_BUFFERLOG_BUFFER Records changes made through the instanceRecords changes made through the instanceIs used sequentiallyIs used sequentiallyIs a circular bufferIs a circular buffer Redo logbufferE-20Copyright Oracle Corp

17、oration,2001.All rights reserved.Rollback Segment Rollback SegmentDML statementDML statementOld imageOld imageNew New imageimageRollback segmentRollback segmentTableTableE-21Copyright Oracle Corporation,2001.All rights reserved.SGARedo logbufferData buffercacheShared pool LGWRInstanceDatabaseRedolog

18、 filesControl filesData files COMMITCOMMIT Processing Processing1234UserprocessServerprocessE-23Copyright Oracle Corporation,2001.All rights reserved.Log Writer(Log Writer(LGWRLGWR)LGWRLGWR writes when:writes when:There is a There is a COMMIT COMMIT The redo buffer log The redo buffer log is one-thi

19、rd fullis one-third fullThere is more than There is more than 1 MB of redo1 MB of redoBefore DBW0 writesBefore DBW0 writesSGARedo logbufferData buffercacheShared pool LGWRDatabaseRedolog filesControl filesData files E-24Copyright Oracle Corporation,2001.All rights reserved.Other Instance ProcessesOt

20、her required processes:Database Writer(DBW0)Process Monitor(PMON)System Monitor(SMON)Checkpoint(CKPT)The archive process(ARC0)is usually createdin a production database E-25Copyright Oracle Corporation,2001.All rights reserved.Database Writer(Database Writer(DBW0DBW0)DBW0DBW0 writes when:writes when

21、:There are many dirty There are many dirty buffersbuffersThere are few free There are few free buffersbuffersTimeout occursTimeout occursCheckpoint occursCheckpoint occursSGARedo logbufferData buffercacheShared poolDatabaseRedolog filesControl filesData files DBW0E-26Copyright Oracle Corporation,200

22、1.All rights reserved.SMON:System MonitorAutomatically recovers the instance:Rolls forward changes in the redo logsOpens the database for user accessRolls back uncommitted transactionsCoalesces free spaceDeallocates temporary segmentsE-27Copyright Oracle Corporation,2001.All rights reserved.PMON:Pro

23、cess MonitorCleans up after failed processes by:Rolling back the transactionReleasing locksReleasing other resourcesE-28Copyright Oracle Corporation,2001.All rights reserved.SummaryIn this appendix,you should have learned how to:Identify database files:data files,control files,online redo logsDescribe SGA memory structures:DB buffer cache,shared SQL pool,and redo log bufferExplain primary background processes:DBW0,LGWR,CKPT,PMON,SMON,and ARC0List SQL processing steps:parse,execute,fetchE-30Copyright Oracle Corporation,2001.All rights reserved.Hidden Slide