1. Administering Oracle Analytics Server
  2. Scale and Deploy for High Availability and Performance
  3. Manage Performance Tuning and Query Caching
  4. Monitor and Manage the Cache
  5. Purge and Maintain Cache Using ODBC Procedures

Purge and Maintain Cache Using ODBC Procedures

The Oracle BI Server provides ODBC-extension functions for purging cache entries.

Some of these functions are particularly useful for embedding in an Extract, Transform, and Load (ETL) task. For example, after a nightly ETL is performed, all Oracle BI Server cache entries can be purged. If only the fact table was modified, then only cache related to that table can be purged. In some cases, you might need to purge the cache entries associated with a specific database.

Only administrators have the right to purge cache. Therefore, scripts that call these ODBC-extension functions must run under credentials with administrator privileges.

The following ODBC functions affect cache entries that are associated with the semantic model specified by the ODBC connection:

  • SAPurgeCacheByQuery. Purges cache entries that exactly match a specified query. For example, using the following query, you would have one or more query cache entries that retrieve the names of all employees earning more than $100,000: 

    SELECT lastname, firstname FROM employee WHERE salary > 100000;

    The following call purges the cache entries that are associated with this query:

    Call SAPurgeCacheByQuery('SELECT lastname, firstname FROM employee WHERE salary > 100000' );

  • SAPurgeCacheByTable. Purges all cache entries that are associated with a specified physical table name (fully qualified) for the semantic model to which the client has connected.

    This function takes up to four parameters that represent the four components (database, catalog, schema, and table name proper) of a fully qualified physical table name. For example, you might have a table with the fully qualified name of DBName.CatName.SchName.TabName. To purge the cache entries that are associated with this table in the physical layer of the semantic model, run the following call in a script: 

    Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

    Note:

    Wildcards aren't supported by the Oracle BI Server for this function. In addition, DBName and TabName can't be null. If either one is null, then an error message is displayed.

  • SAPurgeAllCache. Purges all cache entries. This function takes one parameter that is a comma-separated list of the names of nodes. The following is an example of this call that purges cache entries on the obis1 and obis2 nodes: 

    Call SAPurgeAllCache('obis1,obis2');

  • SAPurgeCacheByDatabase. Purges all cache entries associated with a specific physical database name. A record is returned when any of the ODBC procedures to purge the cache are called. This function takes one parameter that represents the physical database name, and the parameter can't be null. The following shows the syntax of this call: 

    Call SAPurgeCacheByDatabase( 'DBName' );

About ODBC Procedure Syntax

If there's a single quotation mark within the string argument of a procedure, then you must use another single quotation mark to escape it.

For example:

Call SAPurgeCacheByQuery('SELECT TOPN("- Currency"."Markdown %", 10) saw_0,
"XX Line"."Order No" saw_1, "- Bill-To Site"."Customer Name" saw_2, "-
Currency"."Net USD" saw_3, "- Currency"."Markdown USD" saw_4, "-
Currency"."Markdown %" saw_5 FROM "Apps 11i - XX Lines" WHERE 
("XX Line"."Open Flag" = ''Y'') AND ("Operating Unit"."Group Name" = ''Group'')
AND ("- Currency"."Net USD" >= 10000) ORDER BY saw_0');

The line in bold highlights the extra single quotation marks that are used as escape characters for the items ''Y'' and ''Group''.

About Sharing the Presentation Services Query Cache

When users access Answers to run queries, Presentation Services caches the results of the queries.

Presentation Services uses the request key and the logical SQL string to determine if subsequent queries can use cached results. If the cache can be shared, then subsequent queries aren't stored.

SAGetSharedRequestKey: An ODBC procedure that takes a logical SQL statement from Presentation Services and returns a request key value.

The following shows the syntax of this procedure:

SAGetSharedRequestKey('sql-string-literal')

The value of the request key is affected by the following factors:

  • Whether the Virtual Private Database option is selected in the semantic model physical database object

  • Whether any session variables are marked as Security Sensitive in the seamantic model

Presentation Services takes security sensitive variable values into consideration when computing the request key for logical requests against database objects marked as Virtual Private Databases.

See Manage Presentation Services Cache Settings.

About Result Records

A result record is returned after you issue a purge cache command.

The result record contains two columns. The first column is a result code and the second column is a short message that describes the result of the purge operation. The table below shows examples of result records.

Result Code Result Message

1

SAPurgeCacheByDatabase returns successfully.

59115

Operation not performed because caching isn't enabled.

59116

The database specified doesn't exist.

59117

The table specified doesn't exist.