Oracle7 Server Tuning

Contents Index Home Previous Next

Tuning the Shared Pool

This section describes tuning these parts of the shared pool:

This section presents these parts of the shared pool in order of importance. Because the algorithm that Oracle uses to manage data in the shared pool tends to hold dictionary data in memory longer than library cache data, tuning the library cache to an acceptable cache hit ratio often ensures that the data dictionary cache hit ratio is also acceptable. Allocating space in the shared pool for session information is only necessary if you are using the multi-threaded server architecture.

Tuning the Library Cache

The library cache contains shared SQL and PL/SQL areas. This section tells you how to tune the library cache by

Throughout this section, information about shared SQL areas and SQL statements also applies to shared PL/SQL areas and PL/SQL blocks.

Examining Library Cache Activity

Library cache misses can occur on either of these steps in the processing of a SQL statement.

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 Statistics reflecting library cache activity are kept in the dynamic performance table V$LIBRARYCACHE. These statistics reflect all library cache activity since the most recent instance startup. To monitor library cache activity, examine this table. 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:

Rows with other NAMESPACE values reflect library cache activity for object definitions that Oracle uses for dependency maintenance.

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.
Information in the V$LIBRARYCACHE table can also be obtained through SNMP.

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:

Total RELOADS should be near 0. If the ratio of RELOADS to PINS is more than 1%, then you should reduce these library cache misses through the means discussed in the next section.

Reducing Library Cache Misses

You can reduce library cache misses by

Allocating Additional Memory for the Library Cache You may be able to reduce library cache misses on execution calls by allocating additional memory for the library cache. To ensure that shared SQL areas remain in the cache once their SQL statements are parsed, increase the amount of memory available to the library cache until the V$LIBRARYCACHE.RELOADS value is near 0. To increase the amount of memory available to the library cache, increase the value of the initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter depends on your operating system. This measure will reduce implicit reparsing of SQL statements and PL/SQL blocks on execution.

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 share a shared SQL area whenever possible. For two different occurrences of a SQL statement or PL/SQL block to share a shared SQL area, they must be identical according to these criteria:

		SELECT *   FROM emp;

		SELECT * FROM emp;
		SELECT * FROM Emp;

		SELECT * FROM emp;
		SELECT * FROM emp;

		SELECT * FROM bob.emp;

		SELECT * FROM emp WHERE deptno = :department_no;
		SELECT * FROM emp WHERE deptno = :d_no;

Shared SQL areas are most useful for reducing library cache misses for multiple users running the same application. Discuss these criteria with the developers of such applications and agree on strategies to ensure that the SQL statements and PL/SQL blocks of an application can use the same shared SQL areas:

		SELECT ename, empno FROM emp WHERE deptno = 10;
		SELECT ename, empno FROM emp WHERE deptno = 20;

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

You can also increase the likelihood that SQL statements issued by different applications can share SQL areas by establishing these policies among the developers of these applications:

Speeding Access to Shared SQL Areas on Execution Calls

If you have no library cache misses, you may still be able to speed execution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME. This parameter specifies when a shared SQL area can be deallocated from the library cache to make room for a new SQL statement. The default value of this parameter is FALSE, meaning that a shared SQL area can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. The value of TRUE means that a shared SQL area can only be deallocated when all application cursors associated with its statement are closed.

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

If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect system performance. Session cursors can be stored in a session cursor cache. This feature can be particularly useful for applications designed using Oracle Forms because switching between forms closes all session cursors associated with a form.

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

In this section, you will tune the data dictionary cache. These topics are discussed in this section:

Examining Data Dictionary Cache Activity

Determine whether misses on the data dictionary cache are affecting the performance of Oracle. You can examine cache activity by querying the V$ROWCACHE table as described in the following sections.

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 Monitor the statistics in the V$ROWCACHE table over a period of time while your application is running with this query:

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:

Reducing Data Dictionary Cache Misses

Examine cache activity by monitoring the sums of the GETS and GETMISSES columns. For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%. If this ratio continues to increase above this threshold while your application is running, you should consider increasing the amount of memory available to the data dictionary cache. To increase the memory available to the cache, increase the value of the initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter varies depending on your operating system.

Tuning the Shared Pool with the Multi-Threaded Server

In the multi-threaded server architecture, Oracle stores session information in the shared pool rather than in the memory of user processes. Session information includes private SQL areas and sort areas. If you are using the multi-threaded server architecture, you may need to make your shared pool larger to accommodate session information. You can increase the size of the shared pool by increasing the value of the SHARED_POOL_SIZE initialization parameter. This section discusses measuring the size of session information by querying the dynamic performance table V$SESSTAT.

The V$SESSTAT Table

Oracle collects statistics on total memory used by a session and stores them in the dynamic performance table V$SESSTAT. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM. These statistics are useful for measuring session memory use:

session memory The value of this statistic is the amount of memory in bytes allocated to the session.
max session memory The value of this statistic is the maximum amount of memory in bytes ever allocated to the session.

Querying the V$SESSTAT Table

You can use this query to decide how much larger to make the shared pool if you are using the multi-threaded server. Issue these queries while your application is running:

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

The result of the first query indicates that the memory currently allocated to all sessions is 157,125 bytes. This value is the total memory whose location depends on how the sessions are connected to Oracle. If the sessions are connected to dedicated servers processes, this memory is part of the memories of the user processes. If the sessions are connected to shared server processes, this memory is part of the shared pool. The result of the second query indicates the sum of the maximum sizes of the memories for all sessions is 417,381 bytes. The second result is greater than the first because some sessions have deallocated memory since allocating their maximum amounts.

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.


Contents Index Home Previous Next