The single most important component for tuning an Oracle database is the size of the database buffer. The database buffer is where Oracle holds blocks that have already been retrieved by a prior database request. Whenever a new request for data is made, Oracle will first check this buffer. If the block already is in memory, Oracle can deliver the block to the user ten thousand times faster than if Oracle had to perform an I/O to go to the external disk for information. Access time on disks now reaches an impressive speed—between 10 and 25 milliseconds. However, data that already resides in the RAM area of Oracle SGA can be retrieved in nanoseconds. The parameter controlling the size of the buffer is called db_block_buffers, and is one of the parameters in the init.ora file. For more information on db_block_size, see Chapter 6, Oracle DBA Performance And Tuning.

The other important region of the SGA is the shared pool, which keeps a number of subareas. One of the confounding problems with Oracle is that all of these areas are sized by only one parameter, shared_pool_size. It is impossible to dedicate separate regions of memory for the components within the shared pool.

However, the SGA is not the only memory structure used by Oracle. Each application that accesses Oracle receives a program global area, or PGA. The PGA contains private SQL areas that are used by individual programs and keep application-specific database information. The private SQL area keeps the current values of cursors, and other program-dependent information.

The second largest memory component with the SGA is the shared pool. The shared pool holds memory for the following purposes:

•  Library Cache—An area that holds the plan information for SQL that is currently being executed. This area also holds stored procedure and trigger code.

•  Dictionary Cache—Keeps environmental information, including referential integrity, table definitions, indexing information, and other metadata that is stored within Oracle’s internal tables.

•  Session Information—Session information is only kept for systems that are using SQL*Net version 2 with Oracle’s multithreaded server. See Chapter 8 for details on using the multithreaded server.

Oracle Metadata—The V$ Tables

The V$ tables are internal structures built into memory when an Oracle instance is started. Although they appear to be tables, they are really internal memory structures implemented in the C language. Therefore, the V$ tables only exist during the execution of the instance and are destroyed at shutdown time. The V$ tables are used by Oracle to capture information about the overall status of the database, and the information from the V$ tables can provide tremendous insight into the internal operations. While dozens of V$ tables exist, only a handful can be used for Oracle performance and tuning.

The V$ tables have limited use for measuring time-dependent information, since they accumulate information from the moment that the Oracle instance is started up until the present time. As such, measures such as the buffer hit ratio are normalized, presenting only the average for the entire time that the instance has been running. Instead of offering an exhaustive list of all the Oracle V$ tables, I included only relevant queries against the V$ tables throughout this text.

Tuning Oracle Memory

The memory size of the SGA is commonly called the Oracle region. The way that the Oracle memory is managed can have a huge impact on performance, and each SGA can be tuned according to the needs of the application. However, one must remember that the SGA faces dynamic forces, and one transaction can cause problems for other transactions that are accessing Oracle. Hundreds of transactions may be serviced concurrently, each requesting different data. Tuning the memory for the activity at one point in time may not be suitable for another time. Because of the dynamic nature of the Oracle database, only general tuning is possible.

This general approach works out well because of the high level of sophistication: Oracle has only three parameters that affect the size of the parts of the SGA: db_block_buffers, shared_pool_size, and log_buffer.