OBIEE – Purge Server Cache

Caching is a major advantage of obiee.  When a query is run by one user it can be saved as a file to cache; if another user queries for the same information, rather than wait for the information to be fetched from the database it can be reproduced from the cache for a performance improvement.

Purging Repository Cache

This can be done in any of three places; it is quickest to perform the action via the Presentation Services, but you will require Administration privileges.  Alternatively you can purge the cache using the Administration Tool; or you can do it with an OS (Operating System) level command.

Purging the Cache via the Presentation Service

• Select Settings, Administration; the Administration Window will open
• Select to Issue SQL; this window will allow you to issue SQL Directly
• In the command window enter the text below and click to Issue SQL
  • Call SAPurgeAllCache()

 Purging the Cache using ODBC Extension Functions
We can also use the nqcmd executable to issue the same command, taking advantage of ODBC extensions functions.

• Login to the hosting machine and navigate to the Server Bin directory

Linux Command Shell

$ cd /OracleBI/server/Bin$

• Use an editor such as vi to create a file containing the command you want to issue.  The first window shows the command to open the text editor, the next is the contents of the file. 

Linux Command Shell

$ vi purge.txt$

purge.txt

Call SAPurgeAllCache()

•  Follow the instructions listed below to issue the purge command(s)

Linux Command Shell

$ cd /OracleBI/server/Bin$ nqcmd -d “AnalyticsWeb” –u Administrator –p Password –s purge.txt$

 In this example –d is used to identify the datasource, -u username, -p password and –s sql input file
The advantage of the latter approach is that the executable can be scripted to occur at regular intervals.


Reference of ODBC Extension Functions
 In the last example we issued an ODBC extension function to clear the cache programmatically.  We used the command SAPurgeAllCache(); one of three possible commands we could use.l

SAPurgeCacheByQuery

We can issue a command to purge a specific query from the cache; the command takes a single parameter, which should be equal to the exact query registered in the cache.  An example is given.
Call SAPurgeCacheByQuery(‘select lastname, firstname from employee where salary > 100000’ );

SAPurgeCacheByTable

We can select to purge the cache for all queries using a specific table; we must specify the database, physical catalog, schema and table as parameters.  Example below.
Call SAPurgeCacheByTable( ‘DBName’, ‘CatName’, ‘SchName’, ‘TabName’ );

SAPurgeCacheByDatabase

And we can purge the cache for all queries to a specific database.
Call SAPurgeCacheByTable( ‘DBName’ );

SAPurgeAllCache

Finally, we can purge all cache; as we did in our original example above.
Call SAPurgeAllCache();
We can also put multiple commands together in the purge.txt file, as in the example below.

purge.txt

Call SAPurgeCacheByTable( ‘DBNameA’ );Call SAPurgeCacheByTable( ‘DBNameB’ );

 When we run this with the same command as previously all commands will be run.