Oracle Database Cache Concepts and Administration Guide Release 1.0.2 Part Number A86617-01 |
Library | Service | Contents | Index

# 2
Setting Up Oracle Database Cache

To set up Oracle Database Cache, you use Cache Manager, a component of Oracle DBA Studio. Cache Manager is an easy-to-use, Java-based interface that helps you to set up the middle-tier caches, specify the data to be cached in each, and specify the synchronization policy of each table in each cache.

To cache PL/SQL procedures and to route PL/SQL subprograms (procedures and functions) and anonymous blocks to the cache, you use the PL/SQL procedures that are part of the cache management API. The API is provided by Oracle Database Cache and is loaded into the cache during installation.

Note: The cache management API provides PL/SQL procedures and functions that allow you to use a utility such as SQL*Plus to perform many of the tasks you perform with Cache Manager. For more information about the API, see Chapter 6. Oracle Database Cache also provides views that allow you to use SQL to access information, such as a list of cached objects, from the cache. For more information about the views, see Appendix A.

## Setting Up Oracle Database Cache: General Steps

To set up Oracle Database Cache, you take the following general steps:

1. Design your Oracle Database Cache environment, deciding which data to cache. See "Designing Your Oracle Database Cache Environment".

2. Install Oracle Database Cache on the middle-tier nodes, such as Web or application servers, on which you want to cache data. Then, install the management interface on a client node. See "Installing Oracle Database Cache".

3. Invoke Oracle DBA Studio. See "Invoking Oracle DBA Studio".

4. Set up the caches on the middle-tier nodes by specifying the data to be cached in each middle-tier cache and by specifying other characteristics of the cache, such as the synchronization policies, memory allocation, and disk allocation. See "Setting Up the Caches".

5. Optionally, cache PL/SQL packages, procedures, and functions. You should cache only read-only PL/SQL objects. See "Caching PL/SQL Objects".

6. Optionally, enable routing of PL/SQL subprograms and anonymous blocks. See "Enabling and Disabling Routing of PL/SQL".

7. Configure your application environment to use caching. See "Configuring Your Application Environment".

## Designing Your Oracle Database Cache Environment

Before you begin setting up Oracle Database Cache, take time to design your Oracle Database Cache environment. In doing so, you gather the following information:

- What data do you want to cache? See "Determining the Data to Be Cached".

- Where do you want to cache the data? See "Deciding Where to Cache the Data".

- What synchronization policy do you want for each data set? The synchronization policy establishes how and how often the data in the cache is refreshed from the origin database. See "Deciding Synchronization Policies" for information on synchronization policies and your options.

- Do you want to cache any PL/SQL objects (packages, procedures, or functions)? If so, which PL/SQL objects do you want to cache? See "Deciding Which PL/SQL Objects to Cache".

When you have answered these questions, you will have the necessary information to set up and deploy Oracle Database Cache.

### Determining the Data to Be Cached

The first step in designing your Oracle Database Cache environment is determining which data you should cache. To that end, answer the following questions:

- Do your Web site users or application users generate mostly read-only requests?

  Because Oracle Database Cache routes only read requests to the cache, you get the greatest benefit when most of the requests for the data are read-only.

  Read/write transactions may generate some inconsistencies. For example, if a transaction contains a query, then an UPDATE statement for the same table, and subsequently another query of the same data, the data that is returned for the second query will not contain the updated data. The updated data is written to the origin database; the query is executed by the cache.

- What is the most frequently requested data? In other words, what are the most frequently executed queries?

  Your Web sites or applications may have one query that is executed very often or they may have several queries in this category. Before you set up your caches, you can use Oracle Performance Manager or SQL Analyze to help you determine which queries are executed most frequently. Alternatively, you can set up a cache and monitor the queries that are executed by viewing the Hit/Miss Statistics page of Cache Manager (see "Monitoring Hit and Miss Statistics").

- In which database does this frequently requested data reside? In other words, what is the origin database?

  If the data resides in more than one database, which database contains most of the frequently accessed, read-only data? For this release, you can cache data from only one origin database.

- Which database tables contain this data?

  Each query may access data in one or more tables in a database.

- Can the requests tolerate some synchronization delay with data in the origin database?

  That is, does it matter if there is a delay between when new data is entered into the origin database and when that data in the caches is synchronized with the origin database? If a delay is acceptable, then the data is well suited to be cached.

- How often is this frequently requested data updated?

  If the data is updated constantly and extensively and the read-only applications must have current data, you will have to synchronize the data at frequent intervals. See "Deciding Synchronization Policies" for information about synchronizing data.

Now you have identified the data to be cached. Oracle Database Cache caches and manages data in entities called data sets. A data set is any collection of data items that can be expressed using a SQL statement. A data set can be a table, any subset of a table, or data from more than one table.

Note: For this release of Oracle Database Cache, you must cache entire tables; you cannot cache just the parts of tables that satisfy the query. As a result, a data set must be an entire database table.

As an example, after answering the preceding questions, you determine that your company Web site, GreatStore.com, has several frequently requested queries:

- One query asks for the top 100 books sold by your Web site. That data is stored in the BOOKS_ORDERED table, along with information such as book description, author, author's biography, number of pages, and ISBN number. Users can click on the book titles to get more detailed information about each book.

- Six queries request the New York Times best-seller list by category, such as hardcover fiction and paperback nonfiction. That data is stored in the NYT_BEST table. Both tables are stored in the origin database BOOKS.

- Another query requests a page showing the specials on computer software. That data is stored in the table SOFT_SPEC in a different origin database, SOFTWARE.

In this example, you decide that you need to cache the BOOKS_ORDERED and NYT_BEST tables from the BOOKS database and the SOFT_SPEC table from the SOFTWARE database. Each table to be cached is a data set.

### Deciding Where to Cache the Data

Using Oracle Database Cache, you cache the data in a cache on a middle-tier node.

In most cases, a company may have several middle-tier servers, such as Web servers or application servers, that handle all requests. If the data is stored in only one origin database, you can cache the same data on all middle-tier servers. That is, you can have identical caches on all nodes.

In some cases, a company may have several middle-tier servers, but it routes different types of requests to different Web servers. For example, in an online news publishing company, Web servers A and B are responsible for requests for the weather information; Web servers C and D are responsible for requests for sports information. In this situation, you cache different data on Web servers A and B than on C and D.

Note: For this release, data from only one origin database can be cached in any particular cache.

Using the GreatStore.com scenario described in the previous section, you must cache your data on at least two different middle-tier nodes because your data resides in two separate origin databases. You decide to cache data about books in the caches on several middle-tier nodes. You cache identical data sets in these caches. Then, you decide to cache data about computer software in the caches located on several other middle-tier nodes. You cache identical data about computer software in these caches.

### Deciding Synchronization Policies

When you synchronize data, you make the data in the cache consistent with the data in the origin database by refreshing the cache with the current data from the origin database. The synchronization policy establishes how and how often the data in the cache is refreshed from the origin database. You can specify different synchronization policies for each data set.

You can choose incremental or complete synchronization:

- Incremental: Synchronize the data by refreshing only the data that has changed since the data set was cached or since it was last synchronized.

  Use this option when only a portion of the data changes.

- Complete: Completely refresh the data set by deleting all data in the data set and replacing it with the current data from the origin database.

  Use this option when a large percentage of your data changes at the same time, as when the batch updates are loaded into the origin database.

If there is a large amount of data in the data set, a complete synchronization can take considerably more time than an incremental synchronization. However, if a large percentage of data in a data set changes, a complete synchronization is more efficient than an incremental synchronization.

For either of these types of synchronization, you can schedule synchronization at specified intervals or you can use Cache Manager to synchronize the data at any point in time.

When you can predict how often the data needs to be synchronized, schedule the synchronization to occur at specified intervals.

For example, your Web site may list the 100 books that are the top sellers on your Web site. The results of such a query change quite frequently, so you decide to synchronize the data once each hour.

In setting the interval, consider the frequency of updates to the data in the origin database. In addition, consider your environment's tolerance for stale data. If there is low tolerance, synchronize often; if there is high tolerance, synchronize less often.

If you cannot predict how often the data needs to be synchronized, or if you want to synchronize the data at a time other than the scheduled time, you can use Cache Manager to synchronize the data.

For example, a Web site for ordering sporting goods contains a catalog of items. New items are added to the catalog at irregular intervals, making it difficult to predict a schedule for synchronization. In this case, you can synchronize the data when the changes in data in the origin database necessitate it.

When you plan the synchronization of your data, consider carefully how you want the data to be refreshed throughout your Oracle Database Cache environment. You may want to schedule synchronization at a time of day when connectivity is guaranteed or when communication costs are lowest. If your caches or origin database experiences a high volume of transactions during the business day, consider scheduling the synchronization during off-peak hours. Furthermore, you might want to stagger the synchronization to distribute the load on the origin database over time, rather than scheduling the synchronization of many caches at the same time.

If the synchronization intervals are very short or the data changes rapidly, you may see an effect on performance because of the processing required to keep the middle-tier cache synchronized with the origin database.

If the synchronization takes longer than the interval specified, the table will be continually refreshed. For example, if you specify an interval of 5 minutes, but the synchronization takes 6 minutes to complete, the next scheduled synchronization will start immediately after the first one completes.

If two or more requests to synchronize a particular table are issued simultaneously, one of the requests will succeed. The other requests will fail with an error.

Note that while you can easily change the synchronization policy from scheduled to not scheduled, changing the policy from Complete to Incremental or Incremental to Complete consumes more resources and may take considerable time if the table is very large.

### Deciding Which PL/SQL Objects to Cache

You can cache PL/SQL objects (packages, procedures, and functions). Consider the following when deciding which, if any, PL/SQL objects to cache:

- You should cache only PL/SQL objects that contain only read requests.

  If you cache PL/SQL objects that contains both read-only and read/write statements, calls to these objects may generate inconsistencies. For example, if a PL/SQL procedure contains a query, then an UPDATE statement for the same table, and subsequently another query of the same data, the first query will return data, but the UPDATE statement will fail. Oracle Database Cache will mark the call to the PL/SQL procedure as a miss.

- Tables that are referred to by the PL/SQL object must be cached before you cache the PL/SQL object.

  If the tables are not cached, you will receive an error when you attempt to cache the PL/SQL object.

## Installing Oracle Database Cache

You install Oracle Database Cache on all nodes in the middle tier where you want to cache data. When you install Oracle Database Cache, you specify which Oracle database you want to use as the origin database. The installation procedure creates a cache on the Oracle Database Cache node and prepares the origin database for use by Oracle Database Cache.

In addition, you install Oracle Enterprise Manager, including DBA Studio. Cache Manager is a component of DBA Studio.

For more information about how to install Oracle Database Cache, see the installation guide.

## Invoking Oracle DBA Studio

Oracle DBA Studio is a Java-based interface that you use to manage your Oracle environment. Cache Manager, a component of DBA Studio, provides the interface to manage your Oracle Database Cache environment and provides wizards that take you through the process of setting up and deploying your Oracle Database Cache environment.

You can start DBA Studio through the Oracle Enterprise Manager console or as a standalone application.

To invoke the Oracle Enterprise Manager console on UNIX systems, such as Sun Solaris, enter the following command:

    oemapp console

To invoke the Oracle Enterprise Manager console on Windows NT systems, from the Windows NT Start menu, select Programs -> Oracle_home_name -> Oracle Enterprise Manager -> Console.

Then, from the console, select Tools -> Database Applications -> DBA Studio or select the Database Applications icon from the toolbar and then select the DBA Studio icon.

Note that Oracle Database Cache does not support the jobs and events subsystem or discovery of cache nodes.

To start DBA Studio as a standalone application on UNIX systems, enter the following command:

    oemapp dbastudio

To start DBA Studio as a standalone application on Windows NT systems, from the Windows NT Start menu, select Programs -> Oracle_home_name -> DBA Management Pack -> DBA Studio.

When you invoke DBA Studio, it displays the Oracle Enterprise Manager Login dialog box:

Text description of the illustration oemlogon.gif

Select Launch DBA Studio standalone. Click OK.

When you log in successfully, DBA Studio is displayed:

Text description of the illustration dba_studio.gif

Before you can connect to an origin database, you must add it to the list in the Navigator tree. Take the following steps:

1. Choose File -> Add Database to Tree.

2. In the Add Databases to Tree dialog box, select Add a database manually.

   Text description of the illustration dbadd.gif

3. In the dialog box, enter the following information:

   - For Hostname, enter the name of a node that contains the origin database.

   - For Port Number, enter the number of the port of the origin database. By default, the port number is 1521.

   - For SID, enter the Oracle system identifier.

   - When you enter the Hostname and SID, DBA Studio completes the Net Service Name.

   Alternatively, if the origin database is listed in the local tnsnames.ora file, it is displayed in the Service Name list. In this case, you can select Add selected databases from your local tnsnames.ora file. Then, select the database service name.

4. Click OK.

DBA Studio lists the origin database in the Navigator tree. When you log in to DBA Studio in the future, the database will be listed in the tree.

To connect to the database, take the following steps:

1. Select the origin database from the Navigator tree and click the Connect icon. DBA Studio displays the Database Connect Information dialog box.

   Text description of the illustration dblogin.gif

2. Enter the following information:

   - For Username, enter the name of a valid user for the origin database. The user must have DBA privileges, because many of the operations you can perform require this level of privileges.

   - For Password, enter the password for the specified user.

3. Click Save As Local Preferred Credentials. This saves the connect information and allows you to access the database in the future without specifying the connect information.

4. Click OK.

DBA Studio authenticates the user, checking that the user is a valid user with the requisite privileges in the origin database. When you log in successfully, DBA Studio expands the database in the Navigator tree.

To manage your Oracle Database Cache environment, select the Caches folder from the Navigator tree. DBA Studio displays information about Cache Manager and a button to view the Oracle Database Cache Quick Tour.

Expand Caches to see the caches associated with the origin database. Oracle Database Cache attempts to attach to the associated caches. The icons for each cache show the state of the cache.

Text description of the illustration dbtree.gif

Now, select a cache from the Navigator tree to see information about that cache:

Text description of the illustration console_annot.gif

The preceding illustration points out some of the features of DBA Studio and Cache Manager:

- The menu bar contains menus that let you perform many tasks on objects such as caches and tables.

- The toolbar contains buttons for the most common functions, such as changing connections to a different origin database, refreshing the Navigator tree, and adding and removing objects from the tree.

- The Navigator tree shows a hierarchical view of your databases and caches. Under the Caches folder, it shows the caches associated with the origin database.

- The property sheet displays information about the cache selected in the Navigator tree.

  When you select a cache, the General property page is displayed, along with tabs for other property pages. If the cache is stopped, only the General page is displayed. If Oracle Database Cache is not running on the node or the node is not reachable, the General property page will appear dimmed and no other property pages will be shown.

For detailed information about each property page, dialog box, and wizard, see the online Help.

## Setting Up the Caches

The caches were created when you installed Oracle Database Cache. Now, you use Cache Manager to specify which data is cached in which middle-tier caches and the synchronization policy for each table in the caches.

Note: The tables are stored in the cache using the same character set as specified as the character set of the origin database.

Cache Manager provides the Add Tables Wizard, which lets you set up and populate one or more caches at the same time. With the wizard, you can add tables to one or more caches and specify the synchronization policy for each table.

When you are setting up your Oracle Database Cache environment for the first time, consider using the Add Tables Wizard to set up one cache. Then, verify and test that you set up the cache in the best way for your environment. When you are satisfied with the setup, you can easily copy it to other caches. The following sections describe how to use this method.

If you accepted the default value for the amount of disk space allocated to the cache, you should consider increasing the amount of disk space before you add tables to the caches. The following section describes how to add disk space.

### Increasing the Disk Space Allocated to a Cache

If you accepted the default value for the amount of disk space allocated to the cache and you plan to cache a large amount of data, you may need to increase the disk space allocated to the cache.

You can increase the amount of disk space available for a cache in the following ways:

- Adding a new file to hold additional disk space

- Increasing the amount of disk space in a particular file

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.

To modify the amount of disk space allocated to a cache, take the following steps to invoke the Change Allocated Disk Space dialog box:

1. From the Navigator tree, select the cache.

2. From the Cached Tables tab, click Change.

3. Cache Manager displays the Change Allocated Disk Space dialog box, which shows the files that hold the allocated disk space for the cache.

To increase the amount of disk space allocated to a cache by adding a new file, take the following steps:

1. In the Change Allocated Disk Space dialog box, enter the following information in the last row:

   - For File Name, enter the name for the file.

   - For File Directory, enter the full specification for an existing directory on the middle-tier node that will hold the file. Note that the naming convention must follow the rules for the middle-tier node.

   - For Size, enter the size, in megabytes, for the file.

     Text description of the illustration incr_disk1.gif

2. Click OK. Cache Manager updates the Disk Space Allocated field on the Cache Tables page.

3. Click Apply. Cache Manager adds the new file to the cache.

To increase the amount of disk space allocated to a cache by increasing the size of an existing file, take the following steps:

1. In the Change Allocated Disk Space dialog box, enter the new amount, in megabytes, in the Size field for a particular file.

2. Click OK. Cache Manager updates the Disk Space Allocated field on the Cache Tables page.

3. Click Apply. Cache Manager adds the additional disk space to the cache.

### Using the Add Tables Wizard to Set Up Caches

To invoke the Add Tables Wizard, select the Caches folder from the Navigator tree and choose Object -> Add Tables to Caches. Then, take the following steps:

1. The wizard displays the Welcome page. Click Next.

2. The Select Caches page shows the caches to which you can add tables. This page displays all caches that are configured for the current origin database and that are running or paused.

   To cache tables in particular caches, select the caches in which you want to cache tables and click the right arrow (>) to move them from the Available Caches list to the Selected Caches list. To remove caches in which you do not want to cache tables from the Selected Caches list, select the caches and click the left arrow (<).

   Text description of the illustration addwiz_cache.gif
3. Click Next.

4. The Select Tables page lets you select which tables to cache in the selected caches. In the Available Tables list, expand the schemas. The list displays all tables that can be added to the caches you selected. If a table is cached in all selected caches, the table will not be displayed in either list.

   From the Available Tables list, select the tables that you want to include in the caches. Click the right arrow (>) to move the tables to the Selected Tables list.

   Text description of the illustration addwiz_table.gif
5. When you have selected the tables, click Next.

6. On the Synchronization Policy Overview page, you indicate whether you want to use the same synchronization policy for all tables in the caches or to specify different policies for some tables.

   To specify the same synchronization policy for all tables, select Yes, use the same policy for every table. If any caches contain any of the tables you selected on the previous page, the wizard will not add those particular tables to those caches and will not change the synchronization policies of those tables on those caches.

   To specify different synchronization policies for one or more tables, select No, let me set a different policy for some tables.

   Text description of the illustration addwiz_sync.gif
7. Click Next.

8. On the Synchronization Policy Method page, you specify the synchronization method for all or most of the tables you have selected. You can select one of the following methods:

   - Incremental: Synchronize the data by refreshing only the data that has changed since the table was cached or since it was last synchronized.

   - Complete: Synchronize the data by completely refreshing the table. When you select this method, Oracle Database Cache deletes all data in the table and replaces it with the current data from the origin database.

     Text description of the illustration addwiz_sync1.gif

   See "Deciding Synchronization Policies" for more information about synchronization policies.

9. Click Next.

10. On the Synchronization Policy Schedule page, you specify when you want the synchronization to occur for most or all of the tables.

    Text description of the illustration addwiz_sync2.gif

    You can choose one of the following:

    - Yes, schedule the synchronization to: The synchronization occurs at specified intervals. Use this option when you can predict how often the data needs to be synchronized.

      If you choose this option, you specify the start time and the interval between synchronizations:

      - In the Start at field, enter the date and time to start the synchronization. You can select each field in the date and time string and use the arrow to select the correct time. Note that the time reflects the time zone defined for the middle-tier node.

      - In the Every field, enter a number for the interval in the first box and select a unit, such as minutes, hours, days, or weeks, in the second box.

    - No, do not schedule automatic synchronization: The synchronization occurs only when you initiate the action. Use this option when you cannot predict how often the data needs to be synchronized.

11. Click Next.

12. If you selected Yes, use the same policy for every table in step 6, skip to step 14.

    If you selected No, let me set a different policy for some tables, Cache Manager displays the next page, Synchronization Policy for Tables.

    Text description of the illustration addwiz_synctab.gif

    For those tables for which you want a different synchronization policy, take these steps:

    1. From the Synchronization Policy column, select the type of synchronization.

    2. If you select Incremental, Scheduled, or Complete, Scheduled, enter the following information:

       - From the Synchronization Start Time column, select the date and time for the synchronization to begin.

       - From the Synchronization Interval column, enter a number for the interval between synchronizations.

       - From the Synchronization Unit column, select a unit of time for the interval, such as minutes, hours, days, or weeks.

13. Click Next.

14. The wizard displays the Summary page. Click Finish.

Oracle Database Cache adds the tables, their data, and their dependent objects to the caches. The wizard displays a progress box that shows each operation as it completes. If an operation results in an error, the wizard displays an error in the progress box and displays the warning when it completes all operations.

### Verifying Your Cache Setup

To verify that you set up your cache correctly, select the cache from the Navigator tree. Cache Manager displays the property sheet for the cache. Then, take the following steps:

1. Select the Cached Tables tab and check that all tables that you want cached are listed. See "Modifying Which Data Is Cached" for information on adding or removing tables.

   Text description of the illustration incr_mem.gif

2. On the Cached Tables page, check the synchronization policy and intervals for each table. To change the synchronization policy for a table, click in the Synchronization Policy column and select an option.

   If you selected Incremental, Scheduled, or Complete, Scheduled, select a Synchronization Start Time. Then, enter a Synchronization Interval and select a Synchronization Unit, such as minutes, hours, days, or weeks.

3. On the Cached Tables page, check Memory Allocated, which indicates the amount of memory allocated to the cache. To change the amount, enter the new amount in the Memory Allocated box.

   Note: The change to memory allocation will not take effect until the next time the cache is started.

4. On the Cached Tables page, check Disk Space Allocated, which indicates the amount of disk space allocated to the cache. To change the disk space allocated, click Change. Cache Manager displays the Change Allocated Disk Space dialog box. You can increase or decrease the size of a particular file or add a new file.

   To increase the disk space by increasing the size of a particular file, enter the increased size in the Size column.

   Text description of the illustration incr_disk.gif

   "Modifying the Disk Space Allocated to a Cache" provides more information about increasing or decreasing the disk space allocated.

5. Click OK.

6. If you made any changes, click Apply.

If the cache is set up correctly, you can enable caching and test the cache.

### Enabling Routing to the Cache

To enable routing of queries to the middle-tier cache, take the following steps:

1. Select the cache from the Navigator tree. Cache Manager displays the property sheet of the cache.

2. On the General page, click Running.

3. Click Apply.

Oracle Database Cache starts the middle-tier cache and enables routing of queries to the cache.

Now you can test the cache to see that you cached the appropriate tables and that you specified appropriate amounts of disk and memory. For information about monitoring caches, see Chapter 3.

If the cache is set up correctly, you can copy the setup to other middle-tier caches.

### Copying Cached Tables to Other Caches

If you have set up one cache and want to duplicate those cache settings to other nodes, you can easily do so using the Copy Cached Tables dialog box:

1. In the Navigator tree, select the cache that you want to duplicate. Right-click and choose Copy Cached Tables.

2. In the Copy Cached Tables dialog box, from the Available Caches list, select the caches to which you want to copy the setup and click the right arrow (>) to move the caches to the Selected Caches list.

   Text description of the illustration copycache.gif

3. Click OK.

Repeat the steps outlined in "Verifying Your Cache Setup" to verify that these caches are set up correctly.

## Caching PL/SQL Objects

To cache PL/SQL objects (packages, procedures, or functions), you use the DBMS_ICACHE.ADD_PLSQL_OBJECT procedure. This procedure is part of the Cache Management API. The API consists of a PL/SQL package, DBMS_ICACHE, that is loaded into the cache during installation.

You can cache PL/SQL packages, procedures, or functions, but you should cache only ones that contain only read requests. See "Deciding Which PL/SQL Objects to Cache" for more information.

Assume that you have a PL/SQL procedure called GET_TOY_ITEMS in the schema ICPB. Because this procedure contains only queries, it is a good candidate for caching. To cache the procedure, take the following steps:

1. Make sure that any tables that are referenced by the GET_TOY_ITEMS procedure are already cached. If they are not, Oracle Database Cache will return an error about compilation and the procedure will not be cached.

2. Connect to the cache using a utility such as SQL*Plus. Connect as a user with SYSDBA privileges:

       connect system/manager @mycache.us.oracle.com AS SYSDBA;

3. Because the DBMS_ICACHE.ADD_PLSQL_OBJECT procedure must connect to the origin database, you must pass user credentials to the origin database. Execute the DBMS_ICACHE.SET_ORIGIN_CREDENTIALS procedure, specifying the user name and password of a user on the origin database who has DBA privileges:

       execute DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ('system','manager');

4. Execute the DBMS_ICACHE.ADD_PLSQL_OBJECT procedure, specifying the schema name and the procedure name:

       execute DBMS_ICACHE.ADD_PLSQL_OBJECT ('ICPB', -
                   'GET_TOY_ITEMS');

5. To verify that the procedure is cached, use the DESCRIBE statement:

       describe ICPB.GET_TOY_ITEMS

See "ADD_PLSQL_OBJECT Procedure" for more information about caching PL/SQL objects.

## Enabling and Disabling Routing of PL/SQL

By default, Oracle Database Cache routes PL/SQL subprograms (procedures and functions) and anonymous blocks to the cache. To disable routing of PL/SQL subprograms and anonymous blocks to the cache, you use the DBMS_ICACHE.SET_FLAG procedure:

    execute DBMS_ICACHE.SET_FLAG ( -
                DBMS_ICACHE.PLSQL_ROUTING_ENABLED, -
                FALSE );

You can use this procedure to enable routing of PL/SQL subprograms and anonymous blocks if it is disabled. See "SET_FLAG Procedure" for more information about this procedure.

To check whether the routing of PL/SQL subprograms is enabled or disabled, you can use the DBMS_ICACHE.GET_FLAG function. See "GET_FLAG Function" for more information about this function.

## Configuring Your Application Environment

To configure your application environment to use Oracle Database Cache, you must set the environment variable ORA_OCI_CACHE and you must ensure that your applications are using the Oracle home in which Oracle Database Cache is installed.

You set the environment variable ORA_OCI_CACHE to the value "1" in the environment from which your application is invoked. All applications invoked from this environment take advantage of Oracle Database Cache.

For example, on UNIX, you use a shell environment variable; on Windows NT, you use an environment variable.

Note: To use Oracle Database Cache, you must run your application from a node on which Oracle Database Cache is installed.

If ORA_OCI_CACHE is not set, applications will not use Oracle Database Cache. (For this release, if you set the environment variable to a value other than "1", caching will be enabled, although only the value "1" will be supported in future releases.)

See "Controlling Which Applications Use Oracle Database Cache" if you use applications that directly call OCI functions and you want to exercise greater control over which applications use Oracle Database Cache.

You must also make sure that your applications are using the Oracle home in which Oracle Database Cache is installed, including OCI libraries from that Oracle home. For example, on UNIX, set the value of the environment variable ORACLE_HOME to the Oracle home for Oracle Database Cache. Set the value of the environment variable LD_LIBRARY_PATH so that the Oracle Database Cache library directory ($ORACLE_HOME/lib) precedes other Oracle library directories.

On Windows NT, from the Start menu, select Oracle for Windows NT -> Oracle Home Selector. Then, select the Oracle home in which you installed Oracle Database Cache. Set the value of the environment variable PATH so that the Oracle Database Cache library directory (ORACLE_HOME\lib) precedes other Oracle library directories.

For more information about using the Oracle home and libraries for Oracle Database Cache, see the installation guide.

| Copyright © 2000 Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index