1.pdf

Oracle Architectural Components Copyright © Oracle Corporation, 2001. All rights reserved.  Overview of Primary Components Instance User process Shared pool SGA Libr...

Oracle Architectural Components Copyright © Oracle Corporation, 2001. All rights reserved.  Overview of Primary Components Instance User process Shared pool SGA Library cache Database Redo log Server Data Dict. buffer cache buffer cache process cache PGA PMON SMON DBWR LGWR CKPT Others Data Control Redo log Parameter files files files Archived file log files Password file Database 1-2 Copyright © Oracle Corporation, 2001. All rights reserved.  Oracle Server An Oracle server: Oracle Server Is a database management system that provides an open, comprehensive, integrated approach to information management Consists of an Oracle instance and an Oracle database 1-3 Copyright © Oracle Corporation, 2001. All rights reserved.  Oracle Instance An Oracle instance: Is a means to access an Oracle database Always opens one and only one database Consists of memory and process structures Instance Shared pool SGA Library Memory cache structures Database Redo log Data Dictionary buffer cache buffer cache cache Background PMON SMON DBWR LGWR CKPT Others structures 1-4 Copyright © Oracle Corporation, 2001. All rights reserved.  Establishing a Connection and Creating a Session Connecting to an Oracle instance consists of establishing a user connection and creating a session. Server Connection process established Session created User Oracle server process Database user 1-5 Copyright © Oracle Corporation, 2001. All rights reserved.  Oracle Database An Oracle database: Is a collection of data that is treated as a unit Consists of three file types Oracle Database Data Control Redo Parameter files files log files Archived file log files Password file 1-6 Copyright © Oracle Corporation, 2001. All rights reserved.  Physical Structure The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information. Control files Data files Header Control Redo log files files Data files (includes Online data redo log dictionary) files) 1-7 Copyright © Oracle Corporation, 2001. All rights reserved.  Memory Structure Oracle’s memory structure consists of two memory areas known as: System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance Program Global Area (PGA): Allocated when the server process is started 1-8 Copyright © Oracle Corporation, 2001. All rights reserved.  System Global Area (SGA) The SGA consists of several memory structures: – Shared pool – Database buffer cache – Redo log buffer – Other structures (e.g. lock and latch management, statistical data) There are two optional memory structures that can be configured within the SGA: – Large pool – Java pool 1-9 Copyright © Oracle Corporation, 2001. All rights reserved.  System Global Area (SGA) SGA is dynamic and sized using SGA_MAX_SIZE. SGA memory allocated and tracked in granules by SGA components – Contiguous virtual memory allocation – Size based on SGA_MAX_SIZE 1-10 Copyright © Oracle Corporation, 2001. All rights reserved.  Shared Pool The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions. It consists of two key performance-related memory structures: – Library cache – Data dictionary cache Sized by the parameter Shared pool SHARED_POOL_SIZE. Library cache ALTER SYSTEM SET Data dictionary SHARED_POOL_SIZE = 64M; cache 1-11 Copyright © Oracle Corporation, 2001. All rights reserved.  Library Cache The library cache stores information about the most recently used SQL and PL/SQL statements. The library cache: Enables the sharing of commonly used statements Is managed by a least recently used (LRU) algorithm Consists of two structures: – Shared SQL area – Shared PL/SQL area Has its size determined by the shared pool sizing 1-12 Copyright © Oracle Corporation, 2001. All rights reserved.  Data Dictionary Cache The data dictionary cache is a collection of the most recently used definitions in the database. It includes information about database files, tables, indexes, columns, users, privileges, and other database objects. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access. Caching the data dictionary information into memory improves response time on queries. Size is determined by the shared pool sizing. 1-13 Copyright © Oracle Corporation, 2001. All rights reserved.  Database Buffer Cache The database buffer cache stores copies of data blocks that have been retrieved from the data files. It enables great performance gains when you obtain and update data. It is managed through a least recently used (LRU) algorithm. DB_BLOCK_SIZE determines the Database buffer primary block size. cache 1-14 Copyright © Oracle Corporation, 2001. All rights reserved.  Database Buffer Cache Consists of independent sub-caches: – DB_CACHE_SIZE – DB_KEEP_CACHE_SIZE – DB_RECYCLE_CACHE_SIZE Database buffer cache can be dynamically resized to grow or shrink using ALTER SYSTEM. ALTER SYSTEM SET DB_CACHE_SIZE = 96M; DB_CACHE_ADVICE can be set to gather statistics for predicting different cache size behavior. 1-15 Copyright © Oracle Corporation, 2001. All rights reserved.  Redo Log Buffer Cache The redo log buffer cache records all changes made to the database data blocks. Its primary purpose is recovery. Changes recorded within are called redo entries. Redo entries contain information to reconstruct or redo changes. Size is defined by LOG_BUFFER. Redo log buffer cache 1-16 Copyright © Oracle Corporation, 2001. All rights reserved.  Large Pool The large pool is an optional area of memory in the SGA configured only in a shared server environment. It relieves the burden placed on the shared pool. This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations. Unlike the shared pool, the large pool does not use an LRU list. Sized by LARGE_POOL_SIZE. ALTER SYSTEM SET LARGE_POOL_SIZE = 64M; 1-17 Copyright © Oracle Corporation, 2001. All rights reserved.  Java Pool The Java pool services the parsing requirements for Java commands. Required if installing and using Java. It is stored much the same way as PL/SQL in database tables. It is sized by the JAVA_POOL_SIZE parameter. 1-18 Copyright © Oracle Corporation, 2001. All rights reserved.  Program Global Area (PGA) The PGA is memory reserved for each user process that connects to an Oracle database. PGA Dedicated server Shared server Server process Session Stack information Stack sort area, cursor space sort area, cursor space information information User SGA SGA process Session information Shared SQL areas Shared SQL areas 1-19 Copyright © Oracle Corporation, 2001. All rights reserved.  Process Structure An Oracle process is a program that depending on its type can request information, execute a series of steps, or perform a specific task. Oracle takes advantage of various types of processes: User process: Started at the time a database user requests connection to the Oracle server Server process: Connects to the Oracle Instance and is started when a user establishes a session. Background process: Available when an Oracle instance is started 1-20 Copyright © Oracle Corporation, 2001. All rights reserved.  User Process A user process is a program that requests interaction with the Oracle server. It must first establish a connection. It does not interact directly with the Oracle server. Server process User process Connection established Database user 1-21 Copyright © Oracle Corporation, 2001. All rights reserved.  Server Process A server process is a program that directly interacts with the Oracle server. It fulfills calls generated and returns results. Can be dedicated or shared server. Server process Connection established Session created User Oracle server process Database user 1-22 Copyright © Oracle Corporation, 2001. All rights reserved.  Background Processes The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes. Mandatory background processes DBWn PMON CKPT LGWR SMON RECO Optional background processes ARCn LMON Snnn QMNn LMDn CJQ0 Pnnn LCKn Dnnn 1-23 Copyright © Oracle Corporation, 2001. All rights reserved.  Database Writer (DBWn) Instance DBWn writes when: SGA Checkpoint Database Dirty buffers threshold buffer reached cache No free buffers Timeout DBWn RAC ping request Tablespace offline Data Control Redo log Tablespace read only files files files Table DROP or TRUNCATE Database Tablespace BEGIN BACKUP 1-24 Copyright © Oracle Corporation, 2001. All rights reserved.  Log Writer (LGWR) Instance SGA LGWR writes: Redo log At commit buffer When one-third full When there is 1 MB of redo DBWn LGWR Every 3 seconds Before DBWn writes Data Control Redo log files files files Database 1-25 Copyright © Oracle Corporation, 2001. All rights reserved.  System Monitor (SMON) Instance Responsibilities: SGA Instance recovery: – Rolls forward changes in the redo logs – Opens the database for SMON user access – Rolls back uncommitted Data Control Redo log transactions files files files Coalesces free space ever 3 sec Database Deallocates temporary segments 1-26 Copyright © Oracle Corporation, 2001. All rights reserved.  Process Monitor (PMON) Instance SGA Cleans up after failed processes by: Rolling back the transaction Releasing locks PMON Releasing other resources Restarts dead PGA area dispatchers 1-27 Copyright © Oracle Corporation, 2001. All rights reserved.  Checkpoint (CKPT) Instance SGA Responsible for: Redo Log Signalling DBWn Buffer at checkpoints Updating datafile DBWn LGWR DWW0 CKPT headers with checkpoint information Data Control Redo log Updating control files files files files with checkpoint information 1-28 Copyright © Oracle Corporation, 2001. All rights reserved.  Archiver (ARCn) Optional background process Automatically archives online redo logs when ARCHIVELOG mode is set Preserves the record of all changes made to the database ARCn Data Control Redo log Archived files files files Redo log files 1-29 Copyright © Oracle Corporation, 2001. All rights reserved.  Logical Structure The logical structure of the Oracle architecture dictates how the physical space of a database is to be used. A hierarchy exists in this structure that consists of tablespaces, segments, extents, and blocks. Tablespace Data file Segment Segment Extent Blocks 1-30 Copyright © Oracle Corporation, 2001. All rights reserved.  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. 1-31 Copyright © Oracle Corporation, 2001. All rights reserved.