動的SQL文のキャッシング

文キャッシュは、セッションごとの文のキャッシュを提供および管理する機能です。サーバーでは、文を再び解析することなく、カーソルがいつでも使用できるようになっていることを意味します。文のキャッシングは、プリコンパイラ・アプリケーションで有効にでき、動的SQL文に依存するすべてのアプリケーションのパフォーマンス向上に役立ちます。パフォーマンスの向上は、動的文を再利用する際の解析のオーバーヘッドをなくすことで達成されます。

このパフォーマンスの改善は、動的文のキャッシングが可能になる新しいコマンドライン・オプションstmt_cache(文のキャッシュ・サイズ用)を使用することで実現します。この新しいオプションを有効にすると、セッション作成時に文のキャッシュが作成されます。キャッシングは動的文に対してのみ適用され、静的文用のカーソル・キャッシュとこの機能は共存します。

コマンドライン・オプションstmt_cacheには、0から65535の範囲で任意の値を指定できます。デフォルト(値0)で、文キャッシングは無効化されています。stmt_cacheオプションでは、アプリケーションにそれぞれの動的SQL文の予測数を保持するように設定できます。

例13-1 stmt_cacheオプションの使用方法

次の例は、stmt_cacheオプションの使用方法を示しています。このプログラムでは、表に行を挿入し、挿入した行をループ内のカーソルを使用して選択します。このプログラムのプリコンパイルにstmt_cacheオプションを使用すると、通常のプリコンパイルよりもパフォーマンスが向上します。

/*
 *  stmtcache.pc
 *
 *  NOTE: 
 *  When this program is used to measure the performance with and without
 *  stmt_cache option, do the following changes in the program,
 *  1. Increase ROWSCNT to high value, say 10000.
 *  2. Remove all the print statements, usually which comsumes significant
 *     portion of the total program execution time.
 * 
 *  HINT: In Linux, gettimeofday() can be used to measure time. 
 */
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <oraca.h>
 
#define ROWSCNT 10
 
char    *username = "scott";
char    *password = "tiger";
 
/* Function prototypes */
void sql_error(char *msg);
void selectdata();
void insertdata();
 
int main()
{
  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");
 
  /* Connect using the default schema scott/tiger */
  EXEC SQL CONNECT :username IDENTIFIED BY :password;
 
  /* core functions to insert and select the data */
  insertdata();
  selectdata();
 
/* Rollback pll the changes and disconnect from Oracle. */
  EXEC SQL ROLLBACK WORK RELEASE;
 
  exit(0);
}
 
/*Insert the data for ROWSCNT items into tpc2sc01 */
void insertdata()
{
  varchar dynstmt[80];
  int i;
  varchar ename[10];
  float comm;
  char *str;
 
  /* Allocates temporary buffer */
  str = (char *)malloc (11 * sizeof(char));
 
  strcpy ((char *)dynstmt.arr,
          "INSERT INTO bonus (ename, comm) VALUES (:ename, :comm)");
  dynstmt.len = strlen(dynstmt.arr);
  EXEC SQL PREPARE S FROM :dynstmt;
 
  printf ("Inserts %d rows into bonus table using dynamic SQL statement\n",
          ROWSCNT);
  for (i=1; i<=ROWSCNT; i++)
  {
    sprintf (str, "EMP_%05d",i);
    strcpy (ename.arr, str);
    comm = i;
    ename.len = strlen (ename.arr);
    EXEC SQL EXECUTE S USING :ename, :comm;
  }
 
  free(str);
}
 
/* Select the data using the cursor */
void selectdata()
{
  varchar dynstmt[80];
  varchar ename[10];
  float comm;
  int i;
 
  strcpy((char *)dynstmt.arr,
         "SELECT ename, comm FROM bonus WHERE comm = :v1");
  dynstmt.len = (unsigned short)strlen((char *)dynstmt.arr);
 
  printf ("Fetches the inserted rows using using dynamic SQL statement\n\n");
  printf ("  ENAME      COMMISSION\n\n");
 
  for (i=1; i<=ROWSCNT; i++)
  {
    /* Do the prepare in the loop so that the advantage of stmt_caching 
       is visible*/
    EXEC SQL PREPARE S FROM :dynstmt;
 
    EXEC SQL DECLARE C CURSOR FOR S;
    EXEC SQL OPEN C USING :i;
 
    EXEC SQL WHENEVER NOT FOUND DO break;
 
    /* Loop until the NOT FOUND condition is detected. */
    for (;;)
    {
      EXEC SQL FETCH C INTO :ename, :comm;
      ename.arr[ename.len] = '\0';
      printf ("%10s    %7.2f\n", ename.arr, comm);
    }
    /* Close the cursor so that the reparsing is not required for stmt_cache */
    EXEC SQL CLOSE C;
  }
}
 
void sql_error(char *msg)
{
    printf("\n%s", msg);
    sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
    oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0';
    oraca.orasfnm.orasfnmc[oraca.orasfnm.orasfnml] = '\0';
    printf("\n%s\n", sqlca.sqlerrm.sqlerrmc);
    printf("in \"%s...\"\n", oraca.orastxt.orastxtc);
    printf("on line %d of %s.\n\n", oraca.oraslnr,
       oraca.orasfnm.orasfnmc);
 
   /* Disable ORACLE error checking to avoid an infinite loop
    * should another error occur within this routine.
    */
    EXEC SQL WHENEVER SQLERROR CONTINUE;
 
    /* Release resources associated with the cursor. */
    EXEC SQL CLOSE C;
 
    /* Roll back any pending changes and disconnect from Oracle. */  
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}