3
Managing Oracle8i Cache

To manage your Oracle8i Cache environment, you use Cache Manager, a component of Oracle DBA Studio. Cache Manager provides a Java interface that lets you start and stop caching for one or more middle-tier caches, monitor the ratio of hits to misses, modify which data is cached, and modify the synchronization policy.

Enabling and Disabling Caching

Occasionally, as when you perform maintenance on a node, upgrade software, or change the memory allocation for a cache, you may need to start or stop a cache. Likewise, when you want to test the performance of the queries without Oracle8i Cache, you can pause the routing of queries to a middle-tier cache.

Using Cache Manager, you can start or stop caches or pause or resume caching on one or more caches. The General page shows the current state of the cache and lets you change the state:

• Stopped: Indicates that the cache is currently stopped. As a result, the queries are routed to the origin database. If a cache is stopped, you can only view general information about it. You cannot add or remove tables or synchronize a stopped cache. Statistics are not gathered for the cache while it is stopped.

• Paused: Indicates that the cache is running, but that queries are routed directly to the origin database. If a cache is paused, you can view all information about the cache. You can add and remove tables and synchronize a paused cache. Some statistics are gathered while the cache is paused, but the Hit/Miss Percentage and Hit/Miss Count per Second statistics are not gathered.

• Running: Indicates that the cache is running and queries are routed to the cache. You can perform all operations on the cache and all statistics are gathered.

  
  

Select an option to change the state of the cache:

• To start a stopped cache, click Running. Oracle8i Cache starts the selected cache and enables routing of queries to the cache.

• To disable the routing of queries to the cache, click Paused. Oracle8i Cache does not stop the cache, but it routes queries directly to the origin database.

• To resume the routing of queries to a cache that is paused, click Running.

• To stop a running or paused cache, click Stopped. Oracle8i Cache stops the selected cache and routes queries directly to the origin database.

When you click Apply, Oracle8i Cache changes the state of the cache.

Note: If you stop a cache and then restart it, you must also restart your application so that the application can reconnect to the cache.

To start or stop caches or to pause or resume query routing on all caches, select the Caches folder and choose one of the following from the Object menu:

• Start All

• Stop All

• Pause All

A progress box displays the results of the operation. When the operation is complete, click Close to close the progress box.

Monitoring Your Oracle8i Cache Environment

Cache Manager helps you to monitor your Oracle8i Cache environment. It provides information about which queries are hits and which are misses, as well as information about hits and misses per second, processor time, and memory usage.

To monitor a cache, select the cache from the Navigator tree. Cache Manager displays the property sheet for the cache.

Monitoring Hit and Miss Statistics

To view information about which queries are hits and which are misses, select the Hit/Miss Statistics tab. A hit is a query that was satisfied in the middle-tier cache. A miss is a query that could not be satisfied in the cache and that was routed to the origin database.

By selecting an option from the View box, you can display statistics for the following categories of queries:

• Most frequent queries, both hits and misses

• Most frequent queries, hits only

• Most frequent queries, misses only

• Least frequent queries, both hits and misses

• Least frequent queries, hits only

• Least frequent queries, misses only

The page shows the following information about the top 50 queries in the selected category:

• Whether or not each query was a hit or a miss

  Yes denotes a hit; No denotes a miss. Unknown denotes that Oracle8i Cache cannot determine whether the query is a hit or a miss, because new data has recently been cached and the query has not yet been reissued. The next time the query is issued, Oracle8i Cache will determine whether the query is a hit or a miss. It attempts to find the data in the cache. If the query cannot be satisfied in the cache, Oracle8i Cache routes the query to the origin database and marks the query as a miss. If the query can be satisfied in the cache, Oracle8i Cache returns the data to the user and marks the query as a hit.

• The number of times the query was executed since the statistics were last reset

• The text of the queries that were executed

• The name of the user who executed the query

If a query is a miss, but has been executed many times, consider adding the tables listed in the query to the cache.

Sometimes, the query may be marked as a miss, but the table is already cached. In this case, the user information in the cache may need to be updated. For example, the user's password may have changed in the origin database, but that information has not been propagated to the cache. To update the user information in the cache, select the cache from the Navigator tree and choose Update User List from the drop-down menu.

To refresh the statistics, click Refresh. However, even when you click Refresh, you may not receive the most up-to-date statistics, because the statistics are gathered once every 5 minutes.

Resetting Hit and Miss Statistics

You can reset the hit and miss statistics for the cache by clicking Reset. Consider resetting when you add or remove several tables from the cache. Resetting the statistics resets the hit and miss count to zero, making it easier to see which queries are being executed most frequently at the current time.

Monitoring Performance

To view other performance information about the cache, select the Performance Monitor tab.

The page shows the following:

• Hit/Miss Percentages Chart: The percentage of hits and misses for a cache

  This chart shows you the percentage of hits (Hit Percentage) and the percentage of misses (Miss Percentage) for the cache. Other Percentage refers to the percentage of requests that were requests other than queries, such as updates, inserts, or deletes. Reviewing this information can help you determine whether or not you have cached the appropriate data.

  If the Hit Percentage is high or the Miss Percentage is low, you have probably cached the appropriate data. If this is not the case, select the Hit/Miss Statistics tab. For View, select Most Frequent Queries, Misses Only. Consider caching the tables that are accessed by the top queries in this category.

  If the Other Percentage is high, your application may not be ideal for Oracle8i Cache. Consider moving the update, insert, or delete requests to another application.

• Memory/Disk Ratio Chart: The ratio of queries (hits) that are satisfied using the cache memory instead of using data stored on disk

  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. If the percentage is low, consider increasing the amount of memory allocated to the cache. (First, check the Memory Swap Data chart to verify that you have available memory on your system. Then, select the Cached Tables tab and specify the new amount in the Memory Allocated box.)

• Hit/Miss Count per Second Chart: The number of hit queries per second for the cache and the number of miss queries per second for the cache

  This chart, along with the Hit/Miss Percentage chart, helps you to determine if you have cached the appropriate data.

• CPU Utilization Chart: The CPU usage or non-idle processor time for the cache node

  For caches on UNIX systems, this chart shows the CPU utilization for all processes on the cache node. For caches on Windows NT, this chart shows the non-idle processor time utilization for all processes on the cache node. This chart shows you whether the CPU of the cache node is overloaded or underutilized.

• Memory/Swap Data Chart: The memory swap data or the memory usage for the cache node

  This chart shows whether the memory of the cache node is overloaded or underutilized. The numbers refer to the memory on the cache node.

You can customize the charts and view additional information by right-clicking. The context menu shows the options for that chart.

Modifying Which Data Is Cached

Using Cache Manager, you can add tables to one or more caches or remove tables from one or more caches, as the following sections describe.

Note: When you add a table to or remove a table from a cache, Oracle8i Cache changes the status of all queries that were marked as misses to "Unknown." When Oracle8i Cache receives a query marked as Unknown, it attempts to find the data in the cache. If the query cannot be satisfied in the cache, Oracle8i Cache routes the query to the origin database and marks the query as a miss.

Adding Tables to One Cache

You can add tables to one cache by using the property sheet of the cache. Take the following steps:

1. From the Navigator tree, select the cache.

2. Select the Cached Tables tab and then click the Add Cached Tables icon.

3. Cache Manager displays the Add Cached Tables dialog box.

   
   

   To add a table to the cache, expand the schemas and select the table from the Available Tables list. Click the right arrow (>) to move the table to the Cached Tables list.

   You can also remove tables using this dialog box by selecting the table from the Cached Tables list. Click the left arrow (<) to move the table to the Available Tables list.

   As you move tables to or from the Cached Tables list, Cache Manager updates the information in the Disk Space Available and Disk Space Required for Cached Tables boxes. If the tables use more disk space than the Disk Space Available, Cache Manager warns you that it cannot add all of the tables. It adds as many tables as the disk space allows.

4. Click OK.

5. Click Apply.

When you add very large tables to a cache, the operation may take some time to complete.

Adding Tables to One or More Caches

To add tables to one or more caches at the same time, use the Add Tables Wizard. With the wizard, you can add tables to one or more caches and specify the synchronization policy for each table.

To invoke the Add Tables Wizard, select the Caches folder from the Navigator tree and choose Object -> Add Tables to Caches. Then, take the steps outlined in "Using the Add Tables Wizard to Set Up Caches".

Removing Tables from One Cache

You can remove tables from one cache by using the property sheet of the cache. Take the following steps:

1. From the Navigator tree, select the cache.

2. From the Cached Tables tab, select the table and click the Remove Cached Tables icon.

3. Click Apply.

Removing Tables from One or More Caches

To remove tables from one or more caches, you can use the Remove Tables Wizard. Take the following steps:

1. To invoke the wizard, select the Caches folder from the Navigator tree and choose Object -> Remove Tables from Caches.

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

3. The Select Caches page shows the caches from which you can remove tables. By default, the Available Caches list is empty because all available caches are listed in the Selected Caches list. Click the left arrow (<) to move caches from the Selected Caches list so that the list contains only those caches from which you want to remove tables.

   
   

4. Click Next.

5. In the Select Tables page, the Cached Tables list shows the tables that are cached in one or more of the caches. In the Cached Tables list, expand the schemas and select the tables that you want to remove from the caches. Click the right arrow (>) to move the tables to the Tables to Be Removed list.

   
   

6. Click Next.

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

The wizard displays a progress box. When it completes the operation, the selected tables are removed from the caches.

Modifying Characteristics of a Cache

Using the property sheet of a cache, you can change the characteristics of that cache, including the memory and disk space allocated to the cache and the synchronization policy of a table.

Modifying the Memory Allocated to a Cache

To change the amount of memory allocated to a cache, take the following steps:

1. From the Navigator tree, select the cache.

2. From the Cached Tables tab, enter the new amount in the Memory Allocated box.

   
   

3. Click Apply.

Note that the change will not take effect until the next time the cache and applications are started.

Modifying the Disk Space Allocated to a Cache

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

• Add a new file to hold additional disk space

• Increase the amount of disk space in a particular file

• Decrease 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.

     
     

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.

You can delete a file if you have not yet clicked Apply to add the additional file. The row will display an icon in the left column. To delete the file, take the following steps:

1. From the Change Allocated Disk Space dialog box, select the file.

2. Click the Remove File icon.

3. Click OK, then Apply.

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 increases the size of the data file.

To decrease disk space allocated to the cache, you can decrease the size of one or more existing files. Take the following steps:

1. In the Size column for a particular file, enter the new size. It must not be less that the amount of disk space shown in the Used column.

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

3. On the property page, click Apply. Cache Manager decreases the size of the file.

Modifying the Synchronization Policy

The synchronization policy establishes how and how often the data in the cache is refreshed from the origin database. When you set up a middle-tier cache, you specified the synchronization policy for each table in that cache. Sometimes, though, the frequency or extent at which the data in the origin database is modified does not stay the same, necessitating a change to the synchronization policy for a table.

For example, for an online catalog, prices and merchandise are changing more rapidly than when you first created the cache. Instead of refreshing the data once a week, you want to refresh it once a day.

To modify the synchronization policy for a table, take the following steps:

1. From the Navigator tree, select the cache.

2. Select the Cached Tables tab. This page displays the tables in the cache and the synchronization policy for each.

3. For each table that you want to modify, click in the Synchronization Policy column and select an option from the drop-down list.

   
   

4. 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.

   For example, for the table BOOKS, you could select a start time of 02-Feb-2000 3:00 AM, an interval of 1, and a unit of 1 Week. In this case, the table would be refreshed once a week at 3:00 a.m., beginning on February 2.

5. Click Apply.

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.

Synchronizing Data

When the synchronization policy of a table is not scheduled, you must initiate the synchronization of the table using Cache Manager. Even if the synchronization policy is Scheduled, you may decide that you want to initiate the action, rather than wait until the next scheduled synchronization.

For example, the table SPORTSWEAR for your online catalog is scheduled for synchronization in two days, but the data in the origin database has changed substantially with the addition of new spring clothing and markdowns on winter clothing. You do not want to wait for the scheduled synchronization to update the data in the caches.

Cache Manager lets you synchronize tables in two ways:

• You can synchronize tables in one cache by using the Cached Tables tab.

• You can synchronize tables in more than one cache by using the Synchronize Tables Wizard.

The following sections describe these methods.

Synchronizing Data in One Cache

To initiate the synchronization of some or all of your tables in one cache, take the following steps:

1. Select the cache from the Navigator tree.

2. From the Cached Tables tab, click the check box in the Synchronize Now? column for the table or tables you want to synchronize.

   
   

3. Click Apply.

If the synchronization policy of a table is Complete, Cache Manager performs a Complete synchronization on the selected table immediately. Similarly, if the synchronization policy of a table is Incremental, Cache Manager performs an Incremental synchronization immediately.

Synchronizing Data in More than One Cache

To initiate the synchronization of some or all of your tables in more than one cache, you use the Synchronize Tables Wizard. The wizard and Oracle8i Cache synchronize the tables in parallel. For example, if you want to synchronize TABLE_A and TABLE_B on Cache1 and Cache2, the wizard starts the synchronization of TABLE_A on both Cache1 and Cache2. The operations on multiple caches run in parallel. When the synchronization of TABLE_A is completed on Cache1, the wizard starts the synchronization of TABLE_B on Cache1.

If the synchronization policy of a table is Complete, the Synchronize Tables Wizard performs a Complete synchronization on the table immediately. Similarly, if the synchronization policy of a table is Incremental, the Synchronize Tables Wizard performs an Incremental synchronization immediately.

To synchronize tables immediately, take the following steps:

1. From the Navigator tree, select the Caches folder. Choose Object -> Synchronize Cached Tables.

2. Cache Manager displays the Synchronize Tables Wizard Welcome page. Click Next.

3. The Select Caches page shows the caches that you can synchronize. By default, the Available Caches list is empty because all available caches are listed in the Selected Caches list. To synchronize tables in only particular caches, select the caches that you do not want to synchronize and click the left arrow (<) to move those caches to the Available Caches list.

4. Click Next.

5. The Select Tables page shows the tables that are cached in one or more of the selected caches and the synchronization policy of each table. In the Cached Tables list, expand the schemas and select the tables that you want to synchronize. Click the right arrow (>) to move the tables to the Tables to Be Synchronized list.

   
   

   If the synchronization policy is listed as Various, the table is cached in more than one cache and the synchronization policy of the table is not the same in all caches.

6. Click Next.

7. The wizard displays the summary page. Click Finish.

Oracle8i Cache immediately synchronizes the selected tables using the synchronization policy that is specified for each table. That is, if a table has a synchronization policy of Complete (whether or not it is Scheduled), Oracle8i Cache immediately performs a complete synchronization. If a table has a synchronization policy of Incremental (whether or not it is Scheduled), Oracle8i Cache immediately performs an incremental synchronization.

If the tables are very large and the synchronization method is Complete, the operation may take some time to complete. Similarly, if the tables are very large and the synchronization method is Incremental and there are many changes to the data in the origin database, the operation may take some time to complete.

Updating Users

When you install Oracle8i Cache, the installation procedure reads the list of users in the origin database and propagates the list of users and the attributes of the users to the cache. (The procedure copies only the user-defined database users, not system-defined database users, such as SYS and SYSTEM.) As a result, you can cache tables from the schemas associated with these users in your cache, and applications can access data owned by these users.

If a new user-defined database user has been added to the origin database, you must add that user to the cache before you can cache tables from that user's schema. Similarly, if an attribute, such as the password, of an existing user is modified in the origin database, you must update the attribute in the cache. If you do not update the password, for example, applications invoked by that user will not be able to access data in the cache.

If a user is dropped from the origin database, you must drop the user from the cache. If you do not, queries on tables in that user's schema continue to access the data in the cache. In addition, synchronization of the table may fail.

To add or drop a user or update the user's attributes, use Cache Manager to update the list of users for one cache or for all caches associated with an origin database. To maintain consistency between the origin database and all caches, Oracle Corporation recommends that you update users for all caches at the same time.

To update the list of users and their attributes for all caches associated with one origin database, take the following steps:

1. From the Navigator tree, select the Caches folder.

2. Right-click, then choose Update All User Lists from the drop-down menu.

3. Cache Manager displays a confirmation box. Click OK.

Cache Manager updates the user list for all caches and displays a progress box. If Cache Manager cannot update the user list for a cache, perhaps because the cache is not running, it displays a message.

To update the list of users and their attributes for one cache, take the following steps:

1. From the Navigator tree, select the cache.

2. Right-click, then choose Update User List from the drop-down menu.

3. Cache Manager displays a confirmation box. Click OK.

Cache Manager updates the user list for the selected cache and displays a progress box.

To see the date when the user list was last updated for a cache, check the User List Last Updated at field on the General page.

Note: For applications to make use of the updated user list, you must restart your application.

Using the Configuration Assistant to Reconfigure a Cache

To make most changes to a middle-tier cache, such as adding or removing tables or modifying the memory or disk space allocation, you can use Cache Manager, the management interface for Oracle8i Cache.

However, you must use the Oracle8i Cache Configuration Assistant to reconfigure the following characteristics of the cache:

• Changing the host name for the middle-tier cache

• Changing the port number for the listener for Oracle8i Cache

In addition, you can use the Configuration Assistant to change the cache name, although you can also accomplish this using Cache Manager.

For example, to modify a cache on a Sun Solaris system, invoke the Configuration Assistant by using the following commands on the cache node:

$ cd $ORACLE_HOME/bin
$ wtacca

The Configuration Assistant displays the Welcome page. To modify your cache, click Next and take the following steps:

1. On the Origin Database Credentials page, enter the following information:
   • For User Name, enter the name of a user on the origin database who has the SYSDBA role.

   • For Password, enter the password of the specified user.

     
     

   If you enter a user name that is not valid or that does not have SYSDBA privileges, or if you enter an invalid password, the Configuration Assistant returns an error and allows you to enter another value.

2. Click Next.

3. On the Cache Information page, you can modify the following information:
   • For Cache Node, if your system contains more than one network card, you can enter a host name for the middle-tier node.

   • For Port Number, you can change the port number for the listener for Oracle8i Cache. You can enter any valid port number that is not in use.

   • For Cache Name, you can change the name of the cache. You can enter any string that uniquely identifies the cache, although Oracle Corporation recommends that you use only alphanumeric characters. Do not use a single quotation mark (').

     
     

4. Click Next.

5. The Summary page provides information about the origin database, the cache, and the changes to be made. Click Finish.

The Configuration Assistant displays a progress box informing you of the results of the modifications.