Oracle7 Server Tuning

Library

Product

Contents

Index

Tuning Memory Allocation

• Tuning Memory Allocation
• Understanding Memory Allocation Issues
• How to Detect Memory Allocation Problems
• How to Solve Memory Allocation Problems
• Tuning Operating System Memory Requirements
• Tuning the Redo Log Buffer
• Tuning Private SQL and PL/SQL Areas
• Tuning the Shared Pool
• Tuning the Buffer Cache
• Tuning Sort Areas
• Reallocating Memory
• Reducing Total Memory Usage

Tuning Memory Allocation

This chapter explains how to allocate memory to database structures. Proper sizing of these structures can greatly improve database performance. The following topics are covered:

• Understanding Memory Allocation Issues

• How to Detect Memory Allocation Problems

• How to Solve Memory Allocation Problems

• Tuning Operating System Memory Requirements

• Tuning the Redo Log Buffer

• Tuning Private SQL and PL/SQL Areas

• Tuning the Shared Pool

• Tuning the Buffer Cache

• Tuning Sort Areas

• Reallocating Memory

• Reducing Total Memory Usage

Understanding Memory Allocation Issues

Because Oracle's memory requirements vary depending on your application, you should tune memory allocation after tuning your application and your SQL statements. Allocating memory before tuning your application and your SQL statements may make it necessary to resize some Oracle memory structures to meet the needs of your modified statements and application.

Tune memory allocation before you tune I/O. Allocating memory establishes the amount of I/O necessary for Oracle to operate. The present chapter shows you how to allocate memory to perform as little I/O as possible.

See Also: Chapter 14, "Tuning I/O", shows you how to perform I/O as efficiently as possible.

How to Detect Memory Allocation Problems

The SZ statistic is given in page size (normally 4K), and normally includes the shared overhead. To calculate the private, or per-process memory usage, subtract shared memory and executable stack figures from the value of SZ. For example:

SZ	+20,000
minus SHM	-15,000
minus EXECUTABLE	-1,000
actual per-process memory	4,000

In this example, the individual process consumes only 4,000 pages; the other 16,000 pages are shared by all processes.

See Also: Oracle for UNIX Performance Tuning Tips, or your operating system documentation.

How to Solve Memory Allocation Problems

• Tuning Operating System Memory Requirements

• Tuning the Redo Log Buffer

• Tuning Private SQL and PL/SQL Areas

• Tuning the Shared Pool

• Tuning the Buffer Cache

• Tuning Sort Areas

• Reallocating Memory

• Reducing Total Memory Usage

Tuning Operating System Memory Requirements

• Reducing Paging and Swapping

• Fitting the System Global Area into Main Memory

• Allocating Enough Memory to Individual Users

See Also: Refer to your operating system hardware and software documentation as well as your Oracle operating system-specific documentation for more information on tuning operating system memory usage.

Reducing Paging and Swapping

• real memory

• virtual memory

• expanded storage

• disk

Monitor your operating system behavior with operating system utilities. Excessive paging or swapping indicates that new information is often being moved into memory. In this case, your system's total memory may not be large enough to hold everything for which you have allocated memory. Either increase the total memory on your system or decrease the amount of memory you have allocated.

See Also: "Oversubscribe, with Attention to Paging" on page 18-28

Fitting the System Global Area into Main Memory

Although it is best to keep the SGA in memory, the contents of the SGA will be logically split between hot and cold parts. The hot parts will always be in memory because they are always being referenced. Some of the cold parts may be paged out and there may be a performance penalty for bringing them back in. A performance problem is very likely, however, if the hot part of the SGA cannot stay in memory.

Remember that data is swapped to disk because it is not being referenced. You can cause Oracle to read the entire SGA into memory when you start your instance by setting the value of the initialization parameter PRE_PAGE_SGA to YES. Operating system page table entries are then pre-built for each page of the SGA. This setting may increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

PRE_PAGE_SGA may also increase the amount of time needed for process startup, because every process that starts must attach to the SGA. The cost of this strategy is fixed, however: you may simply determine that 20,000 pages must be touched every time a process is started. Whereas this approach may be useful with some applications, it may not be best for all applications. If your system creates and destroys processes all the time (by doing continual logon/logoff, for example) there may be significant overhead. Note that this setting does not prevent your operating system from paging or swapping the SGA after it is initially read into memory.

For example, if the SGA is 80 MB in size, and the page size is 4K, then 20,000 pages must be touched in order to refresh the SGA (80,000/4 = 20,000). If the system permits you to set a 4MB page size, then only 200 pages must be touched to refresh the SGA (80,000/4,000 = 200). Note that the page size is operating-system specific and generally cannot be changed. Some operating systems, however, have a special implementation for shared memory whereby you can change the page size.

You can see how much memory is allocated to the SGA and each of its internal structures by issuing this Server Manager statement:

SVRMGR> SHOW SGA

The output of this statement might look like this:

Total System Global Area        3554188 bytes
              Fixed Size          22208 bytes
           Variable Size        3376332 bytes
        Database Buffers         122880 bytes
      Redo Buffers     32768 bytes

Some operating systems for IBM mainframe computers are equipped with expanded storage or special memory, in addition to main memory, to which paging can be performed very quickly. These operating systems may be able to page data between main memory and expanded storage faster than Oracle can read and write data between the SGA and disk. For this reason, allowing a larger SGA to be swapped may lead to better performance than ensuring that a smaller SGA stays in main memory. If your operating system has expanded storage, you can take advantage of it by allocating a larger SGA despite the resulting paging.

Allocating Enough Memory to Individual Users

Depending on your operating system, these resources may include:

• the Oracle executable image

• the SGA

• Oracle application tools

• application-specific data

Tuning the Redo Log Buffer

Redo Log Buffer

Key log buffer ratio is the space request ratio: redo log space requests / redo entries. If this ratio is greater than 1:5000, then increase the size of the redo log buffer until the space request ratio stops falling.

Tuning Private SQL and PL/SQL Areas

• Identifying Unnecessary Parse Calls

• Reducing Unnecessary Parse Calls

Tuning private SQL areas involves identifying unnecessary parse calls made by your application and then reducing them. To reduce parse calls, you may have to increase the number of private SQL areas that your application can have allocated at once. Throughout this section, information about private SQL areas and SQL statements also applies to private PL/SQL areas and
PL/SQL blocks.

Identifying Unnecessary Parse Calls

Technique 1

Note: This statistic does not include implicit parsing that occurs when an application executes a statement whose shared SQL area is no longer in the library cache. For information on detecting implicit parsing, see "Examining Library Cache Activity" on page 13-14.

If the count value for the Parse step is near the count value for the Execute step for a statement, your application may be deliberately making a parse call each time it executes the statement. Try to reduce these parse calls through your application tool.

Technique 2

SELECT sql_text, parse_count, executions
FROM V$SQLAREA

When the parse_count value is close to that of executions for a given statement, you may be continually reparsing that particular SQL statement.

Technique 3

SELECT * FROM V$STATNAME
WHERE name in ('parse_count','execute_count')

The results of the query will look something like this:

statistic#,		name
------------		---------
100		parse_count
90		execute_count

Then run a query like the following:

SELECT * FROM V$SESSTAT
WHERE statistics# in (90,100)
ORDER BY value, sid;

The result will be a list of all sessions and the amount of reparsing they do. For each system identifier (sid), go to V$SESSION to find the name of the program that causes the reparsing.

Reducing Unnecessary Parse Calls

In general, an application that reuses private SQL areas for multiple SQL statements does not need as many private SQL areas as an application that does not reuse private SQL areas. However, an application that reuses private SQL areas must perform more parse calls because the application must make a new parse call whenever an existing private SQL is reused for a new SQL statement.

Be sure that your application can open enough private SQL areas to accommodate all of your SQL statements. If you allocate more private SQL areas, you may need to increase the limit on the number of cursors permitted for a session. You can increase this limit by increasing the value of the initialization parameter OPEN_CURSORS. The maximum value for this parameter depends on your operating system. The minimum value is 5.

The means by which you control parse calls and allocation and deallocation of private SQL areas varies depending on your Oracle application tool. The following sections introduce the means used for some tools. Note that these means apply only to private SQL areas and not to shared SQL areas.

Reducing Parse Calls with the Oracle Precompilers

• HOLD_CURSOR = yes

• RELEASE_CURSOR = no

• MAXOPENCURSORS = desired value

The precompiler options can be specified in two ways:

• on the precompiler command line

• within the precompiler program

See Also: Programmer's Guide to the Oracle Precompilers for more information on these calls.

Reducing Parse Calls with the Oracle Call Interfaces

See Also: Programmer's Guide to the Oracle Call Interface for more information on these calls.

Reducing Parse Calls with Oracle Forms

• at the trigger level

• at the form level

• at runtime

Tuning the Shared Pool

• Tuning the Library Cache

• Tuning the Data Dictionary Cache

• Tuning the Shared Pool with the Multi-Threaded Server

• Tuning Reserved Space from the Shared Pool

In the shared pool, some of the caches are dynamic--they grow or shrink as needed. These dynamic caches include the library cache and the data dictionary cache. Objects will be paged out of these caches if there is no more room in the shared pool. For this reason you may have to increase shared pool size if the "hot" set of data needed does not fit within it. A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, you should allocate sufficient memory for the shared pool first.

For most applications, shared pool size is critical to Oracle performance. The shared pool holds both the data dictionary cache and the fully parsed or compiled representations of PL/SQL blocks and SQL statements. PL/SQL blocks include procedures, functions, packages, triggers and any anonymous PL/SQL blocks submitted by client-side programs. Shared pool size is less important only for applications which issue a very limited number of discrete SQL statements.

If the shared pool is too small, then the server must dedicate resources to managing the limited space available. This consumes CPU and causes contention, since restrictions must be imposed on the parallel management of the various caches. The more you use triggers and stored procedures, the larger the shared pool must be. It may even reach a size measured in tens of megabytes.

Since it is better to measure statistics over a specific period rather than from startup, you can determine the library cache and row cache hit ratios from the following queries:

select (sum(pins - reloads)) / sum(pins) "Lib Cache"
    from v$librarycache;

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache"
    from v$rowcache;

The results show the miss rates for the library cache. (In general, the number of reparses reflects the library cache.)

The amount of free memory in the shared pool is reported in V$SGASTAT. The instantaneous value can be reported using the query

select * from v$sgastat where name = `free memory';

If there is always free memory available within the shared pool, then increasing the size of the pool will have little or no beneficial effect. On the other hand, just because it is full does not necessarily mean that there is a problem. If the ratios discussed above are close to 1 then there is no need to increase the pool size.

Once an entry has been loaded into the shared pool it cannot be moved. This can cause the pool to become fragmented. On UNIX-based systems, you can use the DBMS_SHARED_POOL PL/SQL package to manage the shared pool. It is located in $ORACLE_HOME/rdbms/admin/dbmspool.sql. The comments in the source code describe how to use the procedures within the package.

For example, PL/SQL tends to allocate large objects. If you are sorting big gets, which are moved out of the shared pool when a small get is moved in, there may not be free contiguous memory to allow the large object to be put back in. Using the DBMS_SHARED_POOL package, you can keep the large objects permanently pinned in the shared pool.

Key ratios are library cache hit ratio and row cache hit ratio. If free memory is close to zero and either the library cache hit ratio or the row cache hit ratio is less than 0.95, then increase the shared pool until the ratios stop improving.

Tuning the Library Cache

Examining Library Cache Activity

Parse If an application makes a parse call for a SQL statement and the parsed representation of the statement does not already exist in a shared SQL area in the library cache, Oracle parses the statement and allocates a shared SQL area. You may be able to reduce library cache misses on parse calls by ensuring that SQL statements can share a shared SQL area whenever possible.

Execute If an application makes an execute call for a SQL statement and the shared SQL area containing the parsed representation of the statement has been deallocated from the library cache to make room for another statement, Oracle implicitly reparses the statement, allocates a new shared SQL area for it, and executes it. You may be able to reduce library cache misses on execution calls by allocating more memory to the library cache.

Determine whether misses on the library cache are affecting the performance of Oracle by querying the dynamic performance table V$LIBRARYCACHE.

The V$LIBRARYCACHE Table You can monitor statistics reflecting library cache activity by examining the dynamic performance table V$LIBRARYCACHE. These statistics reflect all library cache activity since the most recent instance startup. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.

Each row in this table contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows of the table with these NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:

• SQL AREA

• TABLE/PROCEDURE

• BODY

• TRIGGER

These columns of the V$LIBRARYCACHE table reflect library cache misses on execution calls:

PINS	This column shows the number of times an item in the library cache was executed.
RELOADS	This column shows the number of library cache misses on execution steps.

Querying the V$LIBRARYCACHE Table Monitor the statistics in the V$LIBRARYCACHE table over a period of time with this query:

SELECT SUM(pins) "Executions",
   SUM(reloads) "Cache Misses while Executing"
   FROM v$librarycache;

The output of this query might look like this:

Executions Cache Misses while Executing
---------- ----------------------------
  320871                   549

Interpreting the V$LIBRARYCACHE Table Examining the data returned by the sample query leads to these observations:

• The sum of the PINS column indicates that SQL statements, PL/SQL blocks, and object definitions were accessed for execution a total of 320,871 times.

• The sum of the RELOADS column indicates that 549 of those executions resulted in library cache misses causing Oracle to implicitly reparse a statement or block or reload an object definition because it had aged out of the library cache.

• The ratio of the total RELOADS to total PINS is about 0.17%. This value means that only 0.17% of executions resulted in reparsing.

Reducing Library Cache Misses

• allocating additional memory for the library cache

• writing identical SQL statements whenever possible

To take advantage of additional memory available for shared SQL areas, you may also need to increase the number of cursors permitted for a session. You can increase this limit by increasing the value of the initialization parameter OPEN_CURSORS.

Be careful not to induce paging and swapping by allocating too much memory for the library cache. The benefits of a library cache large enough to avoid cache misses can be partially offset by reading shared SQL areas into memory from disk whenever you need to access them.

Writing Identical SQL Statements You may be able to reduce library cache misses on parse calls by ensuring that SQL statements and PL/SQL blocks use a shared SQL area whenever possible. For two different occurrences of a SQL statement or PL/SQL block to use a shared SQL area, they must be identical according to these criteria:

• The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces and case.

  For example, these statements cannot use the same shared SQL area:

  			SELECT * FROM emp;

  			SELECT *   FROM emp;

  These statements cannot use the same shared SQL area:

  			SELECT * FROM emp;

  			SELECT * FROM Emp;

• References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema.

  For example, if the schemas of the users BOB and ED both contain an EMP table and both users issue the following statement, their statements cannot use the same shared SQL area:

  			SELECT * FROM emp;

  			SELECT * FROM emp;

  If both statements query the same table and qualify the table with the schema, as in the following statement, then they can use the same shared SQL area:

  			SELECT * FROM bob.emp;

• Bind variables in the SQL statements must match in name and datatype. For example, these statements cannot use the same shared SQL area:

  			SELECT * FROM emp WHERE deptno = :department_no;

  			SELECT * FROM emp WHERE deptno = :d_no;

• The SQL statements must be optimized using the same optimization approach and, in the case of the cost-based approach, the same optimization goal. For information on optimization approach and goal, see "Choosing a Goal for the Cost-Based Approach" on page 7-6.

• Use bind variables rather than explicitly specified constants in your statements whenever possible.

  For example, the following two statements cannot use the same shared area because they do not match character for character:

  			SELECT ename, empno FROM emp WHERE deptno = 10;

  			SELECT ename, empno FROM emp WHERE deptno = 20;

  You can accomplish the goals of these statements by using the following statement that contains a bind variable, binding 10 for one occurrence of the statement and 20 for the other:

  			SELECT ename, empno FROM emp WHERE deptno = :department_no;

  The two occurrences of the statement can then use the same shared SQL area.

• Be sure that users of the application do not change the optimization approach and goal for their individual sessions.

• Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

• Use stored procedures whenever possible. Multiple users issuing the same stored procedure automatically use the same shared PL/SQL area. Since stored procedures are stored in a parsed form, they eliminate runtime parsing altogether.

Speeding Access to Shared SQL Areas on Execution Calls

Depending on the value of CURSOR_SPACE_FOR_TIME, Oracle behaves differently when an application makes an execution call. If the value is FALSE, Oracle must take time to check that a shared SQL area containing the SQL statement is in the library cache. If the value is TRUE, Oracle need not make this check because the shared SQL area can never be deallocated while an application cursor associated with it is open. Setting the value of the parameter to TRUE saves Oracle a small amount of time and may slightly improve the performance of execution calls. This value also prevents the deallocation of private SQL areas until associated application cursors are closed.

Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if there are library cache misses on execution calls. Such library cache misses indicate that the shared pool is not large enough to hold the shared SQL areas of all concurrently open cursors. If the value is TRUE and there is no space in the shared pool for a new SQL statement, the statement cannot be parsed and Oracle returns an error saying that there is no more shared memory. If the value is FALSE and there is no space for a new statement, Oracle deallocates an existing shared SQL area. Although deallocating a shared SQL area results in a library cache miss later, it is preferable to an error halting your application because a SQL statement cannot be parsed.

Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if the amount of memory available to each user for private SQL areas is scarce. This value also prevents the deallocation of private SQL areas associated with open cursors. If the private SQL areas for all concurrently open cursors fills the user's available memory so that there is no space to allocate a private SQL area for a new SQL statement, the statement cannot be parsed and Oracle returns an error indicating that there is not enough memory.

Caching Session Cursors

Oracle uses the shared SQL area to determine if more than three parse requests have been issued on a given statement. If so, Oracle assumes the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will then find the cursor in the session cursor cache.

To enable caching of session cursors, you must set the initialization parameter SESSION_CACHED_CURSORS. This parameter is a positive integer that specifies the maximum number of session cursors kept in the cache. A least recently used (LRU) algorithm ages out entries in the session cursor cache to make room for new entries when needed.

You can also enable the session cursor cache dynamically with the ALTER SESSION SET SESSION_CACHED_CURSORS command.

To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a larger value.

Tuning the Data Dictionary Cache

Monitoring Data Dictionary Cache Activity

Misses on the data dictionary cache are to be expected in some cases. Upon instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a "steady state" in which the most frequently used dictionary data is in the cache. At this point, very few cache misses should occur. To tune the cache, examine its activity only after your application has been running.

The V$ROWCACHE View Statistics reflecting data dictionary activity are kept in the dynamic performance table V$ROWCACHE. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.

Each row in this table contains statistics for a single type of the data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. These columns in the V$ROWCACHE table reflect the use and effectiveness of the data dictionary cache:

PARAMETER	This column identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.
GETS	This column shows the total number of requests for information on the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file descriptions data
GETMISSES	This column shows the number of data requests resulting in cache misses.

Querying the V$ROWCACHE Table Use the following query to monitor the statistics in the V$ROWCACHE table over a period of time while your application is running:

SELECT SUM(gets)  "Data Dictionary Gets",
   SUM(getmisses) "Data Dictionary Cache Get Misses"
   FROM v$rowcache;

The output of this query might look like this:

Data Dictionary Gets  Data Dictionary Cache Get Misses
--------------------  --------------------------------
             1439044                              3120

Interpreting the V$ROWCACHE Table Examining the data returned by the sample query leads to these observations:

• The sum of the GETS column indicates that there were a total of 1,439,044 requests for dictionary data.

• The sum of the GETMISSES column indicates that 3120 of the requests for dictionary data resulted in cache misses.

• The ratio of the sums of GETMISSES to GETS is about 0.2%.

Reducing Data Dictionary Cache Misses

Tuning the Shared Pool with the Multi-Threaded Server

With very high numbers of connected users, the only way to reduce memory usage to an acceptable level may be to go to three-tier connections. This is a by-product of using a TP monitor. This is only feasible with a pure transactional model, since no locks or uncommitted DML can be held between calls. Oracle's multi-threaded server (MTS) is much less restrictive of the application design than a TP monitor. It dramatically reduces operating system process count, because it normally requires only 5 threads per CPU. It still requires a minimum of around 300K bytes of context per connected user.

The V$SESSTAT Table

To find the value, query V$STATNAME as described in "Technique 3" on page 13-9.

Querying the V$SESSTAT Table

SELECT SUM(value) || ' bytes' "Total memory for all sessions"
   FROM v$sesstat, v$statname
   WHERE name = 'session memory'
      AND v$sesstat.statistic# = v$statname.statistic#;

SELECT SUM(value) || ' bytes' "Total max mem for all sessions"
   FROM v$sesstat, v$statname
   WHERE name = 'max session memory'
      AND v$sesstat.statistic# = v$statname.statistic#;

These queries also select from the dynamic performance table V$STATNAME to obtain internal identifiers for session memory and max session memory. The results of these queries might look like this:

Total memory for all sessions
-----------------------------
157125 bytes

Total max mem for all sessions
------------------------------
417381 bytes

Interpreting the V$SESSTAT Table

You can use the result of either of these queries to determine how much larger to make the shared pool if you use the multi-threaded server. The first value is likely to be a better estimate than the second unless nearly all sessions are likely to reach their maximum allocations at the same time.

Tuning Reserved Space from the Shared Pool

The DBA can reserve memory within the shared pool to satisfy large allocations during operations such as PL/SQL compilation and trigger compilation. Smaller objects will not fragment the reserved list, helping to ensure that the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.

Reserved List Tuning Parameters

SHARED_POOL_RESERVED_SIZE controls the amount of SHARED_POOL_SIZE reserved for large allocations. The fixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin this tuning only after performing all other shared pool tuning on the system.

SHARED_POOL_RESERVED_MIN_ALLOC controls allocation for the reserved memory. To create a reserved list, SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC should be adequate for most systems.

Controlling Space Reclamation of the Shared Pool

Initial Parameter Values

Ideally, you should make SHARED_POOL_RESERVED_SIZE large enough to satisfy any request for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the SGA. Making the SHARED_POOL_RESERVED_SIZE large enough to satisfy any request for memory is, therefore, not a feasible goal.

On a system with ample free memory to increase the SGA, the goal is to have REQUEST_MISS = 0. If the system is constrained for OS memory, the goal is as follows:

• REQUEST_FAILURES = 0 or not increasing

• LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

• AVG_FREE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

SHARED_POOL_ RESERVED_SIZE Too Small

• REQUEST_FAILURES > 0 (and increasing)

• LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

• MAX_FREE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

• FREE_MEMORY < SHARED_POOL_RESERVED_MIN_ALLOC

• increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE accordingly

• increase SHARED_POOL_RESERVED_MIN_ALLOC (but you may need to increase SHARED_POOL_SIZE)

SHARED_POOL_ RESERVED_SIZE Too Large

• REQUEST_MISS = 0 or not increasing

• FREE_MEMORY = > 50% of SHARED_POOL_RESERVED_SIZE minimum

• decrease SHARED_POOL_RESERVED_SIZE

• decrease SHARED_POOL_RESERVED_MIN_ALLOC (if not the default value)

SHARED_POOL_SIZE Too Small

• REQUEST_FAILURES > 0 and increasing

• LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

• decrease SHARED_POOL_RESERVED_SIZE

• decrease SHARED_POOL_RESERVED_MIN_ALLOC (if set larger than the default)

• increase SHARED_POOL_SIZE

Tuning the Buffer Cache

• Examining Buffer Cache Activity via the Cache Hit Ratio

• Sizing the Buffer Cache

• Raising Cache Hit Ratio by Reducing Buffer Cache Misses

• Removing Unnecessary Buffers When Cache Hit Ratio Is High

Examining Buffer Cache Activity via the Cache Hit Ratio

Calculating the Cache Hit Ratio

These statistics are useful for tuning the buffer cache:

db block gets, consistent gets	The sum of the values of these statistics is the total number of requests for data. This value includes requests satisfied by access to buffers in memory.
physical reads	The value of this statistic is the total number of requests for data resulting in access to datafiles on disk.

Monitor these statistics as follows over a period of time while your application is running:

SELECT name, value
   FROM v$sysstat
   WHERE name IN ('db block gets', 'consistent gets',
      'physical reads');

The output of this query might look like this:

NAME                                                        VALUE
------------------------------------------------------ ----------
db block gets                                               85792
consistent gets                                            278888
physical reads                                                       23182

Calculate the hit ratio for the buffer cache with this formula:

Hit Ratio = 1 - ( physical reads / (db block gets + consistent gets) )

Based on the statistics obtained by the example query, the buffer cache hit ratio is 94%.

Evaluating the Cache Hit Ratio

Note: The CACHE_SIZE_THRESHOLD parameter sets the maximum size of a table to be cached, in blocks; it is equal to one tenth of DB_BLOCK_BUFFERS. On a per-table basis, this parameter enables you to determine which tables should and should not be cached.

The solution lies at the design or implementation level, in that repeated scanning of the same large table is rarely the most efficient solution to the problem. It may be better to perform all of the processing in a single pass, even if this means that the overnight batch suite can no longer be implemented as a SQL*Plus script which contains no PL/SQL.

Production sites running with thousands or tens of thousands of buffers are rarely using memory effectively. In any large database running an OLTP application, in any given unit of time, most rows will be accessed either one or zero times. On this basis there is little point in keeping the row (or the block which contains it) in memory for very long following its use.

Finally, the relationship between cache hit ratio and number of buffers is far from a smooth distribution. When tuning the buffer pool, avoid the use of additional buffers which contribute little or nothing to the cache hit ratio. As illustrated in the following figure, there are only narrow bands of values of DB_BLOCK_BUFFERS which are worth considering. The effect is not completely intuitive.

Buffer Pool Cache Hit Ratio

As a rule of thumb, increase DB_BLOCK_BUFFERS while:

• cache hit ratio is less than 0.9

• there is no evidence of undue page faulting

• the previous increase of DB_BLOCK_BUFFERS was effective

Determining Which Buffers Are in the Pool

Perform a query like the following:

SELECT dbafil, COUNT(dbablk), COUNT (DISTINCT dbafil || dbablk)
FROM V$BH
GROUP BY dbafil

Sizing the Buffer Cache

This is difficult to do manually, but scripts exist which take the effort out and report a histogram like the following example:

Buffers		Cache Hit Ratio
1 to	200	0.62
201 to	400	0.77
401 to	600	0.82
601 to	800	0.86
801 to	1000	0.87
1001 to	1200	0.88
1201 to	1400	0.89
1401 to	1600	0.89
1601 to	1800	0.89
1801 to	2000	0.89

Running such scripts involves some performance overhead, but does remove the guesswork.

Raising Cache Hit Ratio by Reducing Buffer Cache Misses

Oracle can collect statistics that estimate the performance gain that would result from increasing the size of your buffer cache. With these statistics, you can estimate how many buffers to add to your cache.

The X$KCBRBH Table

For example, in the first row of the table, the INDX value is 0 and the COUNT value is the number of cache hits to be gained by adding the first additional buffer to the cache. In the second row, the INDX value is 1 and the COUNT value is the number of cache hits for the second additional buffer.

Enabling the X$KCBRBH Table

To enable the collection of statistics in the X$KCBRBH table, set the value of DB_BLOCK_LRU_EXTENDED_STATISTICS. For example, if you set the value of the parameter to 100, Oracle will collect 100 rows of statistics, each row reflecting the addition of one buffer, up to 100 extra buffers.

Collecting these statistics incurs some performance overhead, which is proportional to the number of rows in the table. To avoid this overhead, collect statistics only when you are tuning the buffer cache and disable the collection of statistics when you are finished tuning.

Querying the X$KCBRBH Table

SELECT SUM(count) ach
   FROM sys.x$kcbrbh
   WHERE indx < 20;

You can also determine how these additional cache hits would affect the hit ratio. Use the following formula to calculate the hit ratio based on the values of the statistics db block gets, consistent gets, and physical reads and the number of additional cache hits (ACH) returned by the query:

Hit Ratio = 1 - (physical reads - ACH / (db block gets + consistent gets) )

Grouping Rows in the X$KCBRBH Table

SELECT 250*TRUNC(indx/250)+1||' to '||250*(TRUNC(indx/250)+1)
"Interval", SUM(count) "Buffer Cache Hits"
   FROM sys.x$kcbrbh
   GROUP BY TRUNC(indx/250);

The result of this query might look like

Interval           Buffer Cache Hits
--------------- --------------------
1 to 250                       16080
251 to 500                     10950
501 to 750                       710
751 to 1000                    23140

where:

INTERVAL	Is the interval of additional buffers to be added to the cache.
BUFFER CACHE HITS	Is the number of additional cache hits to be gained by adding the buffers in the INTERVAL column.

Examining the query output leads to these observations:

• If 250 buffers were added to the cache, 16,080 cache hits would be gained.

• If 250 more buffers were added for a total of 500 additional buffers, 10,950 cache hits would be gained in addition to the 16,080 cache hits from the first 250 buffers. This means that adding 500 buffers would yield a total of 27,030 additional cache hits.

• If 250 more buffers were added for a total of 750 additional buffers, 710 cache hits would be gained, yielding a total of 27,740 additional cache hits.

• If 250 buffers were added to the cache for a total of 1000 additional buffers, 23,140 cache hits would be gained, yielding a total of 50,880 additional cache hits.

• It is wise to add 250 or 500 buffers, provided memory resources are available. Both of these increments offer significant performance gains.

• It is unwise to add 750 buffers. Nearly the entire performance gain made by such an increase can be made by adding 500 buffers instead. Also, the memory allocated to the additional 250 buffers may be better used by some other Oracle memory structure.

• It is wise to add 1000 buffers, provided memory resources are available. The performance gain from adding 1000 buffers to the cache is significantly greater than the gains from adding 250, 500, or 750 buffers.

Removing Unnecessary Buffers When Cache Hit Ratio Is High

Oracle can collect statistics to predict buffer cache performance based on a smaller cache size. Examining these statistics can help you determine how small you can afford to make your buffer cache without adversely affecting performance.

The X$KCBCBH Table

The number of rows in this table is equal to the number of buffers in your buffer cache. Each row in the table reflects the number of cache hits attributed to a single buffer. For example, in the second row, the INDX value is 1 and the COUNT value is the number of cache hits for the second buffer. In the third row, the INDX value is 2 and the COUNT value is the number of cache hits for the third buffer.

The first row of the table contains special information. The INDX value is 0 and the COUNT value is the total number of blocks moved into the first buffer in the cache.

Enabling the X$KCBCBH Table

To enable the collection of statistics in the X$KCBCBH table, set the value of DB_BLOCK_LRU_STATISTICS to TRUE.

Collecting these statistics incurs some performance overhead. To avoid this overhead, collect statistics only when you are tuning the buffer cache and disable the collection of statistics when you are finished tuning.

Querying the X$KCBCBH Table

SELECT SUM(count) acm
   FROM sys.x$kcbcbh
   WHERE indx >= 90;

You can also determine the hit ratio based on this cache size. Use the following formula to calculate the hit ratio based on the values of the statistics db block gets, consistent gets, and physical reads and the number of additional cache misses (ACM) returned by the query:

Hit Ratio = 1 - ( physical reads + ACM / (db block gets + consistent gets) )

Another way to examine the X$KCBCBH table is to group the buffers in intervals. For example, if your cache contains 100 buffers, you may want to divide the cache into four 25-buffer intervals. You can query the table with a SQL statement similar to this one:

SELECT 25*TRUNC(indx/25)+1||' to '||25*(TRUNC(indx/25)+1)
"Interval", SUM(count) "Buffer Cache Hits"
   FROM sys.x$kcbcbh
   WHERE indx > 0 	GROUP BY TRUNC(indx/25);

Note that the WHERE clause prevents the query from collecting statistics from the first row of the table. The result of this query might look like

Interval           Buffer Cache Hits
--------------- --------------------
1 to 25                         1900
26 to 50                        1100
51 to 75                        1360
76 to 100                        230

where:

INTERVAL	Is the interval of buffers in the cache.
BUFFER CACHE HITS	Is the number of cache hits attributable to the buffers in the INTERVAL column.

Examining the query output leads to these observations:

• The last 25 buffers in the cache (buffers 76 to 100) contribute 230 cache hits. If the cache were reduced in size by 25 buffers, 230 cache hits would be lost.

• The third 25-buffer interval (buffers 51 to 75) contributes 1,360 cache hits. If these buffers were removed from the cache, 1,360 cache hits would be lost in addition to the 230 cache hits lost for buffers 76 to 100. Removing 50 buffers would result in losing a total of 1,590 cache hits.

• The second 25-buffer interval (buffers 26 to 50) contributes 1,100 cache hits. Removing 75 buffers from the cache would result in losing a total of 2,690 cache hits.

• The first 25 buffers in the cache (buffers 1 to 25) contribute 1,900 cache hits.

• If memory is scarce, it may be wise to remove 25 buffers from the cache. The buffers 76 to 100 contribute relatively few cache hits compared to the total cache hits contributed by the entire cache. Removing 25 buffers will not significantly reduce cache performance, and the leftover memory may be better used by other Oracle memory structures.

• It is unwise to remove more than 25 buffers from the cache. For example, removing 50 buffers would reduce cache performance significantly. The cache hits contributed by these buffers is a significant portion of the total.

Tuning Sort Areas

• to increase the number of sorts which can be conducted entirely within memory

• to speed up those sorts which cannot be conducted entirely within memory

Note that SORT_AREA_RETAINED_SIZE is maintained for each sort operation in a query. Thus if 4 tables are being sorted for a sort merge, Oracle maintains 4 areas of SORT_AREA_RETAINED_SIZE.

See Also: "Chapter 18, "Parallel Query Tuning"

Reallocating Memory

Tune your operating system again. Resizing Oracle memory structures may have changed Oracle memory requirements. In particular, be sure paging and swapping is not excessive. For example, if the size of the data dictionary cache or the buffer cache has increased, the SGA may be too large to fit into main memory. In this case, the SGA could be paged or swapped.

While reallocating memory, you may determine that the optimum size of Oracle memory structures requires more memory than your operating system can provide. In this case, you may improve performance even further by adding more memory to your computer.

Reducing Total Memory Usage

• increase the amount of memory available

• decrease the amount of memory used

Writing applications to conform to this model is not necessarily either restrictive or difficult, but it is most certainly different. Conversion of an existing application, such as an Oracle Forms suite, to conform is not normally possible without a complete rewrite.

The Oracle multi-threaded server (MTS) represents a compromise solution which is highly effective at reducing the number of operating system processes on the server, but less effective in reducing the overall memory requirement. Use of MTS has no effect on the number of network connections.

Shared connections are possible in an Oracle Forms environment by using an intermediate server which is also a client, and using the dbms_pipe mechanism to transmit atomic requests from the user's individual connection on the intermediate server to a shared daemon in the intermediate server. This in turn owns a connection to the central server.

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index