我在11.2.0.3数据库上遇到一个问题,一个奇怪的查询占数据库活动总量的47.42%。
该应用程序是使用Flex开发的,前端是具有Java版本1.6.0_27的Apache Tomcat 6.0.35。
我已经在几个站点上进行了搜索,甚至在这个站点(Mysterious SQL blocking my stored procedure from executing on ORACLE)上也发现了其他有相同问题的人,但是到目前为止没有找到解决方案:-(
查询是:
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
我想知道是否有人找到了解决方案?
最佳答案
您的代码是否使用Spring的SimpleJdbcCall类?此类尝试获取有关存储过程的信息并缓存结果。因此,请确保实例化一次SimpleJdbcCall类,并将其重新用于存储过程的每次调用。
或者,在构造SimpleJdbcCall对象时,可以调用withoutProcedureColumnMetaDataAccess()。