2 Replies Latest reply on Nov 9, 2018 9:53 AM by Kmohan-Oracle

    OCIArrayDescriptorAlloc() Unable to allocate over 3 Million Descriptors in 32bit and 50 million Descriptors on 64 bit

    3807677

      Hi,

       

      We are currently running into an issue with allocating more than 3*10^6 descriptors on 32bit applications and not more than 5*10^7 descriptors for a 64 bit test application  on windows 64 bit environments. This function according to OCI documentation, will throw an error in case it cannot allocate more descriptors, instead we find the oci error handle does not set any messages in the error buffer.We aren't able to even see an error code set by it,  but the function returns -1, so we know its not setting the descriptors and failing instead. This error is also seen in the call to OCIArrayDescriptorFree() in case of too many descriptors being allocated in a 32bit application.


      We tried looking online for official oracle documentation about the issue, but found nothing: https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci16rel002.htm#i493114

      We have no idea what to set the upper limit of the number to as oracle has no documentation of what the upper limit should be, or what the limit depends on , ie: is it dependent on RAM of system, or OS type, et cetera. We have also written simple OCI code to test this and it too has the same issue of not being able to allocate more that 50 mill descriptors.

      Would like inputs on the following:

      • We would like to know if this is an issue with OCI and the reason for failure of allocation of the descriptors.
      • Is there a workaround that would enable us to fetch over 50 million rows of  timestamp timezone in a single OCI fetch.

       

      Dev environment:

      • Hardware: Worksation- 16GB RAM, 1TB HDD, Intel Core i7-6700 CPU
      • Software:Windows 10 64 bit, VS2015 for cpp development
      • Oracle DB version 12C
      • OCI version: 12.2.0.1.0

       

       

      Below is the sample code used for testing the descriptor limit on fetching over 50 million rows of Timestamp Timezone from a 12c data source in a single fetch:

      #include "stdafx.h"
      #include <stdlib.h>
      #include <oratypes.h>
      #include <oci.h>
      
      
      //#include <windows.h>  
      #include <iostream>
      #include<time.h>
      #include<stdio.h>
      #include<chrono>
      #include<thread>
      
      
      #include<string>
      #include <map>
      
      
      #include <list>
      #include <iterator>
      
      
      using namespace std;
      OCISPool * m_ociSPool;
      OCISvcCtx *m_ociService;
      
      
      OCIEnv *ociEnv = NULL;
      OCIError *ociError = NULL;
      
      
      // OCI server handle. (OWN)
      OCIServer *m_ociServer = NULL;
      bool flag;
      OCIBind* bindHandle = (OCIBind  *)NULL;
      OCIStmt *statement = (OCIStmt *)0;
      OCIDefine *definehandle = (OCIDefine *)0;
      OCIBind  *bindhandle = (OCIBind  *)0;
      OCIDateTime * x = (OCIDateTime *)0;
      OCISession *m_session;
      OCIStmt *stmtp;
      OCIDefine *def;
      
      
      void CHK_OCI_SUCCESS(OCIError *errhp, sword status)
      {
          text errbuf[512];
          sb4 errcode = 0;
          std::string x;
          int s = std::string::npos;
          sword err;
          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:
              err = OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
                  errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
              x = std::string((char *)errbuf);
              s = x.find("ORA-01017:");
              if (s != std::string::npos)
              {
                  printf("invalid Proxy UserID/Proxy Password; logon denied");
              }
              (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;
          }
      }
      
      
      void main()
      {
          // OCI handles
          OCIEnv *envhp;
          OCIError *errhp;
          OCIServer *srvhp;
          OCISvcCtx *svchp;
          OCISession *authp;
          OCIStmt *stmtp;
          OCIDefine *defnpp;
      
      
          // Connection information
          text* user = (text*)"test";
          text* pwd = (text*)"abc123";
          text* sid = (text*)"ORCL12C";
      
      
          int fetched;
      
          // we kept cross joins on the same table so as to reach a value above 50 million
          char *query = "select KeyColumn,Column1 from internal_test.Timestamp_Timezone_Table cross join internal_test.multi1000 cross join internal_test.multi1000 cross join internal_test.multi1000 cross join internal_test.multi1000";
      
          // Fetched data
          char owner[100][31];
          char table_name[100][50];
      
      
          // Fetched data indicators, lengths and codes
          sb2 owner_ind[100], table_name_ind[100];
          ub2 owner_len[100], table_name_len[100];
          ub2 owner_code[100], table_name_code[100];
      
      
          unsigned char *m_dataBuffer;
          unsigned char *m_bufferAtRow;
          cout << "Assigning buffer" << endl;
          m_dataBuffer = new unsigned char[900000000];
          m_bufferAtRow = m_dataBuffer;
          int m_moveLength = sizeof(OCIDateTime**);
      
      
          //m_dataBuffer = new unsigned char[m_moveLength * 10];
      
      
          // Allocate environment
          int rc = OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
      
      
          // Allocate error handle
          rc = OCIHandleAlloc(envhp, (void**)&errhp, OCI_HTYPE_ERROR, 0, NULL);
      
      
          // Allocate server and service context handles
          rc = OCIHandleAlloc(envhp, (void**)&srvhp, OCI_HTYPE_SERVER, 0, NULL);
          rc = OCIHandleAlloc(envhp, (void**)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);
      
      
          // Attach to the server
          rc = OCIServerAttach(srvhp, errhp, sid, strlen((char*)sid), 0);
      
      
          // Set server in the service context 
          rc = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid*)srvhp, 0, OCI_ATTR_SERVER, errhp);
      
      
          // Allocate session handle
          rc = OCIHandleAlloc(envhp, (void**)&authp, OCI_HTYPE_SESSION, 0, NULL);
      
      
          // Set user name and password
          rc = OCIAttrSet(authp, OCI_HTYPE_SESSION, (void*)user, strlen((char*)user),
              OCI_ATTR_USERNAME, errhp);
          rc = OCIAttrSet(authp, OCI_HTYPE_SESSION, (void*)pwd, strlen((char *)pwd),
              OCI_ATTR_PASSWORD, errhp);
      
      
          // Connect
          rc = OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT);
      
      
          // Set session in the service context
          rc = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp);
      
      
          // Allocate statement handle
          rc = OCIHandleAlloc(envhp, (void**)&stmtp, OCI_HTYPE_STMT, 0, NULL);
      
      
          // Prepare the query
          rc = OCIStmtPrepare(stmtp, errhp, (text*)query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);
      
      
          // Define the select list items 
          rc = OCIDefineByPos(stmtp, &defnpp, errhp, 1, (void*)owner, 31, SQLT_STR, (void*)owner_ind,
              owner_len, owner_code, OCI_DEFAULT);
          rc = OCIDefineByPos(stmtp, &defnpp, errhp, 2, (void*)m_dataBuffer, 0, SQLT_TIMESTAMP_LTZ, (void*)table_name_ind,
              table_name_len, table_name_code, OCI_DEFAULT);
      
      
          // Execute the statement and perform the initial fetch of 100 rows into the defined array
          rc = OCIStmtExecute(
              svchp,
              stmtp,
              errhp,
              0,
              0,
              NULL,
              NULL,
              32);
          CHK_OCI_SUCCESS(errhp, rc);
      
      
          rc = OCIArrayDescriptorAlloc(
              envhp,
              reinterpret_cast<void**>(m_dataBuffer),
              OCI_DTYPE_TIMESTAMP_LTZ,
              60000000,
              0,
              0);
          CHK_OCI_SUCCESS(errhp, rc);
      // Function returns -1 and error messages from error handle cannot be retrieved
      
      /*   rc = OCIArrayDescriptorFree(
              reinterpret_cast<void**>(m_dataBuffer),
              OCI_DTYPE_TIMESTAMP_TZ);
          CHK_OCI_SUCCESS(errhp, rc);*/
      
      
          rc = OCIStmtFetch2(
              stmtp,
              errhp,
              10,
              OCI_FETCH_NEXT,
              0,
              OCI_DEFAULT);
      
      
      
      
          int m_currentNumFetchedRows;
          rc = OCIAttrGet(
              stmtp,
              OCI_HTYPE_STMT,
              (void*)&m_currentNumFetchedRows,
              NULL,
              OCI_ATTR_ROWS_FETCHED,
              errhp);
          CHK_OCI_SUCCESS(errhp, rc);
      
      
      
      
      
      
      
      
          OCIDateTime* srcBuffer;
          for (int i = 0; i < 10; i++)
          {
              sb2 year;
              ub1 month;
              ub1 day;
              srcBuffer = *reinterpret_cast<OCIDateTime**>(m_bufferAtRow);
              rc = OCIDateTimeGetDate(
                  envhp,
                  errhp,
                  srcBuffer,
                  &year,
                  &month,
                  &day);
      
      
              ub1 hour;
              ub1 minute;
              ub1 second;
              ub4 fraction;
      
      
              rc = OCIDateTimeGetTime(
                  envhp,
                  errhp,
                  srcBuffer,
                  &hour,
                  &minute,
                  &second,
                  &fraction);
      
      
              sb1 hourOffset;
              sb1 minuteOffset;
              rc = OCIDateTimeGetTimeZoneOffset(
                  envhp,
                  errhp,
                  srcBuffer,
                  &hourOffset,
                  &minuteOffset);
      
      
              CHK_OCI_SUCCESS(errhp, rc);   
      
      
              m_bufferAtRow = m_bufferAtRow + 8;
          }
      
      rc = OCIArrayDescriptorFree(
              reinterpret_cast<void**>(m_dataBuffer),
              OCI_DTYPE_TIMESTAMP_TZ);
          CHK_OCI_SUCCESS(errhp, rc);
      
          rc = OCIHandleFree(stmtp, OCI_HTYPE_STMT);
      
      
      
      
          // Disconnect
          rc = OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT);
          rc = OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
          rc = OCIHandleFree(envhp, OCI_HTYPE_ENV);
      
      
          
      }
      

       

      SQL scripts to create the tables:

      CREATE TABLE internal_test.Timestamp_Timezone_Table(KeyColumn VARCHAR2 (255),Column1 TIMESTAMP WITH TIME ZONE);

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalDate', TIMESTAMP '1955-10-11 01:00:00 +2:00');

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalDate', TIMESTAMP '1955-10-11 01:00:00 +3:00');

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalTime', TIMESTAMP '2013-05-01 09:30:12 America/Los_Angeles PDT');

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalTimeBase24', TIMESTAMP '2013-05-01 13:59:23 -8:00');

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TimestampNanoSeconds', TIMESTAMP '1955-10-11 11:10:33.123456789 -12:00');

      INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TimestampMicroSeconds', TIMESTAMP '1955-10-11 11:10:33.123456 -8:00');

       

       

      Thanks in advance