Rittman mead的Blog 上Robin Moffatt介绍的OBIEE缓存管理介绍

9月20几号，Rittman mead的Blog 上Robin Moffatt在的发了2篇blog涉及如何管理OBIEE缓存的   

具体文档见

Managing the OBIEE BI Server Cache from ODI 12c

September 24th, 2015 by Robin Moffatt

http://www.rittmanmead.com/2015/09/managing-the-obiee-bi-server-cache-from-odi-12c/

OBIEE BI Server Cache Management Strategies

September 23rd, 2015 by Robin Moffatt

http://www.rittmanmead.com/2015/09/obiee-bi-server-cache-management-strategies/

我这里就摘录一些我觉得有用的内容，

1.清缓存的多种方法

    内容我就直接copy过来了

    nqcmd – one of the most common ways, repeated on many a blog, but requires nqcmd/OBIEE to be installed on the machine running it. nqcmd is a command-line ODBC client for connecting to the BI Server

    ODBC – requires BI to be installed on the machine running it in order to make the OBIEE ODBC driver available

    JDBC – just requires the OBIEE JDBC driver, which is a single .jar file and thus portable

    Web Service – the OBIEE BI Server Web Service can be used to invoke these procedures from any machine with no dependencies other than some WSM configuration on the OBIEE server side.

    其中nqcmd，ODBC，JDBC都使用SQL命令清空

    SAPurgeCacheByQuery

    SAPurgeCacheByTable

    SAPurgeCacheByDatabase

    SAPurgeAllCache

2.使用JDBC连接BI Server

  jar包位置$FMW_HOME/Oracle_BI1/bifoundation/jdbc/bijdbc.jar

  里面有readme 文档，有具体说明

  我使用DbVisualizer测试成功，设置如下：

3.Web Service使用

  以下是文中给的一个示例， 

curl --silent --header "Content-Type: text/xml;charset=UTF-8" \

--user weblogic:Admin123 \

--data @purge_cache_soap.xml \

http://192.168.56.102:7780/AdminService/AdminService

[...]

[59118] Operation SAPurgeAllCache succeeded!

[...]

其中purge_cache_soap.xml内容还没找到。

如何使用具体web-service可以参考这篇

http://www.rittmanmead.com/2015/09/using-the-bi-server-metadata-web-service-for-automated-rpd-modifications/

我会在下一篇blog把重点内容转出来

4.Event Polling Tables (EPT)

  功效和SAPurgeCacheByTable一致，

  系统安装的时候会建BIPLATFORM.S_NQ_EPT，表结构如下，

SQL> desc biee11g_biplatform.S_NQ_EPT;

 Name                                      Null?    Type

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

 UPDATE_TYPE                               NOT NULL NUMBER(10)

 UPDATE_TS                                 NOT NULL DATE

 DATABASE_NAME                             VARCHAR2(120)

 CATALOG_NAME                              VARCHAR2(120)

 SCHEMA_NAME                               VARCHAR2(120)

 TABLE_NAME                                NOT NULL VARCHAR2(120)

 OTHER_RESERVED                            VARCHAR2(120)

  biee会定期读这张表存在记录就把表清空

  具体我没测试过，按以下文档的说法还需要active一下

  https://docs.oracle.com/cd/E28280_01/bi.1111/e10541/querycaching.htm#BIESG278

5.Cache Seeding

 call SASeedQuery('sql statement');

 可以把具体执行慢的SQL缓存掉