Oracle 10g SQL Fundamentals II--App_d

Category: Entertainment

Presentation Description

No description available.


Presentation Transcript

Oracle Architectural Components : 

Oracle Architectural Components

Objectives : 

Objectives After completing this appendix, you should be able to do the following: Describe the Oracle server architecture and its main components List the structures involved in connecting a user to an Oracle instance List the stages in processing: Queries DML statements Commits

Oracle Database Architecture: Overview : 

Oracle Database Architecture: Overview The Oracle database consists of two main components: The database or the physical structures The instance or the memory structures

Database Physical Architecture : 

Database Physical Architecture Data files Online redo log files Password file Parameter file Control files Archive log files

Control Files : 

Control Files Contains physical database structure information Multiplexed to protect against loss Read at mount stage

Redo Log Files : 

Redo Log Files Record changes to the database Multiplexed to protect against loss Redo log buffer Log Writer LGWR Group 1 Group 2 Group 3

Tablespaces and Data Files : 

Tablespaces and Data Files Tablespaces consist of one or more data files. Data files belong to only one tablespace. USERS tablespace Data file 1 Data file 2

Segments, Extents, and Blocks : 

Segments, Extents, and Blocks Segments 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. Extents Data blocks OS blocks

Oracle Instance Management : 

Oracle Instance Management System Monitor SMON Database Writer DBW0 Log Writer LGWR Process Monitor PMON Archiver ARC0 SGA Java pool Shared pool Large pool Streams pool Database buffer cache Redo log buffer Check point CKPT

Oracle Memory Structures : 

Oracle Memory Structures Java pool Database buffer cache Redo log buffer Shared pool Large pool SGA Streams pool Server process 1 PGA Server process 2 PGA Back- ground process PGA

Oracle Memory Structures (continued) Full Notes Page : 

Oracle Memory Structures (continued) Full Notes Page

Oracle Processes : 

Oracle Processes System monitor SMON Database writer DBW0 Check point CKPT Log writer LGWR Process monitor PMON Archiver ARC0 Server process Server process Server process Server process System Global Area SGA Background processes

Other Key Physical Structures : 

Other Key Physical Structures

Processing a SQL Statement : 

Processing a SQL Statement Connect to an instance using: The user process The server process The Oracle server components that are used depend on the type of SQL statement: Queries return rows DML statements log changes Commit ensures transaction recovery Some Oracle server components do not participate in SQL statement processing.

Connecting to an Instance : 

Connecting to an Instance User Server Server User Client User Server Oracle server Server Application server Browser

Processing a Query : 

Processing a Query Parse: Search for identical statement Check syntax, object names, and privileges Lock objects used during parse Create and store execution plan Execute: Identify rows selected Fetch: Return rows to user process

The Shared Pool : 

The Shared Pool The library cache contains the SQL statement text, parsed code, and execution plan. The data dictionary cache contains table, column, and other object definitions and privileges. The shared pool is sized by SHARED_POOL_SIZE. Data dictionarycache Library cache Shared pool

Database Buffer Cache : 

Database Buffer Cache Stores the most recently used blocks Size of a buffer based on DB_BLOCK_SIZE Number of buffers defined by DB_BLOCK_BUFFERS

Program Global Area (PGA) : 

Program Global Area (PGA) Not shared Writable only by the server process Contains: Sort area Session information Cursor state Stack space Serverprocess PGA

Processing a DML Statement : 

Processing a DML Statement UPDATE employees ... SGA Redo logbuffer Database buffercache Shared pool Userprocess Serverprocess 1 1 2 3 4

Redo Log Buffer : 

Redo Log Buffer Has its size defined by LOG_BUFFER Records changes made through the instance Is used sequentially Is a circular buffer

Rollback Segment : 

Rollback Segment DML statement Old image New image Rollback segment Table

COMMIT Processing : 

COMMIT Processing 1 3 4 Instance SGA Redo logbuffer Database buffercache Shared pool LGWR 2 Userprocess Serverprocess Database Data files Control files Redo log files

Summary : 

Summary In this appendix, you should have learned how to: Identify database files: data files, control files, and online redo logs Describe SGA memory structures: DB buffer cache, shared SQL pool, and redo log buffer Explain primary background processes:DBW0, LGWR, CKPT, PMON, SMON, and ARC0 List SQL processing steps: parse, execute, fetch

authorStream Live Help