• CURSOR_SHARING

Literal replacement is a feature where Oracle replaces literal values in SQL statements to reduce the application Shared Pool footprint and decrease "hard" parsing.  The literal values are replaced with bind variables and if two or more sessions are executing the same SQL statement, they can both use the same cursor with the bind variable instead of creating two unsharable cursors.

For example, two users connected as SCOTT issued the SQL statements "select ename from emp where empno = 20" and "select ename from emp where empno =100".  If cursor_sharing is set to FORCE, Oracle will create one cursor with a bind variable so the statement becomes equivalent to "select ename from emp where empno = :b1".  The two users will share the same cursor object instead of creating two separate Library Cache parent objects and their corresponding child objects.

The parameter has three modes:

• EXACT: no attempt at literal replacement
• FORCE: all literals are replaced; statements are shared without regard for how the literal values may affect the execution plan.
• SIMILAR: all literal are replaced, but statements are shared only if the cursors have the same plan.

Invalidations are caused by either executing DDL against the objects, gathering stats, or granting/revoking privileges.  You should see associated "library cache pin" waits also.

References: 
Note 287059.1 Library Cache Pin/Lock Pile Up hangs the application 
Note 34579.1  WAITEVENT "library cache pin" Reference 
Note 115656.1 WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK

The implementation of SIMILAR is not optimal.  Even though cursors with literal will have the best plans, the match criteria is based on the values of the literals.  The implementation creates a mini-hash table based on literal values, and if the values are the same, the child cursors are shared.  This behavior. can lead to an application's Shared Pool footprint equivalent to running with CURSOR_SHARING=EXACT.   Instead of creating lots of individual cursors with their own children, SIMILAR creates lots of child cursors under the same parent cursor object.  If you see hard parses at 50/sec or higher (Snapshot/AWR), CURSOR_SHARING=EXACT can be helpful.

To Gauge the effectiveness of your current setting for this parameter, refer to Note 208918.1

• CURSOR_SPACE_FOR_TIME

If this parameter is set, Oracle does not unpin the library cache object at the end of execute.  This means that the active amount of memory pinned within the library cache increases as more cursors are opened and executed, reducing the amount of memory that can be aged out of the Shared Pool.  This parameter must be used carefully and with knowledge about the total application footprint within the Shared Pool.  Setting of this parameter without this knowledge can lead to ORA-04031 errors.  Cursors that are on the Session Cached Cursor list do not have their SQL area heaps pinned.

Some sites use this parameter because it avoids the library cache pin and unpin code path.  This is not a recommended parameter to set for performance.  It is very difficult to know whether it is safe to set this parameter.

NOTE:  This parameter can help with datapump performance in 10g.   If investigating ORA-04031 errors in the "sql area" area in the Shared Pool, look for CURSOR_SPACE_FOR_TIME=true.   This can cause the allocations for "sql area" to grow to take up most of the Shared Pool in 10.1.x.

• DB_CACHE_SIZE

Review the size set for the parameter in the RDA. If using SGA_TARGET, this will default to 0. Any hard-coded setting for this parameter when using SGA_TARGET will act as a minimum size for MMAN when it attempts to shrink the Buffer Cache.

• DB_nK_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• DB_KEEP_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• DB_RECYCLE_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• JAVA_POOL_SIZE

If using SGA_TARGET, this will default to 0. Any hard-coded setting for this parameter when using SGA_TARGET will act as a minimum size for MMAN when it attempts to shrink the Java Pool. 

If you are not using SGA_TARGET, the default size for this memory area is usually sufficient unless your environment utilizes a lot of JVM objects.

@ NOTE: In some cases, an ORA-04031 error will occur like
@

If you need to see more detail on how the memory is allocated in the Java Pool, you can issue 

• LARGE_POOL_SIZE

The Large Pool is intended to offload larger memory allocations from the Shared Pool related to Shared Server (UGA), Parallel Processing (Buffer allocations), and RMAN backup operations, and sequential file IO (e.g. IO slave activity).  If you are not using these specific functionality areas, you can set the Large Pool to 0.  The Large Pool does not use a Least Recently Used (LRU) algorithm, so until a session releases larger memory allocations, they remain in the Large Pool.

If you need to see more detail on how the memory is allocated in the Large Pool, you can issue

If using SGA_TARGET, the parameter will show as 0, but you can hard-code a minimum size and MMAN will not attempt to shrink the Large Pool below that setting. 

• LOG_BUFFER

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• OPEN_CURSORS

This parameter sets the upper bound for the number of cursor that a session can have open.  Normally, cursors are opened through an OCI call or through a PL/SQL call to open a cursor.

In versions of Oracle prior to 9.2.0.5, OPEN_CURSORS was used as a cache for PL/SQL cursors.  When PL/SQL closes a cursor PL/SQL has opened, it sees if it can cache the cursor using one of the cursors allocated through OPEN_CURSORS.  The cursor is not really closed, but may be closed and replaced by another cached cursor or a cursor the application explicitly opens.  If an application opened very few cursors using OCI, it could still have many open cursors for PL/SQL's cursor cache if the application uses PL/SQL and the PL/SQL opened and closed many different cursors.

If a user explicitly opened a cursor and the session could not open a new cursor (the number of open cursors was equal to OPEN_CURSORS), one of the cached cursor would be closed so the explicit open would succeed.

Reference:
Note 76684.1  Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
Note 208857.1  SCRIPT. - to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

• PROCESSES / SESSIONS

These processes will impact the size of the shared pool starting in 9.2.x. Memory structures are located in the Shared Pool with 9.2.x to store dynamic parameter setting information per session/process.  NOTE:  The memory structure can take as much as 20 bytes per parameter for 32-bit databases and 32 bytes per parameter for 64-bit databases. At 10.2.x, there are over 1300 dynamic parameters, so this can add up quickly with a lot of users on the database.  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.

SESSION_CACHED_CURSORS cursors are not part of the open_cursors statistic; it is a separate list. Instead of being really closed, Oracle places the cursor on a session-private LRU list and keeps the cursor available for a subsequent parse. If the user executes a new statement, it will first search the Session Cached Cursor list and, if found, uses it.

This parameter was originally conceived for Oracle FORMS applications. To parallelize FORMS development, customers often developed separate forms and navigated from form. to form. The FORMS behavior. is to close all cursors from the previous form. before navigating to the next form. This parameter allowed Oracle to "cache" closed cursors and reused them if the application opens a cursor for the same statement.

Setting this parameter to a high value increases the amount of Library Cache memory (monitored by reviewing the view V$SGASTAT).

In 9.2.0.5 onwards, this parameter has also been overloaded to control the number of cursors PL/SQL caches using cursors allocated through the setting for OPEN_CURSORS.

Reference:
Note 270097.1 ORA-4031 and Very Large Library Cache in Oracle 9.2 with Session_cached_cursors set. Library Cache Pin/Lock Pile Up hangs the application
Note 274496.1 ORA-7445 and ORA-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used

• SGA_TARGET

If this parameter is set, the MMAN process will attempt to grow and shrink auto-tuned memory components.  Interestingly, on 10.2, if you specify in the spfile an explicit setting for SGA_TARGET higher than the explicit value for SGA_MAX_SIZE the next startup will ignore the prior setting for SGA_MAX_SIZE and set it equal to the new SGA_TARGET setting.  This is not the behavior. at 11g.

• SHARED_POOL_SIZE

If using SGA_TARGET, this will default to 0, but a hard-coded setting for this parameter will act as a minimum size for MMAN when it attempts to shrink the Shared Pool.   With 9i and 10g, more SGA fixed memory structures have been moved to the Shared Pool.   This means that when upgrading from Oracle7 and Oracle8/Oracle8i, you must perform. additional tuning analysis on the 9i or 10g memory needs for the Shared Pool.   With 9i and higher, Oracle also implemented a new subpool functionality.   This can require additional tuning analysis as the application(s) will utilize memory differently.  In some cases too many subpools inside the Shared Pool, can cause one of the subpools to be over-utilized and lead to ORA-4031 problems.

Reference:

Note 270935.1 Shared pool sizing in 10g

If you need to see more detail on how the memory is allocated in the Shared Pool, you can issue

• SHARED_POOL_RESERVED_SIZE

This parameter defaults to 5% of the SHARED_POOL_SIZE setting. When using SGA_TARGET, this will be adjusted as the Shared Pool component grows and shrinks automatically.   If you are seeing consistent ORA-4031 errors that indicate memory request failures larger than 4000 bytes, the 5% default value may not be sufficient in your application environment.   You can change the hidden parameter, _shared_pool_reserved_pct, to 10.  This will cause the Reserved Area to utilize 10% of the Shared Pool.  For example,

• STATISTICS_LEVEL

There are additional memory structures in 10g related to Statistics tracking. This parameter controls all major statistics collections or advisories in the database and sets the statistics collection level for the database. The parameter can be set to BASIC, TYPICAL, or ALL.

The default setting of TYPICAL will put strain on the Shared Pool unless you tune the Shared Pool to accommodate the ongoing analysis activity in the database.  In some performance tuning cases, it is necessary to set STATISTICS_LEVEL to ALL. This will use more memory in the Shared Pool than the other settings, so using ALL can cause ORA-4031 problems if the Shared Pool is not tuned to handle the additional memory needs.

In some known bugs on 9i and 10g, the workaround is to set STATISTICS_LEVEL to BASIC. This uses the least Shared Pool memory, but you give up self-tuning functionality (Memory, Advisors, Object Statistics management, etc.).

• STREAMS_POOL_SIZE

This is new memory pool in 10g.  It is intended to alleviate stress on memory structures in the Shared Pool related to Streams operations.  Review the size set for the parameter in the RDA.

If using SGA_TARGET on 10g Release 2, the parameter will be auto-tuned and will show up as 0.  You can hard-code a minimum size with 10g and Release 2 and MMAN will not attempt to shrink the Streams Pool below that setting.

If you need to see more detail on how the memory is allocated in the Java Pool, you can issue

Also review any settings that would indicate the use of Shared Server (MTS_SERVERS, MTS_DISPATCHERS, etc).  If these parameters indicate that Shared Server is configured, you should not see memory structures in the Shared Pool related to Shared Server 

NOTE:  Some of the parameters listed above are only applicable if using SGA_TARGET.  Be sure to investigate exactly how much memory MMAN can work with to grow and shrink the auto-tuned memory components.