A complete production-ready SOP covering Oracle Database architecture from the ground up. Covers physical and logical storage structures, Oracle instance components, SGA and its sub-components, PGA, background processes, connection models, memory management, redo and undo internals, and how everything works together \u2014 with real commands, diagnostic queries, and consultant-level notes that connect architecture to real-world DBA activities.<\/p>\n\n\n\n
\n\n\n\n
1. Document Info<\/h3>\n\n\n\n
Item<\/th>
Detail<\/th><\/tr><\/thead>
Oracle Version<\/td>
19c (19.3+)<\/td><\/tr>
OS<\/td>
Oracle Linux 7.x \/ RHEL 7.x or 8.x<\/td><\/tr>
Purpose<\/td>
Foundation knowledge for all DBA activities<\/td><\/tr>
MOS Reference<\/td>
Doc ID 1523319.1 (Oracle Architecture Overview)<\/td><\/tr>
MOS Reference<\/td>
Doc ID 430473.1 (SGA and Memory Management)<\/td><\/tr>
MOS Reference<\/td>
Doc ID 1549180.1 (Background Processes Reference)<\/td><\/tr>
2. The Big Picture \u2014 What is an Oracle Database?<\/h3>\n\n\n\n
\n
\ud83d\udcdd Most people confuse Oracle Instance with Oracle Database. These are two completely separate things and understanding the difference is the most fundamental concept in Oracle architecture.<\/strong><\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
Oracle Instance vs Oracle Database<\/h4>\n\n\n\n
<\/th>
Oracle Instance<\/th>
Oracle Database<\/th><\/tr><\/thead>
What it is<\/td>
Memory structures + Background processes running in RAM<\/td>
Physical files stored on disk<\/td><\/tr>
Lives in<\/td>
Server RAM<\/td>
Server disk \/ storage<\/td><\/tr>
Can exist without the other?<\/td>
Yes \u2014 instance can run without a mounted database<\/td>
Yes \u2014 database files can exist without a running instance<\/td><\/tr>
Multiple instances per DB?<\/td>
Yes \u2014 RAC has one database with multiple instances<\/td>
One database accessed by multiple instances in RAC<\/td><\/tr>
When you start the DB<\/td>
You start the INSTANCE which then opens the DATABASE<\/td>
sqlplus \/ as sysdba\n\nset linesize 200\nset pagesize 50\n\n-- Shows INSTANCE information (what is in memory)\ncol instance_name for a15\ncol host_name for a25\ncol version for a15\ncol status for a12\ncol startup_time for a25\n\nSELECT instance_name,\n host_name,\n version_full,\n status,\n TO_CHAR(startup_time,'YYYY-MM-DD HH24:MI:SS') startup_time\nFROM v$instance;\n\n-- Shows DATABASE information (what is on disk)\ncol name for a12\ncol db_unique_name for a20\ncol open_mode for a15\ncol log_mode for a15\ncol database_role for a20\n\nSELECT name,\n db_unique_name,\n open_mode,\n log_mode,\n database_role,\n created\nFROM v$database;<\/code><\/pre>\n\n\n\n\n\n\n\n
3. Physical Database Structures (Files on Disk)<\/h3>\n\n\n\n
\n
\ud83d\udcdd Physical structures are the actual files Oracle creates and manages on disk. Without these files \u2014 there is no database.<\/strong><\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
3.1 \u2014 Datafiles<\/h4>\n\n\n\n
\n
\ud83d\udcdd What are datafiles?<\/strong> Datafiles are the actual files that store all the data \u2014 tables, indexes, PL\/SQL code, views, sequences, everything. Each tablespace is made up of one or more datafiles.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why does this matter for DBA work?<\/strong> When a datafile runs out of space, tables cannot grow and inserts fail with ORA-01653<\/code>. When a datafile is accidentally deleted, that tablespace goes offline and applications fail. Every space management task, every backup, and every recovery starts with knowing your datafiles.<\/p>\n<\/blockquote>\n\n\n\n
-- List all datafiles with sizes and status\nset linesize 200\nset pagesize 100\ncol file# for 999\ncol tablespace_name for a25\ncol status for a12\ncol autoextensible for a5\ncol size_mb for 99999999\ncol maxsize_mb for 99999999\ncol name for a70\n\nSELECT f.file#,\n t.name tablespace_name,\n f.status,\n ROUND(f.bytes\/1024\/1024,2) size_mb,\n f.autoextensible,\n ROUND(f.maxbytes\/1024\/1024,2) maxsize_mb,\n f.name\nFROM v$datafile f,\n v$tablespace t\nWHERE f.ts# = t.ts#\nORDER BY t.name, f.file#;<\/code><\/pre>\n\n\n\n\n\n\n\n
3.2 \u2014 Control Files<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is the control file?<\/strong> The control file is the most critical file in an Oracle database. It contains:<\/p>\n\n\n\n
\n
Database name and DBID<\/li>\n\n\n\n
Timestamp of database creation<\/li>\n\n\n\n
List of all datafiles and their locations<\/li>\n\n\n\n
List of all redo log files and their locations<\/li>\n\n\n\n
Current redo log sequence number<\/li>\n\n\n\n
Checkpoint information<\/li>\n\n\n\n
RMAN backup information (when no catalog is used)<\/li>\n<\/ul>\n\n\n\n
Oracle reads the control file at database startup to find all other files. If all copies of the control file are lost and there is no backup \u2014 the database cannot be opened.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why multiple control files?<\/strong> Oracle strongly recommends multiplexing (having multiple copies of) the control file on different disks. If one disk fails, the other copies protect you. Always have at least 2, preferably 3 control files on different storage locations.<\/p>\n<\/blockquote>\n\n\n\n
-- List all control files\nset linesize 200\nset pagesize 50\ncol status for a10\ncol name for a80\n\nSELECT status, name\nFROM v$controlfile\nORDER BY name;\n\n-- Check control file record section sizes\nset linesize 200\nset pagesize 100\ncol type for a30\ncol records_total for 9999999\ncol records_used for 9999999\n\nSELECT type, records_total, records_used\nFROM v$controlfile_record_section\nORDER BY type;\n\n-- How many control file copies are configured\nSHOW PARAMETER control_files;<\/code><\/pre>\n\n\n\n\n\n\n\n
3.3 \u2014 Online Redo Log Files<\/h4>\n\n\n\n
\n
\ud83d\udcdd What are online redo logs?<\/strong> Redo logs record every change made to the database \u2014 every INSERT, UPDATE, DELETE, and DDL operation is written to the redo log BEFORE it is written to the datafile. This is the cornerstone of Oracle’s crash recovery mechanism.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why do redo logs matter for DBA work?<\/strong><\/p>\n\n\n\n
\n
Too small = frequent log switches = performance impact on commits (log file sync<\/code> wait event)<\/li>\n\n\n\n
Too few groups = archiver cannot keep up = database hangs (log switch\/archiving needed<\/code> wait)<\/li>\n\n\n\n
If current redo log is corrupted = database cannot open without incomplete recovery<\/li>\n\n\n\n
Redo logs are the foundation of Data Guard \u2014 the standby applies these logs<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd How does Oracle use redo logs?<\/strong> Oracle writes to redo log groups in circular fashion. It writes to Group 1, fills it, switches to Group 2, fills it, switches to Group 3, then back to Group 1. Before reusing a group, it must be archived (in ARCHIVELOG mode). The LGWR background process writes redo.<\/p>\n<\/blockquote>\n\n\n\n
-- Check online redo log groups and members\nset linesize 200\nset pagesize 100\ncol l# for 999\ncol status for a12\ncol members for 999\ncol member for a70\n\nSELECT l.group# l#,\n l.members,\n l.bytes\/1024\/1024 size_mb,\n l.status,\n l.archived,\n l.sequence#,\n f.member\nFROM v$log l,\n v$logfile f\nWHERE l.group# = f.group#\nORDER BY l.group#, f.member;\n\n-- Check which group is currently being written to\nSELECT group#, status FROM v$log WHERE status = 'CURRENT';<\/code><\/pre>\n\n\n\n\n\n\n\n
3.4 \u2014 Archive Log Files<\/h4>\n\n\n\n
\n
\ud83d\udcdd What are archive logs?<\/strong> When Oracle finishes writing to a redo log group and switches to the next group, it copies the completed redo log to the archive log destination. This copy is called an archivelog. Archive logs allow recovery to any point in time \u2014 you restore the datafiles from backup and then apply all the archive logs created after that backup.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Archive logs are the bridge between backups.<\/strong> Without archive logs you can only recover to the last backup point. With archive logs you can recover to any point between backups \u2014 even to one second before a disaster.<\/p>\n<\/blockquote>\n\n\n\n
-- Check archive log mode and destination\nARCHIVE LOG LIST;\n\n-- List recent archive logs\nset linesize 200\nset pagesize 100\ncol name for a70\ncol sequence# for 9999999\ncol first_time for a25\ncol completion_time for a25\ncol archived for a10\n\nSELECT sequence#,\n name,\n TO_CHAR(first_time,'YYYY-MM-DD HH24:MI:SS') first_time,\n TO_CHAR(completion_time,'YYYY-MM-DD HH24:MI:SS') completion_time,\n blocks * block_size \/ 1024 \/ 1024 size_mb\nFROM v$archived_log\nWHERE completion_time > SYSDATE - 1\nORDER BY sequence# DESC\nFETCH FIRST 20 ROWS ONLY;\n\n-- Check archive destination status\nset linesize 200\nset pagesize 50\ncol dest_name for a25\ncol status for a12\ncol target for a12\ncol destination for a50\n\nSELECT dest_id, dest_name, status, target, destination\nFROM v$archive_dest\nWHERE status != 'INACTIVE'\nORDER BY dest_id;<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd What is the parameter file?<\/strong> The parameter file contains initialization parameters that control how Oracle starts and behaves \u2014 memory sizes, number of processes, file locations, feature settings. Oracle reads it at instance startup.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd spfile vs pfile:<\/strong><\/p>\n\n\n\n
\n
spfile<\/code> (Server Parameter File) \u2014 binary file managed by Oracle. Changes made with ALTER SYSTEM<\/code> are written directly to spfile. This is the production standard.<\/li>\n\n\n\n
pfile<\/code> (init.ora) \u2014 plain text file. Must be manually edited. Oracle cannot update it automatically. Used for emergencies and special startup scenarios.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
-- Check if using spfile or pfile\nSELECT decode(value,NULL,'PFILE','SPFILE') parameter_file_type,\n value spfile_location\nFROM v$parameter\nWHERE name = 'spfile';\n\n-- Show spfile location\nSHOW PARAMETER spfile;\n\n-- List all non-default parameters (what you have customized)\nset linesize 200\nset pagesize 100\ncol name for a45\ncol value for a50\ncol description for a60\n\nSELECT name, value, description\nFROM v$parameter\nWHERE isdefault = 'FALSE'\nORDER BY name;<\/code><\/pre>\n\n\n\n
# View pfile\/spfile content (as oracle OS user)\n# spfile is binary -- cannot cat it\n# Create a pfile from spfile to read it\nsqlplus \/ as sysdba<\/code><\/pre>\n\n\n\n
-- Create a readable pfile from spfile\nCREATE PFILE='\/tmp\/init_ORCL_readable.ora' FROM SPFILE;\nEXIT;<\/code><\/pre>\n\n\n\n
\ud83d\udcdd What is the password file?<\/strong> The password file stores the encrypted password for the SYS user (and other users granted SYSDBA\/SYSOPER privileges). It allows remote SYSDBA connections \u2014 for example sqlplus sys\/password@ORCL as sysdba<\/code> over the network. Without the password file, SYSDBA login only works locally via OS authentication.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why does this matter for DBA work?<\/strong> Data Guard requires password files because the standby must authenticate with the primary over the network as SYS. RMAN duplicate requires it. OEM agent uses it. If the password file is missing or mismatched, these features break.<\/p>\n<\/blockquote>\n\n\n\n
# Check password file location (Convention A)\nls -lh $ORACLE_HOME\/dbs\/orapw${ORACLE_SID}\n\n# Convention B\nls -lh $ORACLE_HOME\/dbs\/orapwORCL\n\n# List users who have SYSDBA or SYSOPER privilege\n# (these users are in the password file)<\/code><\/pre>\n\n\n\n
set linesize 150\nset pagesize 50\ncol username for a25\ncol sysdba for a8\ncol sysoper for a8\ncol sysbackup for a10\ncol sysdg for a8\n\nSELECT username, sysdba, sysoper, sysbackup, sysdg\nFROM v$pwfile_users\nORDER BY username;<\/code><\/pre>\n\n\n\n\n\n\n\n
4. Logical Database Structures<\/h3>\n\n\n\n
\n
\ud83d\udcdd Logical structures are how Oracle organizes data internally \u2014 they are not individual files on disk but rather Oracle’s way of grouping and managing storage.<\/strong><\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
4.1 \u2014 The Logical Storage Hierarchy<\/h4>\n\n\n\n
DATABASE\n \u2502\n \u251c\u2500\u2500 TABLESPACE (logical grouping)\n \u2502 \u2502\n \u2502 \u251c\u2500\u2500 SEGMENT (one object = one or more segments)\n \u2502 \u2502 \u2502\n \u2502 \u2502 \u251c\u2500\u2500 EXTENT (contiguous set of blocks)\n \u2502 \u2502 \u2502 \u2502\n \u2502 \u2502 \u2502 \u2514\u2500\u2500 ORACLE BLOCK (smallest unit \u2014 8KB default)\n \u2502 \u2502 \u2502\n \u2502 \u2502 \u2514\u2500\u2500 EXTENT\n \u2502 \u2502\n \u2502 \u2514\u2500\u2500 SEGMENT\n \u2502\n \u2514\u2500\u2500 TABLESPACE\n \u2502\n \u2514\u2500\u2500 maps to physical DATAFILE(s) on disk<\/code><\/pre>\n\n\n\n
1 Tablespace \u2192 Many Datafiles + Many Segments<\/li>\n\n\n\n
1 Segment \u2192 Many Extents (segments grow by adding extents)<\/li>\n\n\n\n
1 Extent \u2192 Many Oracle Blocks (contiguous)<\/li>\n\n\n\n
Oracle Block \u2192 smallest I\/O unit (default 8KB)<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n\n\n\n\n
4.2 \u2014 Tablespaces<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is a tablespace?<\/strong> A tablespace is a logical storage container. It maps to one or more physical datafiles on disk. Every database object (table, index, etc.) belongs to exactly one tablespace.<\/p>\n<\/blockquote>\n\n\n\n
Default tablespaces in a fresh Oracle 19c database:<\/strong><\/p>\n\n\n\n
Tablespace<\/th>
Purpose<\/th>
Notes<\/th><\/tr><\/thead>
SYSTEM<\/td>
Oracle data dictionary \u2014 tables, views, packages owned by SYS<\/td>
Never store application data here<\/td><\/tr>
SYSAUX<\/td>
Auxiliary to SYSTEM \u2014 AWR, Statspack, OEM repository<\/td>
Never store application data here<\/td><\/tr>
USERS<\/td>
Default tablespace for user objects<\/td>
General purpose<\/td><\/tr>
UNDOTBS1<\/td>
Undo segments \u2014 stores before-images for rollback and read consistency<\/td>
Managed automatically<\/td><\/tr>
TEMP<\/td>
Temporary segments for sort operations, hash joins<\/td>
Shared across sessions<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
-- Complete tablespace overview\nset linesize 200\nset pagesize 100\ncol tablespace_name for a25\ncol contents for a12\ncol status for a10\ncol total_mb for 9999999\ncol free_mb for 9999999\ncol used_pct for 999.99\ncol autoextend for a5\n\nSELECT df.tablespace_name,\n t.contents,\n t.status,\n ROUND(df.total_bytes\/1024\/1024,2) total_mb,\n ROUND(NVL(fs.free_bytes,0)\/1024\/1024,2) free_mb,\n ROUND((1-NVL(fs.free_bytes,0)\/df.total_bytes)*100,2) used_pct,\n df.autoextend\nFROM (SELECT tablespace_name,\n SUM(bytes) total_bytes,\n MAX(DECODE(autoextensible,'YES','YES','NO')) autoextend\n FROM dba_data_files\n GROUP BY tablespace_name) df,\n (SELECT tablespace_name,\n SUM(bytes) free_bytes\n FROM dba_free_space\n GROUP BY tablespace_name) fs,\n dba_tablespaces t\nWHERE df.tablespace_name = fs.tablespace_name(+)\nAND df.tablespace_name = t.tablespace_name\nORDER BY used_pct DESC;<\/code><\/pre>\n\n\n\n\n\n\n\n
4.3 \u2014 Segments, Extents, and Blocks<\/h4>\n\n\n\n
-- Check segment sizes for a specific schema\nset linesize 200\nset pagesize 100\ncol owner for a20\ncol segment_name for a35\ncol segment_type for a20\ncol tablespace_name for a20\ncol size_mb for 99999999\n\nSELECT owner,\n segment_name,\n segment_type,\n tablespace_name,\n ROUND(bytes\/1024\/1024,2) size_mb,\n extents,\n blocks\nFROM dba_segments\nWHERE owner = 'HR'\nORDER BY bytes DESC\nFETCH FIRST 20 ROWS ONLY;\n\n-- Check block size\nSHOW PARAMETER db_block_size;\n\n-- Check extent management mode\nset linesize 200\nset pagesize 50\ncol tablespace_name for a25\ncol extent_management for a20\ncol allocation_type for a15\ncol segment_space_management for a25\n\nSELECT tablespace_name,\n extent_management,\n allocation_type,\n segment_space_management\nFROM dba_tablespaces\nORDER BY tablespace_name;<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd The Oracle Instance consists of memory (SGA + PGA) and background processes. When you start an Oracle instance, Oracle allocates a large chunk of RAM for the SGA and starts multiple background processes.<\/strong><\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
5.1 \u2014 SGA \u2014 System Global Area<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is the SGA?<\/strong> The SGA (System Global Area) is a shared memory region allocated when the Oracle instance starts. ALL sessions connected to the instance share the SGA. It is the single largest consumer of RAM on an Oracle database server.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why is SGA critical?<\/strong> The bigger the SGA (specifically the buffer cache), the more data Oracle can keep in memory and the fewer disk reads are needed. Disk reads are 1000x slower than memory reads. Proper SGA sizing is one of the most impactful performance tuning actions.<\/p>\n<\/blockquote>\n\n\n\n
-- Overall SGA summary\nset linesize 200\nset pagesize 50\ncol name for a30\ncol value_mb for 9999999.99\n\nSELECT name,\n ROUND(value\/1024\/1024,2) value_mb\nFROM v$sga\nORDER BY value DESC;\n\n-- Detailed SGA component breakdown\nset linesize 200\nset pagesize 100\ncol component for a40\ncol current_mb for 9999999\ncol min_mb for 9999999\ncol max_mb for 9999999\n\nSELECT component,\n ROUND(current_size\/1024\/1024,0) current_mb,\n ROUND(min_size\/1024\/1024,0) min_mb,\n ROUND(max_size\/1024\/1024,0) max_mb\nFROM v$sga_dynamic_components\nWHERE current_size > 0\nORDER BY current_size DESC;<\/code><\/pre>\n\n\n\n\n\n\n\n
5.2 \u2014 SGA Components in Detail<\/h4>\n\n\n\n
SGA (System Global Area)\n\u251c\u2500\u2500 Buffer Cache \u2190 Cached datafile blocks (largest component)\n\u251c\u2500\u2500 Shared Pool \u2190 SQL cache, data dictionary cache, PL\/SQL code\n\u2502 \u251c\u2500\u2500 Library Cache \u2190 Parsed SQL statements, execution plans, PL\/SQL code\n\u2502 \u2514\u2500\u2500 Dictionary Cache \u2190 Metadata about tables, columns, indexes, users\n\u251c\u2500\u2500 Redo Log Buffer \u2190 Recent redo entries before LGWR writes to disk\n\u251c\u2500\u2500 Large Pool \u2190 Used by RMAN, shared server, parallel query\n\u251c\u2500\u2500 Java Pool \u2190 Java Virtual Machine memory (if Java used)\n\u251c\u2500\u2500 Streams Pool \u2190 GoldenGate, Advanced Queuing, Streams\n\u2514\u2500\u2500 Fixed SGA \u2190 Internal Oracle overhead (small, fixed size)<\/code><\/pre>\n\n\n\n\n\n\n\n
5.3 \u2014 Buffer Cache<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is the Buffer Cache?<\/strong> The Buffer Cache is a pool of memory that holds copies of Oracle data blocks read from datafiles. When a session needs a block, Oracle first checks the buffer cache (logical read). If found \u2014 great, no disk I\/O needed. If not found (cache miss), Oracle reads the block from disk into the cache (physical read).<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Buffer Cache replacement algorithm:<\/strong> Oracle uses a modified LRU (Least Recently Used) algorithm called the “touch count” mechanism. Recently and frequently accessed blocks stay at the hot end of the LRU list. Infrequently accessed blocks migrate to the cold end and are eligible for replacement.<\/p>\n<\/blockquote>\n\n\n\n
-- Buffer cache hit ratio\n-- Formula: 1 - (physical reads \/ (consistent gets + db block gets))\n-- Target: > 95% (higher is better -- means more reads from cache)\nset linesize 150\nset pagesize 50\n\nSELECT ROUND(\n (1 - (phy.value \/ NULLIF(con.value + cur.value, 0))) * 100, 2\n) buffer_cache_hit_pct\nFROM v$sysstat phy,\n v$sysstat con,\n v$sysstat cur\nWHERE phy.name = 'physical reads'\nAND con.name = 'consistent gets'\nAND cur.name = 'db block gets';\n\n-- Buffer cache size and advisory\nset linesize 200\nset pagesize 100\ncol size_mb for 9999999\ncol estd_physical_reads for 9999999999\ncol estd_pct_of_base for 999.99\n\nSELECT size_for_estimate\/1024\/1024 size_mb,\n estd_physical_reads,\n ROUND(estd_physical_read_factor*100,2) estd_pct_of_base\nFROM v$db_cache_advice\nWHERE name = 'DEFAULT'\nAND block_size = (SELECT TO_NUMBER(value)\n FROM v$parameter\n WHERE name = 'db_block_size')\nORDER BY size_for_estimate;<\/code><\/pre>\n\n\n\n\n\n\n\n
5.4 \u2014 Shared Pool<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is the Shared Pool?<\/strong> The Shared Pool caches parsed SQL statements, execution plans, PL\/SQL compiled code, and data dictionary information. Its purpose is to avoid redundant parsing work.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Library Cache \u2014 SQL caching explained:<\/strong> When a SQL statement is first executed, Oracle parses it (syntax check, semantic check, optimization, plan generation) \u2014 called a Hard Parse. This is expensive. If the SAME statement is executed again, Oracle finds it in the Library Cache and reuses the plan \u2014 called a Soft Parse. Much cheaper.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd The bind variable connection:<\/strong> For the Library Cache to reuse a plan, the SQL text must match EXACTLY. SELECT * FROM emp WHERE id = 100<\/code> and SELECT * FROM emp WHERE id = 101<\/code> are TWO DIFFERENT statements and both need a hard parse. But SELECT * FROM emp WHERE id = :id<\/code> with different bind variable values uses ONE cached plan. This is why bind variables are critical for performance.<\/p>\n<\/blockquote>\n\n\n\n
\ud83d\udcdd What is the Redo Log Buffer?<\/strong> The Redo Log Buffer is a circular buffer in the SGA where Oracle temporarily stores redo entries before the LGWR background process writes them to the online redo log files on disk. It is small (default 3-15 MB) compared to the buffer cache.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd How commits work with the redo log buffer:<\/strong><\/p>\n\n\n\n\n
Session makes a change (INSERT, UPDATE, DELETE)<\/li>\n\n\n\n
Redo entry describing the change is written to Redo Log Buffer<\/li>\n\n\n\n
Session issues COMMIT<\/li>\n\n\n\n
LGWR immediately flushes all redo for this transaction from buffer to redo log file on disk<\/li>\n\n\n\n
COMMIT returns to the session ONLY AFTER LGWR confirms the write<\/li>\n\n\n\n
This ensures committed transactions are never lost even if the server crashes<\/li>\n<\/ol>\n<\/blockquote>\n\n\n\n
-- Check redo log buffer size\nSHOW PARAMETER log_buffer;\n\n-- Check redo log buffer efficiency\n-- \"redo log space requests\" should be near 0\n-- If high -- redo log buffer is too small\nset linesize 150\nset pagesize 50\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN (\n 'redo log space requests',\n 'redo buffer allocation retries',\n 'redo writes',\n 'redo size'\n)\nORDER BY name;<\/code><\/pre>\n\n\n\n\n\n\n\n
5.6 \u2014 Large Pool<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is the Large Pool?<\/strong> The Large Pool is optional memory used for:<\/p>\n\n\n\n
\n
RMAN backup and restore operations (I\/O server processes)<\/li>\n\n\n\n
Shared server architecture (UGA \u2014 User Global Area)<\/li>\n\n\n\n
Parallel query operations<\/li>\n\n\n\n
Oracle Advanced Queuing<\/li>\n<\/ul>\n\n\n\n
Without a Large Pool, these operations steal memory from the Shared Pool causing shared pool fragmentation.<\/p>\n<\/blockquote>\n\n\n\n
-- Check Large Pool size\nSHOW PARAMETER large_pool_size;\n\n-- Check Large Pool usage\nSELECT name, ROUND(bytes\/1024\/1024,2) mb\nFROM v$sgastat\nWHERE pool = 'large pool'\nORDER BY bytes DESC;<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd Three memory management modes \u2014 know which one your environment uses:<\/strong><\/p>\n<\/blockquote>\n\n\n\n
Mode<\/th>
Parameters<\/th>
How It Works<\/th><\/tr><\/thead>
Manual SGA Management<\/td>
Set each component individually (db_cache_size, shared_pool_size etc.)<\/td>
DBA manually sizes each component. Most control but most work.<\/td><\/tr>
Automatic SGA Management (ASMM)<\/td>
Set SGA_TARGET<\/td>
Oracle automatically redistributes memory between components based on workload. Recommended for most environments.<\/td><\/tr>
Automatic Memory Management (AMM)<\/td>
Set MEMORY_TARGET<\/td>
Oracle manages BOTH SGA and PGA automatically. Not recommended for large databases \u2014 can cause paging.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
-- Check which mode is in use\nset linesize 200\nset pagesize 50\ncol name for a30\ncol value for a20\n\nSELECT name, value\nFROM v$parameter\nWHERE name IN (\n 'memory_target',\n 'memory_max_target',\n 'sga_target',\n 'sga_max_size',\n 'pga_aggregate_target',\n 'db_cache_size',\n 'shared_pool_size'\n)\nORDER BY name;\n\n-- If memory_target > 0 = AMM mode\n-- If sga_target > 0 and memory_target = 0 = ASMM mode\n-- If both = 0 = Manual mode<\/code><\/pre>\n\n\n\n\n\n\n\n
6. PGA \u2014 Program Global Area<\/h3>\n\n\n\n
\n
\ud83d\udcdd What is PGA?<\/strong> PGA (Program Global Area) is private memory allocated for each individual server process (or each session in shared server mode). Unlike SGA which is shared between all sessions, PGA is private to each session. When the session disconnects, its PGA is freed.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd What is PGA used for?<\/strong><\/p>\n\n\n\n
\n
Sort operations (ORDER BY, GROUP BY, UNION)<\/li>\n\n\n\n
Hash join operations<\/li>\n\n\n\n
Bitmap merge operations<\/li>\n\n\n\n
Session variables and stack space<\/li>\n<\/ul>\n\n\n\n
If a sort operation does not fit in PGA, Oracle writes to TEMP tablespace on disk \u2014 much slower. Properly sized PGA reduces temp tablespace writes and dramatically improves sort and join performance.<\/p>\n<\/blockquote>\n\n\n\n
PGA (per session \u2014 private)\n\u251c\u2500\u2500 Sort Area \u2190 Memory for sort operations\n\u251c\u2500\u2500 Hash Area \u2190 Memory for hash join operations\n\u251c\u2500\u2500 Bitmap Merge Area \u2190 Memory for bitmap index operations\n\u251c\u2500\u2500 Session Variables \u2190 Bind variables, session state\n\u2514\u2500\u2500 Stack Space \u2190 Call stack for PL\/SQL<\/code><\/pre>\n\n\n\n
-- Overall PGA usage and statistics\nset linesize 200\nset pagesize 100\ncol name for a45\ncol value for a25\n\nSELECT name, value\nFROM v$pgastat\nWHERE name IN (\n 'aggregate PGA target parameter',\n 'aggregate PGA auto target',\n 'total PGA inuse',\n 'total PGA allocated',\n 'maximum PGA allocated',\n 'total freeable PGA memory',\n 'cache hit percentage',\n 'recompute count (total)'\n)\nORDER BY name;\n\n-- PGA sizing advisory\nset linesize 200\nset pagesize 100\ncol pga_target_mb for 9999999\ncol estd_pga_cache_hit_pct for 999.99\ncol estd_overalloc_cnt for 9999999\n\nSELECT pga_target_for_estimate\/1024\/1024 pga_target_mb,\n ROUND(estd_pga_cache_hit_pct,2) estd_pga_cache_hit_pct,\n estd_overalloc_cnt\nFROM v$pga_target_advice\nORDER BY pga_target_for_estimate;<\/code><\/pre>\n\n\n\n\n\n\n\n
7. Oracle Background Processes<\/h3>\n\n\n\n
\n
\ud83d\udcdd What are background processes?<\/strong> Background processes are OS-level processes started by Oracle when the instance starts. They run continuously in the background handling specific tasks \u2014 writing data to disk, writing redo logs, cleaning up, archiving etc. You can see them in ps -ef | grep ora_<\/code>.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why must a DBA know background processes?<\/strong> When you see ora_lgwr_ORCL<\/code> in the process list, you know LGWR is running. When you see redo-related performance problems, you know to look at LGWR. When archiving stops, you look at ARCn. When data is not being written to disk, you look at DBWn. Background processes are the engine room of Oracle.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
\ud83d\udcdd These processes MUST be running for the database to operate. If any of these die, the database shuts down.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
Process<\/th>
Full Name<\/th>
What It Does<\/th><\/tr><\/thead>
PMON<\/td>
Process Monitor<\/td>
Cleans up after failed user processes \u2014 rolls back their transactions, frees locks, releases resources. Also registers the DB with listeners (dynamic registration).<\/td><\/tr>
SMON<\/td>
System Monitor<\/td>
Instance recovery after a crash \u2014 applies redo from online redo logs. Also coalesces free space in tablespaces and cleans up temporary segments.<\/td><\/tr>
DBWR (DBWn)<\/td>
Database Writer<\/td>
Writes dirty blocks (modified data blocks in buffer cache) to datafiles on disk. Multiple writers possible (DBW0, DBW1… DBW9, DBWa-DBWj). Triggered by checkpoints and buffer cache shortage.<\/td><\/tr>
LGWR<\/td>
Log Writer<\/td>
Writes redo entries from the Redo Log Buffer to online redo log files. Triggered by COMMIT, every 3 seconds, when buffer is 1\/3 full, before DBWn writes. Critical for commit performance.<\/td><\/tr>
CKPT<\/td>
Checkpoint<\/td>
Updates the control file and datafile headers with checkpoint information. Also signals DBWn to write dirty blocks during checkpoints.<\/td><\/tr>
MMON<\/td>
Manageability Monitor<\/td>
Takes AWR snapshots, runs ADDM analysis, collects statistics for OEM. Runs every 60 minutes by default.<\/td><\/tr>
MMNL<\/td>
Manageability Monitor Lite<\/td>
Flushes ASH (Active Session History) from memory to AWR tables. Runs every 60 seconds.<\/td><\/tr>
RECO<\/td>
Recoverer<\/td>
Resolves distributed transactions that failed due to network problems. Handles two-phase commit recovery.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n\n\n\n\n