oracle / node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
http://oracle.github.io/node-oracledb/
Other
2.26k stars 1.07k forks source link

Client Result Cache #1368

Closed sandrozbinden closed 3 years ago

sandrozbinden commented 3 years ago

Problem

Hi there

I try to get the oracle client result cache working without any modifications on the oracle database server. According to the documentation (Client Result Cache) the cache can be enabled by creating a oraaccess.xml configuration file and adding a /*+ result_cache */ hint on the sql query

However I does not seem to have an impact on the query execution time. So my questions:

Any help much apprechiated

oraaccess.xml

<?xml version="1.0"?>
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
           xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
           schemaLocation="http://xmlns.oracle.com/oci/oraaccess
  http://xmlns.oracle.com/oci/oraaccess.xsd">
    <default_parameters>
        <prefetch>
            <rows>50</rows>
        </prefetch>
        <statement_cache>
            <size>100</size>
        </statement_cache>
        <result_cache>
            <max_rset_rows>100</max_rset_rows>
            <max_rset_size>10K</max_rset_size>
            <max_size>64M</max_size>
        </result_cache>
    </default_parameters>
</oraaccess>

NodeJS Code

  import oracledb, { BindParameters, Result } from 'oracledb';
  import { logger } from '../utils/logger';

  oracledb.initOracleClient({configDir: 'C:\\dev\\database\\config',}); //oraaccess.xml is inside config directory
  oracledb.fetchAsString = [oracledb.CLOB, oracledb.DATE];
  oracledb.fetchArraySize = 5000;
  oracledb.autoCommit = true;

  export async function createConnectionPool(): Promise<boolean> {
      const connectionSettings = createConnectionSettings();
      try {
          await oracledb.createPool(connectionSettings);
          logger.info('Connection pool started');
          return true;
      } catch (err) {
          logger.error('createConnectionPool() error: ' + err.message);
          return false;
      }
  }

export async function executeQuery<T = unknown>(sql: string, bindParams: BindParameters): Promise<Result<T>> {
    logger.debug(`ExecuteQuery ${sql} with bindParams: ${JSON.stringify(bindParams)}`);
    const conn = await oracledb.getPool().getConnection();
    return conn
        .execute<T>(sql, bindParams, { outFormat: oracledb.OUT_FORMAT_OBJECT })
        .catch((e) => {
            logger.error(
                `Error executeQuery ${sql} with bindParams: ${JSON.stringify(bindParams)} with exeception ${e}`
            );
            throw e;
        })
        .finally(() => conn.close());
}

export async function loadPatient(id: string): Promise<Patient> {
    const sql = 'SELECT /*+ result_cache */ * FROM PATIENT where id= :id';
    const result: Result<PatientModel> = await executeQuery(sql, { id });
    const dbPatient = result.rows ? result.rows[0] : undefined;
    if (!dbPatient) {
        throw new Error(`Patient with id ${id} does not exist`);
    }
    return mapPatientModelToPatient(dbPatient);
}

Version

Description Version
oracledb.versionString 5.1.0
oracle Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
process.platform win32
process.version v12.18.3
process.arch x64
oracledb.oracleClientVersionString 19.8.0.0.0
cjbj commented 3 years ago

The oraacess.xml doc says that its settings override any sqlnet.ora settings. SQL*Net doc seems mostly AWOL, but here it says:

If client result cache is enabled on the server by CLIENT_RESULT_CACHE_SIZE, then its value can be overridden by the sqlnet.ora configuration parameter OCI_RESULT_CACHE_MAX_SIZE. If client result cache is disabled on the server, then OCI_RESULT_CACHE_MAX_SIZE is ignored and client result cache cannot be enabled on the client.

In summary, I believe you will need to enable CRC on the DB server.

I'll update node-oracledb's use of 'alternatively', and also see about some improvements in the SQL*Net doc - it's possible these didn't end up being SQLNet parameters and the doc references that say 'SQL\Net' should say 'OCI'.

Regarding verifying CRC, the traditional way is to check the number of statement executions the DB performed. If CRC is enabled, then the DB never gets sent the statement and doesn't execute it.

In SQL*Plus I did this:

ALTER SYSTEM SET CLIENT_RESULT_CACHE_LAG = 3000 SCOPE=SPFILE;
ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE = 64K SCOPE=SPFILE;
STARTUP FORCE

And then ran crc.js which runs a pair of statements 100 times:

    const q = `select id from crc_tab`;
    const qc = `select /*+ result_cache */ id from crc_tab`;

The output was:

No CRC 100 executions
CRC 1 executions

If I tried your oraacess.xml files without the ALTER SYSTEM commands, I got a result showing that CRC wasn't enabled:

No CRC 100 executions
CRC 100 executions
sandrozbinden commented 3 years ago

Hi Christopher. Thanks a lot for your detailed description on how to get this working.