Within the library cache, hit ratios can be determined for all dictionary objects that are loaded. These include table/procedures, triggers,
indices, package bodies, and clusters. If any of the hit ratios fall below 75 percent, you should add to the shared_pool_size. Proper sizing of the shared pool can reduce resource consumption in at least three ways:
- Parse time is avoided if the SQL statement is already in the shared pool. This saves CPU resources.
- Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
- I/O resources are saved, since dictionary elements which are in the shared pool do not require disk access.
Setting cursor_space_for_time parameter to FALSE will accelerate executions within the library cache. This tells Oracle that a shared SQL area
can be deallocated from the library cache to make room for a new SQL statement.
Setting cursor_space_for_time to TRUE means that all shared SQL areas are pinned in the cache until all application cursors are closed. When set to TRUE,
Oracle will not bother to check the library cache on subsequent execution calls because it has already pinned the SQL in the
cache. This technique can improve the performance for some queries, but cursor_space_for_time should not be set to TRUE if there are cache misses on execution calls.
Cache misses indicate that the shared_pool_size is already too small, and forcing the pinning of shared SQL areas
will only aggravate the problem.
Another way to improve performance
in the library cache is to use the
init.ora session_cached_cursors parameter.
In the next lesson, you will learn about pinning packages in the shared pool.