Cache Management & Purging Cache in OBIEE 10g

Cache Management is a major advantage of OBIEE. Whenever a saved request or any dashboard is accessed by one user it can be saved as a file to cache. So if any other user accessing the same saved request, instead of waiting for the information to be fetched from the database it can be reproduced from the cache for a performance improvement.

Purging the Cache through the Presentation Service

Select Settings, Administration; the Administration Window will open from that 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()

Now Check the Cache Manager in Administration tool and the cache entries will be cleared there.

Purging the cache By Manual

Open Administration Tool - > Go to Manage -> Click Cache.

The Cache Manager Window will open. In the right side of you can view how many is cached and its related logical query. The logical query will be cached for only the accessed reports in the dashboards.

                            

From the right side pan right click on the cached query and click on ‘Select All’ now the entire query will be selected. Now if you are selecting a mouse right click you will get an option ‘Purge’. Once you clicked the ‘purge’ the entire cached query will be deleted.

                         

Note:

1. We can delete only a particular line of cached query in cache manager by selecting only 

    the query you want purge

2. We can clear the cache for only the selected BMM layer by selecting the particular BMM  

    Model in left side pan of the Cache Manager.

Purging the cache By Scheduling

I’m here writing how to purge the cache by scheduler using scheduled task in windows environment.

Before going the schedule the task we have to know what is the command we are using to purge the cache.

We can purge the entire cache in one shot or purge the cache by query or purge the cache by table or purge the cache by database.

We are going to learn how to use the entire cache functions one by one:

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 EMPD, EMPNAME from HR_EMP_D 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. 

Call SAPurgeCacheByTable( ‘DBName’, ‘PhyCatName’, ‘SchemaName’, ‘TableName’ );

SAPurgeCacheByDatabase()

we can purge the cache for all queries to a specific database.

Call SAPurgeCacheByTable( ‘DatabaseName’ );

SAPurgeAllCache()

This is the function will clear the entire cached query in one shot.

Call SAPurgeAllCache();

We can also use multiple commands together in the purge.sql file, as in the example below.

Call SAPurgeCacheByTable( ‘DBNameA’ );

Call SAPurgeCacheByTable( ‘DBNameB’ );

Now we will learn how to use these functions in a scheduled task.

Create the “Purge.sql” file with any of the above explained functions and create “purgecache.log” and save it in a folder.

Now open the notepad and type the below command and save the file like “PurgeCache.bat”.

------------------------------------------------------------------------------------------------------

C:\OracleBI\server\Bin\nqcmd.exe  -d AnalyticsWeb -u Administrator -p Password 

-s "C:\OracleBI\Purge\PurgeAllCache.sql" -o "C:\OracleBI\Purge\PurgeAllCache.log"

-------------------------------------------------------------------------------------------------------

-d Data source Name

-u User name

-p Password

-s Source file

-o output file

Now go to scheduled task from below path:

All Programs -> Accessories -> System Tools -> Scheduled Task -> Click on Add Scheduled Task -> Scheduled task wizard window will open.

Click on ‘Browse’ button to select “purgecache.bat” batch file. Once  you selected the file it will ask to run the task daily, weekly, monthly  and some other options. You select the option by your business Requirement. By giving all the required details in next, next step the task will be scheduled. Once the task has been scheduled you can check the log file. The entry will be updated in log file and the cache will be cleared in the cache manager.

Happy Blogging,

Bose