Lesson 10
Tuning Shared Pool Conclusion
This module has focused on tuning the Oracle shared pool.
We have looked closely at broader tuning issues and those specifically related to the library cache. Now you should be able to:
- List shared pool tuning techniques
- Pin packages in the shared pool
- Reserve space for large objects with shared_pool_reserved size
- Find high-use packages
- Gather information about the number of reloads required
- Detect library cache contention
- Determine library cache misses and parse calls for SQL
- Understand SQL re-use in the library cache
"Pin packages" in the Oracle shared pool
In Oracle, pinning packages refers to the practice of keeping frequently accessed packages, procedures, functions, triggers, or sequences in memory, specifically in the shared pool, to improve database performance. The shared pool is a component of the Oracle System Global Area (SGA) that stores parsed SQL statements, PL/SQL blocks, dictionary cache, and server handles among other things.
The term "pinning" comes from the concept that you're "pinning" an object in memory so it's not aged out over time by the least recently used (LRU) algorithm, which Oracle uses to manage memory in the shared pool.
Pinning packages and other objects can be beneficial for performance in some cases because it reduces the overhead of re-loading and re-parsing objects. When an unpinned package is called, if it is not already in the shared pool, it must be loaded from disk. This requires I/O operations and takes more time than retrieving it from memory. If the package is already in memory because it's been pinned, then the system can skip the loading process.
The DBMS_SHARED_POOL package in Oracle provides the capabilities for pinning and unpinning objects in the shared pool. The syntax to pin an object is as follows:
In the above example, 'SCOTT.EMP_PKG' is the name of the package, and 'P' is the type of the object (in this case, package). However, it's important to note a couple of things about pinning:
Pinning isn't always necessary and can be overused. Oracle Database is very efficient at managing memory. In most cases, frequently accessed objects naturally remain in the shared pool because the LRU algorithm keeps recently used objects in memory.
Pinning consumes memory, and over-pinning can lead to issues such as shared pool fragmentation. Pinning should be used judiciously, only for objects that are large and are accessed frequently but not frequently enough to stay naturally in the shared pool.
As of Oracle Database 10g and later, Oracle introduced the concept of Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM), which further reduce the need for manual pinning.
Thus, while pinning can be used as a technique to improve database performance, it should be used judiciously and as part of a broader performance optimization strategy. It's always a good idea to monitor and understand your database's behavior before implementing such changes.
The term "pinning" comes from the concept that you're "pinning" an object in memory so it's not aged out over time by the least recently used (LRU) algorithm, which Oracle uses to manage memory in the shared pool.
Pinning packages and other objects can be beneficial for performance in some cases because it reduces the overhead of re-loading and re-parsing objects. When an unpinned package is called, if it is not already in the shared pool, it must be loaded from disk. This requires I/O operations and takes more time than retrieving it from memory. If the package is already in memory because it's been pinned, then the system can skip the loading process.
The DBMS_SHARED_POOL package in Oracle provides the capabilities for pinning and unpinning objects in the shared pool. The syntax to pin an object is as follows:
EXEC DBMS_SHARED_POOL.KEEP('SCOTT.EMP_PKG', 'P');
In the above example, 'SCOTT.EMP_PKG' is the name of the package, and 'P' is the type of the object (in this case, package). However, it's important to note a couple of things about pinning:
Pinning isn't always necessary and can be overused. Oracle Database is very efficient at managing memory. In most cases, frequently accessed objects naturally remain in the shared pool because the LRU algorithm keeps recently used objects in memory.
Pinning consumes memory, and over-pinning can lead to issues such as shared pool fragmentation. Pinning should be used judiciously, only for objects that are large and are accessed frequently but not frequently enough to stay naturally in the shared pool.
As of Oracle Database 10g and later, Oracle introduced the concept of Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM), which further reduce the need for manual pinning.
Thus, while pinning can be used as a technique to improve database performance, it should be used judiciously and as part of a broader performance optimization strategy. It's always a good idea to monitor and understand your database's behavior before implementing such changes.
Automatic Shared Memory Management
The background processes interact with the operating system and each other to manage the memory structures for the instance.
These processes also manage the actual database on disk and perform general housekeeping for the instance.
Figure 3-10 illustrates the memory structures and background processes discussed in the following section.
Figure 3-10 illustrates the memory structures and background processes discussed in the following section.
Additional background processes may exist when you use certain other features of the database; for example, shared servers (formerly the Multi-Threaded Server or MTS prior to Oracle9i), or job queues and replication.
New terms
Here are some terms from this module that may have been new to you:
- pinning packages: The process of keeping packages inside the library cache
- estat-bstat utility: Oracle begin statistics (utlbstat.sql) and end statistics (utlestat.sql). These are used to create an elapsed-time snapshot of Oracle activity.
- shared SQL area: The shared SQL area is used to keep and process SQL statements and PL/SQL code.
- library cache miss ratio: Represents the ratio of the sum of library cache reloads to the sum of pins.