0059-0061

Previous Table of Contents Next

Page 59

Figure 4.4.
The Oracle SGA in
memory.


Database Buffer Cache

The database buffer cache of the SGA holds Oracle blocks that have been read in from the database files. When one process reads the blocks for a table into memory, all the processes for that instance can access those blocks.

If a process needs to access some data, Oracle determines whether the block is already in this cache (thereby avoiding a disk read). If the Oracle block is not in the buffer, it must be read from the database files into the buffer cache. The buffer cache must have a free block available before the data block can be read from the database files.

The Oracle blocks in the database buffer cache in memory are arranged with the most recently used at one end and the least recently used at the other. This list is constantly changing as the database is used. If data must be read from the database files into memory, the blocks at the least recently used end are written back to the database files first (if they've been modified). The DBWR process is the only process that writes the blocks from the database buffer cache to the database files.

The more database blocks you can hold in real memory, the quicker your instance will run.

Redo Cache

The online redo log files record all the changes made to user objects and system objects. Before the changes are written out to the redo logs, Oracle stores them in the redo cache memory area. For example, the entries in the redo log cache are written down to the online redo logs when the cache becomes full or when a transaction issues a commit. The entries for more than one transaction can be included together in the same disk write to the redo log files.

The LGWR background process is the only process that writes out entries from this redo cache to the online redo log files.

Page 60

Shared Pool Area

The shared pool area of the SGA has two main components: the SQL area and the dictionary cache. You can alter the size of these two components only by changing the size of the entire shared pool area.

SQL Area

A SQL statement sent for execution to the database server must be parsed before it can execute. The SQL area of the SGA contains the binding information, runtime buffers, parse tree, and execution plan for all the SQL statements sent to the database server. Because the shared pool area is a fixed size, you might not see the entire set of statements that have been executed since the instance first came up; Oracle might have flushed out some statements to make room for others.

If a user executes a SQL statement, that statement takes up memory in the SQL area. If another user executes exactly the same statement on the same objects, Oracle doesn't need to reparse the second statement because the parse tree and execution plan is already in the SQL area. This part of the architecture saves on reparsing overhead. The SQL area is also used to hold the parsed, compiled form of PL/SQL blocks, which can also be shared between user processes on the same instance.

TIP
Make SQL statements exactly the same in application code (using procedures) to avoid reparsing overhead.

Dictionary Cache

The dictionary cache in the shared pool area holds entries retrieved from the Oracle system tables, otherwise known as the Oracle data dictionary. The data dictionary is a set of tables located in the database files, and because Oracle accesses these files often, it sets aside a separate area of memory to avoid disk I/O.

The cache itself holds a subset of the data from the data dictionary. It is loaded with an initial set of entries when the instance is first started and then populated from the database data dictionary as further information is required. The cache holds information about all the users, the tables and other objects, the structure, security, storage, and so on.

The data dictionary cache grows to occupy a larger proportion of memory within the shared pool area as needed, but the size of the shared pool area remains fixed.

Process Global Area

The process global area, sometimes called the program global area or PGA, contains data and control structures for one user or server process. There is one PGA for each user process (connection) to the database.

Page 61

The actual contents of the PGA depend on whether the multithreaded server configuration is implemented, but it typically contains memory to hold the session's variables , arrays, some rows results, and other information. If you're using the multithreaded server, some of the information that is usually held in the PGA is instead held in the common SGA.

The size of the PGA depends on the operating system used to run the Oracle instance, and once allocated, it remains the same. Memory used in the PGA does not increase according to the amount of processing performed in the user process. The database administrator can control the size of the PGA by modifying some of the parameters in the instance parameter file INIT.ORA; one parameter that DBAs often change is the SORT_AREA_SIZE.

The Oracle Programs

The Oracle server code mentioned previously is code that performs the same function regardless of which tool the front-end programs are using (such as SQL*Plus, Oracle Forms, Reports, Excel, and so on). On some platforms, the server code is loaded only once into the machine's memory, and all the processes using the instance can share it ”even across instances (as long as you are running the same version of Oracle for both instances). This kernel code, also known as reentrant code, saves memory because it requires that only one copy of the code be loaded into memory.

Storage

In this section, I discuss the organization of the database files themselves . I do not discuss the control and redo log files in this section.

Tablespaces and Database Files

For management, security, and performance reasons, the database is logically divided into one or more tablespaces that each comprise one or more database files. A database file is always associated with only one tablespace.

NOTE
A tablespace is a logical division of a database comprising one or more physical data-base files.

Every Oracle database has a tablespace named SYSTEM that has the very first file of the database allocated to it. The SYSTEM tablespace is the default location of all objects when a database is first created. The simplest database setup is one database file in the SYSTEM tablespace (simple, but not recommended).

Typically, you create many tablespaces to partition the different parts of the database. For example, you might have one tablespace for tables, another to hold indexes, and so on, and each of these tablespaces would have one or more database files associated with them.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net