fetch with function returns slower compared to the same PLSQL Developer 14 query

[william-oliveira]

1. What versions are you using? Platform: win32 Version: v14.17.3 Arch: x64 OracleDB: 5.2.0 Client: 11.2.0.2.0 Connection: { _enableStats: true, enableStatistics: true, user: 'user', password: 'password', connectString: 'connectString', sessionCallback: [Function: initSession], poolMax: 10, poolMin: 10, poolIncrement: 0 }

2. Describe the problem searches using select or procedures that contain functions to fetch information are very slow comparing to the same PLSQL Developer fetch

3. Include a runnable Node.js script that shows the problem.

const run = async () => { const connection = { _enableStats: true, enableStatistics: true, user: 'wmsnovoc', password: 'wmsnovoc', connectString: 172.25.100.247:1521/full11g, sessionCallback: initSession, poolMax: 10, poolMin: 10, poolIncrement: 0, }; console.log('Connection: ', connection);

await oracledb
    .createPool(connection)
    .then((pool) => {
        console.log('pool: ', pool);
        require('./utils/db')(pool);

        console.log('Server initialization completed!');

    })
    .catch((error) => {
        console.log(
            'ERROR: ',
            new Date(),
            ': createPool() call,back: ' + error.message
        );
    });

console.log(
    ' #################################### Start test #################################### '
);

// console.log(' ---- TEST Insert ----');
// await oracleTest.testInsert();

console.log(' ---- TEST Query ----');
await testQuery();

// console.log(' ---- TEST Procedure ----');
// oracleTest.testProcedure();

console.log(
    ' #################################### End test  #################################### '
);

};

const initSession = (connection, requestedTag, cb) => { connection.execute( begin execute immediate 'alter session set nls_sort = WEST_EUROPEAN_AI '; execute immediate 'alter session set nls_comp = LINGUISTIC '; execute immediate 'alter session set nls_date_format = ''rrrr-mm-dd hh24:mi:ss'' '; execute immediate 'alter session set nls_numeric_characters = ''.,'' '; -- execute immediate -- 'ALTER SYSTEM FLUSH BUFFER_CACHE'; -- execute immediate -- 'ALTER SYSTEM FLUSH SHARED_POOL'; end;, cb ); };

const testQuery = async () => { let result;

console.time(' ---------------- No Function');
result = await db.doExecuteSql(querys.semFunction, null, {}, 'No Function');
console.timeEnd(' ---------------- No Function');
console.log(' -------- No Function', result.length);

console.time(' ---------------- With Function');
result = await db.doExecuteSql(querys.comFunction, null, {}, 'With Function');
console.timeEnd(' ---------------- With Function');
console.log(' -------- With Function', result.length);

};

const doExecuteSql = async (sql, vars, options = {}, name) => {
    console.time(name + '-doConnect');
    const connectinon = await doConnect();
    console.timeEnd(name + '-doConnect');

    options = {
            // resultSet: true,
            // outFormat: oracledb.OUT_FORMAT_OBJECT,
            prefetchRows: 500,
            fetchArraySize: 500,
        }

    if (connectinon) {
        try {
            console.time(name + '-doExecuteSql');

            console.time(name + '-executeSQL');

            // const result = await connectinon.execute(sql, vars || [], options);

            // result = await connectinon.execute(sql, [], {
            //     resultSet: true,
            //     outFormat: oracledb.OUT_FORMAT_OBJECT,
            //     prefetchRows: 1000,
            //     fetchArraySize: 1000,
            // });
            result = await connectinon.execute(sql, [], options);
            console.timeEnd(name + '-executeSQL');

            // const rs = result.resultSet;
            // let row,
            //     rows = [];
            // while ((row = await rs.getRow())) {
            //     rows.push(row);
            // }

            // rs.close();

            console.time(name + '-getRowsSQL');

            const rows = result.rows;

            console.timeEnd(name + '-getRowsSQL');

            return rows;
        } catch (error) {
            console.log(error);
            return [];
        } finally {
            console.timeEnd(name + '-doExecuteSql');
            try {
                connectinon.close();
            } catch (error) {
                console.log(error);
                return [];
            }
        }
    }
};
const doConnect = async () => {
    try {
        return await pool.getConnection();
    } catch (error) {
        console.error('Could not connect to database!', error);
        return null;
    }
};

const semFunction = SELECT c.id, c.num, 0 tot_1, 0 tot_2, 0 tot_3, 0 tot_4, 0 tot_5, 0 tot_6, 0 tot_7, 0 tot_8 FROM cargas c;

const comFunction = SELECT c.id, c.num, FROM wms_cargas c;

[william-oliveira]

the problem was in the sessions.