Page 782
This yields the maximum amount of UGA session memory used since instance startup. You may want to sample this over time and take the maximum of the maximums. Then increase SHARED_POOL_SIZE by this amount.
NOTE |
In Oracle8, use `session uga memory max' for the previous query.n |
With MTS, you have some control over the distribution of server versus user memory. Two init.ora parameters that affect user memory are
If desired, set SESSION_CACHED_CURSORS to your expected maximum number of session cursors to be cached in the users' memory area. As long as reparsing is kept low, this will help offload server memory requirements at the expense of increasing user memory. Optionally, set this parameter when statements are frequently reused.
The default setting for CLOSE_CACHED_OPEN_CURSORS is FALSE, meaning that cursors are not closed on COMMIT. Optionally, set this to TRUE if most SQL statements are rarely reused.
CAUTION |
Make sure your settings for CURSOR_SPACE_FOR_TIME and CLOSE_CACHE_OPEN_CURSORS do not conflict. For example, setting both to TRUE or both to FALSE seems the best approach. |
Next, let's turn our attention to the database buffer cache.
Perhaps the single most important tuning change you can make to improve the performance of your Oracle system is to properly set the size of your database buffer cache. The database buffer cache is the cache structure in the SGA and holds copies in memory of the Most Recently Used (MRU) Oracle data blocks. The two parameters that size this area are
DB_BLOCK_SIZE is the size of an Oracle block. This can range from 2KB (2,048 bytes) up to 64KB (65,536 bytes) on UNIX platforms. For performance, generally the higher the better. If your database has already been created with a relatively small block size (such as the default 2KB), consider rebuilding it if that is feasible for your application. If so, do the following:
Page 783
If your database is too large to be able to use export/import, ensure you have ASCII files of your table data (select them if necessary, or use a third-party tool). Rerun your DDL create scripts. Use SQL*Loader to reload the tables, in parallel if possible.
DB_BLOCK_BUFFERS is the number of Oracle blocks to be held in memory. Each buffer equals one block. This should be sufficiently high to yield an efficient cache hit ratio, but not so high as to cause operating system paging. The last thing you want is to have your SGA being paged in and out of memory by the OS. Paging is Oracle's primary job when it comes to the DB_BLOCK _BUFFERS, and you don't want the OS paging underneath Oracle. It pages them in on demand. Hence, your database buffer cache, along with the shared pool, should fit comfortably in real (available core) memory, and not be close to it or larger than it. Reasonable sizes are 1/2 to 3/4 of total system memory. For example, on a 1GB UNIX system, you might want to set it so that your SGA takes about 3/4, or 750MB. You must be careful to take into account other Oracle and non-Oracle application memory requirements, user memory requirements, and the operating system requirements. The size of the database buffer cache is
DB_BLOCK_BUFFERS x DB_BLOCK_SIZE
The database buffer cache is somewhat of a misnomer. Recall that a cache is a special type of buffer. Hence, buffer cache is actually redundant, not to mention a little confusing. (Maybe Oracle could have called it the database block cache?) In any case, all you really need to understand is that it caches the Oracle blocks. It is different from the shared pool in that it caches data and not programs.
The Oracle RDBMS server always reads Oracle blocks into the database buffer cache before passing them onto user processes. A user process, or application, always reads from (and writes to) the database buffer cache. The following are the steps in the buffer management of an I/O request:
Indexes are accessed one block at a time. Full table scans may have multiple blocks read with one request. Set the number of blocks (batch size) by setting the following:
DB_FILE_MULTIPLE_BLOCK_READ_COUNT = <the number of blocks to be read>
Page 784
Buffers may be free (clean), dirty, current, or read-consistent (rollback). A free buffer is one that has yet to be used since instance startup, or one that has been used and is now available. A dirty buffer is one that has been used, but has not been flushed, or written out by DBWR on checkpoint. A current buffer is one used in service of an INSERT, UPDATE, or DELETE. By their very nature, current buffers more often than not become dirty. Read-consistent buffers serve SELECT statements and rollback. Blocks read in service of full table scans are placed at the Least Recently Used (LRU) end of the LRU buffer chain. However, you can cache whole tables on the MRU end of the chain.
How do you tune the database buffer cache? Because memory I/O is several magnitudes faster than disk I/O (nanoseconds versus milliseconds), you want I/O requests to be satisfied by memory as often as possible. Namely, you want blocks to be found frequently (more than 90 percent of the time) in the database buffer cache, as opposed to having to fetch them from disk. You also want to minimize LRU latch contention. The LRU buffer chain, or list, is locked through latch mechanisms, just like those used throughout the Oracle kernel, and in the library cache in particular. As with any latch approach, you must have enough because latches (also known as spin locks) contain no queuing mechanisms as with semaphores.
As mentioned in tuning the library cache, the cache hit ratio for most any cache structure should be greater than or equal to 90 percent to be considered good. Again, this is the ratio of hits to requests. In other words, this is the number of times Oracle data blocks satisfy an I/O request, divided by the total number of I/O requests. A hit is when the block is in cache, and a miss is when it is not (and must be read from disk). There are at least two ways to measure the database buffer cache hit ratio. One way is to run the following query:
SQL> SELECT 1-(P.VALUE/(D.VALUE+C.VALUE)) "CACHE HIT RATIO" 2> FROM V$SYSSTAT P, V$SYSSTAT C, V$SYSSTAT D 3> WHERE P.NAME='physical reads' 5> AND D.NAME='db block gets' 4> AND C.NAME='consistent gets';
where `physical reads' is the number of blocks read from disk, `db block gets' is the number of blocks read from current copies of blocks in cache, and `consistent gets' is the number of read-consistent (rollback) copies of blocks in cache. Hence, the database buffer cache hit ratio formula is really
1 - (physical reads / logical reads)
The number of logical reads equals the number of current copies read plus the number of consistent copies read. The number of physical reads represents the number of misses. The number of logical reads represents the number of requests. From report.txt under the Statistics section, you also can gather the physical reads, db block gets, and consistent gets. Use the same formula to compute the cache hit ratio.
NOTE |
Guideline: If the database buffer cache hit ratio is less than .90, increase DB_BLOCK_BUFFERS and rerun utlbstat.sql/utlestat.sql. Increasing this parameter, as with most init.ora parameters, requires an instance shutdown and startup. However, it will not cause a major headache, such as changing the DB_BLOCK_SIZE would once the database has been built.n |