Mysterious SQL blocking my stored procedure from executing on ORACLE

Question

I am trying to run a procedure on ORACLE with the thin jdbc client and c3p0.

here's the code that's supposed to run:

        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("I_NODE_ID", nodeId);
        paramMap.put("I_PARENT_ID", parentId);
        paramMap.put("I_STRUCTURE_ID", structureId);
        paramMap.put("I_FROM_DATE", beginTime);
        paramMap.put("I_TO_DATE", END_OF_TIME);

        new SimpleJdbcCall(jdbcTemplate).withProcedureName("TC_INSERT_NODE_A").execute(paramMap);

        paramMap.put("I_NOW_DATE", currentTime);
        new SimpleJdbcCall(jdbcTemplate).withProcedureName("TC_INSERT_NODE_B").execute(paramMap);

The system is seemingly hanging, and the following query appears on Enterprise Manager as taking up 100% of my CPU.

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

What does this mean? Where should I start looking to solve this problem?

Answer 1

This SQL queries the parameters of a stored procedure. It looks very inefficient because it uses several (probably unnecessary) LIKE operators. This can be very slow, in particular if you have a system with lots of PL/SQL and in particular with Oracle 11g, which has gotten much slower for meta data queries like this.

Could it be that the Spring framework runs queries like this a part of calling a stored procedure (I've never used Spring)?