Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係 #5 （最終回） Tweet

これってクライアント結果キャッシュをちゃんと動作させる為じゃなく、cdemoqc.cってクライアント結果キャッシュのデモを実行した時に、別クライアントからclient_result_cache_stats$ビューの作成、参照件数等をチェックすることで動作確認できるようにするための改造なので、動作確認できたらdual表を無理矢理問い合わせるなんてぇロジック不要ですから、お間違いのないように。m(_ _)m 2010/9/13追記。

さて、Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係も今回が最終回.

最終回なのでOracleのバージョン情報も載せておきますね。

19:54:51 SYS> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

経過: 00:00:00.00
19:55:56 SYS> 

Client Result Cacheを有効化する為のプログラミング上のポイント！
・OCIStmtPrepare2()を使う。

マニュアルにも記載されてますが、Client Result Cacheを有効かするためにはStatement cache（文キャッシュ）を有効化している必要がある。
　OCIStmtPrepare()を使ってしまうと文キャッシュされないのでClient result cacheが効かない。

・OCIStmtFetch2()でOCI_NO_DATAまでフェッチする。

例えば、１行しかないからOCIStmtExecute()の後、OCIStmtFetch2()を１度しか行っていないと、OCI_NO_DATAが発生しないのでClient Result Cacheに問合せ結果が乗らない。

その他、初期化パラメータや　/*+ RESULT_CACHE */　ヒントを利用する等といった前提はマニュアルを読んでね。

最後に総まとめとして、cdemoqc.cを思いっきり修正し必要最低限かつ、cdemoqc.c内でclient_result_cache_stats$を確認するという怪しい作りじゃなく、別途、SQL*Plusからclient_result_cache_stats$ビューを問い合わせ、Client result cache（クライアント結果キャッシュ）が出来ているか確認できるように改造しちゃいました。

但し、削除次いでにwindows対応の部分までも削ぎ落としてしまいました。ごめんなさい。ごめんなさい。

以下、Mac De Oracle版 cdemoqc.c （除くWindows)のコード。

[oracle@lampeye demo]$ cat $ORACLE_HOME/rdbms/demo/cdemoqc.c
/* Copyright (c) 2007, Oracle. All rights reserved.  */

/*
  NAME
    cdemoqc - Basic OCI Query Cache(result cache) functionality 
         
  DESCRIPTION
    This program demonstrate the working of Query cache. 
    To use the query cache feature (and hence this program), database
    should have been brought up with client side result cache enabled. To bring
    the database with result cache enabled, add the following lines in
    initialization parameter file,
       client_result_cache_size=<cache size in bytes>
       client_result_cache_lag=<timeout value for cache in ms>
       compatible=11.0.0.0.0
    and bring up the database.

*/


# include <oci.h>

# include <stdio.h>
# include <string.h>
# include <stdlib.h>
# include <time.h>

#include <unistd.h>

#define EMPLOYEE 0

/* Function prototypes */
static void query_tab();
static void dummy_query();
static void dbLogon();
static void dbLogoff();
static void Checkerr(OCIError *errhp, sword status, text *msg);

int main();

static sword status = 0;
static OCISvcCtx *svchp;
static OCIError   *errhp;
static OCIEnv     *envhp;

/* Queries with result cache hints */
static text *cache_query=
(text *)"SELECT /*+ result_cache */ empno, ename, sal FROM qctable";
//(text *)"SELECT empno, ename, sal FROM qctable";

static text *dummy= (text *)"SELECT COUNT(*) FROM DUAL";
/* - main -------------------------------------------------------------------*/ 
int main ()
{
  long t;
  time(&t);
  printf ("Start -- %s",ctime(&t));
  printf ("Query cache is enabled by using result_cache hints\n\n");

  /* Logging on to multiple sessions */
  dbLogon ();

  printf ("Employee: Execute will fetch rows from server\n");
  query_tab(EMPLOYEE);
  printf ("\n\nEmployee: Execute will fetch rows from local cache, 4 times\n");
  query_tab(EMPLOYEE);
  query_tab(EMPLOYEE);
  query_tab(EMPLOYEE);
  query_tab(EMPLOYEE);
  printf ("\n\nTotal query 5 times\nSleeping...30sec..\n");
  sleep(30);
  time(&t);
  dummy_query(EMPLOYEE);
  printf("dummyクエリ実行済み、１０秒待機中。\n");
  printf("この間にclient_result_cache_stats$ビューを他端末からチェック！\n\n%s",
	 ctime(&t));
  sleep(10);

  dbLogoff ();
  return 0;
}

static void dummy_query ()
{
  OCIStmt *stmthp = (OCIStmt *)0;
  OCIDefine *def1hp = (OCIDefine *)0;
  ub4 ctr;
  sb4 ctrSz = sizeof(ctr);
  ub2 datelen=0;
  ub4 prefetch = 0;

  Checkerr (errhp,
    OCIStmtPrepare2 ((OCISvcCtx *)svchp,(OCIStmt **)&stmthp,
    (OCIError *)errhp, (text *)dummy, (ub4)strlen((char *)dummy),
    (oratext *)NULL, (ub4) 0, (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT),
    (oratext *)"OCIStmtPrepare");

  /* Setting the prefetch count = 0 */
  Checkerr(errhp,
    OCIAttrSet((OCIStmt *) stmthp, OCI_HTYPE_STMT, (dvoid *)&prefetch,
    sizeof(prefetch), OCI_ATTR_PREFETCH_ROWS, (OCIError *)errhp),
    (oratext *) "OCIAttrSet-prefetch");

  Checkerr (errhp,
    OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def1hp,
    (OCIError *)errhp, (ub4)1, (dvoid *)&(ctr), (sb4)ctrSz, (ub2)SQLT_INT,
    (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
    (oratext *)"OCIDefByPos");

  if ((status = OCIStmtExecute ((OCISvcCtx *)svchp, (OCIStmt *)stmthp,
                  (OCIError *)errhp, (ub4)0, (ub4)0, (OCISnapshot *)0,
                  (OCISnapshot *)0, (ub4)OCI_DEFAULT)) != OCI_SUCCESS )
  {
    printf ("OCIStmtExecute for Query Dual - Fail\n" );
    Checkerr (errhp, status,(oratext *)"Stmt Execute");
  }
  else
  {
    status = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
             (ub2)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT);

    if (status == OCI_ERROR || status == OCI_INVALID_HANDLE)
    {
      Checkerr(errhp, status, (oratext *)"OCIStmtFetch2");
      return;
    }
    else if (status != OCI_NO_DATA)
    {
      printf("Dual selected.\n");
    }
  }

  Checkerr (errhp,
    OCIStmtRelease ((OCIStmt *)stmthp, (OCIError *)errhp,(dvoid *)NULL, 0,
    OCI_DEFAULT), (oratext *)"StmtRelease");
}

/* - Logon to the DB in two sessions ----------------------------------------*/ 
static void dbLogon ()
{
  ub4 cachesize=10;
  OraText *connStr = (text *)"";
  OraText *username = (text *)"ocitest";
  OraText *password = (text *)"ocitest";
  OCIAuthInfo *authhp = (OCIAuthInfo *)0;

  OCIEnvCreate ((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
                (dvoid * (*)(dvoid *, size_t))0,
                (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0);

  OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                  (size_t) 0, (dvoid **) 0);

  OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&authhp,
                  (ub4)OCI_HTYPE_AUTHINFO, (size_t)0, (dvoid **)0);

  OCIAttrSet ((dvoid *)authhp, (ub4)OCI_HTYPE_AUTHINFO, (dvoid *)username,
     (ub4)strlen((char *)username), (ub4)OCI_ATTR_USERNAME,
     (OCIError *)errhp);

  OCIAttrSet ((dvoid *)authhp, (ub4)OCI_HTYPE_AUTHINFO, (dvoid *)password,
     (ub4)strlen((char *)password), (ub4)OCI_ATTR_PASSWORD,
     (OCIError *)errhp);

  Checkerr (errhp, 
     OCISessionGet ((OCIEnv *)envhp, (OCIError *)errhp,
     (OCISvcCtx **)&svchp, (OCIAuthInfo *)authhp, (OraText *)connStr,
     (ub4)strlen((char *)connStr), (OraText *)NULL, (ub4)0, (OraText **)0,
     (ub4 *)0, (boolean *)0,(ub4)OCI_DEFAULT),
     (oratext *)"OCISessionGet");

  printf ("Connected to Employee Session\n");

  OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)&cachesize,
     (ub4)0,OCI_ATTR_STMTCACHESIZE,errhp);

}

/* - Execute SQL query and prints the data ----------------------------------*/ 
static void query_tab ()
{
  OCIStmt *stmthp = (OCIStmt *)0; 
  OCIDefine *def1hp = (OCIDefine *)0;
  OCIDefine *def2hp = (OCIDefine *)0;
  OCIDefine *def3hp = (OCIDefine *)0;
  ub4 empno;
  text ename[100];
  ub4 sal;
  sb4 empnoSz = sizeof (empno);
  sb4 enameSz = sizeof (ename);
  sb4 salSz = sizeof (sal);
  ub2 datelen=0;
  ub4  prefetch = 0;

  Checkerr (errhp, 
    OCIStmtPrepare2 ((OCISvcCtx *)svchp,(OCIStmt **)&stmthp,
    (OCIError *)errhp, (text *)cache_query, (ub4)strlen((char *)cache_query), 
    (oratext *)NULL, (ub4) 0, (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT),
    (oratext *)"OCIStmtPrepare");

  /* Setting the prefetch count = 0 */
  Checkerr(errhp, 
    OCIAttrSet((OCIStmt *) stmthp, OCI_HTYPE_STMT, (dvoid *)&prefetch,
    sizeof(prefetch), OCI_ATTR_PREFETCH_ROWS, (OCIError *)errhp),
    (oratext *) "OCIAttrSet-prefetch");

  Checkerr (errhp, 
    OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def1hp,
    (OCIError *)errhp, (ub4)1, (dvoid *)&(empno), (sb4)empnoSz, (ub2)SQLT_INT,
    (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
    (oratext *)"OCIDefByPos");

  Checkerr (errhp, 
    OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def2hp,
    (OCIError *)errhp, (ub4)2, (dvoid *)&(ename), (sb4)enameSz, (ub2)SQLT_STR,
    (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
    (oratext *)"OCIDefByPos2");

  Checkerr (errhp, 
    OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def3hp,
    (OCIError *)errhp, (ub4)3, (dvoid *)&(sal), (sb4)salSz, (ub2)SQLT_INT,
    (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
    (oratext *)"OCIDefByPos3");

  if ((status = OCIStmtExecute ((OCISvcCtx *)svchp, (OCIStmt *)stmthp,
                  (OCIError *)errhp, (ub4)0, (ub4)0, (OCISnapshot *)0,
                  (OCISnapshot *)0, (ub4)OCI_DEFAULT)) != OCI_SUCCESS )
  {
    printf ("OCIStmtExecute for SELECT - Fail\n" );
    Checkerr (errhp, status,(oratext *)"Stmt Execute");
  }
  else
  {
    do 
    {
      status = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
                 (ub2)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT);

      if (status == OCI_ERROR || status == OCI_INVALID_HANDLE)
      {
        Checkerr(errhp, status, (oratext *)"OCIStmtFetch2");
        break;
      }
      else if (status != OCI_NO_DATA)
      {
        printf("\nEMPNO is %d, ENAME is %s, SAL is %d\n", empno, ename, sal);
      } 
    }while(status !=  OCI_NO_DATA);
  }

  Checkerr (errhp,
    OCIStmtRelease ((OCIStmt *)stmthp, (OCIError *)errhp,(dvoid *)NULL, 0,
    OCI_DEFAULT), (oratext *)"StmtRelease");
}

/* - Session Logoff --------------------------------------------------------*/
static void dbLogoff ()
{
  printf ("\nLogging off the connected session.\n");

  Checkerr (errhp, 
    OCISessionRelease(svchp, errhp, 0,0, OCI_DEFAULT),
    (oratext *) "Session-Release");
}

/* - Error checking routing -------------------------------------------------*/
void Checkerr(OCIError *errhp, sword status, text *msg)
{
  text  msgbuf[512];
  sb4   errcode = 0;

  memset((void *) msgbuf, (int)'\0', (size_t)512);
  if(status!=OCI_SUCCESS)
  {
    printf("error msg: %s\n",msg);
  }

  switch (status)
  {
  case OCI_SUCCESS: break;
  case OCI_SUCCESS_WITH_INFO:
    printf("status = OCI_SUCCESS_WITH_INFO\n");
    OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
    printf("ERROR CODE = %d\n", errcode);
    printf("%.*s\n", 512, msgbuf);
    if (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439)
      exit(1);
    break;
  case OCI_NEED_DATA:
    printf("status = OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
     printf("status = OCI_NO_DATA\n");
    break;
  case OCI_ERROR:
     printf("status = OCI_ERROR\n");
     OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
     printf("ERROR CODE = %d\n", errcode);
     printf("%.*s\n", 512, msgbuf);
    if (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439)
      exit(1);
    break;
  case OCI_INVALID_HANDLE:
     OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
     printf("ERROR CODE = %d\n", errcode);
     printf("%.*s\n", 512, msgbuf);
     printf("status = OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
     printf("status = OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
     printf("status = OCI_CONTINUE\n");
    break;
  default:
    break;
  }
  return;
}

64bit 環境なので以下のように demo_rdbms64.mkでmakeしま〜す。

[oracle@lampeye demo]$  make -f demo_rdbms64.mk build EXE=cdemoqc OBJS="cdemoqc.o"

/usr/bin/gcc -fPIC -c -I/opt/u01/app/oracle/product/11.1.0/db_1/rdbms/demo 
-I/opt/u01/app/oracle/product/11.1.0/db_1/rdbms/public 
-I/opt/u01/app/oracle/product/11.1.0/db_1/plsql/public -I/opt/u01/app/oracle/product/11.1.0/db_1/network/public 
-I/opt/u01/app/oracle/product/11.1.0/db_1/precomp/public cdemoqc.c -DLINUX -DORAX86_64 -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 
-D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DNS_THREADS -DLONG_IS_64 -DSS_64BIT_SERVER
/usr/bin/gcc -L/opt/u01/app/oracle/product/11.1.0/db_1/lib/ 
-L/opt/u01/app/oracle/product/11.1.0/db_1/rdbms/lib/ -o cdemoqc cdemoqc.o -lclntsh   
`cat /opt/u01/app/oracle/product/11.1.0/db_1/lib/sysliblist` -ldl -lm  -lpthrea

実行！！！ 同一表(1行だけ登録されてます）を５回問合せ、うち４回はClient result cacheにヒットさせます。
Sleeping...30sec..の待機は、オリジナルのコードでは60秒待機させていましたが私の環境だと30秒待機でOKでした。
dummyクエリ実行済み、１０秒待機中。のメッセージが表示されたら、予め起動していたSQL*Plusからclient_result_cache_stats$を問い合わせます。クライアント結果キャッシュが効いているか確認できるはずです。

[oracle@lampeye demo]$ ./cdemoqc
Start -- Thu Sep  9 19:54:09 2010
Query cache is enabled by using result_cache hints

Connected to Employee Session
Employee: Execute will fetch rows from server

EMPNO is 1, ENAME is EMP_1, SAL is 900


Employee: Execute will fetch rows from local cache, 4 times

EMPNO is 1, ENAME is EMP_1, SAL is 900

EMPNO is 1, ENAME is EMP_1, SAL is 900

EMPNO is 1, ENAME is EMP_1, SAL is 900

EMPNO is 1, ENAME is EMP_1, SAL is 900


Total query 5 times
Sleeping...30sec..
Dual selected.
dummyクエリ実行済み、１０秒待機中。
この間にclient_result_cache_stats$ビューを他端末からチェック！

Thu Sep  9 19:54:39 2010

Logging off the connected session.
[oracle@lampeye demo]$ 

SQL*Plusからの確認！

・デモプログラム起動直後の状態
19:39:02 SYS> select * from client_result_cache_stats$;

  CACHE_ID    STAT_ID NAME                                VALUE
---------- ---------- ------------------------------ ----------
       111          1 Block Size                              0
       111          2 Block Count Max                         0
       111          3 Block Count Current                     0
       111          4 Hash Bucket Count                       0
       111          5 Create Count Success                    0
       111          6 Create Count Failure                    0
       111          7 Find Count                              0
       111          8 Invalidation Count                      0
       111          9 Delete Count Invalid                    0
       111         10 Delete Count Valid                      0

10行が選択されました。

経過: 00:00:00.00

・この間にclient_result_cache_stats$ビューを他端末からチェック！が表示された時点の状態
19:54:13 SYS> /

  CACHE_ID    STAT_ID NAME                                VALUE
---------- ---------- ------------------------------ ----------
       111          1 Block Size                            256
       111          2 Block Count Max                      4096
       111          3 Block Count Current                   128
       111          4 Hash Bucket Count                    1024
       111          5 Create Count Success                    1
       111          6 Create Count Failure                    0
       111          7 Find Count                              4
       111          8 Invalidation Count                      0
       111          9 Delete Count Invalid                    0
       111         10 Delete Count Valid                      0

10行が選択されました。

経過: 00:00:00.00

・デモプログラム終了直後の状態
19:54:40 SYS> /

レコードが選択されませんでした。

経過: 00:00:00.00

---

・Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係 #1
・Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係 #2
・Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係 #3
・Client result cache (クライアント結果キャッシュ）と client_result_cache_stats$の怪しい関係 #4