The Shared Pool consists of two memory areas:
Library Cache
This memory area holds the parsed and executable versions of SQL and PL/SQL code.
All SQL statements undergo the following steps during their processing:
• Parsing, which includes syntactic and semantic verification of SQL statements and checking
of object privileges to perform the actions.
• Optimization, where the Oracle optimizer evaluates how to process the statement with the
least cost, after it evaluates several alternatives.
• Execution, where Oracle uses the optimized physical execution plan to perform the action
stated in the SQL statement.
• Fetching, which only applies to SELECT statements where Oracle has to return rows to you. This step isn’t necessary in any nonquery-type statements.
Parsing is a resource-intensive operation, and if your application needs to execute the same SQL statement repeatedly, having a parsed version in memory will reduce contention for latches, CPU, I/O, and memory usage.
The first time Oracle parses a statement, it creates a parse tree. The optimization step is necessary only for the first execution of a SQL statement. Once the statement is optimized, the best access path is encapsulated in the access plan. Both the parse tree and the access plan are stored in the library cache before the statement is executed for the first time. Future invocation of the same statement will need to go through only the last stage, execution, which avoids the overhead of parsing and optimizing as long as Oracle can find the parse tree and access plan in the library cache. Of course, if the statement is a SQL query, the last step will be the fetch operation.
The library cache, being limited in size, discards old SQL statements when there’s no more room for new SQL statements.
Dictionary Cache
The dictionary cache, as mentioned earlier, caches data dictionary information. This cache is much smaller than the library cache, and to increase or decrease it you modify the shared pool accordingly. If your library cache is satisfactorily configured, chances are that the dictionary cache is going to be fine too.
You can get an idea about the efficiency of the dictionary cache by using the following query:
SQL> SELECT (sum(gets - getmisses - fixed)) / SUM(gets)
2 "data dictionary hit ratio" from v$rowcache;
data dictionary hit ratio
-------------------------
.936781093
Usually, it’s a good idea to shoot for a dictionary hit ratio as high as 95 to 99 percent, although Oracle itself sometimes seems to refer to a figure of 85 percent as being adequate. To increase the library cache ratio, you simply increase the shared pool size for the instance.
Comments