My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.