Tuning the shared pool and large pool in Oracle 19c is essential for optimizing database performance. The shared pool stores a variety of critical memory structures, such as SQL execution plans, while the large pool is used primarily for large memory allocations, like RMAN backups and I/O server processes. This article will explore strategies for effective shared pool tuning and large pool tuning, ensuring optimal performance and resource utilization.
Shared Pool Tuning
Understanding the Shared Pool
The shared pool is a crucial component of the Oracle instance’s memory architecture. It stores parsed SQL statements, PL/SQL code, data dictionary cache, and various other control structures. Efficient shared pool tuning can significantly enhance database performance by reducing parse times and improving the execution speed of SQL statements.
đŸ“¢ You might also like: Oracle 19c Real Application Testing Overview (Category: Performance Management and Tuning)
Key Areas for Shared Pool Tuning
Library Cache: The library cache stores the parsed SQL statements and execution plans. Ensuring that frequently executed statements are retained in the cache can reduce the need for re-parsing and optimize performance.
Data Dictionary Cache: This cache stores metadata about database objects. Properly tuning the data dictionary cache ensures quick access to metadata, enhancing overall database performance.
SQL Execution Plans: By keeping frequently used SQL execution plans in the shared pool, the database can avoid the overhead of re-optimization, thereby speeding up query execution.
Tuning Parameters for the Shared Pool
To effectively tune the shared pool, several parameters must be configured:
SHARED_POOL_SIZE: This parameter specifies the size of the shared pool. Increasing the shared pool size can help accommodate more SQL statements and reduce the likelihood of cache misses.
ALTER SYSTEM SET SHARED_POOL_SIZE = 500M SCOPE = BOTH;
CURSOR_SHARING: Setting this parameter to ‘FORCE’ can help Oracle reuse execution plans for similar SQL statements, thus optimizing shared pool usage.
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;
SHARED_POOL_RESERVED_SIZE: This parameter defines the amount of memory reserved for large objects. Proper configuration can prevent fragmentation and ensure smooth allocation of large memory chunks.
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 100M SCOPE = BOTH;
Monitoring and Managing the Shared Pool
V$LIBRARYCACHE: This view provides information about the library cache’s performance. Regular monitoring can help identify areas for improvement.
SELECT namespace, gets, gethits, pins, pinhits, reloads
FROM V$LIBRARYCACHE;
V$SGASTAT: This view provides detailed statistics about the SGA, including the shared pool.
SELECT * FROM V$SGASTAT WHERE POOL = 'shared pool';
DBMS_SHARED_POOL Package: This package offers procedures to manage objects in the shared pool, such as keeping frequently used objects in memory.
BEGIN
DBMS_SHARED_POOL.KEEP('HR.EMPLOYEES');
END;
/
Using the Shared Pool Advisory
The Shared Pool Advisory helps determine the optimal size for the shared pool by providing recommendations based on the current workload.
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- Run a representative workload
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
SELECT * FROM V$SHARED_POOL_ADVICE;
Large Pool Optimization
Understanding the Large Pool
The large pool is an optional memory area that relieves the burden on the shared pool by allocating large memory chunks for specific operations. It is used for activities such as RMAN backup and restore operations, parallel query buffers, and large I/O server processes.
Key Areas for Large Pool Tuning
RMAN Operations: The large pool is heavily utilized during RMAN backup and restore operations. Allocating sufficient memory to the large pool can enhance the performance and reliability of these operations.
Parallel Execution: Large pool memory is used to store the message buffers used in parallel execution. Proper tuning ensures efficient parallel processing without impacting the shared pool.
I/O Server Processes: Operations requiring large memory allocations, such as certain I/O server processes, benefit from the large pool. This separation helps avoid fragmentation in the shared pool.
Tuning Parameters for the Large Pool
LARGE_POOL_SIZE: This parameter specifies the size of the large pool. Adequate sizing of the large pool can prevent out-of-memory errors during intensive operations.
ALTER SYSTEM SET LARGE_POOL_SIZE = 300M SCOPE = BOTH;
PARALLEL_EXECUTION_MESSAGE_SIZE: This parameter defines the size of the messages used in parallel execution. Tuning this parameter can optimize the memory usage for parallel operations.
ALTER SYSTEM SET PARALLEL_EXECUTION_MESSAGE_SIZE = 16K SCOPE = BOTH;
Monitoring and Managing the Large Pool
V$SGASTAT: This view provides information about the allocation and usage of the large pool.
SELECT * FROM V$SGASTAT WHERE POOL = 'large pool';
Practical Examples and Commands
Example 1: Tuning Shared Pool for High OLTP Workload
For a high OLTP workload, where numerous small transactions occur frequently, it is crucial to optimize the shared pool to handle the increased number of SQL executions and reduce parse times.
Increase Shared Pool Size
ALTER SYSTEM SET SHARED_POOL_SIZE = 700M SCOPE = BOTH;
Use CURSOR_SHARING to Optimize SQL Reuse
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;
Monitor Library Cache Efficiency
SELECT namespace, gets, gethits, pins, pinhits, reloads
FROM V$LIBRARYCACHE;
Example 2: Optimizing Large Pool for RMAN Backups
For databases with heavy RMAN operations, optimizing the large pool ensures smooth backup and restore processes without impacting the shared pool.
Set an Adequate LARGE_POOL_SIZE
ALTER SYSTEM SET LARGE_POOL_SIZE = 500M SCOPE = BOTH;
Monitor Large Pool Usage
SELECT * FROM V$SGASTAT WHERE POOL = 'large pool';
Adjust PARALLEL_EXECUTION_MESSAGE_SIZE for Efficient Parallel Processing
ALTER SYSTEM SET PARALLEL_EXECUTION_MESSAGE_SIZE = 32K SCOPE = BOTH;
Best Practices for Tuning Shared Pool and Large Pool
Regular Monitoring: Continuously monitor the shared pool and large pool using views such as V$SGASTAT and V$LIBRARYCACHE to identify and address performance issues promptly.
Adequate Sizing: Ensure that both pools are adequately sized based on the workload. Over-provisioning can lead to wasted memory resources, while under-provisioning can cause performance degradation.
SQL Plan Management: Use SQL Plan Management to stabilize execution plans and reduce the overhead on the shared pool.
Avoid Fragmentation: Properly manage the allocation of large objects to avoid fragmentation in the shared pool. Use SHARED_POOL_RESERVED_SIZE to reserve space for large allocations.
Leverage Automatic Memory Management (AMM): Oracle’s AMM can dynamically allocate memory to different components, including the shared pool and large pool, based on the workload. Consider enabling AMM for more flexible memory management.
Advanced Tuning Techniques
Using ASMM (Automatic Shared Memory Management): ASMM allows Oracle to automatically adjust the sizes of the shared pool, large pool, buffer cache, and other SGA components based on the workload.
ALTER SYSTEM SET SGA_TARGET = 2G SCOPE = BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 2G SCOPE = SPFILE;
Memory Advisor Tools: Oracle provides memory advisor tools to assist in tuning the shared pool and large pool. Use the SGA Advisor to get recommendations on memory distribution.
Persistent Memory Allocation: For workloads that require persistent memory allocation, configure the MEMORY_TARGET parameter properly to ensure efficient memory usage across all SGA components.
Conclusion
Tuning the shared pool and large pool in Oracle 19c is critical for maintaining optimal database performance. By understanding the key components and parameters involved, database administrators can effectively manage memory resources, reduce parse times, and enhance the efficiency of SQL execution and backup operations. Regular monitoring and adherence to best practices ensure that the database remains responsive and reliable under various workloads.
See more on Oracle’s website!
Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!
