| Oracle® Database Performance Tuning Guide 10g Release 1 (10.1) Part Number B10752-01 |
|
|
View PDF |
| Oracle® Database Performance Tuning Guide 10g Release 1 (10.1) Part Number B10752-01 |
|
|
View PDF |
This chapter explains how to allocate memory to Oracle memory caches, and how to use those caches. Proper sizing and effective use of the Oracle memory caches greatly improves database performance.
Oracle recommends automatic memory configuration for your system using the SGA_TARGET and PGA_AGGREGATE_TARGET initialization parameters. However, you can manually adjust the memory pools on your system and that process is provided in this chapter.
This chapter contains the following sections:
| See Also:
Oracle Database Concepts for information on the memory architecture of an Oracle database |
Oracle stores information in memory caches and on disk. Memory access is much faster than disk access. Disk access (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more efficient for data requests for frequently accessed objects to be satisfied solely by memory, rather than also requiring disk access.
A performance goal is to reduce the physical I/O overhead as much as possible, either by making it more likely that the required data is in memory or by making the process of retrieving the required data more efficient.
Oracle strongly recommends the use of automatic memory management. Before setting any memory pool sizes, review the following:
If you need to configure memory allocations, Oracle Enterprise Manager provides the Memory Advisor for updates. To access the Memory Advisor through Oracle Enterprise Manager Database Control:
The main Oracle memory caches that affect performance are:
Automatic Shared Memory Management simplifies the configuration of the SGA and is the recommended memory configuration. To use Automatic Shared Memory Management, set the SGA_TARGET initialization parameter to a nonzero value and set the STATISTICS_LEVEL initialization parameter to TYPICAL or ALL. The value of the SGA_TARGET parameter should be set to the amount of memory that you want to dedicate for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools:
If these automatically tuned memory pools had been set to nonzero values, those values are used as a minimum levels by Automatic Shared Memory Management. You would set minimum values if an application components needs a minimum amount of memory to function properly.
SGA_TARGET is a dynamic parameter and can be changed through Oracle Enterprise Manager or with the ALTER SYSTEM command. SGA_TARGET can be set less than or equal to the value of SGA_MAX_SIZE initialization parameter. Changes in the value of SGA_TARGET automatically resize the automatically tuned memory pools.
See Also:
|
If you set SGA_TARGET to 0, Automatic Shared Memory Management is disabled and you can manually size the memory pools with the DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE initialization parameters. See "Dynamically Changing Cache Sizes".
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
KEEP, RECYCLE, and other block sizesTo manually size these memory pools, you need to set the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, STREAMS_POOL_SIZE, and LOG_BUFFER initialization parameters. The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
See Also:
|
If the system is not using Automatic Shared Memory Management, you can choose to dynamically reconfigure the sizes of the shared pool, the large pool, the buffer cache, and the process-private memory. The following sections contain details on sizing of caches:
The size of these memory caches is configurable using initialization configuration parameters, such as DB_CACHE_ADVICE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, LOG_BUFFER, and SHARED_POOL_SIZE. The values for these parameters are also dynamically configurable using the ALTER SYSTEM statement except for the log buffer pool and process-private memory, which are static after startup.
Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance.
The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.
You can expand the total SGA size to a value equal to the SGA_MAX_SIZE parameter. If the SGA_MAX_SIZE is not set, you can decrease the size of one cache and reallocate that memory to another cache if necessary. SGA_MAX_SIZE defaults to the aggregate setting of all the components.
The maximum amount of memory usable by the instance is determined at instance startup by the initialization parameter SGA_MAX_SIZE. You can specify SGA_MAX_SIZE to be larger than the sum of all of the memory components, such as buffer cache and shared pool. Otherwise, SGA_MAX_SIZE defaults to the actual size used by those components. Setting SGA_MAX_SIZE larger than the sum of memory used by all of the components lets you dynamically increase a cache size without needing to decrease the size of another cache.
The following views provide information about dynamic SGA resize operations:
V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.V$SGA_RESIZE_OPS: Information about the last 400 completed SGA resize operations. This does not include any operations currently in progress.V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup.V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations.
See Also:
|
With memory configuration, it is important to size the cache appropriately for the application's needs. Conversely, tuning the application's use of the caches can greatly reduce resource requirements. Efficient use of the Oracle memory caches also reduces the load on related resources, such as the latches that protect the caches, the CPU, and the I/O system.
For best performance, you should consider the following:
Making changes or additions to an existing application might require resizing Oracle memory structures to meet the needs of your modified application.
If your application uses Java, you should investigate whether you need to modify the default configuration for the Java pool. See the Oracle Database Java Developer's Guide for information on Java memory usage.
For most operating systems, it is important to consider the following:
Paging occurs when an operating system transfers memory-resident pages to disk solely to allow new pages to be loaded into memory. Many operating systems page to accommodate large amounts of information that do not fit into real memory. On most operating systems, paging reduces performance.
Use the operating system utilities to examine the operating system, to identify whether there is a lot of paging on your system. If there is, then the total memory on the system might 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 SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.
|
Note: The |
To see how much memory is allocated to the SGA and each of its internal structures, enter the following SQL*Plus statement:
SHOW SGA
The output of this statement will look similar to the following:
Total System Global Area 840205000 bytes Fixed Size 279240 bytes Variable Size 520093696 bytes Database Buffers 318767104 bytes Redo Buffers 1064960 bytes
When sizing the SGA, ensure that you allow enough memory for the individual server processes and any other programs running on the system.
Configuring memory allocation involves distributing available memory to Oracle memory structures, depending on the needs of the application. The distribution of memory to Oracle structures can affect the amount of physical I/O necessary for Oracle to operate. Having a good first initial memory configuration also provides an indication of whether the I/O system is effectively configured.
It might be necessary to repeat the steps of memory allocation after the initial pass through the process. Subsequent passes let you make adjustments in earlier steps, based on changes in later steps. For example, decreasing the size of the buffer cache lets you increase the size of another memory structure, such as the shared pool.
For many types of operations, Oracle uses the buffer cache to store blocks read from disk. Oracle bypasses the buffer cache for particular operations, such as sorting and parallel reads. For operations that use the buffer cache, this section explains the following:
To use the buffer cache effectively, SQL statements for the application should be tuned to avoid unnecessary resource consumption. To ensure this, verify that frequently executed SQL statements and SQL statements that perform many buffer gets have been tuned.
| See Also:
Chapter 12, "SQL Tuning Overview" for information on how to do this |
When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.
A number of statistics can be used to examine buffer cache activity. These include the following:
This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.
Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.
There is some overhead associated with this advisory. When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.
Oracle uses DBA-based sampling to gather cache advisory statistics. Sampling substantially reduces both CPU and memory overhead associated with bookkeeping. Sampling is not used for a buffer pool if the number of buffers in that buffer pool is small to begin with.
To use V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should be set to ON, and a representative workload should be running on the instance. Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE view.
The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)' COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers' COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor' COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads' SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';
The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.
However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host machine and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.
Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads ---------------- ------------ ----------- ------------ 30 3,802 18.70 192,317,943 10% of Current Size 60 7,604 12.83 131,949,536 91 11,406 7.38 75,865,861 121 15,208 4.97 51,111,658 152 19,010 3.64 37,460,786 182 22,812 2.50 25,668,196 212 26,614 1.74 17,850,847 243 30,416 1.33 13,720,149 273 34,218 1.13 11,583,180 304 38,020 1.00 10,282,475 Current Size 334 41,822 .93 9,515,878 364 45,624 .87 8,909,026 395 49,426 .83 8,495,039 424 53,228 .79 8,116,496 456 57,030 .76 7,824,764 486 60,832 .74 7,563,180 517 64,634 .71 7,311,729 547 68,436 .69 7,104,280 577 72,238 .67 6,895,122 608 76,040 .66 6,739,731 200% of Current Size
This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size. The data also includes a physical read factor, which is a factor by which the current number of physical reads is estimated to change if the buffer cache is resized to a given value.
|
Note: With Oracle, physical reads do not necessarily indicate disk reads; physical reads may well be satisfied from the file system cache. |
The relationship between successfully finding a block in the cache and the size of the cache is not always a smooth distribution. When sizing the buffer pool, avoid the use of additional buffers that contribute little or nothing to the cache hit ratio. In the example illustrated in Figure 7-1, only narrow bands of increments to the cache size may be worthy of consideration.
Text description of the illustration pfgrf041.gif
Examining Figure 7-1 leads to the following observations:
The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.
The statistics in Table 7-1 are used to calculate the hit ratio.
Example 7-1 has been simplified by using values selected directly from the V$SYSSTAT table, rather than over an interval. It is best to calculate the delta of these statistics over an interval while your application is running, then use them to determine the hit ratio.
| See Also:
Chapter 6, "Automatic Performance Diagnostics" for more information on collecting statistics over an interval |
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
| See Also:
Oracle Database Reference for information on the |
There are many factors to examine before considering whether to increase or decrease the buffer cache size. For example, you should examine V$DB_CACHE_ADVICE data and the buffer cache hit ratio.
A low cache hit ratio does not imply that increasing the size of the cache would be beneficial for performance. A good cache hit ratio could wrongly indicate that the cache is adequately sized for the workload.
To interpret the buffer cache hit ratio, you should consider the following:
As a general rule, investigate increasing the size of the cache if the cache hit ratio is low and your application has been tuned to avoid performing full table scans.
To increase cache size, first set the DB_CACHE_ADVICE initialization parameter to ON, and let the cache statistics stabilize. Examine the advisory data in the V$DB_CACHE_ADVICE view to determine the next increment required to significantly decrease the amount of physical I/O performed. If it is possible to allocate the required extra memory to the buffer cache without causing the host operating system to page, then allocate this memory. To increase the amount of memory allocated to the buffer cache, increase the value of the DB_CACHE_SIZE initialization parameter.
If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.
The DB_CACHE_SIZE parameter specifies the size of the default cache for the database's standard block size. To create and use tablespaces with block sizes different than the database's standard block sizes (such as to support transportable tablespaces), you must configure a separate cache for each block size used. The DB_nK_CACHE_SIZE parameter can be used to configure the nonstandard block size needed (where n is 2, 4, 8, 16 or 32 and n is not the standard block size).
|
Note: The process of choosing a cache size is the same, regardless of whether the cache is the default standard block size cache, the |
| See Also:
Oracle Database Reference and Oracle Database Administrator's Guide for more information on using the |
If the cache hit ratio is high, then the cache is probably large enough to hold the most frequently accessed data. Check V$DB_CACHE_ADVICE data to see whether decreasing the cache size significantly causes the number of physical I/Os to increase. If not, and if you require memory for another memory structure, then you might be able to reduce the cache size and still maintain good performance. To make the buffer cache smaller, reduce the size of the cache by changing the value for the parameter DB_CACHE_SIZE.
A single default buffer pool is generally adequate for most systems. However, users with detailed knowledge of an application's buffer pool might benefit from configuring multiple buffer pools.
With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).
Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with 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 a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same LRU replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).
By allocating objects to appropriate buffer pools, you can:
A problem can occur with an LRU aging method when a very large segment is accessed with a large or unbounded index range scan. 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 can be considered very large. Random reads to 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 it 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. However, the problem affects warm segments that are not accessed frequently enough to survive the buffer aging caused by the large segment reads. There are three options for solving this problem:
RECYCLE cache so that it does not affect 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.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 do not age out.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 can be different sizes or not defined at all. Tune each instance according to the application requirements for that instance.
To define a default buffer pool for an object, use the BUFFER_POOL keyword of the STORAGE clause. This clause is valid for CREATE and ALTER TABLE, CLUSTER, and INDEX SQL statements. After a buffer pool has been specified, all subsequent blocks read for the object are placed in that 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 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.
| See Also:
Oracle Database SQL Reference for information on specifying |
V$DB_CACHE_ADVICE can be used to size all pools configured on an instance. Make the initial cache size estimate, run the representative workload, then simply query the V$DB_CACHE_ADVICE view for the pool you want to use.
For example, to query data from the KEEP pool:
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS FROM V$DB_CACHE_ADVICE WHERE NAME = 'KEEP' AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') AND ADVICE_STATUS = 'ON';
The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools within one set of statistics. To determine the hit ratio for the buffer pools individually, query the V$BUFFER_POOL_STATISTICS view. This view maintains statistics for each pool on the number of logical reads and writes.
The buffer pool hit ratio can be determined using the following formula:
1 - (physical_reads/(db_block_gets + consistent_gets))
The ratio can be calculated with the following query:
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS;
| See Also:
Oracle Database Reference for information on the |
The V$BH view shows the data object ID of all blocks that currently reside in the SGA. To determine which segments have many buffers in the pool, you can use one of the two methods described in this section. You can either look at the buffer cache usage pattern for all segments (Method 1) or examine the usage pattern of a specific segment, (Method 2).
The following query counts the number of blocks for all segments that reside in the buffer cache at that point in time. Depending on buffer cache size, this might require a lot of sort space.
COLUMN OBJECT_NAME FORMAT A40 COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999 SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*); OBJECT_NAME NUMBER_OF_BLOCKS ---------------------------------------- ---------------- OA_PREF_UNIQ_KEY 1 SYS_C002651 1 .. DS_PERSON 78 OM_EXT_HEADER 701 OM_SHELL 1,765 OM_HEADER 5,826 OM_INSTANCE 12,644
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 DBA_OBJECTS WHERE OBJECT_NAME = UPPER('segment_name');
Because two objects can have the same name (if they are different types of objects), use the OBJECT_TYPE column to identify the object of interest.
SEGMENT_NAME:
SELECT COUNT(*) BUFFERS FROM V$BH WHERE OBJD = data_object_id_value;
where data_object_id_value is from step 1.
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) > 0;
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently. Application developers and DBAs can determine which tables are candidates.
You can check the number of blocks from candidate tables by querying V$BH, as described in "Determining Which Segments Have Many Buffers in the Pool".
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 blocks used by all objects assigned to this pool. If you gather statistics on the segments, you can query DBA_TABLES.BLOCKS and DBA_TABLES.EMPTY_BLOCKS to determine the number of blocks used.
Calculate the hit ratio by taking two snapshots of system performance at different times, using the previous query. Subtract the more recent values for physical reads, block gets, and consistent gets from the older values, and use the results to compute the hit ratio.
A buffer pool hit ratio of 100% might 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 the KEEP buffer pool to other buffer pools.
|
Note: If an object grows in size, then it might no longer fit in the |
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.
It is possible to configure a RECYCLE buffer pool for blocks belonging to those segments that you do not want to remain in memory. The RECYCLE pool is good for segments that are scanned rarely or are not referenced frequently. 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). Consequently, the object's blocks need not be cached; those cache buffers can be allocated to other objects.
Memory is allocated to the RECYCLE buffer pool by setting the parameter DB_RECYCLE_CACHE_SIZE to the required size. This memory for the RECYCLE buffer pool is not a subset of the default pool.
Do not discard blocks from memory too quickly. If the buffer pool is too small, then blocks can age out of the cache before the transaction or SQL statement has completed execution. For example, an application might 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.
Oracle uses the shared pool to cache many different types of data. Cached data includes the textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data, and other data.
Proper use and sizing of the shared pool can reduce resource consumption in at least four ways:
This section covers the following:
The main components of the shared pool are the library cache and the dictionary cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out of these caches to accommodate new entries when the shared pool does not have free space.
A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.
A number of features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.
| See Also:
"Using the Large Pool" for more information on configuring the large pool |
Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough contiguous memory due to fragmentation.
Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool.
| See Also:
"Configuring the Reserved Pool" for more information on the reserved area of the shared pool |
Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.
The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.
When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss. See "SQL Sharing Criteria" for details on when a SQL and PL/SQL statements can be shared.
Library cache misses can occur on either the parse step or the execute step when processing a SQL statement. When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not already exist in the library cache, then Oracle parses the statement and stores the parsed form in the shared pool. This is a hard parse. You might be able to reduce library cache misses on parse calls by ensuring that all shareable SQL statements are in the shared pool whenever possible.
If an application makes an execute call for a SQL statement, and if the executable portion of the previously built SQL statement has been aged out (that is, deallocated) from the library cache to make room for another statement, then Oracle implicitly reparses the statement, creating a new shared SQL area for it, and executes it. This also results in a hard parse. Usually, you can reduce library cache misses on execution calls by allocating more memory to the library cache.
In order to perform a hard parse, Oracle uses more resources than during a soft parse. Resources used for a soft parse include CPU and library cache latch gets. Resources required for a hard parse include additional CPU, library cache latch gets, and shared pool latch gets. See "SQL Execution Efficiency" for a discussion of hard and soft parsing.
Oracle automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
Oracle performs the following steps for the comparison:
SELECT * FROM employees; SELECT * FROM Employees; SELECT * FROM employees;
Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following SQL statements do not resolve to the same SQL area:
SELECT count(1) FROM employees WHERE manager_id = 121; SELECT count(1) FROM employees WHERE manager_id = 247;
The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE. Similar statements can share SQL areas when the CURSOR_SHARING parameter is set to SIMILAR or FORCE. The costs and benefits involved in using CURSOR_SHARING are explained later in this section.
| See Also:
Oracle Database Reference for more information on the |
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 two users each issue the following SQL statement:
SELECT * FROM employees;
and they each have their own employees table, then this statement is not considered identical, because the statement references different tables for each user.
For example, the following statements cannot use the same shared SQL area, because the bind variable names differ:
SELECT * FROM employees WHERE department_id = :department_id; SELECT * FROM employees WHERE department_id = :dept_id;
Many Oracle products, such as Oracle Forms and the precompilers, convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements. This allows multiple executions of the same SQL or PL/SQL code to be performed without the resources required for a hard parse, which results in significant reductions in CPU, memory, and latch usage.
The shared pool is also able to support unshared SQL in data warehousing applications, which execute low-concurrency, high-resource SQL statements. In this situation, using unshared SQL with literal values is recommended. Using literal values rather than bind variables allows the optimizer to make good column selectivity estimates, thus providing an optimal data access plan.
In an OLTP system, there are a number of ways to ensure efficient use of the shared pool and related resources. Discuss the following items with application developers and agree on strategies to ensure that the shared pool is used effectively:
Efficient use of the shared pool in high-concurrency OLTP systems significantly reduces the probability of parse-related application scalability issues.
Reuse of shared SQL for multiple users running the same application, avoids hard parsing. Soft parses provide a significant reduction in the use of resources such as the shared pool and library cache latches. To share cursors, do the following:
SELECT employee_id FROM employees WHERE department_id = 10; SELECT employee_id FROM employees WHERE department_id = 20;
By replacing the literals with a bind variable, only one SQL statement would result, which could be executed twice:
SELECT employee_id FROM employees WHERE department_id = :dept_id;
|
Note: For existing applications where rewriting the code to use bind variables is impractical, it is possible to use the |
V$SQL_SHARED_CURSOR to determine why the cursors are not shared. This would include optimizer settings and bind variable mismatches.Large OLTP systems where users log in to the database as their own user ID can benefit from explicitly qualifying the segment owner, rather than using public synonyms. This significantly reduces the number of entries in the dictionary cache. For example:
SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
An alternative to qualifying table names is to connect to the database through a single user ID, rather than individual user IDs. User-level validation can take place locally on the middle tier. Reducing the number of distinct userIDs also reduces the load on the dictionary cache.
Using stored PL/SQL packages can overcome many of the scalability issues for systems with thousands of users, each with individual user sign-on and public synonyms. This is because a package is executed as the owner, rather than the caller, which reduces the dictionary cache load considerably.
|
Note: Oracle Corporation encourages the use of definer-rights packages to overcome scalability issues. The benefits of reduced dictionary cache load are not as obvious with invoker-rights packages. |
Avoid performing DDL operations on high-usage segments during peak hours. Performing DDL on such segments often results in the dependent SQL being invalidated and hence reparsed on a later execution.
Allocating sufficient cache space for frequently updated sequence numbers significantly reduces the frequency of dictionary cache locks, which improves scalability. The CACHE keyword on the CREATE SEQUENCE or ALTER SEQUENCE statement lets you configure the number of cached entries for each sequence.
| See Also:
Oracle Database SQL Reference for details on the |
Depending on the Oracle application tool you are using, it is possible to control how frequently your application performs parse calls.
The frequency with which your application either closes cursors or reuses existing cursors for new SQL statements affects the amount of memory used by a session and often the amount of parsing performed by that session.
An application that closes cursors or reuses cursors (for a different SQL statement), does not need as much session memory as an application that keeps cursors open. Conversely, that same application may need to perform more parse calls, using extra CPU and Oracle resources.
Cursors associated with SQL statements that are not executed frequently can be closed or reused for other statements, because the likelihood of reexecuting (and reparsing) that statement is low.
Extra parse calls are required when a cursor containing a SQL statement that will be reexecuted is closed or reused for another statement. Had the cursor remained open, it could have been reused without the overhead of issuing a parse call.
The ways in which you control cursor management depends on your application development tool. The following sections introduce the methods used for some Oracle tools.
See Also:
|
When using Oracle Call Interface (OCI), do not close and reopen cursors that you will be reexecuting. Instead, leave the cursors open, and change the literal values in the bind variables before execution.
Avoid reusing statement handles for new SQL statements when the existing SQL statement will be reexecuted in the future.
When using the Oracle precompilers, you can control when cursors are closed by setting precompiler clauses. In Oracle mode, the clauses are as follows:
Oracle Corporation 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 on the precompiler command line or within the precompiler program. With these clauses, you can employ different strategies for managing cursors during execution of the program.
Prepare the statement, then reexecute the statement with the new values for the bind variables. The cursor stays open for the duration of the session.
Avoid closing cursors if they will be reexecuted, because the new literal values can be bound to the cursor for reexecution. Alternatively, JDBC provides a SQL statement cache within the JDBC client using the setStmtCacheSize() method. Using this method, JDBC creates a SQL statement cache that is local to the JDBC program.
| See Also:
Oracle Database JDBC Developer's Guide and Reference for more information on using the JDBC SQL statement cache |
With Oracle Forms, it is possible to control some aspects of cursor management. You can exercise this control either at the trigger level, at the form level, or at run time.
When configuring a brand new instance, it is impossible to know the correct size to make the shared pool cache. Typically, a DBA makes a first estimate for the cache size, then runs a representative workload on the instance, and examines the relevant statistics to see whether the cache is under-configured or over-configured.
For most OLTP applications, shared pool size is an important factor in application performance. Shared pool size is less important for applications that issue a very limited number of discrete SQL statements, such as decision support systems (DSS).
If the shared pool is too small, then extra resources are used to manage the limited amount of available space. This consumes CPU and latching resources, and causes contention. Optimally, the shared pool should be just large enough to cache frequently accessed objects. Having a significant amount of free memory in the shared pool is a waste of memory. When examining the statistics after the database has been running, a DBA should check that none of these mistakes are in the workload.
When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.
The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.
The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero. This means SQL statements that could have been shared were invalidated by some operation (for example, a DDL). This statistic should be near zero on OLTP systems during peak loads.
Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from V$SGASTAT, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system.
Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.
These statistics are discussed in more detail in the following section.
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.
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 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.
| See Also:
Oracle Database Reference for information about the dynamic performance |
To examine each namespace individually, use the following query:
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE;
The output of this query could look like the following:
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS --------------- ---------- ---------- ---------- ------------- BODY 8870 8819 0 0 CLUSTER 393 380 0 0 INDEX 29 0 0 0 OBJECT 0 0 0 0 PIPE 55265 55263 0 0 SQL AREA 21536413 21520516 11204 2 TABLE/PROCEDURE 10775684 10774401 0 0 TRIGGER 1852 1844 0 0
To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
Using the library cache hit ratio formula, the cache hit ratio is the following:
SUM(PINHITS)/SUM(PINS) ---------------------- .999466248
|
Note: These queries return data from instance startup, rather than statistics gathered during an interval; interval statistics can better pinpoint the problem. |
| See Also:
Chapter 6, "Automatic Performance Diagnostics" for information on how gather information over an interval |
Examining the returned data leads to the following observations:
SQL AREA namespace, there were 21,536,413 executions.RELOAD).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' AND POOL = 'shared pool'; The output will be similar to the following: POOL NAME BYTES ----------- -------------------------- ---------- shared pool free memory 4928280
If free memory is always available in 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. It may be indicative of a well-configured system.
The amount of memory available for the library cache can drastically affect the parse rate of an Oracle instance. The shared pool advisory statistics provide a database administrator with information about library cache memory, allowing a DBA to predict how changes in the size of the shared pool can affect aging out of objects in the shared pool.
The shared pool advisory statistics track the library cache's use of shared pool memory and predict how the library cache will behave in shared pools of different sizes. Two fixed views provide the information to determine how much memory the library cache is using, how much is currently pinned, how much is on the shared pool's LRU list, as well as how much time might be lost or gained by changing the size of the shared pool.
The following views of the shared pool advisory statistics are available. These views display any data when shared pool advisory is on. These statistics reset when the advisory is turned off.
This view displays information about estimated parse time in the shared pool for different pool sizes. The sizes range from 10% of the current shared pool size or the amount of pinned library cache memory, whichever is higher, to 200% of the current shared pool size, in equal intervals. The value of the interval depends on the current size of the shared pool.
This view displays information about memory allocated to library cache memory objects in different namespaces. A memory object is an internal grouping of memory for efficient management. A library cache object may consist of one or more memory objects.
These views contain Java pool advisory statistics that track information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate.
V$JAVA_POOL_ADVICE displays information about estimated parse time in the Java pool for different pool sizes. The sizes range from 10% of the current Java pool size or the amount of pinned Java library cache memory, whichever is higher, to 200% of the current Java pool size, in equal intervals. The value of the interval depends on the current size of the Java pool.
| See Also:
Oracle Database Reference for information about the dynamic performance |
Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.
Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.
Each row in the V$ROWCACHE view contains statistics for a single type of data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. The columns in the V$ROWCACHE view that reflect the use and effectiveness of the data dictionary cache are listed in Table 7-2.
Use the following query to monitor the statistics in the V$ROWCACHE view over a period of time while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:
column parameter format a21 column pct_succ_gets format 999.9 column updates format 999,999,999 SELECT parameter , sum(gets) , sum(getmisses) , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets , sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
The output of this query will be similar to the following:
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES --------------------- ---------- -------------- ------------- ------------ dc_database_links 81 1 98.8 0 dc_free_extents 44876 20301 54.8 40,453 dc_global_oids 42 9 78.6 0 dc_histogram_defs 9419 651 93.1 0 dc_object_ids 29854 239 99.2 52 dc_objects 33600 590 98.2 53 dc_profiles 19001 1 100.0 0 dc_rollback_segments 47244 16 100.0 19 dc_segments 100467 19042 81.0 40,272 dc_sequence_grants 119 16 86.6 0 dc_sequences 26973 16 99.9 26,811 dc_synonyms 6617 168 97.5 0 dc_tablespace_quotas 120 7 94.2 51 dc_tablespaces 581248 10 100.0 0 dc_used_extents 51418 20249 60.6 42,811 dc_user_grants 76082 18 100.0 0 dc_usernames 216860 12 100.0 0 dc_users 376895 22 100.0 0
Examining the data returned by the sample query leads to these observations:
It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
Shared pool statistics indicate adjustments that can be made. The following sections describe some of your choices.
Increasing the amount of memory for the shared pool increases the amount of memory available to both the library cache and the dictionary cache.
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 zero. 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 might 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.
Examine cache activity by monitoring 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%, depending on the application.
Consider increasing the amount of memory available to the cache if all of the following are true:
Increase the amount of memory available to the data dictionary cache by increasing the value of the initialization parameter SHARED_POOL_SIZE.
If your RELOADS are near zero, and if you have a small amount of free memory in the shared pool, then the shared pool is probably large enough to hold the most frequently accessed data.
If you always have significant amounts of memory free in the shared pool, and if you would like to allocate this memory elsewhere, then you might be able to reduce the shared pool size and still maintain good performance.
To make the shared pool smaller, reduce the size of the cache by changing the value for the parameter SHARED_POOL_SIZE.
Unlike the shared pool, the large pool does not have an LRU list. Oracle does not attempt to age objects out of the large pool.
You should consider configuring a large pool if your instance uses any of the following:
Parallel query uses shared pool memory to cache parallel execution message buffers.
| See Also:
Oracle Data Warehousing Guide for more information on sizing the large pool with parallel query |
Recovery Manager uses the shared pool to cache I/O buffers during backup and restore operations. For I/O server processes and backup and restore operations, Oracle allocates buffers that are a few hundred kilobytes in size.
| See Also:
Oracle Database Recovery Manager Reference for more information on sizing the large pool when using Recovery Manager |
In a shared server architecture, the session memory for each client process is included in the shared pool.
As Oracle allocates shared pool memory for shared server session memory, the amount of shared pool memory available for the library cache and dictionary cache decreases. If you allocate this session memory from a different pool, then Oracle can use the shared pool primarily for caching shared SQL and not incur the performance overhead from shrinking the shared SQL cache.
Oracle recommends using the large pool to allocate the shared server-related User Global Area (UGA), rather that using the shared pool. This is because Oracle uses the shared pool to allocate System Global Area (SGA) memory for other purposes, such as shared SQL and PL/SQL procedures. Using the large pool instead of the shared pool decreases fragmentation of the shared pool.
To store shared server-related UGA in the large pool, specify a value for the initialization parameter LARGE_POOL_SIZE. To see which pool (shared pool or large pool) the memory for an object resides in, check the column POOL in V$SGASTAT. The large pool is not configured by default; its minimum value is 300K. If you do not configure the large pool, then Oracle uses the shared pool for shared server user session memory.
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, assuming that the shared server requires 200K to 300K to store session information for each active session. If you anticipate 100 active sessions simultaneously, then configure the large pool to be 30M, or increase the shared pool accordingly if the large pool is not configured.
See Also:
|
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 shared servers, the total amount of memory use decreases. This is because there are fewer processes; therefore, Oracle uses less PGA memory with shared servers when compared to dedicated server environments.