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

[spring-projects-issues]

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

[spring-projects-issues]

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

[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.