1 / 24

Understanding Oracle Architecture Components

Understanding Oracle Architecture Components. Upon completion of this lesson you should: Understand the architecture of the Oracle server and be able to describe its main components Be able to describe how the Oracle server operates in the processing of: Queries DML statements Commits.

lenora
Download Presentation

Understanding Oracle Architecture Components

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Understanding Oracle Architecture Components Upon completion of this lesson you should: • Understand the architecture of the Oracle server and be able to describe its main components • Be able to describe how the Oracle server operates in the processing of: • Queries • DML statements • Commits

  2. The Database Architecture • The DBMS loads processes and selected data into primary memory (RAM) and all processing must occur there. • This is referred to as the database instance • Since RAM is volatile, all data of the database must be stored in 2ndary storage (usually hard disks) • This storage is referred to as the Database • Results of all data that has been COMMITted must be stored in the Database.

  3. The Database The Control File • Manages interactions of the Oracle instance with all of the other files of the database • Maintains information on the status of the database files and coordinating information required for recovery in case of failure • Is connected to an instance as soon as the instance is allocated by the parameter file Database Undo System Data Redo Log 1 Control File Index Tools Redo Log 3 Redo Log 2 Temp . . .

  4. The Database The System Tablespace • Contains data dictionary information (metadata) • Table and view definitions, constraints, etc. • Must always be on-line when the database is open Database System Undo Data Redo Log 1 Control File Index Tools Redo Log 3 Redo Log 2 Temp . . .

  5. The Database Non-System Tablespaces • Created as needed to enhance performance • All user created objects should be stored in this type of tablespace • Tablespaces are logical structures – a tablespace may span multiple physical files • Tablespaces may be accessed randomly Data Database Undo System Redo Log 1 Index Control File Tools Redo Log 3 Redo Log 2 Temp . . .

  6. The Database Redo Log Files • Contain before and after images of data that have changed • Are written (& thus must be read) sequentially • Written to in a circular fashion • Used for recovery of entire system Undo Tablespace • Consists of segments which are written to in a circular fashion • But can be retrieved randomly & are used for failures involving a single user. Redo Log 1 Database Undo System Data Control File Index Tools Redo Log 3 Redo Log 2 Temp . . .

  7. The Database Instance • Resides in the Database server’s Primary Memory (in RAM) • Consists of data and instructions needed for processing (the SGA) and a set of background processes • Background processes are Oracle software elements which perform operations to maintain the instance and database. • As the name implies, background processes normally do their work automatically as needed without being explicitly invoked by the user

  8. Shared Pool Systems Global Area (SGA) The SGA Shared Pool • SQL Cache • Contains instructions (SQL statements) and their parsed execution plans needed to perform the work of the instructions • Dictionary Cache • Consists of data dictionary information needed for current processing • As new entries in these areas the least recently used (LRU) entry is removed

  9. Shared Pool Systems Global Area (SGA) The SGA Data Buffer Cache • Consists of blocks of data retrieved from tablespaces as needed for processing • All changes to data occur here • If a block is changed, both the before and after versions are initially kept in memory • Least recently used (LRU) blocks are replaced when we run out of space • Before replacing modified blocks: • must write to their tablespace in secondary storage and if necessary write before version to undo tablespace

  10. Shared Pool Systems Global Area (SGA) The SGA Redo Log Buffer • Records before and after images of data in data buffer cache as it is modified (only changed bytes recorded) • The data in this buffer is flushed to the redo log files of the database: • Whenever there is a commit • When the buffer becomes more than 1/3rd full • After a specified time period has elapsed • Emptying of redo log buffer to redo log files must be complete before a user is notified that their transactions has been committed.

  11. Background Processes Process monitor (PMON) • Cleans up after failed user processes • Releases locks held by process • Reclaims memory and other resources allocated to the failed process • Performs rollback of any unfinished transactions for the process System Monitor (SMON) • Performs automatic recovery (from online redo logs) in case of system failure Process Monitor (PMON) System Monitor (SMON)

  12. Background Processes Log Writer • Flushes Redo Log Buffer by writing its contents to a Redo Log File • When current redo log file is full a checkpoint is initiated and log writer switches to the next Redo Log File • Switch occurs only if the prior checkpoint for the next Redo Log File has been completed Log Writer (LGWR)

  13. Background Processes Checkpoint • Initiated by log writer at log switch • Writes a checkpoint number to the redo log file • Coordinates the process of seeing that all transactions completed (or rolled back) prior to the checkpoint time have their effects written back to secondary storage • Writes a checkpoint number to the header of each tablespace and to the control file once step 3 is completed 22 22 22 22 22 Check Point (CKPT)

  14. Database Writer • Writes “dirty” buffers from the data buffer cache to the database (including undo tablespace(s) ) when: • Directed by the checkpoint process • # of “free” buffers gets too low • When activated, makes copy of the buffer list and writes out buffers “dirty’ at that time Buffer Status Free buffer – data unchanged and not in use Pinned buffer – data in current use Dirty buffer – data has been changed but not in current use Database Writer (DBWR)

  15. Archiver • If archiving is turned on the archiver process writes from redo log files to off-line archival storage (disk or tape) • Begins writing when a log file becomes inactive • Must complete writing to archive before that redo log file is used again by log writer system hangs if this process is not completed Archiver (ARCH) Redo Log 1 Redo Log 3 Redo Log 2 Archived Redo Logs

  16. SERVER PROCESS • Process calls from client • Returns results to client • Is not shared -1 for each client • Contains an exclusive PGA • PROGRAM GLOBAL AREA (PGA) • Written to only by server process • Contains: • Sort area • Session Information • Cursor state • Stack space Server Process PGA 1

  17. Query Processing • Parse • Search for identical statement in SQL Cache • Check syntax, object names, and privileges • Lock objects used during parse • (Create and store execution plan or use execution plan for identical statement if found in step 1) • Execute: Identify rows selected (prepare to retrieve them to data buffer cache if necessary) • Fetch: Return rows to the user process

  18. Query Processing Example 1 2 6 5 1. User requests: SELECT wage from EMP WHERE name = ‘Jones’ 3 4 2. Search SQL cache for identical statement 3. Retrieve metadata, check syntax, etc., lock objects create & store execution plan. 4. Retrieve data from tablespaces to Data Buffer Cache, if needed 5. Identify and retrieve to server process data to meet users request 6. Return result to user: wage 850

  19. DML Statement Processing 1 5 4 3 1. User sends SQL statement UPDATE EMP SET wage = 925 WHERE name = ‘Jones’ 2 2. Data to be modified retrieved to data buffer cache (if necessary) and locks acquired 3. Write before and after image of changes to redo log buffer 4. Copy block in data buffer area; modify 1 copy; keep old copy for rollback 5. Inform user of result: 1 Record Updated

  20. COMMIT Processing 1 2 4 5 • 1. User process requests a • commit 2. Server Process sends a COMMIT to the Redo Log Buffer, a System Change Number (SCN) is assigned 3 3. Log writer writes all new entries in redo log buffer to redo log file 5. Undo data blocks associated with the transaction are released 4. User is informed that transaction has been committed

  21. System Recovery • Occurs when volatile memory lost (power outage) without loss of data in 2nd storage • When system restarted Control file recognizes that recovery is needed • SMON background process reads all Redo Log file data for entries after the last Checkpoint • After images of all committed transactions are posted to the database files • Before images of all ROLLED back transactions or in-process transactions are posted to database files. • Database re-opened

  22. Summary You should now understand the basic elements of the Oracle server architecture • The Oracle database consists of: • Control file • Database tablespaces • System, Undo, Temporary tablespace(s) • Data, index, … tablespaces as needed to support processing • Redo log files • Offline archive files (optional but highly recommended)

  23. SUMMARY (Continued) The Oracle Instance contains: • Systems Global Area (SGA) • SQL Cache • Dictionary cache • Database Buffer Cache • Redo Log File • Server Processes (usually 1 per user session) • Has a Program Global Area (PGA) and • Connects to a user process which performs some database related functions on the client machine • Background Processes: • LGWR, PMON, SMON, CKPT, DBWO, ARCO

  24. SUMMARY (Continued) • Query Processing consists of three steps: • Parse (if identical statement not in Library Cache), • Execute, and • Fetch • DML Statement Processing • Locks records to be updated • Writes before and after images to redo log • Maintains linked current and rollback data blocks • Commit processing • Ensures that redo log information for a transaction is written to secondary storage (redo log files) before telling a user their transaction is committed • System Recovery

More Related