How to Seed & Purge the OBIEE query cache

 

The OBIEE query cache can be programmatically purged then seeded at the end of each ETL.

· The utility nqcmd can be used for this purpose and can be wrapped in a shell script and scheduled at the end of the daily ETL

· A list of logical SQL statements should be passed to the utility

· The logical SQL statements to seed can be extracted from Usage Tracking

· Note: the development environment has the query cache OFF by default to help with physical SQL debugging

See Query Caching section of System Administration guide:

http://docs.oracle.com/cd/E21764_01/bi.1111/e10541/querycaching.htm

See Section 7.6.2 Purging and Maintaining Cache Using ODBC Procedures.

The query cache can be completely purged, then seeded at the end of the ETL through the used of the nqcmd utility.

Example

Create script which first purges OBI cache, then seeds it with query results (example below is run on Sample Application npe-obi-app01:7002)

Sample Script:

Call SAPurgeAllCache();

SELECT

0 s_0,

"A - Sample Sales"."Time"."T05 Per Name Year" s_1,

"A - Sample Sales"."Base Facts"."1- Revenue" s_2

FROM "A - Sample Sales"

ORDER BY 1, 2 ASC NULLS LAST;

Run Command:

D:\obi_sample\Oracle_BI1\bifoundation\server\bin>nqcmd -d coreapplication_OH1901

154131 -u weblogic -p Admin123 -s HNZSeedCache.txt

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

Oracle BI ODBC Client

Copyright (c) 1997-2011 Oracle Corporation, All rights reserved

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

Call SAPurgeAllCache()

Call SAPurgeAllCache()

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

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

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

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

RESULT_CODE RESULT_MESSAGE

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

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

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

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

1 [59118] Operation SAPurgeAllCache succeeded!

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

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

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

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

Row count: 1

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

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

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

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

SELECT

0 s_0,

"A - Sample Sales"."Time"."T05 Per Name Year" s_1,

"A - Sample Sales"."Base Facts"."1- Revenue" s_2

FROM "A - Sample Sales"

ORDER BY 1, 2 ASC NULLS LAST

SELECT

0 s_0,

"A - Sample Sales"."Time"."T05 Per Name Year" s_1,

"A - Sample Sales"."Base Facts"."1- Revenue" s_2

FROM "A - Sample Sales"

ORDER BY 1, 2 ASC NULLS LAST

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

s_0 s_1 s_2

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

0 2008 16500000.00

0 2009 15000000.00

0 2010 18500000.00

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

Row count: 3

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

Processed: 2 queries

D:\obi_sample\Oracle_BI1\bifoundation\server\bin>