OCI Consuming Excessive CPU With Oracle 11.2.0.1 (Doc ID 1286601.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

OCI consumes a lot of CPU with Oracle 11.2.0.1, not seen this on previous releases.The OCI release session can take up to a minute to release the sessions, when using session pooling.

Testcase using OCI session pooling:

#include <stdio.h>
#include <string.h>
#include <Windows.h>
// windows.h only needed for Sleep(1000);
// This is not a platform-specific bug.
#include <time.h>
extern "C"
{
#include <oci.h>
#include <ocidem.h>
#include <ociapr.h>
#include <ociap.h>
}

//***********************************************************************************
static ub4 sessMin = 1;
static ub4 sessMax = 6;
static ub4 sessIncr = 2;

static OCIError   *errhp;
static OCIEnv     *envhp;
static OCISPool   *poolhp=(OCISPool *) 0;

static OraText *poolName;
static ub4 poolNameLen;
static CONST OraText *database = (text *)"v102";
static CONST OraText *appusername =(text *)"scott";
static CONST OraText *apppassword =(text *)"tiger";

static void checkerr (OCIError *errhp, sword status);
//********************************************************************************************************


int main(int argc, char* argv[])
{
    int i = 0;
    sword lstat;
    int timeout = 10;
    time_t start, end, poolStart, now;

  OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0,  NULL,
                NULL, NULL, 0, (dvoid **)0);
 
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                        (size_t) 0, (dvoid **) 0);
 
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_SPOOL,
                        (size_t) 0, (dvoid **) 0);

  /* Set the timeout */
  checkerr(errhp, OCIAttrSet((dvoid *) poolhp,
           (ub4) OCI_HTYPE_SPOOL, (dvoid *) &timeout, (ub4)0,
           OCI_ATTR_SPOOL_TIMEOUT, errhp));

  /* Create the session pool */
  if (lstat = OCISessionPoolCreate(envhp, errhp,poolhp, (OraText **)&poolName,
              (ub4 *)&poolNameLen, database,
              (ub4)strlen((const char *)database),
              sessMin, sessMax, sessIncr,
              (OraText *)appusername,
              (ub4)strlen((const char *)appusername),
              (OraText *)apppassword,
              (ub4)strlen((const char *)apppassword),
              OCI_DEFAULT))
  {
    checkerr(errhp,lstat);
  }

  time(&poolStart);
  printf("Session Pool Created \n");

  //============

  OCISvcCtx *svchp1 = (OCISvcCtx *) 0;
  OCISvcCtx *svchp2 = (OCISvcCtx *) 0;
  OCISvcCtx *svchp3 = (OCISvcCtx *) 0;

  OCIStmt *stmthp = (OCIStmt *)0;
  OCIAuthInfo *authp = (OCIAuthInfo *)0;


  lstat =  OCIHandleAlloc((dvoid *) envhp,
                          (dvoid **)&authp, (ub4) OCI_HTYPE_AUTHINFO,
                          (size_t) 0, (dvoid **) 0);
  if (lstat)
    checkerr(errhp, lstat);

  checkerr(errhp, OCIAttrSet((dvoid *) authp,(ub4) OCI_HTYPE_AUTHINFO,
           (dvoid *) appusername, (ub4) strlen((char *)appusername),
           (ub4) OCI_ATTR_USERNAME, errhp));

  checkerr(errhp,OCIAttrSet((dvoid *) authp,(ub4) OCI_HTYPE_AUTHINFO,
           (dvoid *) apppassword, (ub4) strlen((char *)apppassword),
           (ub4) OCI_ATTR_PASSWORD, errhp));


  for (int i = 0; i < 10000; i++)
  {
      if  (lstat = OCISessionGet(envhp, errhp, &svchp1, authp,
          (OraText *)poolName, (ub4)strlen((char *)poolName), NULL,
          0, NULL, NULL, NULL, OCI_SESSGET_SPOOL))
      {
          checkerr(errhp,lstat);
      }

      if  (lstat = OCISessionGet(envhp, errhp, &svchp2, authp,
          (OraText *)poolName, (ub4)strlen((char *)poolName), NULL,
          0, NULL, NULL, NULL, OCI_SESSGET_SPOOL))
      {
          checkerr(errhp,lstat);
      }

      if  (lstat = OCISessionGet(envhp, errhp, &svchp3, authp,
          (OraText *)poolName, (ub4)strlen((char *)poolName), NULL,
          0, NULL, NULL, NULL, OCI_SESSGET_SPOOL))
      {
          checkerr(errhp,lstat);
      }

      time(&now);
      printf("\n\nReleasing sessions. Time since pool creation: %d \n", now-poolStart);

      time(&start);
      checkerr(errhp, OCISessionRelease(svchp1, errhp, NULL, 0, OCI_DEFAULT));
      time(&end);
      printf("Session 1 took %d seconds to be released. \n", end-start);

      time(&start);
      checkerr(errhp, OCISessionRelease(svchp2, errhp, NULL, 0, OCI_DEFAULT));
      time(&end);
      printf("Session 2 took %d seconds to be released. \n", end-start);

      time(&start);
      checkerr(errhp, OCISessionRelease(svchp3, errhp, NULL, 0, OCI_DEFAULT));
      time(&end);
      printf("Session 3 took %d seconds to be released. \n", end-start);

      Sleep(1000);
     
      //===============================================
  }
  OCIHandleFree((dvoid *)authp, OCI_HTYPE_AUTHINFO);
  lstat =  OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

  printf("Session Pool Destroyed \n");

  if (lstat != OCI_SUCCESS)
      checkerr(errhp, lstat);

  checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL));

  checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
  return 0;
}

/* This function prints the error */
void checkerr(OCIError *errhp, sword status)
{
  text errbuf[512];
  sb4 errcode = 0;
 
  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                       errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}


Partial output, with timeout set to 10:

Releasing sessions. Time since pool creation: 0
Session 1 took 0 seconds to be released.
Session 2 took 0 seconds to be released.
Session 3 took 0 seconds to be released.

(same for time=1..8)

Releasing sessions. Time since pool creation: 9
Session 1 took 0 seconds to be released.
Session 2 took 12 seconds to be released.
Session 3 took 11 seconds to be released.


Releasing sessions. Time since pool creation: 34
Session 1 took 0 seconds to be released.
Session 2 took 11 seconds to be released.
Session 3 took 12 seconds to be released.


Releasing sessions. Time since pool creation: 58
Session 1 took 0 seconds to be released.
Session 2 took 12 seconds to be released.
Session 3 took 12 seconds to be released.


With expired time < timeout, the time to release sessions is zero.  After that the release time is approximately equal to the timeout, and this is true for all other timeouts tested: 20, 5 and 0 seconds, so a quick workaround is simply to set the timeout to zero.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms