Spring A heavy CPU consumption SQL detected after executing jdbctemplate call to oracle 11gr2 store procedure [SPR-13432]

Hendry Lai opened SPR-13432 and commented

Spring Framework 4.0.6 , using jdbctemplate call oracle 11gr2 store procedure a heavy CPU consumption SQL detected after executing jdbctemplate call to oracle 11gr2 store procedure.
We found that root cause is : jdbcUtil try to get procedure metadata before execute store procedure, and that sql in oracle 11g is very slow ( reference to URL or SQL below ) For oracle maybe it can be improved performance if LIKE operator can be replaced as equal operator and prevent IS NULL condition (such as sub-query from ALL_OBJECTS view )

detected top sql like http://stackoverflow.com/questions/3894896/mysterious-sql-blocking-my-stored-procedure-from-executing-on-oracle

SELECT package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, argument_name AS column_name, DECODE(position, 0, 5, DECODE(in_out, 'IN', 1, 'OUT', 4, 'IN/OUT', 2, 0)) AS column_type, DECODE(data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 91, 'RAW', -3, 'LONG RAW', -4, 'TIMESTAMP', 93, 'TIMESTAMP WITH TIME ZONE', -101, 'TIMESTAMP WITH LOCAL TIME ZONE', -102, 'INTERVAL YEAR TO MONTH', -103, 'INTERVAL DAY TO SECOND', -104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, DECODE(data_type, 'OBJECT', type_owner || '.' || type_name, data_type) AS type_name, DECODE(data_precision, NULL, data_length, data_precision) AS precision, data_length AS length, data_scale AS scale, 10 AS radix, 1 AS nullable, NULL AS remarks, sequence, overload, default_value FROM all_arguments WHERE owner LIKE :1 ESCAPE '/' AND object_name LIKE :2 ESCAPE '/' AND package_name IS NULL AND (argument_name LIKE :5 ESCAPE '/' OR (argument_name IS NULL AND data_type IS NOT NULL)) ORDER BY procedure_schem, procedure_name, overload, sequence

---

Affects: 4.0.6

Reference URL: http://stackoverflow.com/questions/3894896/mysterious-sql-blocking-my-stored-procedure-from-executing-on-oracle

1 votes, 2 watchers

Comment From: spring-projects-issues

Bulk closing outdated, unresolved issues. Please, reopen if still relevant.

Comment From: stephanedaviet

I can confirm this bug. One way to prevent this request is to disable the retrieval of metadata by Spring JDBC before the procedure call by calling .withoutProcedureColumnMetaDataAccess() on SimpleJdbcCall. Here is a sample code:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
        .withoutProcedureColumnMetaDataAccess()
        .withSchemaName("<schema-name>")
        .withCatalogName("<catalog-name>")
        .withProcedureName("proc-name")
        .withNamedBinding()
        .useInParameterNames(<list-of-named-parameters> ...)
        .declareParameters(new SqlOutParameter("<param-name>", OracleTypes.NUMBER))
        ...

This behavior comes from GenericCallMetaDataProvider.java and is still present in the same class in latest 5.1.x Spring release.

Comment From: caspinos

I can confirm issue is still present in Spring Framework 6.1.13 (Spring JDBC 6.1.13). OracleDriver from ojdbc11 version 21.9.0.0. Oracle database 19c. Metadata query adds about 100ms of delay in my app. Using method withoutProcedureColumnMetaDataAccess mitigates this delay.