Use the Cache Manager

The Cache Manager lets you view information about the entire query cache and information about individual entries in the query cache that are associated with the open semantic model.

You can also use the Cache Manager to select specific cache entries and perform various operations on those entries, such as viewing and saving the cached SQL statement, or purging them.

1. In the Model Administration Tool toolbar, select Manage, then select Cache.

2. Click the Cache tab to view the cache entries for the current semantic model, business models, and users.

   The associated cache entries are reflected in the right pane, with the total number of entries shown in the view-only field at the top.

3. Select Edit, then select Options from the Cache Manager, or select Tools, Options, and then Cache Manager from the Model Administration Tool menu to control the cache entry information and its display sequence.

You can expand the semantic model tree to display all the business models with cache entries, and expand the business models to display all users with cache entries. The right pane displays only the cache entries associated with the selected item in the hierarchical tree.

About Cache Manager Options

The Cache Manager can display the options described here.

Option	Description
User	The ID of the user who submitted the query that resulted in the cache entry.
Created	The time the cache entry's result set was created.
Last used	The last time the cache entry's result set satisfied a query. (After an unexpected shutdown of the Oracle BI Server, the last used time might temporarily have a stale value—a value that's older than the true value.)
Creation elapsed time	The time, in seconds, that's needed to create the result set for this cache entry. The value that's stored in the cache object descriptor on disk is in units of milliseconds. The value is converted to seconds for display purposes.
Row count	The number of rows generated by the query.
Row size	The size of each row (in bytes) in this cache entry's result set.
Full size	Full size is the maximum size used, considering variable length columns, compression algorithm, and other factors. The actual size of the result set is smaller than Full size.
Column count	The number of columns in each row of this cache entry's result set.
Logical Request	The logical request that's associated with this cache entry. If subrequests are being cached, then this column shows the text of the subrequest.
Use count	The number of times that this cache entry's result set has satisfied a query since Oracle BI Server startup.
Business model	The name of the business model that's associated with the cache entry.
Repository	The name of the semantic model associated with this cache entry.
SQL	The SQL statement associated with this cache entry. If subrequests are being cached, then there might be multiple cache entries associated with a single SQL statement.
Query Server	The Oracle BI Server that serviced the query.
Fact Table Source	The fact table associated with the logical request for this cache entry.

Display Global Cache Information in the Cache Manager

Select Action, then select Show Info to display global cache information.

The table below describes the information that appears in the Global Cache Information window.

Column	Description
Amount of space still available for cache storage use	The amount of space, in megabytes, still available for cache storage.
Amount of space used on disks containing cache related files	The total amount of space, in megabytes, used on the disk that contains cache-related files (not just space used for the cache-related files).
Maximum allowable number of entries in cache	The maximum number of entries that can be in the cache, from the MAX_CACHE_ENTRIES parameter in the NQSConfig.INI file.
Maximum allowable number of rows per cache entry result set	The maximum number of rows that are allowed for each cache entry's result set, from the MAX_ROWS_PER_CACHE_ENTRY parameter in the NQSConfig.INI file.
Number of entries currently in cache	The current number of entries in the global cache. These entries might relate to multiple semantic models.
Number of queries not satisfied from cache since startup of Oracle BI Server	Cache misses, since the last time the Oracle BI Server was started.
Number of queries satisfied from cache since startup of Oracle BI Server	Cache hits, since the last time the Oracle BI Server was started.

With the Cache Manager as the active window, press F5, or select Action, then Refresh to refresh the display. This retrieves the current cache entries for the semantic model that you have open and the current global cache information. If the DSN is clustered, then information about all semantic models in the cluster is displayed.

Purge the Cache in the Model Administration Tool

Purging the cache deletes entries from the query cache.

You can purge cache entries in the following ways:

• Manually, using the Model Administration Tool Cache Manager facility.

• Automatically, by setting the Cache Persistence Time field in the Physical Table dialog for a particular table.

• Automatically, by setting up an Oracle BI Server event polling table.

• Automatically, as the cache storage space fills up.

Note:

You can also purge the cache programmatically using ODBC-extension functions. See Purge and Maintain Cache Using ODBC Procedures.

In addition, cache can be purged when the value of dynamic or global semantic model variables changes. See Changes to Dynamic or Global Semantic Model Variables.

You can manually purge cache entries in the Cache Manager as follows:

1. Use the Model Administration Tool to open a semantic model.
2. Select Manage, then Cache to open the Cache Manager dialog.
3. Select Cache or Physical mode by selecting the appropriate tab in the left pane.
4. Browse the explorer tree to display the associated cache entries in the right pane.
5. Select the cache entries to purge, and then select Edit, then Purge to remove them. Or, right-click the selected entries and then select Purge.

   • In Cache mode, select the entries to purge from those displayed in the right pane.

   • In Physical mode, select the database, catalog, schema or tables to purge from the explorer tree in the left pane.

   In Cache mode, you can purge:

   • One or more selected cache entries that are associated with the open semantic model.

   • One or more selected cache entries that are associated with a specified business model.

   • One or more selected cache entries that are associated with a specified user within a business model.

   In Physical mode, you can purge:

   • All cache entries for all tables that are associated with one or more selected databases.

   • All cache entries for all tables that are associated with one or more selected catalogs.

   • All cache entries for all tables that are associated with one or more selected schemas.

   • All cache entries that are associated with one or more selected tables.

Purging deletes the selected cache entries and associated metadata. Select Action, then Refresh or press F5 to refresh the cache display.