Release 2 (8.1.6)
Part Number A76992-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Designing and Tuning for Performance Release 2 (8.1.6) Part Number A76992-01 |
|
This chapter explains how to allocate memory to database structures. Proper sizing of these structures greatly improves database performance.
This chapter contains the following sections:
Oracle stores information in memory and on disk. Memory access is much faster than disk access; therefore, it is better for data requests to be satisfied by access to memory instead of by access to disk. For best performance, store as much data as possible in memory. However, memory resources on your operating system are likely to be limited. Tuning memory allocation involves distributing available memory to Oracle memory structures.
Oracle's memory requirements depend on your application. Therefore, tune memory allocation after tuning your application and SQL statements. If you allocate memory before tuning your application and SQL statements, then you may need to resize some Oracle memory structures to meet the needs of your modified statements and application.
Also, tune memory allocation before you tune I/O. Allocating memory establishes the amount of I/O necessary for Oracle to operate. This chapter shows you how to allocate memory to perform as little I/O as possible.
The following terms are used in this discussion:
|
See Also:
For information on how to perform I/O as efficiently as possible, see Chapter 20, "Tuning I/O". |
When you use operating system tools to examine the size of Oracle processes, such as ps -efl or ps -aux on UNIX, you may notice that the processes seem large. To interpret the statistics shown, determine how much of the process size is attributable to shared memory, heap, and executable stack, and how much is the actual amount of memory the given process consumes.
The SZ statistic is given in units of page size (normally 4KB), and it 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.
The rest of this chapter explains how to tune memory allocation. For best results, resolve memory issues in the order presented here:
Begin tuning memory allocation by tuning your operating system with these goals:
These goals apply in general to most operating systems, but the details of operating system tuning vary.
Your operating system may store information in these places:
The operating system may also move information from one storage location to another. This process is known as paging or swapping. Many operating systems page and swap to accommodate large amounts of information that do not fit into real memory. However, excessive paging or swapping can reduce the performance of many operating systems.
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 allocated.
Because the purpose of the System Global Area (SGA) is to store data in memory for fast access, the SGA should always be within main memory. If pages of the SGA are swapped to disk, then its data is no longer quickly accessible. On most operating systems, the disadvantage of excessive paging significantly outweighs the advantage of a large SGA.
Although it is best to keep the entire SGA in memory, the contents of the SGA are split logically between hot and cold parts. The hot parts are always in memory, because they are always being referenced. Some cold parts may be paged out, and a performance penalty may result from bringing them back in. A performance problem likely occurs, however, when the hot part of the SGA cannot remain in memory.
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 increase the process startup duration, because every process that starts must attach itself 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 starts. This approach may be useful with some applications, but not with all applications. Overhead may be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.
The advantage that PRE_PAGE_SGA can afford depends on page size. For example, if the SGA is 80MB in size, and the page size is 4KB, then 20,000 pages must be touched to refresh the SGA (80,000/4 = 20,000).
If the system permits you to set a 4MB page size, then only 20 pages must be touched to refresh the SGA (80,000/4,000 = 20). 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 the following SQL statement:
SHOW SGA
The output of this statement could look like the following:
Total System Global Area 18847360 bytes Fixed Size 63104 bytes Variable Size 14155776 bytes Database Buffers 4096000 bytes Redo Buffers 532480 bytes
Some IBM mainframe computer operating systems have 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 remains in main memory. If your operating system has expanded storage, then take advantage of it by allocating a larger SGA despite the resulting paging.
On some operating systems, you may have control over the amount of physical memory allocated to each user. Be sure that all users are allocated enough memory to accommodate the resources they need to use their application with Oracle.
Depending on your operating system, these resources may include:
On some operating systems, Oracle software can be installed so that a single executable image can be shared by many users. By sharing executable images among users, you can reduce the amount of memory required by each user.
The LOG_BUFFER parameter reserves space for the redo log buffer that is fixed in size. On machines with fast processors and relatively slow disks, the processors may be filling the rest of the buffer in the time it takes the redo log writer to move a portion of the buffer to disk. The log writer process (LGWR) always starts when the buffer begins to fill. For this reason, a larger buffer makes it less likely that new entries collide with the part of the buffer still being written.
The log buffer is normally small compared with the total SGA size, and a modest increase can significantly enhance throughput.
When LGWR writes redo entries from the redo log buffer to a redo log file or disk, user processes can then copy new entries over the entries in memory that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.
The statistic REDO BUFFER ALLOCATION RETRIES reflects the number of times a user process waits for space in the redo log buffer. This statistic is available through the dynamic performance view V$SYSSTAT. By default, this view is available only to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.
Use the following query to monitor these statistics over a period of time while your application is running:
SELECT NAME, VALUE FROM V$SYSSTAT `WHERE NAME = 'REDO BUFFER ALLOCATION RETRIES';
The information in V$SYSSTAT can also be obtained through the Simple Network Management Protocol (SNMP).
The value of REDO BUFFER ALLOCATION RETRIES should be near zero. If this value increments consistently, then processes have had to wait for space in the buffer. The wait may be caused by the log buffer being too small or by checkpointing. Increase the size of the redo log buffer, if necessary, by changing the value of the initialization parameter LOG_BUFFER. The value of this parameter, expressed in bytes, must be a multiple of DB_BLOCK_SIZE. Alternatively, improve the checkpointing or archiving process.
This section explains how to tune private SQL and PL/SQL areas in the following ways:
A trade-off exists between memory and reparsing. With significant amounts of reparsing, less memory is needed. If you reduce reparsing by creating more SQL statements, then client memory requirements increase. This is due to an increase in the number of open cursors.
Tuning private SQL areas entails identifying unnecessary parse calls made by your application and then reducing them. To reduce parse calls, you may need 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.
This section describes three techniques for identifying unnecessary parse calls.
Run your application with the SQL trace facility enabled. For each SQL statement in the trace output, the "count" statistic for the Parse step tells you how many times your application makes a parse call for the statement. This statistic includes parse calls satisfied by access to the library cache, as well as parse calls resulting in actually parsing the statement.
|
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". |
If the count value for the Parse step is near the count value for the Execute step for a statement, then 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.
Another way to identify unnecessary parse calls is to check the V$SQLAREA view. Enter the following query:
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA;
When the PARSE_CALLS value is close to the EXECUTION value for a given statement, you may be continually reparsing that statement.
You can also identify unnecessary parse calls by identifying the session in which they occur. It may be that particular batch programs or certain types of applications do most of the reparsing. To do this, execute the following query:
SELECT * FROM V$STATNAME WHERE NAME IN ('parsecount (hard)','executecount');
Oracle responds with something similar to:
STATISTIC#, NAME ------------ --------- 100 parsecount 90 executecount
Then, run a query similar to the following:
SELECT * FROM V$SESSTAT WHERE STATISTICS# IN (90,100) ORDER BY VALUE, SID;
The result is 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.
Depending on the Oracle application tool you are using, you may be able to control how frequently your application performs parse calls and allocates and deallocates private SQL areas. Whether your application reuses private SQL areas for multiple SQL statements determines how many parse calls your application performs and how many private SQL areas the application requires.
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 area is reused for a new SQL statement.
Be sure that your application can open enough private SQL areas to accommodate all your SQL statements. If you allocate more private SQL areas, then 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 default value for OPEN_CURSORS is 50, and the range is from 1 to UB4MAXVAL.
The ways in which you control parse calls and allocation and deallocation of private SQL areas depends on your Oracle application tool. The following sections introduce the methods used for some tools. These methods apply only to private SQL areas and not to shared SQL areas.
When using the Oracle precompilers, you can control private SQL areas and parse calls by setting three clauses. In Oracle mode, the clauses and their defaults are as follows:
Oracle recommends that you not use ANSI mode, in which the values of HOLD_CURSOR and RELEASE_CURSOR are switched.
The precompiler clauses can be specified in two ways:
With these clauses, you can employ different strategies for managing private SQL areas during the course of the program.
With Oracle Forms, you also have some control over whether your application reuses private SQL areas. You can exercise this control in three places:
The shared pool contains the library cache of shared SQL requests, the dictionary cache, stored procedures, and other cache structures that are specific to a particular instance configuration. For example, in a multi-threaded server (MTS) configuration, the session and private SQL area for each client process is included in the shared pool. When the instance is configured for parallel execution, the shared pool includes the parallel execution message buffers.
Proper sizing of the shared pool can reduce resource consumption in at least three ways:
The algorithm Oracle uses to manage data in the shared pool tends to hold dictionary cache data in memory longer than library cache data. Therefore, 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 necessary only if you are using MTS architecture.
In the shared pool, some of the caches are dynamic--their sizes automatically increase or decrease as needed. These dynamic caches include the library cache and the data dictionary cache. Objects are aged out of these caches if the shared pool runs out of room. For this reason you may need to increase the shared pool size if the frequently used set of data 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, allocate sufficient memory to the shared pool before allocating to the buffer cache.
For most applications, shared pool size is critical to Oracle performance. (Shared pool size is less important only for applications that issue a very limited number of discrete SQL statements.) 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 programs.
If the shared pool is too small, then the server must dedicate resources to managing the limited amount of available space. This consumes CPU resources and causes contention, because Oracle imposes restrictions 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 hundreds of megabytes.
Because it is better to measure statistics during a confined period than from startup, you can determine the library cache and row cache (data dictionary cache) hit ratios from the following queries. The results show the miss rates for the library cache and row cache. In general, the number of reparses reflects the library cache. If the ratios are close to 1, then you do not need to increase the pool size.
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 amount of free memory in the shared pool is reported in V$SGASTAT. Report the current value from this view using the following 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 offers little or no benefit. However, just because the shared pool is full does not necessarily mean there is a problem.
After an entry has been loaded into the shared pool, it cannot be moved. As more entries are loaded, the free memory becomes discontiguous, and the shared pool may become fragmented.
You can use the PL/SQL package DBMS_SHARED_POOL, located in dbmspool.sql, to manage the shared pool. The comments in the code describe how to use the procedures within the package.
Oracle loads objects into the shared pool using pages that are 4KB in size. These pages load chunks of segmented PL/SQL code. The pages do not need to be contiguous. Therefore, Oracle does not need to allocate large sections of contiguous memory for loading objects into the shared pool. This reduces the need for contiguous memory and improves performance. However, Oracle loads all of a package if any part of the package is called.
Depending on user needs, it may or may not be prudent to pin packages in the shared pool. Nonetheless, Oracle recommends pinning, especially for frequently used application objects.
|
See Also:
For information on how to pin packages with the |
Library cache and row cache hit ratios are important. If free memory is near zero, and if either the library cache hit ratio or the row cache hit ratio is less than 0.95, then increase the size of the shared pool until the ratios stop improving.
The following sections explains how to allocate memory for key memory structures of the shared pool. Structures are listed in order of importance for tuning.
The library cache holds executable forms of SQL cursors, PL/SQL programs, and JAVA classes. It also caches descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs. The latter type of memory is seldom a concern for performance, so this section focuses on tuning as it relates to cursors, PL/SQL programs, and JAVA classes. These are collectively referred to as application logic.
Library cache misses can occur on either the parse or the execute step in the processing of a SQL statement.
If an application makes a parse call for a SQL statement, and if the parsed representation of the statement does not already exist in a shared SQL area in the library cache, then 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.
If an application makes an execute call for a SQL statement, and if the shared SQL area containing the parsed representation of the statement has been deallocated from the library cache to make room for another statement, then 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.
You can monitor statistics reflecting library cache activity by examining the dynamic performance view V$LIBRARYCACHE. These statistics reflect all library cache activity since the most recent instance startup. By default, this view is available only to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.
Each row in this view 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 the following 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 |
Shows the number of times an item in the library cache was executed. |
RELOADS |
Shows the number of library cache misses on execution steps. |
Monitor the statistics in the V$LIBRARYCACHE table over a period of time with the following query:
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
The output of this query could look like the following:
EXECUTIONS CACHE MISSES WHILE EXECUTING ---------- ---------------------------- 320871 549
Examining the data returned by the sample query leads to these observations:
EXECUTIONS column indicates that SQL statements, PL/SQL blocks, and object definitions were accessed for execution a total of 320,871 times.
CACHE MISSES WHILE EXECUTING 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 aged out of the library cache.
Total misses should be near 0. If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses through the means discussed in the next section.
You can reduce library cache misses by:
To ensure that shared SQL areas remain in the cache after 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 reduces 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 do this 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.
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. Two separate occurrences of a SQL statement or PL/SQL block can use a shared SQL area if they follow these criteria:
The following statements cannot use the same shared SQL area:
SELECT * FROM emp; SELECT * FROM emp;
These statements also cannot use the same shared SQL area:
SELECT * FROM emp; SELECT * FROM Emp;
INSERT INTO T VALUES(1, 'foo', 4) INSERT INTO T VALUES(2, 'bar', 7)
|
See Also:
Such statements can use the same shared SQL area only when |
For example, if the schemas of the users Bob and Ed both contain an emp table, and if both users issue the following statement, then their statements cannot use the same shared SQL area:
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;
SELECT * FROM emp WHERE deptno = :department_no; SELECT * FROM emp WHERE deptno = :d_no;
For information on optimization approach and goal, see Chapter 9, "Optimizing SQL Statements".
See Also:
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:
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.
CURSOR_SHARING parameter may solve some performance problems. It has the following values: FORCE and EXACT (default).
Setting CURSOR_SHARING to FORCE forces similar statements to share SQL by replacing literals with system generated bind variables. Replacing literals with bind variables improves cursor sharing with reduced memory usage, faster parses, and reduced latch contention.
The V$SQL_BIND_METADATA and V$SQL_BIND_DATA views show the transformed text. These tables show bind metadata and bind data for all bind variables, including system generated bind variables. System generated bind variables can be distinguished from user bind variables based on the value of SHARED_FLAG2 in V$SQL_BIND_DATA.
For example, the following statement shows bind data only for system generated bind variables.
SELECT * FROM V$SQL_BIND_DATA
WHERE BITAND(SHARED_FLAG2, 256) = 256;
This parameter should be set to FORCE only when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.
You should consider setting CURSOR_SHARING to FORCE if you can answer 'yes' to both of the following questions:
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly.
If you have no library cache misses, then you may still be able to accelerate execution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME. This parameter specifies whether a shared SQL area can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:
false (the default), then a shared SQL area can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle must verify that a shared SQL area containing the SQL statement is in the library cache.
true, then a shared SQL area can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a shared SQL area is in the cache, 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 you have found 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 if the shared pool has no space for a new SQL statement, then the statement cannot be parsed, and Oracle returns an error saying that there is no more shared memory. If the value is false, and if there is no space for a new statement, then 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, then the statement cannot be parsed, and Oracle returns an error indicating that there is not enough memory.
If an application repeatedly issues parse calls on the same set of SQL statements, then 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 from one form to another closes all session cursors associated with the first form.
Oracle uses the shared SQL area to determine whether 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 then find the cursor in the session cursor cache.
To enable caching of session cursors, you must set the initialization parameter SESSION_CACHED_CURSORS. The value of this parameter is a positive integer specifying the maximum number of session cursors kept in the cache. An LRU (Least Recently Used) algorithm removes 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 statement:
ALTERSESSIONSETSESSION_CACHED_CURSORS.
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, then you should consider setting SESSION_CACHED_CURSORS to a larger value.
This section describes how to tune the data dictionary cache with the following:
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.
Statistics reflecting data dictionary activity are kept in the dynamic performance table V$ROWCACHE. By default, this table is available only 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:
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 could look like this:
DATA DICTIONARY GETS DATA DICTIONARY CACHE GET MISSES -------------------- -------------------------------- 1439044 3120
Examining the data returned by the sample query leads to these observations:
GETS column indicates that there was a total of 1,439,044 requests for dictionary data.
GETMISSES column indicates that 3120 of the requests for dictionary data resulted in cache misses.
GETMISSES to GETS is about 0.2%.
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 the ratio continues to increase above this threshold while your application is running, then 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 depends on your operating system.
Oracle recommends using the large pool to allocate MTS-related UGA (User Global Area), not the shared pool. This is because Oracle uses the shared pool to allocate SGA (Shared Global Area) memory for other purposes, such as shared SQL and PL/SQL procedures. Using the large pool, instead of the shared pool, will decrease fragmentation of the shared pool.
To store MTS-related UGA in the large pool, specify a value for the parameter LARGE_POOL_SIZE. LARGE_POOL_SIZE does not have a default value, but its minimal value is 300K. If you do not set a value for LARGE_POOL_SIZE, then Oracle uses the shared pool for MTS user session memory. Oracle has a default value for SHARED_POOL_SIZE of 8MB on 32-bit systems and 64MB on 64 bit systems.
Configure the size of the large pool based on the number of simultaneously active sessions. Each application requires a different amount of memory for session information, and your configuration of the large pool or SGA should reflect the memory requirement. For example, in some applications, MTS requires 200K - 300K to store session information for each active session. If you anticipate 100 active sessions simultaneously, then you should configure the large pool to be 30M, or increase the shared pool accordingly if the large pool is not configured.
|
Note:
If MTS is used, then Oracle allocates some fixed amount of memory (about 10K) per configured session from the shared pool, even if you have configured the large pool. The |
The exact amount of UGA Oracle uses depends on each application. To determine an effective setting for the large or shared pools, observe UGA use for a typical user, and multiply this amount by the estimated number of user sessions.
Even though use of shared memory increases with MTS, the total amount of memory use decreases. This is because there are fewer processes, and therefore, Oracle uses less PGA memory with MTS when compared to dedicated server environments.
You can set the PRIVATE_SGA parameter to limit the memory used by each client session from the SGA. PRIVATE_SGA defines the number of bytes of memory used from the SGA by a session. However, this parameter is rarely used because most DBAs do not limit SGA consumption an a user-by-user basis.
If you have a high number of connected users, then you can reduce memory use to an acceptable level by implementing "three-tier connections". This by-product of using a TP monitor is feasible only with pure transactional models, because locks and uncommitted DMLs cannot be held between calls. MTS is much less restrictive of the application design than a TP monitor. It dramatically reduces operating system process count and context switches by enabling users to share a pool of servers. MTS also substantially reduces overall memory usage even though more SGA is used in MTS mode.
Oracle collects statistics on total memory used by a session and stores them in the dynamic performance view V$SESSTAT. By default, this view is available only 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:
To find the value, query V$STATNAME as described in "Technique 3".
You can use the following query to decide how much larger to make the shared pool if you are using a 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 UGA 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 = 'SESSION UGA MEMORY MAX' 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 could look like this:
TOTAL MEMORY FOR ALL SESSIONS ----------------------------- 157125 BYTES TOTAL MAX MEM FOR ALL SESSIONS ------------------------------ 417381 BYTES
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 server processes, then this memory is part of the memories of the user processes. If the sessions are connected to shared server processes, then this memory is part of the shared pool.
The result of the second query indicates that 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 a 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.
On busy systems, the database may have difficulty finding a contiguous piece of memory to satisfy a large request for memory. This search may disrupt the behavior of the shared pool, leading to fragmentation and poor performance.
You can reserve memory within the shared pool to satisfy large allocations during operations such as PL/SQL compilation and trigger compilation. Smaller objects do not fragment the reserved list, helping to ensure that the reserved list has large contiguous chunks of memory. After the memory allocated from the reserved list is freed, it returns to the reserved list.
The ABORTED_REQUEST_THRESHOLD procedure in the DBMS_SHARED_POOL package lets you limit the size of allocations allowed to flush the shared pool if the free lists cannot satisfy the request size. The database incrementally flushes unused objects from the shared pool until there is sufficient memory to satisfy the allocation request. In most cases, this frees enough memory for the allocation to complete successfully.
If the database flushes all objects currently not in use on the system without finding a large enough piece of contiguous memory, then an error occurs. Flushing all objects, however, affects other users on the system, as well as system performance. The ABORTED_REQUEST_THRESHOLD procedure lets you localize the error to the process that could not allocate memory.
The size of the reserved list, and the minimum size of the objects that can be allocated from the reserved list, can be controlled by the initialization parameter SHARED_POOL_RESERVED_SIZE. Begin this tuning only after performing all other shared pool tuning.
The default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list is always configured.
If SHARED_POOL_RESERVED_SIZE > 1/2 SHARED_POOL_SIZE, then Oracle signals an error. Ideally, this parameter should be large enough to satisfy any request scanning 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 shared pool. In general, set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value is sufficient if you have already tuned the shared pool. If you increase this value, then the database allows fewer allocations from the reserved list and requests more memory from the shared pool list.
Statistics from the V$SHARED_POOL_RESERVED view help you tune these parameters. On a system with ample free memory to increase the size of the SGA, the goal is to have REQUEST_MISSES = 0. If the system is constrained for operating system memory, then the goal is to not have REQUEST_FAILURES or at least prevent this value from increasing.
If you cannot achieve this, then increase the value for SHARED_POOL_RESERVED_SIZE. Also, increase the value for SHARED_POOL_SIZE by the same amount, because the reserved list is taken from the shared pool.
The reserved pool is too small when the value for REQUEST_FAILURES is more than zero and increasing. To resolve this, increase the value for the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE accordingly. The settings you select for these depend on your system's SGA size constraints.
This option increases the amount of memory available on the reserved list without having an effect on users who do not allocate memory from the reserved list. As a second option, reduce the number of allocations allowed to use memory from the reserved list; however, doing so increases the normal shared pool, which may have an effect on other users on the system.
Too much memory may have been allocated to the reserved list if:
If either of these is true, then decrease the value for SHARED_POOL_RESERVED_SIZE.
The V$SHARED_POOL_RESERVED fixed table can also indicate when the value for SHARED_POOL_SIZE is too small. This may be the case if REQUEST_FAILURES > 0 and increasing.
If you have enabled the reserved list, then decrease the value for SHARED_POOL_RESERVED_SIZE. If you have not enabled the reserved list, then you could increase SHARED_POOL_SIZE.
You can use or bypass the Oracle buffer cache for particular operations. Oracle bypasses the buffer cache for sorting and parallel reads. For operations that use the buffer cache, this section explains:
After tuning private SQL and PL/SQL areas and the shared pool, you can devote the remaining available memory to the buffer cache. It may be necessary to repeat the steps of memory allocation after the initial pass through the process. Subsequent passes allow you to make adjustments in earlier steps based on changes in later steps. For example, if you increase the size of the buffer cache, then you may need to allocate more memory to Oracle to avoid paging and swapping.
Physical I/O takes a significant amount of time, typically in excess of 15 milliseconds. Physical I/O also increases the CPU resources required, owing to the path length in device drivers and operating system event schedulers. Your goal is to reduce this overhead as much as possible by making it more likely that the required block is in memory. The extent to which you achieve this is measured using the cache hit ratio. Within Oracle, this term applies specifically to the database buffer cache.
Oracle collects statistics that reflect data access and stores them in the dynamic performance view V$SYSSTAT. By default, this table is available only to the user SYS and to users, such as SYSTEM, who have the SELECT ANY TABLE system privilege. Information in the V$SYSSTAT view can also be obtained through the Simple Network Management Protocol (SNMP).
These statistics are useful for tuning the buffer cache:
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 could look like the following:
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%.
These statistics are useful in evaluating buffer pinning:
These statistics are not incremented when a client performs such a check before releasing it, because the client does not intend to use the buffer in this case.
These statistics provide a measure of how often a long consistent read pin on a buffer is beneficial. If the client is able to reuse the pinned buffer many times, then it indicates that it is useful to have the buffer pinned.
When looking at the cache hit ratio, remember that blocks encountered during a "long" full table scan are not put at the head of the LRU list; therefore, repeated scanning does not cause the blocks to be cached.
Repeated scanning of the same large table is rarely the most efficient approach. 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 that contains no PL/SQL. The solution lies at the design or implementation level.
Production sites running with thousands, or tens of thousands, of buffers rarely use memory effectively. In any large database running OLTP applications, in any given unit of time, most rows are accessed either one or zero times. On this basis, there is little point in keeping the row, or the block that contains it, in memory for very long following its use.
Finally, the relationship between the cache hit ratio and the number of buffers is far from a smooth distribution. When tuning the buffer pool, avoid the use of additional buffers that contribute little or nothing to the cache hit ratio. As illustrated in Figure 19-2, only narrow bands of values of DB_BLOCK_BUFFERS are worth considering.
As a general rule, increase DB_BLOCK_BUFFERS when:
DB_BLOCK_BUFFERS was effective.
The CATPARR.SQL script creates the view V$BH, which shows the file number and block number of blocks that currently reside within the SGA. Although CATPARR.SQL is primarily intended for use in parallel server environments, you can run it as SYS even if you're operating a single instance environment.
Perform a query similar to the following:
SELECT file#, COUNT(block#), COUNT (DISTINCT file# || block#) FROM V$BH GROUP BY file#;
If your hit ratio is low, or less than 60% or 70%, then you may want to increase the number of buffers in the cache to improve performance. To make the buffer cache larger, increase the value of the initialization parameter DB_BLOCK_BUFFERS.
If your hit ratio is high, then your cache is probably large enough to hold your most frequently accessed data. In this case, you may be able to reduce the cache size and still maintain good performance. To make the buffer cache smaller, reduce the value of the initialization parameter DB_BLOCK_BUFFERS. The minimum value for this parameter is 50, but it is unusual for systems to run with less than 1,000. You can use any leftover memory for other Oracle memory structures, for example, if you're using parallel query.
Both temporary and permanent LOBs can use the buffer cache.
Temporary LOBs created with the CACHE parameter set to TRUE move through the buffer cache. Temporary LOBs created with the CACHE parameter set to FALSE are read directly from, and written directly to, disk.
You can use durations for automatic cleanup to save time and effort. Also, it is more efficient for the database to end a duration and free all temporary LOBs associated with a duration than it is to free each one explicitly.
Temporary LOBs create entirely new copies of themselves on assignments. For example:
LOCATOR1 BLOB; LOCATOR2 BLOB; DBMS_LOB.CREATETEMPORARY (LOCATOR1,TRUE,DBMS_LOB.SESSION); LOCATOR2 := LOCATOR;
The above code causes a copy of the temporary LOB pointed to by LOCATOR1 to be created. You may also want to consider using pass by reference semantics in PL/SQL.
Or, in OCI, you may declare pointers to locators as in the following example:
OCILOBDESCRIPTOR *LOC1; OCILOBDESCRIPTOR *LOC2; OCILOBCREATETEMPORARY (LOC1,TRUE,OCIDURATIONSESSION); LOC2 = LOC1;
Avoid using OCILobAssign() statements, because these also cause deep copies of temporary LOBs. In other words, a new copy of the temporary LOB is created.
Pointer assignment does not cause deep copies; it just causes pointers to point to the same thing.
This section covers:
Schema objects are referenced with varying usage patterns; therefore, their cache behavior may be quite different. Multiple buffer pools enable you to address these differences. You can use a KEEP buffer pool to maintain objects in the buffer cache and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is allocated to a cache, all blocks from that object are placed in that cache. Oracle maintains a DEFAULT buffer pool for objects that have not been assigned to one of the buffer pools.
Each buffer pool in Oracle comprises a number of working sets. A different number of sets can be allocated for each buffer pool. All sets use the same LRU (Least Recently Used) replacement policy. A strict LRU aging policy provides good hit rates in most cases, but you can sometimes improve hit rates by providing some hints.
The main problem with the LRU list occurs when a very large segment is accessed frequently in a random fashion. Here, very large means large compared to the size of the cache. Any single segment that accounts for a substantial portion (more than 10%) of nonsequential physical reads is probably one of these segments. Random reads to such a large segment can cause buffers that contain data for other segments to be aged out of the cache. The large segment ends up consuming a large percentage of the cache, but does not benefit from the cache.
Very frequently accessed segments are not affected by large segment reads, because their buffers are warmed frequently enough that they do not age out of the cache. The main trouble occurs with "warm" segments that are not accessed frequently enough to survive the buffer flushing caused by the large segment reads.
You have two options for solving this problem. One option is to move the large segment into a separate RECYCLE cache, so that it does not disturb the other segments. The RECYCLE cache should be smaller than the DEFAULT buffer pool, and it should reuse buffers more quickly than the DEFAULT buffer pool.
The other option is to move the small warm segments into a separate KEEP cache that is not used at all for large segments. The KEEP cache can be sized to minimize misses in the cache. You can make the response times for specific queries more predictable by putting the segments accessed by the queries in the KEEP cache to ensure that they are never aged out.
When you examine system I/O performance, you should analyze the schema and determine whether multiple buffer pools would be advantageous. Consider a KEEP cache if there are small, frequently accessed tables that require quick response time. Very large tables with random I/O are good candidates for a RECYCLE cache.
Use the following steps to determine the percentage of the cache used by an individual object at a given point in time:
SELECT DATA_OBJECT_ID, OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME = '<SEGMENT_NAME>';
Because two objects can have the same name (if they are different types of objects), you can use the OBJECT_TYPE column to identify the object of interest. If the object is owned by another user, then use the view DBA_OBJECTS or ALL_OBJECTS instead of USER_OBJECTS.
SEGMENT_NAME:
SELECT COUNT(*) BUFFERS FROM V$BH WHERE OBJD = <DATA_OBJECT_ID>;
where DATA_OBJECT_ID is from Step 1.
SELECT VALUE "TOTAL BUFFERS" FROM V$PARAMETER WHERE NAME = 'DB_BLOCK_BUFFERS';
SEGMENT_NAME.
If the number of local block gets equals the number of physical reads for statements involving such objects, then consider using a RECYCLE cache, because of the limited usefulness of the buffer cache for the objects.
When you partition your buffer cache into multiple buffer pools, each buffer pool can be used for blocks from objects that are accessed in different ways. If the blocks of a particular object are likely to be reused, then you should pin that object in the buffer cache so that the next use of the block does not require disk I/O. Conversely, if a block probably will not be reused within a reasonable period of time, then discard it to make room for more frequently used blocks.
By properly allocating objects to appropriate buffer pools, you can:
You can create multiple buffer pools for each database instance. The same set of buffer pools need not be defined for each instance of the database. Among instances, the buffer pools may be different sizes or not defined at all. Tune each instance separately.
You can define each buffer pool using the BUFFER_POOL_name initialization parameter. You can specify two attributes for each buffer pool: the number of buffers in the buffer pool, and the number of LRU latches allocated to the buffer pool.
The initialization parameters used to define buffer pools are:
For example:
BUFFER_POOL_KEEP = #buffers |(buffers:#buffers, lru_latches:#latches) |(lru_latches:#latches, buffers:#buffers) |(buffers:#buffers)
The size of each buffer pool is subtracted from the total number of buffers defined for the entire buffer cache (that is, the value of the DB_BLOCK_BUFFERS parameter). The aggregate number of buffers in all buffer pools cannot, therefore, exceed this value. Likewise, the number of LRU latches allocated to each buffer pool is taken from the total number allocated to the instance by the DB_BLOCK_LRU_LATCHES parameter. If either constraint is violated, then Oracle displays an error, and the database is not mounted.
The minimum number of buffers you must allocate to each buffer pool is 50 times the number of LRU latches. For example, a buffer pool with 3 LRU latches must have at least 150 buffers.
Oracle automatically defines three buffer pools: KEEP, RECYCLE, and DEFAULT. The DEFAULT buffer pool always exists. You do not explicitly define the size of the DEFAULT buffer pool or the number of working sets assigned to the DEFAULT buffer pool. Rather, each value is inferred from the total number allocated minus the number allocated to every other buffer pool. There is no requirement that any one buffer pool be defined for another buffer pool to be used.
This section describes how to establish a DEFAULT buffer pool for an object. All blocks for the object go in the specified buffer pool.
The BUFFER_POOL clause is used to define the DEFAULT buffer pool for an object. This clause is valid for CREATE and ALTER table, cluster, and index DDL statements. The buffer pool name is case insensitive. The blocks from an object without an explicitly set buffer pool go into the DEFAULT buffer pool.
If a buffer pool is defined for a partitioned table or index, then each partition of the object inherits the buffer pool from the table or index definition unless you override it with a specific buffer pool.
When the DEFAULT buffer pool of an object is changed using the ALTER statement, all buffers currently containing blocks of the altered segment remain in the buffer pool they were in before the ALTER statement. Newly loaded blocks and any blocks that have aged out and are reloaded go into the new buffer pool.
The syntax of the BUFFER_POOL clause is: BUFFER_POOL {KEEP | RECYCLE | DEFAULT}
For example:
BUFFER_POOLKEEP
or
BUFFER_POOLRECYCLE
The following DDL statements accept the buffer pool clause:
CREATE TABLE table name... STORAGE (buffer_pool_clause)
A buffer pool is not permitted for a clustered table. The buffer pool for a clustered table is specified at the cluster level.
For an index-organized table, a buffer pool can be defined on both the index and the overflow segment.
For a partitioned table, a buffer pool can be defined on each partition. The buffer pool is specified as a part of the storage clause for each partition.
For example:
CREATE TABLEtable_name(col_1NUMBER,col_2NUMBER)PARTITION BY RANGE(col_1)(PARTITION ONE VALUES LESS THAN(10)STORAGE(INITIAL10KBUFFER_POOL RECYCLE),PARTITION TWO VALUES LESS THAN(20)STORAGE(BUFFER_POOL KEEP));
CREATE INDEX index name... STORAGE (buffer_pool_clause)
For a global or local partitioned index, a buffer pool can be defined on each partition.
CREATE CLUSTER cluster_name...STORAGE (buffer_pool_clause)
ALTER TABLE table_name... STORAGE (buffer_pool_clause)
A buffer pool can be defined during simple ALTER TABLE, MODIFY PARTITION, MOVE PARTITION, ADD PARTITION, and SPLIT PARTITION statements for both new partitions.
ALTER INDEX index_name... STORAGE (buffer_pool_clause)
A buffer pool can be defined during simple ALTER INDEX, REBUILD, MODIFY PARTITION, SPLIT PARTITION statements for both new partitions, and rebuild partitions.
ALTER CLUSTER cluster_name... STORAGE (buffer_pool_clause)
The following dictionary views have a BUFFER POOL column indicating the DEFAULT buffer pool for the given object.
The views V$BUFFER_POOL_STATISTICS and GV$BUFFER_POOL_STATISTICS describe the buffer pools allocated on the local instance and entire database, respectively. To create these views you must run the CATPERF.SQL file.
This section explains how to size the following:
The goal of the KEEP buffer pool is to retain objects in memory, thus avoiding I/O operations. The size of the KEEP buffer pool, therefore, depends on the objects that you want to keep in the buffer cache. You can compute an approximate size for the KEEP buffer pool by adding together the sizes of all objects dedicated to this pool. Use the ANALYZE statement to obtain the size of each object. Although the ESTIMATE clause provides a rough measurement of sizes, the COMPUTE STATISTICS clause is preferable because it provides the most accurate value possible.
The buffer pool hit ratio can be determined using the formula:
Where the values of physical reads, block gets, and consistent gets can be obtained for the KEEP buffer pool from the following query:
SELECT PHYSICAL_READS, BLOCK_GETS, CONSISTENT_GETSFROM V$BUFFER_POOL_STATISTICS WHERE NAME = 'KEEP';
The KEEP buffer pool has a 100% hit ratio only after the buffers have been loaded into the buffer pool. Therefore, do not compute the hit ratio until after the system runs for a while and achieves steady-state performance. Calculate the hit ratio by taking two snapshots of system performance at different times using the above query. Subtract the newest values from the older values for physical reads, block gets, and consistent gets, and use these values to compute the hit ratio.
A 100% buffer pool hit ratio may not be optimal. Often, you can decrease the size of your KEEP buffer pool and still maintain a sufficiently high hit ratio. Allocate blocks removed from use for the KEEP buffer pool to other buffer pools.
Each object kept in memory results in a trade-off: it is beneficial to keep frequently accessed blocks in the cache, but retaining infrequently used blocks results in less space for other, more active blocks.
The goal of the RECYCLE buffer pool is to eliminate blocks from memory as soon as they are no longer needed. If an application accesses the blocks of a very large object in a random fashion, then there is little chance of reusing a block stored in the buffer pool before it is aged out. This is true regardless of the size of the buffer pool (given the constraint of the amount of available physical memory). Because of this, the object's blocks should not be cached; those cache buffers can be allocated to other objects.
Be careful, however, not to discard blocks from memory too quickly. If the buffer pool is too small, then blocks may age out of the cache before the transaction or SQL statement has completed execution. For example, an application may select a value from a table, use the value to process some data, and then update the record. If the block is removed from the cache after the select statement, then it must be read from disk again to perform the update. The block should be retained for the duration of the user transaction.
By executing statements with a SQL statement tuning tool, such as Oracle Trace, or with the SQL trace facility enabled and running TKPROF on the trace files, you can get a listing of the total number of data blocks physically read from disk. (This number appears in the "disk" column in the TKPROF output.) The number of disk reads for a particular SQL statement should not exceed the number of disk reads of the same SQL statement with all objects allocated from the DEFAULT buffer pool.
Two other statistics can tell you whether the RECYCLE buffer pool is too small. If the "free buffer waits" statistic ever becomes excessive, then the pool is probably too small. Likewise, the number of "log file sync" wait events will increase. One way to size the RECYCLE buffer pool is to run the system with the RECYCLE buffer pool disabled. At steady state, the number of buffers in the DEFAULT buffer pool being consumed by segments that would normally go in the RECYCLE buffer pool can be divided by four. Use the result as a value for sizing the RECYCLE cache.
A good candidate for a segment to put into the RECYCLE buffer pool is a segment that is at least twice the size of the DEFAULT buffer pool and has incurred at least a few percent of the total I/Os in the system.
A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system.
The trouble with these rules is that it can sometimes be difficult to determine the number of I/Os per segment if a tablespace has more than one segment. One way to solve this problem is to sample the I/Os that occur over a period of time by selecting from V$SESSION_WAIT to determine a statistical distribution of I/Os per segment.
LRU latches regulate the least recently used buffer lists used by the buffer cache. If there is latch contention, then processes are waiting and spinning before obtaining the latch.
You can set the overall number of latches in the database instance using the DB_BLOCK_LRU_LATCHES parameter. When each buffer pool is defined, a number of these LRU latches can be reserved for the buffer pool. The buffers of a buffer pool are divided evenly between the LRU latches of the buffer pool.
To determine whether your system is experiencing latch contention, begin by determining whether there is LRU latch contention for any individual latch.
SELECTCHILD#,SLEEPS/GETSRATIOFROMV$LATCH_CHILDRENWHERENAME='cachebufferslruchain';
The miss ratio for each LRU latch should be less than 3%. A ratio above 3% for any particular latch is indicative of LRU latch contention and should be addressed. You can determine the buffer pool to which the latch is associated as follows:
SELECTNAMEFROMV$BUFFER_POOLWHERElo_setid<=child_latch_numberANDhi_setid>=child_latch_number;
Where child_latch_number is the child# from the previous query.
You can alleviate LRU latch contention by increasing the overall number of latches in the system and the number of latches allocated to the buffer pool indicated in the second query.
The maximum number of latches allowed is the lower of:
number_of_cpus * 2 * 3 or number_of_buffers / 50
This limitation exists because no set can have fewer than 50 buffers. If you specify a value larger than the maximum, then Oracle automatically resets the number of latches to the largest value allowed by the formula.
For example, if the number of CPUs is 4 and the number of buffers is 200, then a maximum of 4 latches would be allowed (minimum of 4*2*3, 200/50). If the number of CPUs is 4 and the number of buffers is 10000, then the maximum number of latches allowed is 24 (minimum of 4*2*3, 10000/50).
If large sorts occur frequently, then consider increasing the value of the parameter SORT_AREA_SIZE with either or both of two goals in mind:
Large sort areas can be used effectively if you combine a large SORT_AREA_SIZE with a minimal SORT_AREA_RETAINED_SIZE. If memory is not released until the user disconnects from the database, then large sort work areas could cause problems. The SORT_AREA_RETAINED_SIZE parameter lets you specify the level down to which memory should be released as soon as possible following the sort. Set this parameter to zero if large sort areas are being used in a system with many simultaneous users.
SORT_AREA_RETAINED_SIZE is maintained for each sort operation in a query. Thus, if 4 tables are being sorted for a sort merge, then Oracle maintains 4 areas of SORT_AREA_RETAINED_SIZE.
After resizing your Oracle memory structures, re-evaluate the performance of the library cache, the data dictionary cache, and the buffer cache. If you have reduced the memory consumption of any of these structures, then you may want to allocate more memory to another. For example, if you have reduced the size of your buffer cache, then you may want to use the additional memory by for the library cache.
Tune your operating system again. Resizing Oracle memory structures may have changed Oracle memory requirements. In particular, be sure paging and swapping are not excessive. For example, if the size of the data dictionary cache or the buffer cache has increased, then 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.
If the overriding performance problem is that the server simply does not have enough memory to run the application as currently configured, and the application is logically a single application (that is, it cannot readily be segmented or distributed across multiple servers), then only two possible solutions exist:
The most dramatic reductions in server memory usage always come from reducing the number of database connections, which in turn can resolve issues relating to the number of open network sockets and the number of operating system processes. However, to reduce the number of connections without reducing the number of users, the connections that remain must be shared. This forces the user processes to adhere to a paradigm in which every message request sent to the database describes a complete or atomic transaction.
Writing applications to conform to this model is not necessarily either restrictive or difficult, but it is 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 architecture is an effective solution for reducing the number of server operating system processes. MTS is also quite effective at reducing overall memory requirements. You can also use MTS to reduce the number of network connections when you use MTS with connection pooling and connection concentration.
Shared connections are possible in Oracle Forms environments when you use an intermediate server that is also a client. In this configuration, use the DBMS_PIPE package to transmit atomic requests from the user's individual connection on the intermediate server to a shared daemon in the intermediate server. The daemon, in turn, owns a connection to the central server.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|