Before talking about tuning the shared pool, let me define some terms related to it:
Cursor : It is basically a memory area in library cache where various information about the sql statement being executed is stored. The info stored is
– text of sql statement
– its execution plan
– its execution statistics
– Environment
The pointers to cursors are called cursor handles. The hash chains in the library cache contain cursor handles.
When a SQL statement is issued,
– The statement is checked for syntax and semantics.
– The PGA is searched for any open cursor for the statement. If open cursor is found, the statement is executed. Availability of the open cursors is influenced by the parameters OPEN_CURSORS and CURSOR_SPACE_FOR_TIME.
– If an open cursor is not found in PGA, it is searched for closed cursors i.e. for pointers to the cursor in library cache. If closed cursor is found, hash chains need not be searched and library cache lock does not have to be obtained. If a closed cursor is found, cursor is opened i.e. a pin is obtained and an instantiation of the cursor is created in the PGA and then the statement is executed.
The availability of the closed cursors is influenced by the parameter SESSION_CACHED_CURSORS.
– If closed cursor is also not found, hash chains in the library cache need to be searched to locate the cursor handle for the statement. Hence SQL statement is hashed and the hash bucket is located where cursor handle might be found. Latch on the bucket is obtained to search the bucket.
– If the cursor handle is found in hash chain (soft parse),
. library cache lock is obtained,
. cursor handle is stored in the PGA,
. pin is obtained,
. an instantiation of the cursor is created in the PGA,
. latch on the bucket is released,
. the statement is executed,
. latch on the bucket is obtained,
. statement is unpinned and
. latch on the bucket is released.
- If the cursor handle is not found in hash chain (hard parse), some memory needs to be allocated in library cache to create the cursor
. library cache load lock is obtained
. memory is allocated (If free memory is not available, some objects may be aged out using LRU algorithm).
. The location of the cursor is stored in the Cursor handle in hash chain
. library cache lock is obtained,
. THe cursor handle is pinned.
. Execution plan for the statement is created and stored in the cursor.
. cursor handle is stored in the PGA,
. an instantiation of the cursor is created in the PGA,
. latch on the bucket is released,
. the statement is executed,
. latch on the bucket is obtained,
. statement is unpinned and
. latch on the bucket is released.
Waits on library cache may be caused due to following:
- Open/Closed cursor is not found in PGA as
- Earlier cursor was invalidated because
– any of the objects the statement depended on have been altered
– Procedure was recompiled
- Parameter OPEN_CURSORS/SESSION_CACHED_CURSORS was set to a low value.
- Cursors closed explicitly after each execution -
- Frequent logins and logoffs
- A lot of hard parsing is there because
- Open/closed cursors were not found
- Earlier cursors have aged out due to inadequate size of shared pool
- Earlier cursor was invalidated because
– any of the objects the statement depended on have been altered
– Procedure was recompiled
- Bind variables were not used for the statement causing multiple cursors to be created
- CURSOR_SHARING was set to EXACT
– Multiple sessions executing same SQL/Procedure so that they have to wait to pin the cursor
– Simultaneous compilation of the procedure which is being executed in another session
– Concurrent DDL on the underlying objects causing the depenedent SQL/Procedures to be invalidated
– Repetitive loading of frequently executed SQL/Procedures/Packages
– Using sequences without caching or a small value of cache
– Multiple parent cursors creation due to
- . Non Standardized SQL
- . Parameter CURSOR_SHARING=EXACT
– Multiple child cursors creation due to
- . Optimizer mismatches
- . Parameter mismatches
- . Translation mismatches
- . Bind variable mismatches
- . Language mismatches
SOLUTIONS:
- Avoid hard parsing by using bind variables instead of literal values in your queries.
The script below can be used to find sqls which can use literals i.e. those statements
which use the same execution plan but differ in text:
select plan_hash_value, count(plan_hash_value) cnt
from v$sql
group by plan_hash_value
having cnt > 5
order by count(plan_hash_value);
PLAN_HASH_VALUE CNT
————— ———-
272002086 520
select sql_text
from v$sql
where plan_hash_value = 272002086
and rownum < 10;
– Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements. Typically, the majority of data required by most users can be satisfied using preset queries. Use dynamic SQL where such functionality is required.
-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.
- Single-User Logon and Qualified Table Reference :
. 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.
- Use of PL/SQL :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.
– 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.
– Cache Sequence Numbers : Allocating sufficient cache space for frequently updated sequence numbers significantly reduces the frequency of dictionary cache locks, which improves scalability.
– Using related database parameters
. CURSOR_SHARING: Setting this parameter to smilar can solve your hard parse problems caused by using literals but can have side effects mostly on DSS environments and systems which uses stored outlines.
- – CURSOR_SHARING=FORCE will substitute bind variables for literals and only one cursor will be created.
- – CURSOR_SHARING=SIMILAR will substitute bind variables for literals and cause only one parent but multiple child cursors to be created.
. CURSOR_SPACE_FOR_TIME: 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:
- - FALSE (default): 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 must verify that the cursor containing the SQL statement is in the library cache.
- – TRUE : A cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a cursor is in the cache, because it cannot be deallocated while an application cursor associated with it is open.
You must be sure that the shared pool is large enough for the work load otherwise performance will be badly affected and ORA-4031 eventually signalled.
. OPEN_CURSORS: This parameter sets the upper bound for the number of cursor that a session can have open and if you size it correctly, cached cursors can be stay opened and won’t have to be closed to let new cursor open
. PROCESSES / SESSIONS: You can review the high water mark for Sessions and Processes in the V$RESOURCE_LIMIT view. If the hard-coded values for these parameters are much higher than the high water mark information, consider decreasing the parameter settings to free up some memory in the Shared Pool for other uses.
. SESSION_CACHED_CURSORS: When a cursor is closed, Oracle divorces all association between the session and the library cache state. If no other session has the same cursor opened, the library cache object and its heaps are unpinned and available for an LRU operation. The parameter SESSION_CACHED_CURSORS controls the number of cursors “soft” closed, much like the cached PL/SQL cursors. Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.
To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic session cursor cache hits in the V$SYSSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, then consider setting SESSION_CACHED_CURSORS to a larger value.
Once activated, Oracle maintains a cache of open cursors that correspond to SQL statements that are repeatedly executed by the application. When the application re-executes a cursor, it can be found in the cursor cache and a reparse is avoided.
In general, you should set SESSION_CACHED_CURSORS to 20. Using this parameter can impact memory utilization.
– Eliminate large anonymous PL/SQL block. Large anonymous PL/SQL blocks should be turned into small anonymous PL/SQL blocks that call packaged functions. The packages should be ‘kept’ in memory. To view candidates:
select sql_text
from v$sqlarea
where command_type=47 — command type for anonymous block
and length(sql_text) > 500;
- Pin large SQL and PL/SQL statements in memory using the DBMS_Shared_Pool: Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool. You should pin objects you find immediatelly after the each restart of instance.Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. You can pin the object by DBMS_SHARED_POOL package like below
EXECUTE DBMS_SHARED_POOL.KEEP(OWNER.TRIGGER, ‘R’)
– Avoid closing of rapidly executed cursors.
- Sizing the shared pool : 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.
To see Current size of the shared pool;
SQL>column bytes format 999999999999999
select bytes,bytes/1024/1024/1024 size_gb
from v$sgainfo
where name=’Shared Pool Size';
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.
SQL>SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;
High invalidations indicates that there is parsing problem with the namespace and high reloads indicates that there is a sizing problem which causes aging out.
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.
To find the free memory in shared pool:
SQL>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 that there is a problem. It may be indicative of a well-configured 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.
To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL>SELECT SUM(PINHITS)/SUM(PINS)
FROM V$LIBRARYCACHE;
Low hit ratio is an indication of a sizing or caching problem..
You can use the sizing advices from the view v$shared_pool_advice. This view displays information about estimated parse time in the shared pool for different pool sizes and the sizes range from %10 to %200 of current shared pool size. This can give you idea for sizing SGA and obliquely shared pool by the help of ASMM.
SQL>select *
from V$SHARED_POOL_ADVICE;
Dictionary cache stats
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;
High updates with low pct_succ_gets can be a clue of performance problems when accessing that dictionary object. For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%, depending on the application. If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size
SQL>SELECT (SUM(GETS – GETMISSES – FIXED)) / SUM(GETS) “ROW CACHE”
FROM V$ROWCACHE;
Low hit ratio is an indication of a sizing problem.
– Size the Reserved Area properly
An ORA-04031 error referencing a large failed requests indicates the Reserved Area is too fragmented.
SQL>col free_space for 999,999,999,999 head “TOTAL FREE”
col avg_free_size for 999,999,999,999 head “AVERAGE|CHUNK SIZE”
col free_count for 999,999,999,999 head “COUNT”
col request_misses for 999,999,999,999 head “REQUEST|MISSES”
col request_failures for 999,999,999,999 head “REQUEST|FAILURES”
col max_free_size for 999,999,999,999 head “LARGEST CHUNK”
select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures
from v$shared_pool_reserved;
TOTAL FREE AVERAGE CHUNK SIZE COUNT LARGEST CHUNK REQUEST MISSES REQUEST FAILURES
7,238,192 212,888 34 212,888 0 0
The reserved pool is small when:
REQUEST_FAILURES > 0 (and increasing)
The DBA should Increase shared_pool_reserved_size and shared_pool_size together.
Too much memory has been allocated to the reserved pool
If REQUEST_MISS = 0 or not increasing
FREE_MEMORY = > 50% of shared_pool_reserved_size minimum
The DBA should Decrease shared_pool_reserved_size
You should also use hidden and unsupported parameter “_shared_pool_reserved_pct” to control reserved pool. This parameter controls the allocated percentage of shared pool for reserved pool. By default it is %5 of the shared pool and if you use ASMM for memory management you can set this value higher like 10 to allocate reserved pool dynamically. When you set the parameter you will see the shared_pool_reserved_size parameter will be adjusted to the new setting.
The parameter can not be modified when instance is started. You can use the query below to see the current value
SQL>select a.ksppinm “Parameter”,
b.ksppstvl “Session Value”,
c.ksppstvl “Instance Value”
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm = ‘_shared_pool_reserved_pct';
Parameter Session Value Instance Value
_shared_pool_reserved_pct 10 10
Adding memory to the shared pool can also be problematic, particularly if large amounts of non-reusable SQL are being generated. The shared pool is managed by a number of link lists. As more and more SQL statements are added to the shared pool, the set of link lists gets longer, and it takes Oracle longer to traverse the link lists as it searches for parsed SQL. In this case, it is much better to tune your SQL using bind variables than to add memory.
If it appears that you need to add memory, (particularly if the system has been operating for some time and you have established that the initial settings seem sufficient), it is best to first determine if there is inefficient SQL being executed that needs to be tuned. This is often SQL that has been added to the system recently. Modifying memory allocations should be the last possible solution.
Decrease the shared pool size when the application does not use bind variables and when cursors are not shared and reused. When cursors are not shared, a smaller shared pool will be more efficient as a larger shared pool causes Oracle to perform more searches to find free memory for new cursors.
References:
———————————————————————————————
Related links :
Home
Tuning Index
Cursor Sharing Demystified
Library Cache Lock And Pin Demonstrated
Latches, Locks, Pins And Mutexes
Parent And Child Cursors In Oracle
Shared Pool Architecture
————
Great job ! Thanks
Thanks Kais for your time and feedback.
Your comments and feedback are always welcome!!
Regards
Anju
Awesome explanation in detail
Thanks Pawan for your time and feedback.
Regards
Anju Garg
If I restart the database what happen to existing statements in sql and plsql area of shared pool. are they still available or flushed out
Hello Manoj,
Existing statements in sql and plsql area of shared pool will be flushed out.
Hope it helps.
Your comments and suggestions are always welcome.
regards
Anju Garg