Strange SQL wasting my resources

Question

I'm facing an issue on my 11.2.0.3 database, with a strange query that is responsible for 47.42% of total DB activity.

The app was developed with Flex and the front end is an Apache Tomcat 6.0.35 with Java version 1.6.0_27.

I have searched on several sites and found other people with same issue, even on this site ( Mysterious SQL blocking my stored procedure from executing on ORACLE ), but no solution found so far :-(

The query is:

     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

I'm wondering if someone has found the solution?

Answer 1

Does your code, perchance, use Spring's SimpleJdbcCall class? This class attempts to get information about the stored procedure and caches the results. So make sure you instantiate the SimpleJdbcCall class once, and reuse it for each invocation of the stored procedure.

Alternately, you can call withoutProcedureColumnMetaDataAccess() when constructing the SimpleJdbcCall object.