While capitalizing a single letter, adding an extra space between verbs, or using a different variable name may seem trivial, the Oracle software is not sufficiently intelligent to recognize that the statements are identical. Consequently, Oracle will reparse and execute the second SQL statement, even though it is functionally identical to the first SQL statement.

Another problem occurs when values are hard coded into SQL statements. For example, Oracle considers the following statements to be different:

SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'NEW';

SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'PREFERRED';

This problem is easily alleviated by using an identical bind variable:

SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = :var1;

The best way to prevent reloads from happening is to encapsulate all SQL into stored procedures, bundling the stored procedures into packages. This removes all SQL from application programs and moves them into Oracle’s data dictionary. This method also has the nice side effect of making all database calls appear as functions. As such, we have created a layer of independence between the application and the database. Again, by efficiently reusing identical SQL, the number of reloads will be kept at a minimum and the library cache will function at optimal speed.

The cursor_space_for_time parameter can be used to speed executions within the library cache. Setting cursor_space_for_time to FALSE tells Oracle that a shared SQL area may 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 on the library cache is to use the init.ora session_cached_cursors parameter. As we know, Oracle checks the library cache for parsed SQL statements, but session_cached_cursors can be used to cache the cursors for a query. This is especially useful for tasks that repeatedly issue parse calls for the same SQL statement—for instance, where an SQL statement is repeatedly executed with a different variable value. An example would be an SQL request that performs the same query 50 times, once for each state:

SELECT SUM(sale_amount)
FROM SALES
WHERE
state_code = :var1;

Tuning The Dictionary Cache

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL that is stored in packages. Based on my experience, I highly recommend that all SQL should be stored in packages, so let’s take a look at how packages interact with the dictionary cache.

When a package is invoked, Oracle will first check the dictionary cache to see if the package is already in memory. Of course, a package will not be in memory the first time it is requested, and Oracle will register a “dictionary cache miss.” Consequently, it is virtually impossible to have an instance with no dictionary cache misses, since each item must be loaded once.

The V$rowcache table is used to measure dictionary cache activity. Three columns are of interest. The first column, Data Dict., describes the type of dictionary object that has been requested. The second parameter, gets, provides the total number of requests for objects of that type. The last column, getmisses, counts the number of times that Oracle had to perform a disk I/O to retrieve a row from its dictionary tables.

The data dictionary cache hit ratio is used to measure the ratio of dictionary hits to misses. Bear in mind, however, that this ratio is only good for measuring the average hit ratio for the life of the instance.

The data dictionary cache hit ratio can be measured with the script in Listing 3.4.

Listing 3.4 The script that measures the cache hit ratio.

dict.sql - displays the dictionary cache hit ratio
PROMPT
PROMPT
PROMPT         =========================
PROMPT         DATA DICT HIT RATIO
PROMPT         =========================
PROMPT (should be higher than 90 else increase shared_pool_size in
       init.ora)
PROMPT

COLUMN "Data Dict. Gets"            FORMAT 999,999,999
COLUMN "Data Dict. cache misses"    FORMAT 999,999,999
SELECT sum(gets) "Data Dict. Gets",
       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100)
       "DATA DICT CACHE HIT RATIO"
FROM v$rowcache;

SQL> @t2

This is the output of the script:

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora)
Fri Feb 23                                                      page    1
                               dbname Database
                          Data Dictionary Hit Ratios

Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
-----------------------------------------------------------------
409,288          11,639                   97

1 row selected.

Listing 3.5 measures the contention for each dictionary object type.

Listing 3.5 The script that measures the contention.

ddcache.sql - Lists all data dictionary contention
REM SQLX SCRIPT
SET PAUSE OFF;
SET ECHO OFF;
SET TERMOUT OFF;
SET LINESIZE 78;
SET PAGESIZE 60;
SET NEWPAGE 0;
TTITLE "dbname Database|Data Dictionary Hit Ratios";
SPOOL /tmp/ddcache
SELECT   SUBSTR(PARAMETER,1,20) PARAMETER,
         gets,getmisses,count,usage,
         ROUND((1 - getmisses / decode(gets,0,1,gets))*100,1) HITRATE
FROM     v$rowcache
ORDER BY 6,1;
SPOOL OFF;