奇怪的 SQL 浪费我的资源 [英] Strange SQL wasting my resources

问题描述

我的 11.2.0.3 数据库遇到一个问题，一个奇怪的查询占总数据库活动的 47.42%.

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.

该应用使用 Flex 开发，前端为 Apache Tomcat 6.0.35，Java 版本为 1.6.0_27.

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

我在几个网站上搜索过，发现其他人也有同样的问题，即使在这个网站上也是如此(神秘的 SQL 阻止了我的存储过程在 ORACLE 上执行 )，但目前没有找到解决方案:-(

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 :-(

查询是:

     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?

推荐答案

您的代码是否使用 Spring 的 SimpleJdbcCall 类?此类尝试获取有关存储过程的信息并缓存结果.因此，请确保将 SimpleJdbcCall 类实例化一次，并在每次调用存储过程时重复使用它.

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.

或者，您可以在构造 SimpleJdbcCall 对象时调用 withoutProcedureColumnMetaDataAccess().

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

这篇关于奇怪的 SQL 浪费我的资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！