The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
■ Slow physical I/O
Generally, this is caused by poorly-configured disks. However, it could also be caused by a significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.
■ Latch contention
Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes.
■ Excessive CPU usage
Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.
There are two distinct types of tuning:
Proactive Monitoring usually occurs on a regularly scheduled interval, where several performance statistics are examined to identify whether the system behavior and resource usage has changed.
Bottleneck Elimination Tuning usually implies fixing a performance problem. Usually, the purpose for tuning is to reduce resource consumption or to reduce the elapsed time for an operation to complete.
Configuring and Using Memory
Proper sizing and effective use of the Oracle Database memory caches greatly improves database performance.
Understanding Memory Allocation Issues
Oracle Database 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 of frequently accessed objects to be perform 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.
Automatic Memory Management
Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET).
The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory
allocations, consider using the Memory Advisor for managing memory.
Automatic SGA management distributes the memory appropriately for the following memory pools:
■ Database buffer cache (default pool)
■ Shared pool
■ Large pool
■ Java pool
■ Streams pool
If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
■ Log buffer
■ Other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)
■ Fixed SGA and other internal allocations
To manually size these memory pools, you must set the DB_KEEP_CACHE_SIZE, DB_
RECYCLE_CACHE_SIZE, DB_nK_CACHE_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.
Operating System Memory Use
Reduce Paging
Use operating system utilities to examine the operating system, to identify whether there is a lot of paging on your system. If so, then the total system 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.
Fit the SGA into Main Memory
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.
Use the LOCK_SGA parameter to lock the SGA into physical memory and prevent it from being paged out.The database does not use the MEMORY_TARGET and MEMORY_MAX_TARGET
parameters when the LOCK_SGA parameter is enabled.
Allow Adequate Memory to Individual Users
When sizing the SGA, ensure that you allow enough memory for the individual server processes and any other programs running on the system.
Using the Buffer Cache Effectively
Verify that frequently executed SQL statements and SQL statements that perform many buffer gets have been tuned.
Using V$DB_CACHE_ADVICE
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.
This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size.
Calculating the Buffer Cache Hit Ratio
The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access.
Increasing Memory Allocated to the Buffer Cache
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.
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. You can use the DB_nK_ CACHE_SIZE parameter to configure the nonstandard block size needed (where n is 2, 4,
8, 16 or 32 and n is not the standard block size).
Considering Multiple Buffer Pools
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.
Using Multiple Buffer Pools
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.
SQL> CREATE TABLE t1 (
2 my_date DATE NOT NULL,
3 my_number NUMBER(12,10) NOT NULL,
4 my_row NUMBER(12) NOT NULL)
5 STORAGE (BUFFER_POOL KEEP);
Table created.
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.
Configuring and Using the Shared Pool and Large Pool
Oracle Database 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, result cache data, and other data. Proper use and sizing of the shared pool can reduce resource consumption in at least
four ways:
■ Parse overhead is avoided if the SQL statement is in the shared pool. This saves
CPU resources on the host and elapsed time for the end user.
■ Latching resource usage is significantly reduced, which results in greater
scalability.
■ Shared pool memory requirements are reduced, because all applications use the
same pool of SQL statements and dictionary resources.
■ I/O resources are saved, because dictionary elements that are in the shared pool
do not require disk access.
Shared Pool Concepts
- A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache.
- Several 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.
- Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces 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 5 KB. To allow these allocations to occur most efficiently, Oracle Database 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.
Dictionary Cache Concepts
- Information about usernames, segment information, profile data, tablespace information, and sequence numbers, metadata, about schema objects
Library Cache Concepts
- holds executable forms of SQL cursors, PL/SQL programs, and Java classes
- In order to perform a hard parse, Oracle Database uses more resources than during a soft parse.
SQL Sharing Criteria
Oracle Database automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
- The text of the statement is hashed. If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
- Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following 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 is set to SIMILAR or FORCE.
Using the Shared Pool Effectively
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 several ways to ensure efficient use of the shared pool and related resources.
Shared Cursors
To share cursors, do the following:
■ Use bind variables rather than literals in SQL statements whenever possible.
■ Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements.
■ Ensure that users of the application do not change the optimization approach and goal for their individual sessions.
■ Establish the following policies for application developers:
– Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
– Consider using stored procedures whenever possible. Multiple users issuing the same stored procedure use the same shared PL/SQL area automatically. Because stored procedures are stored in a parsed form, their use reduces run-time parsing.
■ For SQL statements which are identical but are not being shared, you can query V$SQL_SHARED_CURSOR to determine why the cursors are not shared. This would include optimizer settings and bind variable mismatches.
Avoid Performing DDL
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.
Sizing the Shared Pool
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).
Optimally, the shared pool should be just large enough to cache frequently accessed objects.
Shared Pool: Library Cache Statistics
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.
Using the Large Pool
Unlike the shared pool, the large pool does not have an LRU list. Oracle Database 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
Parallel query uses shared pool memory to cache parallel execution message buffers.
■ Recovery Manager
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 Database allocates buffers that are a few hundred kilobytes in size.
■ Shared server
In a shared server architecture, the session memory for each client process is included in the shared pool.
Using CURSOR_SPACE_FOR_TIME
If you have no library cache misses, then you might be able to accelerate execution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME to true. This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:
■ If CURSOR_SPACE_FOR_TIME is set to false (the default), then a cursor can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle Database must verify that the cursor containing the SQL statement is in the library cache.
■ If CURSOR_SPACE_FOR_TIME is set to true, then a cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle Database need not verify that a cursor is in the cache because it cannot be deallocated while an application cursor associated with it is open.
Setting the value of the parameter to true saves Oracle Database a small amount of time and can slightly improve the performance of execution calls. This value also prevents the deallocation of cursors 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.
Caching Session Cursors
The session cursor cache contains closed session cursors for SQL and PL/SQL, including recursive SQL.
This cache can be useful for applications that use Oracle Forms because switching from one form to another closes all session cursors associated with the first form. If an application repeatedly issues parse calls on the same set of SQL statements, then reopening session cursors can degrade performance. By reusing cursors, the database can reduce parse times, leading to faster overall execution times.
Enabling the Session Cursor Cache
The following initialization parameters are relevant to the cursor cache:
■ SESSION_CACHED_CURSORS
This parameter sets the maximum number of cached closed cursors for each session. The default setting is 50. You can use this parameter to prevent a session from opening an excessive number of cursors, thereby filling the library cache or forcing excessive hard parses.
■ OPEN_CURSORS
This parameter specifies the maximum number of cursors a session can have open simultaneously. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.
Configuring the Reserved Pool
If there is not enough free space in the shared pool, then Oracle Database must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Thus, Oracle Database internally reserves a small memory area in the shared pool that the database can use if the shared pool does not have enough space. This reserved pool makes allocation of large chunks more efficient.
You probably will not need to change the default amount of space Oracle Database reserves. However, if necessary, the reserved pool size can be changed by setting the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside space in the shared pool for unusually large allocations.
Keeping Large Objects to Prevent Aging
The DBMS_SHARED_POOL package lets you keep objects in shared memory, so that they do not age out with the normal LRU mechanism. The DBMS_SHARED_POOL package is useful for the following:
■ When loading large PL/SQL objects, such as the STANDARD and DIUTIL packages.
When large PL/SQL objects are loaded, user response time may be affected if smaller objects that must age out of the shared pool to make room. In some cases, there might be insufficient memory to load the large objects.
■ Frequently executed triggers. You might want to keep compiled triggers on frequently used tables in the shared pool.
To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps:
1. Decide which packages or cursors to pin in memory.
2. Start up the database.
3. Make the call to DBMS_SHARED_POOL.KEEP to pin your objects.
SQL> CREATE OR REPLACE PROCEDURE testproc IS
2 BEGIN
3 NULL;
4 END testproc;
5 /
Procedure created.
SQL> exec sys.dbms_shared_pool.keep('testproc', 'P');
PL/SQL procedure successfully completed.
SQL> SELECT owner, name, type
2 FROM gv$db_object_cache
3 WHERE kept = 'YES'
4 AND owner = 'SYS' and name like 'TEST%';
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE
----------------------------
SYS
TESTPROC
PROCEDURE
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
BEGIN dbms_shared_pool.purge('SYS.TESTPROC'); END;
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 58
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 77
ORA-06512: at line 1
SQL> exec dbms_shared_pool.unkeep('SYS.TESTPROC', 'P');
PL/SQL procedure successfully completed.
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
PL/SQL procedure successfully completed.
SQL>
PGA Memory Management
- private memory region containing data and control information for a server process.
- An example of such information is the run-time area of a cursor.
- bigger work areas can significantly improve the performance of a particular operator at the cost of higher
memory consumption.
- set PGA_AGGREGATE_TARGET to value you desire for PGA auto management
- For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.
- For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total
- Good initial values for the parameter PGA_AGGREGATE_TARGET might be:
■ For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
■ For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
- V$PGASTAT This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager.
- V$PROCESS This view has one row for each Oracle process connected to the instance. The columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM can be used to monitor the PGA memory usage of these processes.
Configuring OLAP_PAGE_POOL_SIZE
The OLAP_PAGE_POOL_SIZE initialization parameter specifies (in bytes) the maximum size of the paging cache to be allocated to an OLAP session. For performance reasons, it is usually preferable to configure a small OLAP paging cache and set a larger default buffer pool with DB_CACHE_SIZE. An OLAP paging cache of 4 MB is fairly typical, with 2 MB used for systems with limited memory.
Optimizer Access Paths
- ways in which data is retrieved from the database.
- index access paths are useful for statements that retrieve a small subset of table rows (OLTP systems)
- full scans are more efficient when accessing a large portion of the table (DSS systems)
Full Table Scans
- all blocks in the table that are under the high water mark are scanned
- The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads, the database can perform a full table scan very efficiently. The database reads each block only once.
-If the optimizer thinks that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available.
- Use the hint FULL(table alias) to instruct the optimizer to use a full table scan.
Parallel Query Execution
- When a full table scan is required, the database can improve response time by using multiple parallel execution servers.
- Typically, parallel queries occur in low-concurrency data warehouses because of the potential resource usage.
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
■ Slow physical I/O
Generally, this is caused by poorly-configured disks. However, it could also be caused by a significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.
■ Latch contention
Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes.
■ Excessive CPU usage
Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.
There are two distinct types of tuning:
Proactive Monitoring usually occurs on a regularly scheduled interval, where several performance statistics are examined to identify whether the system behavior and resource usage has changed.
Bottleneck Elimination Tuning usually implies fixing a performance problem. Usually, the purpose for tuning is to reduce resource consumption or to reduce the elapsed time for an operation to complete.
Configuring and Using Memory
Proper sizing and effective use of the Oracle Database memory caches greatly improves database performance.
Understanding Memory Allocation Issues
Oracle Database 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 of frequently accessed objects to be perform 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.
Automatic Memory Management
Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET).
The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory
allocations, consider using the Memory Advisor for managing memory.
Automatic SGA management distributes the memory appropriately for the following memory pools:
■ Database buffer cache (default pool)
■ Shared pool
■ Large pool
■ Java pool
■ Streams pool
If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
■ Log buffer
■ Other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)
■ Fixed SGA and other internal allocations
To manually size these memory pools, you must set the DB_KEEP_CACHE_SIZE, DB_
RECYCLE_CACHE_SIZE, DB_nK_CACHE_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.
Operating System Memory Use
Reduce Paging
Use operating system utilities to examine the operating system, to identify whether there is a lot of paging on your system. If so, then the total system 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.
Fit the SGA into Main Memory
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.
Use the LOCK_SGA parameter to lock the SGA into physical memory and prevent it from being paged out.The database does not use the MEMORY_TARGET and MEMORY_MAX_TARGET
parameters when the LOCK_SGA parameter is enabled.
Allow Adequate Memory to Individual Users
When sizing the SGA, ensure that you allow enough memory for the individual server processes and any other programs running on the system.
Using the Buffer Cache Effectively
Verify that frequently executed SQL statements and SQL statements that perform many buffer gets have been tuned.
Using V$DB_CACHE_ADVICE
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.
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';
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';
This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size.
Calculating the Buffer Cache Hit Ratio
The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access.
Increasing Memory Allocated to the Buffer Cache
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.
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. You can use the DB_nK_ CACHE_SIZE parameter to configure the nonstandard block size needed (where n is 2, 4,
8, 16 or 32 and n is not the standard block size).
Considering Multiple Buffer Pools
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.
Using Multiple Buffer Pools
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.
SQL> CREATE TABLE t1 (
2 my_date DATE NOT NULL,
3 my_number NUMBER(12,10) NOT NULL,
4 my_row NUMBER(12) NOT NULL)
5 STORAGE (BUFFER_POOL KEEP);
Table created.
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.
Configuring and Using the Shared Pool and Large Pool
Oracle Database 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, result cache data, and other data. Proper use and sizing of the shared pool can reduce resource consumption in at least
four ways:
■ Parse overhead is avoided if the SQL statement is in the shared pool. This saves
CPU resources on the host and elapsed time for the end user.
■ Latching resource usage is significantly reduced, which results in greater
scalability.
■ Shared pool memory requirements are reduced, because all applications use the
same pool of SQL statements and dictionary resources.
■ I/O resources are saved, because dictionary elements that are in the shared pool
do not require disk access.
Shared Pool Concepts
- A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache.
- Several 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.
- Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces 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 5 KB. To allow these allocations to occur most efficiently, Oracle Database 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.
Dictionary Cache Concepts
- Information about usernames, segment information, profile data, tablespace information, and sequence numbers, metadata, about schema objects
Library Cache Concepts
- holds executable forms of SQL cursors, PL/SQL programs, and Java classes
- In order to perform a hard parse, Oracle Database uses more resources than during a soft parse.
SQL Sharing Criteria
Oracle Database automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
- The text of the statement is hashed. If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
- Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following 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 is set to SIMILAR or FORCE.
Using the Shared Pool Effectively
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 several ways to ensure efficient use of the shared pool and related resources.
Shared Cursors
To share cursors, do the following:
■ Use bind variables rather than literals in SQL statements whenever possible.
■ Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements.
■ Ensure that users of the application do not change the optimization approach and goal for their individual sessions.
■ Establish the following policies for application developers:
– Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
– Consider using stored procedures whenever possible. Multiple users issuing the same stored procedure use the same shared PL/SQL area automatically. Because stored procedures are stored in a parsed form, their use reduces run-time parsing.
■ For SQL statements which are identical but are not being shared, you can query V$SQL_SHARED_CURSOR to determine why the cursors are not shared. This would include optimizer settings and bind variable mismatches.
Avoid Performing DDL
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.
Sizing the Shared Pool
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).
Optimally, the shared pool should be just large enough to cache frequently accessed objects.
Shared Pool: Library Cache Statistics
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.
Using the Large Pool
Unlike the shared pool, the large pool does not have an LRU list. Oracle Database 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
Parallel query uses shared pool memory to cache parallel execution message buffers.
■ Recovery Manager
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 Database allocates buffers that are a few hundred kilobytes in size.
■ Shared server
In a shared server architecture, the session memory for each client process is included in the shared pool.
Using CURSOR_SPACE_FOR_TIME
If you have no library cache misses, then you might be able to accelerate execution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME to true. This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:
■ If CURSOR_SPACE_FOR_TIME is set to false (the default), then a cursor can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle Database must verify that the cursor containing the SQL statement is in the library cache.
■ If CURSOR_SPACE_FOR_TIME is set to true, then a cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle Database need not verify that a cursor is in the cache because it cannot be deallocated while an application cursor associated with it is open.
Setting the value of the parameter to true saves Oracle Database a small amount of time and can slightly improve the performance of execution calls. This value also prevents the deallocation of cursors 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.
Caching Session Cursors
The session cursor cache contains closed session cursors for SQL and PL/SQL, including recursive SQL.
This cache can be useful for applications that use Oracle Forms because switching from one form to another closes all session cursors associated with the first form. If an application repeatedly issues parse calls on the same set of SQL statements, then reopening session cursors can degrade performance. By reusing cursors, the database can reduce parse times, leading to faster overall execution times.
Enabling the Session Cursor Cache
The following initialization parameters are relevant to the cursor cache:
■ SESSION_CACHED_CURSORS
This parameter sets the maximum number of cached closed cursors for each session. The default setting is 50. You can use this parameter to prevent a session from opening an excessive number of cursors, thereby filling the library cache or forcing excessive hard parses.
■ OPEN_CURSORS
This parameter specifies the maximum number of cursors a session can have open simultaneously. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.
Configuring the Reserved Pool
If there is not enough free space in the shared pool, then Oracle Database must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Thus, Oracle Database internally reserves a small memory area in the shared pool that the database can use if the shared pool does not have enough space. This reserved pool makes allocation of large chunks more efficient.
You probably will not need to change the default amount of space Oracle Database reserves. However, if necessary, the reserved pool size can be changed by setting the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside space in the shared pool for unusually large allocations.
The default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE.
Keeping Large Objects to Prevent Aging
The DBMS_SHARED_POOL package lets you keep objects in shared memory, so that they do not age out with the normal LRU mechanism. The DBMS_SHARED_POOL package is useful for the following:
■ When loading large PL/SQL objects, such as the STANDARD and DIUTIL packages.
When large PL/SQL objects are loaded, user response time may be affected if smaller objects that must age out of the shared pool to make room. In some cases, there might be insufficient memory to load the large objects.
■ Frequently executed triggers. You might want to keep compiled triggers on frequently used tables in the shared pool.
To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps:
1. Decide which packages or cursors to pin in memory.
2. Start up the database.
3. Make the call to DBMS_SHARED_POOL.KEEP to pin your objects.
--Determine address and hash value of the SQL statement
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';
-- substitute your query results for mine, below.
exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';
-- substitute your query results for mine, below.
exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');
| Flag Values | Description |
| C | cursor |
| JC | java class |
| JD | java shared data |
| JR | java resource |
| JS | java source |
| P | procedure |
| Q | sequence |
| R | trigger |
| T | type |
SQL> CREATE OR REPLACE PROCEDURE testproc IS
2 BEGIN
3 NULL;
4 END testproc;
5 /
Procedure created.
SQL> exec sys.dbms_shared_pool.keep('testproc', 'P');
PL/SQL procedure successfully completed.
SQL> SELECT owner, name, type
2 FROM gv$db_object_cache
3 WHERE kept = 'YES'
4 AND owner = 'SYS' and name like 'TEST%';
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE
----------------------------
SYS
TESTPROC
PROCEDURE
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
BEGIN dbms_shared_pool.purge('SYS.TESTPROC'); END;
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 58
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 77
ORA-06512: at line 1
SQL> exec dbms_shared_pool.unkeep('SYS.TESTPROC', 'P');
PL/SQL procedure successfully completed.
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
PL/SQL procedure successfully completed.
SQL>
PGA Memory Management
- private memory region containing data and control information for a server process.
- An example of such information is the run-time area of a cursor.
- bigger work areas can significantly improve the performance of a particular operator at the cost of higher
memory consumption.
- set PGA_AGGREGATE_TARGET to value you desire for PGA auto management
- For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.
- For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total
- Good initial values for the parameter PGA_AGGREGATE_TARGET might be:
■ For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
■ For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
- V$PGASTAT This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager.
- V$PROCESS This view has one row for each Oracle process connected to the instance. The columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM can be used to monitor the PGA memory usage of these processes.
Configuring OLAP_PAGE_POOL_SIZE
The OLAP_PAGE_POOL_SIZE initialization parameter specifies (in bytes) the maximum size of the paging cache to be allocated to an OLAP session. For performance reasons, it is usually preferable to configure a small OLAP paging cache and set a larger default buffer pool with DB_CACHE_SIZE. An OLAP paging cache of 4 MB is fairly typical, with 2 MB used for systems with limited memory.
Optimizer Access Paths
- ways in which data is retrieved from the database.
- index access paths are useful for statements that retrieve a small subset of table rows (OLTP systems)
- full scans are more efficient when accessing a large portion of the table (DSS systems)
Full Table Scans
- all blocks in the table that are under the high water mark are scanned
- The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads, the database can perform a full table scan very efficiently. The database reads each block only once.
-If the optimizer thinks that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available.
- Use the hint FULL(table alias) to instruct the optimizer to use a full table scan.
Parallel Query Execution
- When a full table scan is required, the database can improve response time by using multiple parallel execution servers.
- Typically, parallel queries occur in low-concurrency data warehouses because of the potential resource usage.
No comments:
Post a Comment