26.oracleocp题库dba经典辅助学习

1、Oracle Architectural ComponentsObjectivesAfter completing this appendix, you should be able to do the following:Describe the Oracle server architecture and its main componentsList the structures involved in connecting a user to an Oracle instanceList the stages in processing:QueriesDML statementsCom

2、mitsOracle Database Architecture: OverviewThe Oracle database consists of two main components:The database or the physical structuresThe instance or the memory structuresDatabase Physical Architecture Data filesOnline redo log filesPassword fileParameter fileControl filesArchive log filesControl Fil

3、esContains physical database structure informationMultiplexed to protect against lossRead at mount stageControl filesRedo Log FilesRecord changes to the databaseMultiplexed to protect against lossRedo log bufferLogWriterLGWRGroup 1Group 2Group 3Tablespaces and Data FilesTablespaces consist of one or

4、 more data files.Data files belong to only one tablespace.USERS tablespaceData file 1Data file 2Segments, Extents, and BlocksSegments exist within a tablespace.Segments consist of a collection of extents.Extents are a collection of data blocks.Data blocks are mapped to OS blocks.SegmentExtentsData b

5、locksOS blocksOracle Instance ManagementSystemMonitorSMONDatabaseWriterDBW0LogWriterLGWRProcessMonitorPMONArchiverARC0SGAJava poolShared poolLarge poolStreams poolDatabasebuffer cacheRedo log bufferCheckpointCKPTOracle Memory StructuresJava poolDatabasebuffer cacheRedo log bufferShared poolLarge poo

6、lSGAStreams poolServerprocess1PGAServerprocess2PGABack-groundprocessPGAOracle Memory Structures (continued) Full Notes PageOracle ProcessesSystemmonitorSMONDatabasewriterDBW0CheckpointCKPTLogwriterLGWRProcessmonitorPMONArchiverARC0ServerprocessServerprocessServerprocessServerprocessSystem Global Are

7、aSGABackground processesOther Key Physical StructuresArchived log filesParameterfilePasswordfileDatabaseProcessing a SQL StatementConnect to an instance using:The user processThe server processThe Oracle server components that are used depend on the type of SQL statement:Queries return rowsDML state

8、ments log changesCommit ensures transaction recoverySome Oracle server components do not participate in SQL statement processing.Connecting to an InstanceUserServerServerUserClientUserServerOracle serverServerApplication serverBrowser Processing a QueryParse:Search for identical statementCheck synta

9、x, object names, and privilegesLock objects used during parseCreate and store execution planExecute: Identify rows selectedFetch: Return rows to user processThe Shared PoolThe library cache contains the SQL statement text, parsed code, and execution plan.The data dictionary cache contains table, col

10、umn, and other object definitions and privileges.The shared pool is sized by SHARED_POOL_SIZE. Data dictionarycacheLibrarycacheShared poolDatabase Buffer CacheStores the most recently used blocksSize of a buffer based on DB_BLOCK_SIZENumber of buffers defined by DB_BLOCK_BUFFERS Database buffercache

11、Program Global Area (PGA)Not sharedWritable only by the server processContains:Sort areaSession informationCursor stateStack spaceServerprocessPGAProcessing a DML StatementUPDATE employees .SGARedo logbufferDatabase buffercacheShared poolUserprocessServerprocessDatabaseData files Control filesRedolo

12、g files11234Redo Log BufferHas its size defined by LOG_BUFFERRecords changes made through the instanceIs used sequentiallyIs a circular bufferDatabase buffercache Rollback SegmentDML statementOld imageNew imageRollback segmentTableCOMMIT Processing134InstanceSGARedo logbufferDatabase buffercacheShared pool LGWR2UserprocessServerprocessDatabaseData files Control filesRedolog filesSummaryIn this appendix, you should have learned how to:Identify database files: data files, control files, and online redo logsDescribe