Page 56
FIG. 5.1
The Oracle Instance is a
complex interaction of
discrete processes.
Many parameters and techniques exist to help you configure the instance to best support your applications and requirements. Configuring the instance objects for peak performance is, in most cases, a trial and error procedureyou can start with likely parameter values, but only time and monitoring give you the best possible mix of all settings and variables.
Configuring instance parameters involves changing the necessary init.ora parameter and bouncing (stopping and starting) the database. There are numerous init.ora parameters, and many of these are undocumented. Although you should not change or add unfamiliar initialization parameters, you can reference the internal x$ksppi table to view all the possible initialization parameters for a database. The ksppinm and ksppdesc columns give you the parameter name and a brief description of the parameter, respectively.
NOTE |
Manipulating initialization file parameters without a clear understanding of the possible consequences is dangerous! There are many parameters that exist for pure diagnostic reasons, which can leave your database in an unsynchronized or corrupted state. Undocumented parameters are named with a leading underscore. Do not add or change keys or values in the init.ora file unless you are confident in what you are doing! |
For the most part, instance configuration is primarily concerned with the objects in the SGA, and you find most of your database configuration and tuning time spent with these structures. However, there are issues and configuration options with the background processes that also need to be addressed, and we explore those parts of the instance as well.
Page 57
The SGA is the primary component of the instance. It holds all the memory structures necessary for data manipulation, SQL statement parsing, and redo caching. The SGA is shared, meaning that multiple processes can access and modify the data contained within it at the same time. All database operations use structures contained in the SGA at one point or another. As mentioned in the previous section, the SGA is when the instance is created, during the nomount stage of the database, and is deallocated when the instance is shut down.
The SGA consists of the following:
These are each explained in the following sections.
The Shared PoolThe shared pool (see Figure 5.2) contains the library cache, the dictionary cache, and server control structures (such as the database character set). The library cache stores the text, parsed format, and execution plan of SQL statements that have been submitted to the RDBMS, as well as the headers of PL/SQL packages and procedures that have been executed. The dictionary cache stores data dictionary rows that have been used to parse SQL statements.
FIG. 5.2
The shared pool caches
information used when
parsing and executing
SQL statements.
The Oracle server uses the library cache to improve the performance of SQL statements. When a SQL statement is submitted, the server first checks the library cache to see if an identical statement has already been submitted and cached. If it has, Oracle uses the stored parse tree and execution path for the statement, rather than rebuilding these structures from scratch. Although this may not affect the performance of ad-hoc queries, applications using stored code can gain significant performance improvements by utilizing this feature.
NOTE |
For a SQL statement to use a previously cached version, it must be identical in ALL respects to the cached version, including punctuation and letter caseupper versus lower. Oracle identifies the statements by applying a hashing algorithm to the text of the statementthe hash value generated must be identical for both the current and cached statements in order for the cached version to be used. |
Page 58
The library cache contains both shared and private SQL areas. The shared SQL area contains the parse tree and execution path for SQL statements, while the private SQL area contains session-specific information, such as bind variables, environment and session parameters, runtime stacks and buffers, and so on. A private SQL area is created for each transaction initiated, and deallocated after the cursor corresponding to that private area is closed. The number of private SQL areas a user session can have open at one time is limited by the value of the OPEN_CURSORS init.ora parameter. Using these two structures, the Oracle server can reuse the information common across all executions of a SQL statement, while session-specific information to the execution can be retrieved from the private SQL area.
NOTE |
An application that does not close cursors as they are used continues to allocate more and more memory for the application, in part because of the private SQL areas allocated for each open cursor. |
The private SQL area of the library cache is further divided into persistent and runtime areas. Persistent areas contain information that is valid and applicable through multiple executions of the SQL statement, while the runtime area contains data that is used only while the SQL statement is being executed.
The dictionary cache holds data dictionary information used by the RDBMS engine to parse SQL statements. Information such as segment information, security and access privileges, and available free storage space is held in this area.
The size of the shared pool is determined by the init.ora parameter SHARED_POOL_SIZE. This value is specified in bytes. You must set this value high enough to ensure that enough space is available to load and store PL/SQL blocks and SQL statements. The shared pool becomes fragmented over time from the loading and unloading of data objects, and errors can occur if there is not enough contiguous free space in the pool to load an object. You can solve this problem in the short term by issuing the SQL command ALTER SYSTEM FLUSH SHARED_POOL, but if you are regularly encountering shared pool errors during database operation, you have to increase the shared pool size.
The Database Buffer Cache The operation of the database buffer cache is one of the biggest factors affecting overall database performance. The buffer cache is made up of memory blocks the same size as the Oracle blocks. All data manipulated by Oracle is first loaded into the buffer cache before being used. Any data updates are performed on the blocks in memory. For this reason, it is obviously very important to size the buffer cache correctly. Memory access is hundreds of times faster than disk access, and in an OLTP environment, most of your data operations should take place completely in memory, using database blocks already loaded into the cache.
The Oracle RDBMS swaps data out of the buffer cache according to a Least Recently Used (LRU) list. The LRU list keeps track of what data blocks are accessed, and how often. When a block is accessed or retrieved into the buffer cache, it is placed on the Most Recently Used (MRU) end of the list. When the Oracle server needs more space in the buffer cache to read a data block from disk, it accesses the LRU list to decide which blocks to swap out. Those blocks