4
Tuning Your Oracle Database Cache Environment

In most cases, after you use Cache Manager to set up your Oracle Database Cache environment, you do not need to take any further action to realize improvements in throughput and performance. However, this chapter provides some hints that may be useful, depending on your configuration.

Linking Applications That Use Static OCI Libraries

To take advantage of the benefits of Oracle Database Cache, you do not need to modify your applications. Applications that use SQL statements to access the database are routed to the middle-tier cache automatically when you have defined the environment variable ORA_OCI_CACHE.

Similarly, applications that are linked with Oracle Call Interface (OCI) using dynamic libraries and layered directly on OCI are routed to the cache automatically. Dynamic libraries, also called shared libraries, are libraries that are linked into the application at runtime. (Applications layered directly on OCI are those applications that call OCI directly or use ODBC, JDBC-OCI, or other interfaces that call OCI directly.)

Applications that are linked with OCI using static libraries and layered directly on OCI require that you relink them with the OCI library in the Oracle Database Cache Oracle home. Static libraries are libraries that are linked together into a single executable program with objects of the whole application.

For more information about linking applications with OCI, see the Oracle Call Interface Programmer's Guide and the Oracle8i Administrator's Reference for your platform.

Controlling Which Applications Use Oracle Database Cache

All applications that are started from and running in a process in which the environment variable ORA_OCI_CACHE is set to "1" and that call OCI (directly or indirectly), use the Oracle Database Cache middle-tier cache. (See "Configuring Your Application Environment" for information about this environment variable.) If ORA_OCI_CACHE is not set, applications will not use Oracle Database Cache.

If you want to exercise greater control over which applications use Oracle Database Cache, you can use a combination of the environment variable ORA_OCI_CACHE and either the OCI_CACHE or OCI_NO_CACHE value to the mode parameter for the OCIEnvCreate() function call. Setting the mode creates an environment handle with the characteristics of that mode.

To specify that an application uses the Oracle Database Cache middle-tier cache, specify OCI_CACHE as a value to the mode parameter for the OCIEnvCreate() function call. To specify that an application does not use Oracle Database Cache, specify OCI_NO_CACHE as a value to the mode parameter for the OCIEnvCreate() function call.

The following shows an example of specifying OCI_CACHE in the OCIEnvCreate() function call.

OCIEnvCreate((dvoid**)&envhp, (ub4) OCI_DEFAULT | OCI_CACHE,
                            (dvoid *)0,
                            (dvoid * (*)(dvoid *, size_t)) 0,
                            (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                            (void (*)(dvoid *, dvoid *)) 0,
                            (size_t)0, (dvoid **)0))

Note the following:

• The OCI_CACHE or OCI_NO_CACHE value to the mode parameter overrides the ORA_OCI_CACHE environment variable setting. It will affect the behavior of all sessions, connections, and statement executions under that environment handle.

• Only one of the two values should be used in an application. If both are used, the environment handle is created with OCI_NO_CACHE.

• After you modify your application, you must recompile and relink the application.

Controlling Which Statements Use Oracle Database Cache

You can control which statements within an application use Oracle Database Cache by using the OCI_ATTR_NO_CACHE statement handle attribute. By default, all statements are routed to the cache.

To specify that a statement not be routed to the cache, use the OCIAttrSet() function to set the value of the attribute to 1. If you set the value to 0 or if the attribute is not set, the statement will be routed to the cache for execution. You use the OCIAttrGet() function to retrieve the value of the attribute.

The following example shows how to use the OCI_ATTR_NO_CACHE attribute:

  if (OCIAttrSet((dvoid *)stmthp1, (ub4)OCI_HTYPE_STMT,
                  (dvoid *) &rem, (ub4)0, (ub4)OCI_ATTR_NO_CACHE,
                  errhp1))

Consider using this attribute to route complex retrieval statements that you want to be executed in the origin database directly to the origin database.

Note the following:

• If caching has been disabled (by not setting the environment variable ORA_OCI_CACHE or by specifying the OCI_NO_CACHE value to the mode parameter for OCIEnvCreate()), this attribute has no effect.

• After you modify your application, recompile and relink the application.

• This attribute is unrelated to the attribute OCI_ATTR_CACHE.

Tuning Hints

The following tips contain information to help you get the best performance from Oracle Database Cache:

• If you need to increase the disk space allocated to the cache, note that performance improves if you allocate the disk space in large amounts. That is, performance is better if you allocate disk space using a few large files rather than many smaller files.

• For better performance, a high percentage of queries should be satisfied by the data in cache memory, because access to memory is much faster than access to disk. The optimal range is 90% or higher. To see the percentage, select the Performance Monitor tab and check the Memory/Disk Ratio chart. If the percentage is low, consider increasing the amount of memory allocated to the cache. (Select the Cached Tables tab and specify the new amount in the Memory Allocated box.)