Shared Pool Concept and Efficient Utilization

For every statement issued against the server, Oracle searches the shared pool to see if the statement has already been parsed.  If an exact text match of the statement is already present in the shared pool a soft parse is performed as the execution plan for the statement has already been created and can be reused.  If the statement is not found in the shared pool a hard parse must be performed to determine the optimal execution path.

The important thing to remember from the previous paragraph is the term “exact text match”, as different numbers of spaces, literal values and case will result in a failure to find a text match, such that the following statements are considered different.

SELECT 1 FROM dual WHERE dummy = ‘X’;

SELECT 1 FROM dual WHERE dummy = ‘Y’;
SELECT 1 FROM DUAL WHERE dummy = ‘X’;

SELECT 1 FROM dual WHERE  dummy = ‘X’;

The first two statements only differ by the value of the search criteria, specified using a literal.  In these situations exact text matches can be achieved by replacing the literal values with bind variables that have the correct values bound to them.  Using the previous example the statement passed to the server might look like this.

SELECT 1 FROM dual WHERE dummy = :B1;

For every execution the bind variable may have a different value, but the text sent to the server is the same allowing for an exact text, which results in a soft parse. 

There are two main problems associated with applications that do not use bind variables:

• Parsing SQL statements is a CPU intensive process, so reparsing similar statements constantly represents a waste of CPU cycles.
• Parsed statements are stored in the shared pool until they are aged out.  By not using bind variables the shared pool can rapidly become filled with similar statements, which waste memory and make the instance less efficient.

The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the server result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The server result cache stores the results of queries and PL/SQL function results.

Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out to accommodate new entries when the shared pool does not have free space. A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.

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.

 Dictionary Cache Concepts

Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.

Library Cache Concepts

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.

When application code is run, Oracle Database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then the database reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle Database cannot use existing code, then the database must build a new executable version of the application code. This is known as a hard parse, or a library cache miss.

Now we need to understand when a SQL and PL/SQL statements can be shared.

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.

Oracle Database performs the following steps to compare the text of the SQL statement to existing SQL statements in the shared pool:

1. 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.
   
   
2. If there is a matching hash value for an existing SQL statement in the shared pool, then Oracle Database compares the text of the matched statement to the text of the statement hashed to see if they are identical. The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments. For example, the following statements cannot use the same shared SQL area:
   SELECT * FROM employees;

   SELECT * FROM Employees;
   
   SELECT *  FROM employees;
   

   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. The costs and benefits involved in using CURSOR_SHARING are explained in "When to Set CURSOR_SHARING to a Nondefault Value".
   
   
3. The objects referenced in the issued statement are compared to the referenced objects of all existing statements in the shared pool to ensure that they are identical.
   References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema. For example, if two users each issue the following SQL statement and they each have their own employees table, then this statement is not considered identical, because the statement references different tables for each user:

   SELECT * FROM employees;
   

4. Bind variables in the SQL statements must match in name, data type, and length.
   For example, the following statements cannot use the same shared SQL area, because the bind variable names differ:

   
   SELECT * FROM employees WHERE department_id = :department_id;
   
   SELECT * FROM employees WHERE department_id = :dept_id;
   

   Many Oracle products, such as Oracle Forms and the precompilers, convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
   
   
5. The session's environment must be identical. For example, SQL statements must be optimized using the same optimization goal.

CURSOR_SHARING parameter

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

It has following Values:

• FORCE
  Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
• SIMILAR
  Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
• EXACT
  Only allows statements with identical text to share the same cursor.

How Similar Statements Can Share SQL Areas

When SQL statements use literals rather than bind variables, a nondefault setting for CURSOR_SHARING enables the database to replace literals with system-generated bind variables. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area.

When CURSOR_SHARING is set to a nondefault value, the database performs the following steps during the parse:

1. Searches for an identical statement in the shared pool
   If an identical statement is found, then the database skips to Step 3. Otherwise, the database proceeds to the next step.
2. Searches for a similar statement in the shared pool
   If a similar statement is not found, then the database performs a hard parse. If a similar statement is found, then the database proceeds to the next step.
3. Proceeds through the remaining steps of the parse phase to ensure that the execution plan of the existing statement is applicable to the new statement
   If the plan is not applicable, then the database performs a hard parse. If the plan is applicable, then the database proceeds to the next step.
4. Shares the SQL area of the statement

When to Set CURSOR_SHARING to a Nondefault Value

The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.

Setting CURSOR_SHARING to FORCE or SIMILAR has the following drawbacks:

• The database must perform extra work during the soft parse to find a similar statement in the shared pool.
• There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement. However, the actual length of the data returned does not change.
• Star transformation is not supported.
• If stored outlines were generated with CURSOR_SHARING set to EXACT, then the database does not use stored outlines generated with literals. To avoid this problem, generate outlines with CURSOR_SHARING set to FORCE or SIMILAR and use the CREATE_STORED_OUTLINES parameter.

When deciding whether to set CURSOR_SHARING to FORCE or SIMILAR, consider the performance implications of each setting. When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement. When set to SIMILAR, database behavior depends on the presence of histograms:

• Histogram absent for column with system-generated bind value.
  Only one parent cursor and one child cursor exists for each distinct SQL statement. In this case, all executions of a SQL statement use the same plan.
• Histogram present for column with system-generated bind value
  If the same SQL statement is executed multiple times, each execution has its own child cursor. In this case, the database peeks at bind variable values and create a new child cursor for each distinct value. Thus, each statement execution uses a plan based on the specific literals in the statement.

For example, consider the following statement:

SELECT * FROM hr.employees WHERE employee_id = 101;

If FORCE is used, or if SIMILAR is used when no histogram exists, then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan. If SIMILAR is used, and if a histogram does exist, then the database does not treat the statement as if a bind variable were used. The same query for a different employee may not use the same plan.

If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment. FORCE limits the growth of child cursors that can occur when the setting is SIMILAR. Also, function-based indexes may not work when using SIMILAR because the database converts index parameters to bind variables. For example, if the index is SUBSTR(id,1,3), then the database converts it to SUBSTR("ID",:SYS_B_0,:SYS_B_1)=:id, rendering the index invalid.

Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to share SQL. The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.