You can also purge cache direct from Dashboard by following the given steps.
Goto Settings—>Administration —> Issue sql
call sapurgeallcache() and click issues sql..below is the screen shot
PRESENTATION CACHEBy default, the cache files for the presentation server reside in the \tmp directory within the respective subdirectories sawcharts, sawrptcache and sawvc; while the xml cache files lying in the \tmp folder itself.
Chart Cache - \OracleBIData\tmp\sawcharts\
Report Cache - \OracleBIData\tmp\sawrptcache\
State Pool Cache - \OracleBIData\tmp\sawvc\
XML Cache - \OracleBIData\tmp\
See image below for an example of how to modify the instance config to explicitly change the default presentation cache directory locations.
Additionally, and only specific to the XML Cache directory location change, you must also make a change to the nqconfig file as follows:
WORK_DIRECTORY_PATHS = "C:\DataSources\Cache\tmp";
ENABLE PRESENTATION CACHE
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )
<ServerInstance>
<Cache>
<Query>
<MaxEntries>100</MaxEntries>
<MaxExpireMinutes>60</MaxExpireMinutes>
<MinExpireMinutes>10</MinExpireMinutes>
<MinUserExpireMinutes>10</MinUserExpireMinutes>
</Query>
</Cache>
<ServerInstance>
DISABLE PRESENTATION CACHE FOR ENTIRE APPLICATION
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>
BYPASS PRESENTATION CACHE
Sometimes you want to bypass the presentation / cache for development purposes. Or more often when you get weird write back behaviour. Add this to the instanceconfig file:
<CacheMaxExpireMinutes>-1</CacheMaxExpireMinutes>
<CacheMinExpireMinutes>-1</CacheMinExpireMinutes>
<CacheMinUserExpireMinutes>-1</CacheMinUserExpireMinutes>
CLEAR PRESENTATION CACHE
Via Oracle BI Web Application
Settings-->Administration-->Reload Files and Metadata-->Finished
or
Via Server Service
Shut down Presentation Services to remove the files for the Presentation Cache
**If you delete cache files when Presentation Services are still running or SAW does not shut down cleanly, then various cache files might be left on disk.
BI SERVER CACHE
By default, the BI Server Cache is stored in the \OracleBIData\cache\ directory and stored as NQS*.tbl files.
BI SERVER CACHE FILE NAME FORMAT
ENABLE BI SERVER CACHE
To enable the query cache for the entire application, you must set the ENABLE cache parameter value to YES in the file nqsconfig. ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = YES;
DISABLE BI SERVER CACHE FOR ENTIRE APPLICATION
To disable the query cache for the entire application, you must set the ENABLE cache parameter value to NO in the file nqsconfig. ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = NO;
BYPASS BI SERVER CACHE FOR SINGLE REPORT
In Answers, goto Advanced Reporting tab when building report, set Prefix value to following:
SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1, LOGLEVEL=7;
CLEAR BI SERVER CACHE
To clear the BI Server Cache files, run the following command file: \OracleBI\server\cache_purge_reseed\call.bat
This calls the purge.txt file, which simply contains the following command:
call SAPurgeAllCache();
This will clear the BI Server Cache (.TBL) files in the Cache directory:
\OracleBIData\cache\
or
An alternate way of doing this is via the OBI Admin Tool, using the Cache management feature.
BI SERVER CACHE ENABLED BUT NOT CACHING
OBIEE cache is enabled, but why is the query not cached?...
Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.
Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.
Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
Caching is not configured: Caching is not enabled in NQSConfig.ini file.
Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.
Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.
Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.
OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2
Chart Cache - \OracleBIData\tmp\sawcharts\
Report Cache - \OracleBIData\tmp\sawrptcache\
State Pool Cache - \OracleBIData\tmp\sawvc\
XML Cache - \OracleBIData\tmp\
See image below for an example of how to modify the instance config to explicitly change the default presentation cache directory locations.
Additionally, and only specific to the XML Cache directory location change, you must also make a change to the nqconfig file as follows:
WORK_DIRECTORY_PATHS = "C:\DataSources\Cache\tmp";
ENABLE PRESENTATION CACHE
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )
<ServerInstance>
<Cache>
<Query>
<MaxEntries>100</MaxEntries>
<MaxExpireMinutes>60</MaxExpireMinutes>
<MinExpireMinutes>10</MinExpireMinutes>
<MinUserExpireMinutes>10</MinUserExpireMinutes>
</Query>
</Cache>
<ServerInstance>
DISABLE PRESENTATION CACHE FOR ENTIRE APPLICATION
In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>
BYPASS PRESENTATION CACHE
<CacheMaxExpireMinutes>-1</CacheMaxExpireMinutes>
<CacheMinExpireMinutes>-1</CacheMinExpireMinutes>
<CacheMinUserExpireMinutes>-1</CacheMinUserExpireMinutes>
Via Oracle BI Web Application
Settings-->Administration-->Reload Files and Metadata-->Finished
or
Via Server Service
Shut down Presentation Services to remove the files for the Presentation Cache
**If you delete cache files when Presentation Services are still running or SAW does not shut down cleanly, then various cache files might be left on disk.
BI SERVER CACHE
By default, the BI Server Cache is stored in the \OracleBIData\cache\ directory and stored as NQS*.tbl files.
BI SERVER CACHE FILE NAME FORMAT
NQS(Prefix)_VMVGGOBI(Originating Server Name)_733547(Days passed since 1-1-0000)40458(Seconds passed since last midnight)_00000006(Incremental number since last BI server start).TBL
ENABLE BI SERVER CACHE
To enable the query cache for the entire application, you must set the ENABLE cache parameter value to YES in the file nqsconfig. ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = YES;
DISABLE BI SERVER CACHE FOR ENTIRE APPLICATION
To disable the query cache for the entire application, you must set the ENABLE cache parameter value to NO in the file nqsconfig. ( \OracleBI\server\Config\NQSConfig.INI )
ENABLE = NO;
BYPASS BI SERVER CACHE FOR SINGLE REPORT
In Answers, goto Advanced Reporting tab when building report, set Prefix value to following:
SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1, LOGLEVEL=7;
CLEAR BI SERVER CACHE
To clear the BI Server Cache files, run the following command file: \OracleBI\server\cache_purge_reseed\call.bat
This calls the purge.txt file, which simply contains the following command:
call SAPurgeAllCache();
This will clear the BI Server Cache (.TBL) files in the Cache directory:
\OracleBIData\cache\
or
An alternate way of doing this is via the OBI Admin Tool, using the Cache management feature.
BI SERVER CACHE PERSISTENCE
...When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.
Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.
BI SERVER CACHE ENABLED BUT NOT CACHING
OBIEE cache is enabled, but why is the query not cached?...
Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.
Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.
Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
Caching is not configured: Caching is not enabled in NQSConfig.ini file.
Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.
Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.
Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.
OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2
Seeding / Filling the cache
- Now why should you want to do that? OBIEE is not a database, so why would this be an advantage?
- If you have a "slow” connection to your source database, maybe because it's on the other side of the world.
- If you have "slow" aggregation views.
- If you can "predict" what 90% percent of your queries look like .
From your user statistics you can pick up the top 10 of your queries, Check if you can make them "dynamic" by changing fixed date into dynamic dates.
Put the following in a .txt file in your maintenance directory.
// Load cache by Query
// John Minkjan CIBER Nederland
// FileName: LoadCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\LoadCache.txt
// Query 1
SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE >= TIMESTAMPADD(SQL_TSI_YEAR, -2,NOW());
// Query 2
SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM
Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE <= TIMESTAMPADD(SQL_TSI_YEAR, -3,NOW());
// The Queries are separated with a ;
