神秘的SQL阻止我的存储过程在ORACLE上执行 [英] Mysterious SQL blocking my stored procedure from executing on ORACLE

问题描述

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

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

这是应该运行的代码:

        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.

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?

推荐答案

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

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.

Spring框架是否可以像这样运行查询，这是调用存储过程的一部分(我从未使用过Spring)?

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

这篇关于神秘的SQL阻止我的存储过程在ORACLE上执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！