Database Systems Lecture 5 Overview of Oracle Database Architecture

1. Database Systems Lecture 5 Overview of Oracle Database Architecture - Concept Manual : Chapters 1,8 Lecturer : Dr Bela Stantic Database Systems Slide 1

2. How Oracle Works The following example is based on an Oracle configuration where the user and associated server process are on separate machines (connected through a network). • An instance is started on the computer running Oracle (host /server). • A computer (client) runs an application (say SQLPLUS) in a user process. The application attempts to establish a connection to the server. • The server detects the connection request from the application and creates a dedicated server process on behalf of the user process. • The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table. • The server process receives the statement and checks the user’s access privileges to the requested data, so it can be parsed and processed. • The server process retrieves the necessary data values from the datafile or memory. • The server process modifies data in the system global area (SGA). The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file. • If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted. • Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users’ transactions and prevents contention between transactions that request the same data. Database Systems Slide 2

3. Database Systems Slide 3

4. Oracle Architecture – Key Components Database Systems Slide 4

5. Overview of Physical Database Structures The physical database structures of an database consist of: • Datafiles, • Redo Log Files, • Control Files, • Other Files. Datafiles Every database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. The characteristics of datafiles are: • A datafile can be associated with only one database. • Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space. • One or more datafiles form a logical unit of database storage called a tablespace. Database Systems Slide 5

6. Control Files • Every database has a Control File. A control file contains entries that specify the physical structure of the database. For example, it contains the following information: • Database name • Names and locations of datafiles and redo log files • Time stamp of database creation • Because control files are important we can multiplex them, to protect against a failure involving the control file. • Every time an instance of a database is started, its control file identifies the database and redo log files that must be opened for database operation to proceed. • A control file is also used in database recovery. Database Systems Slide 6

7. Redo Log Files • A database has a set of two or more redo log files. A redo log is made up of redo entries (also called redo records). • The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. • To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks. • The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles. • For example, if an unexpected power outage terminates database operation, then data in memory cannot be written to the datafiles, and the data is lost. However, lost data can be recovered when the database is opened, after power is restored. • By applying the information in the most recent redo log files to the database datafiles, Oracle restores the database to the time at which the power failure occurred. • The process of applying the redo log during a recovery operation is called rolling forward. Database Systems Slide 7

8. Other Files • Archive Log Files You can enable automatic archiving of the redo log. Oracle automatically archives log files when the database is in ARCHIVELOG mode. • Parameter Files Parameter files contain a list of configuration parameters for that instance and database. • Alert and Trace Log Files Each server and background process can write to an associated trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. • Trace file information is also used to tune applications and instances • The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors • Backup Files To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file. Database Systems Slide 8

9. Overview of Logical Database Structures The logical storage structures, including data blocks, extents, and segments, enable DBMS to have fine-grained control of disk space use. • Tablespaces • A database is divided into logical storage units called tablespaces, which is the largest logical structure. • Tablespaces commonly group together all application objects to simplify some administrative operations. • One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. More about tablespaces will be covered in the lecture on Managing Storage in Databases Database Systems Slide 9

10. Overview of Logical Database Structures … contd() • Oracle Data Blocks At the finest level of granularity, database data are stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. • Extents The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information. • Segments Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a certain logical structure. Database Systems Slide 10

11. Memory Architecture • RDBMS uses memory to store information such as the following: • Program code • Information about a connected session, even if it is not currently active • Information needed during program execution • Information that is shared and communicated among other processes (for example, locking information) • Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries) • The basic memory structures associated with Oracle include: • System Global Area (SGA), which is shared by all server and background processes. • Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process. Database Systems Slide 11

12. Memory Structures Database Systems Slide 12

13. Overview of the System Global Area • The System Global Area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. • If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. • The SGA and Oracle processes together constitute an Oracle instance. • Oracle automatically allocates memory for an SGA when you start an instance. • Each database instance has its own SGA. • The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance’s SGA, and several processes write to the SGA during execution of Oracle. • The SGA contains the following data structures: • Database buffer cache • Redo log buffer • Shared pool • Java pool • Large pool (optional) • Streams pool • Data dictionary cache • Other miscellaneous information Database Systems Slide 13

14. Database Buffer Cache • The Database Buffer Cache is the portion of the SGA that holds copies of data blocks read from datafiles. • The buffers in the cache are organized in two lists: • The write list (Also called dirty list) • the least recently used (LRU) list. • The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. • The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. • Free buffers do not contain any useful data and are available for use. • Pinned buffers are currently being accessed. • When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list. Database Systems Slide 14

15. Database Buffer Cache … contd() • When user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. • If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. • The LRU Algorithm and Full Table Scans • When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. Database Systems Slide 15

16. Redo Log Files Database Buffer Cache SGA DBWR LGWR Server proc. User proc. Memory Structures … contd() Redo Log Shared Pool Data Files Database Systems Slide 16

17. Redo Log Buffer • The Redo Log Buffer is a circular buffer in the SGA that holds information about changes made to the database. • This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. • Redo entries are used for database recovery, if necessary. • Redo entries are copied by Oracle database processes from the user’s memory space to the redo log buffer in the SGA. • The redo entries take up continuous, sequential space in the buffer. • The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk. Database Systems Slide 17

18. Shared Pool The shared pool portion of the SGA contains following buffers for parallel execution messages, and control structures: • the library cache, • the dictionary cache, Library Cache The library cache includes: • the shared SQL areas, • private SQL areas, • PL/SQL procedures and packages, • control structures such as locks and library cache handles. Database Systems Slide 18

19. Shared SQL Areas • Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA. • Oracle represents each SQL statement it runs with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statement’s private SQL area. • A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application. • Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. • If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement’s shared SQL area. • If Oracle deallocates a shared SQL area, the associated SQL statement must be reparsed and reassigned to another shared SQL area at its next execution. Database Systems Slide 19

20. Private SQL Area • A private SQL area contains data such as bind information and runtime memory structures. • Each session that issues a SQL statement has a private SQL area. • Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area. • The private SQL area of a cursor is itself divided into two areas whose lifetimes are different: • The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed. • The run-time area, which is freed when the execution is terminated. • Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been run. For queries, Oracle frees the runtime area only after all rows are fetched or the query is cancelled. • If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA. Database Systems Slide 20

21. PL/SQL Program Units and the Shared Pool • Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements. • Oracle allocates a shared area to hold the parsed, compiled form of a program unit. • Oracle allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. • If more than one user runs the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session. • Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that runs the statement. Database Systems Slide 21

22. Dictionary Cache • The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. • Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle. • The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. • One area is called the Data Dictionary Cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). • The other area in memory to hold dictionary data is the Library Cache. All Oracle user processes share these two caches for access to data dictionary information. Database Systems Slide 22

23. Large Pool • The database administrator can configure an optional memory area called the Large Pool to provide large memory allocations for: • Session memory for the shared server • I/O server processes, • Oracle backup and restore operations • By allocating session memory from the Large Pool for shared server, Oracle can use the Shared Pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache. • In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes. • The Large Pool is better able to satisfy such large memory requests than the Shared Pool. • The Large Pool does not have an LRU list. It is different from reserved space in the Shared Pool, which uses the same LRU list as other memory allocated from the Shared Pool. Database Systems Slide 23

24. Java and Streams Pool • Java Pool memory is used in server memory for all session-specific Java code. • Java Pool memory is used in different ways, depending on what mode the Oracle server is running in. • In a single database, you can specify that Streams memory be allocated from a new pool in the SGA called the Streams Pool. • If the size of the Streams Pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams Pool. • If the size of the Streams Pool is zero, then the memory used by Streams is allocated from the Shared Pool and may use up to 10% of the Shared Pool. Database Systems Slide 24