Oracle 4
01 04 Oracle 07 Oracle Oracle Instance Oracle Instance Oracle Instance Oracle Database Oracle Database Instance Parameter File Pfile Instance Instance Instance Instance Oracle Instance System Global Area SGA Background Process 4-1 Instance Instance Oracle Other Process Oracle 4-1 Oracle Instance 99
01 Oracle Oracle I/O Instance Oracle Instance Oracle 8i Oracle 9i Dynamic SGA 04-1 4-1 System Global Area SGA Oracle Oracle Oracle Oracle 4-2 07 Database Buffer Cache Redo Log Buffer Shared Pool Large pool Java Java Pool 100
01 04 4-1-1 4-2 Oracle I/O I/O I/O Database Buffer Cache Oracle 07 Oracle Oracle Cache Hit I/O Oracle Cache Miss Oracle Blocks Block Block 4-3 101
01 Oracle 04-1 4-3 Oracle 8 Pool Default Pool Oracle Keep Pool Recycle Pool 07 Oracle 8i 8i DB_BLOCK_SIZE * DB_BLOCK_ BUFFERS Oracle 8i 8i DB_BLOCK_SIZE Oracle 9i DB_CACHE_SIZE Oracle 9i data block size 2K 4K 8KB 16KB 32KB DB_BLOCK_SIZE DB_BLOCK_SIZE Standard Block 2~32K 1
01 04 4-1-1-1 Dirty Buffer Oracle Background Process Dirty Buffer 4-2 Free Buffer Block buffer Oracle 07 Pinned Buffer Block Buffer I/O 4-1-1-2 Oracle Dirty List LRU List Dirty List Dirty Buffer LRU List Free Buffer Dirty Buffer Pinned Buffers DBWRn 4-2-1 DBWRn 1
01 Oracle 4-1-2 Redo Log Buffer Insert Update Delete Create Alter Drop Server Process Background Process Oracle Server Redo Entry Redo Entry Redo Entry Redo Entry Oracle Redo Entry 3-1-2 Redo Entry Log Writer 4-2-2 LGWR LOG_BUFFER Bytes size OS block size 4 04-1 07 4-1-3 Share Pool SQL Data Dictionary PL/SQL table Library Cache Data Dictionary Cache Control Structure Reusable runtime memory Library Cache Data Dictionary Cache 4-4 104
01 04 4-4 4-1-3-1 Library Cache Library Cache Shared SQL Area Private SQL Area Package Area PL/ SQL Oracle Parse Tree Execution Plan Shared SQL Area Oracle 07 Shared SQL Area Shared SQL Area Oracle Parse Tree Execution Plan SQL Shared SQL Area Shared SQL Area 1. SQL Statement Text SQL 2. Parse Tree Oracle 3. Execution Plan SQL Shared SQL Area Parse tree Execute plan PL/SQL Package Area PL/SQL Package Area Oracle PL/SQL Procedures Functions Packages Anonymous Blocks Database Triggers PL/SQL PL/SQL Parsed Compiled Program Unit Private Area 1
01 Oracle Data Dictionary Cache Data Dictionary Cache Oracle SQL Data Dictionary SHARED_POOL_SIZE 4-1-4 Java Pool Large Pool Java Pool Java Java Pool 30MB Large Pool RMAN Oracle Dedicate Multi-Threaded Server MTS Dedicate Program Global Area PGA Sort Area Hash Area 4-3-3 Multi-Threaded Server MTS Oracle 2G Oracle 2000 Large Pool RMAN Large Pool RMAN 9 RMAN 04-1 07 1
01 04 ( ) Oracle Oracle 07 4-2 Oracle 100 1000 Oracle 5 UNIX Daemand NT Service User Logging User Logoff Mandatory Background Processes Oracle DBWRn PMON CKPT LGWR SMON 107
01 Oracle Optional Background Processes ARCn 4-2-1 DBWRn DBWRn Database Writer Oracle DBWRn Oracle Dirty Buffer 4-5 3-1-1 04-2 07 4-5 DBWn Database Writer Oracle Instance 10 DBWRn DBWR0-DBWR9 DBWRn Dirty Buffer Write-ahead Protocol Commit Redo Entry LGWR Commit checkpoint 108
01 04 Oracle 07 Dirty Buffer DBWRn DBWRn Redo Entry DBWRn Redo Entry LGWR DBWRn Dirty Buffer Oracle Checkpoint 4-1-2 Checkpoint DBWRn Redo Entry Commit LGWR Log Writer 4-2-2 LGWR LRU Least Recently Used DBWR LRU Oracle LRU LRU LRU LRU Least Recently Used MRU Most Recently Used MRU LRU 4-6 State1 State2 State3 disk LRU list A Y LRU Free List State 4 A State3 A A MRU 4-6 109
01 Oracle 04-2 4-6 LRU Least Recently Used DBWRn Dirty Buffer Free Buffer 3 Checkpoint Checkpoint 1. 3-1-2 2. LOG_CHECKPOINT_TIMEOUT checkpoint 1,800 3. LOG_CHECKPOINT_INTERVAL Checkpoint 0 4. 5. Checkpoint Alter system checkpoint; 6. Tablespace Offline 07 110
01 4-2-2 LGWR LGWR Log Writer Redo Log Buffer LGWR Redo Entry Commit LGWR 4-7 04 Oracle 07 LGWR 4-7 LGWR Log Writer Commit LGWR 3 2/3 LGWR 3 1Megabyte DBWRn 111
01 Oracle 4-2-3 SMON SMON System Monitor Instance Recovery SMON Instance Recovery Oracle Instance Recovery Media Recovery Instance Recovery SMON Instance Recovery Media Recovery Media Recovery 04-2 Instance Recovery SMON 4-2-4 PMON PMON Process Monitor PMON Process List Process ID Commit Oracle Server Server Process Server Process User Process User Process Server Process Process Monitor Server Process User Process 07 112
01 04 Oracle 07 PMON PMON Commit 4-2-5 CKPT CKPT Checkpoint Dirty Buffer DBWRn CKPT DBWRn Dirty Buffer Checkpoint CKPT DBWR CKPT Checkpoint Instance Checkpoint DBWRn LRU Checkpoint LRU I/O CKPT DBWRn Checkpoint DBWRn DBWRn Redo Entry LGWR DBWRn 3-1-4 113
01 Oracle 4-2-6 ARCn Oracle ARCn Archiver NOARCHIVELOG ARCHIVELOG Oracle ARCHIVELOG Mode ARCn Log Switch Redo Log File Archived redo logs 3-1-6 Oracle 8i 10 ARCn Background Process LOG_ARCHIVE_MAX_PROCESSES Alter System Oracle 9i ARCHIVELOG Mode ARCn ARCn LGWR ARCn 04-3 4-3 Oracle Instance Other Process User Process Server Process Oracle Instance Oracle Process User Process Command Server Process Server Process Instance 07 4-3-1 User Process Pro*C Oracle tool SQL*plus Oracle Form Oracle User Process SQL SQL User Process Server Process Server Process Oracle User Process Server Process 114
01 04 Oracle 07 4-3-2 Server Process Oracle Server Process User Process Oracle User Process Server Process Server Process SQL Session Connection Connection Session User Process Connection User Process Oracle Instance Session Connection Session Oracle Server Oracle Server User Session SQL/PLUS Oracle User Process Server Process Session User Session Connection Communication Communication Session MSN Session User Process Server Process SQL SQL 115
01 Oracle 4-3-3 Server Process Program Global Area PGA Server Process 4-8 04-3 4-8 PGA Program Global Area Oracle Server Process Server Process Oracle Server Process Server Process PGA Server Process Server Process SQL Sort Area Hash Area 07 Sort Area select * from where Server Process Oracle Oracle Instance select * from where order by having group by PGA SQL Statement Oracle Server PGA 116
01 04 Oracle 07 PGA PGA 3-2-1 I/O 14,000 SQL Session Informafion Cursor State Private SQL Area Server Process SQL Server Process SQL Cursor Private SQL Area Cursor Private SQL Area SQL Cursor Private SQL Area Share Pool SQL SQL Cursor Private SQL Area SQL Server Process CPU OLTP Online Transaction Process OLTP SQL Working Set Table SQL Server Process SQL Bind Variable Cursor Private SQL Area OPEN_ CORSORS 117
01 Oracle Stack Space Oracle Oracle Oracle DBA Oracle SGA 4-1 04-3 SGA SGA_MAX_SIZE SGA DATABASE BUFFER CACHE DB_CACHE_SIZE SGA_MAX_SIZE Oracle SGA_MAX_SIZE SGA SGA_MAX_SIZE Database Buffer Cache 07 DB_BLOCK_SIZE Data Block Oracle I/O DB_BLOCK_BUFFERS D a t a b a s e B u f f e r C a c h e = DB_BLOCK_SIZE * DB_BLOCK_ BUFFERS Oracle 8i 8i 4-1 SGA 118
01 04 BUFFER_POOL_KEEP Database Buffer Cache Keep Pool Database Buffer Cache BUFFER_POOL_ RECYELE Database Buffer Cache Recyele Pool REDO LOG BUFFER LOG_BUFFER Log Buffer Redo Log Buffer Size OS Block Size 4 SHARED POOL SHARED_POOL_SIZE Shared Pool LARGE POOL LARGE_POOL_SIZE Large Pool 0 JAVA POOL JAVA_POOL_SIZE JAVA Pool PGA SORT_AREA_SIZE Oracle 07 SORT_AREA_RETAINED_ SIZE HASH_AREA_SIZE Server Process SORT_AREA_SIZE SORT_AREA_SIZE DBWn (Database Writer) DB_WRITER_ PROCESSES Database Writer 4-1 SGA ( ) 119