java - 神秘的 SQL 阻止我的存储过程在 ORACLE 上执行

Question

我正在尝试使用瘦 jdbc 客户端和 c3p0 在 ORACLE 上运行一个过程。

这是应该运行的代码:

        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);

系统似乎挂起，并且以下查询在企业管理器上显示，占用了 100% 的 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

这是什么意思？我应该从哪里开始寻找解决这个问题的方法？

Answer 1

此 SQL 查询存储过程的参数。它看起来效率很低，因为它使用了多个(可能不必要的)LIKE 运算符。这可能会非常慢，特别是如果您的系统有大量 PL/SQL，尤其是 Oracle 11g，对于像这样的元数据查询来说，它会变得慢得多。

Spring 框架是否会在调用存储过程时运行这样的查询(我从未使用过 Spring)？