每天看看库上的awr报告,发现有条sql三个小时内执行了190多万次:
SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE '/' AND o.object_type IN ('xxx', 'TABLE') ORDER BY table_type, table_schem, table_name |
一开始以为是用户程序执行的,仔细一看,发现像是oracle内部sql,百度了些资料,确实有其它道友遇到类似情况,但没有给出详细说明。
看到一篇盖国强的文章,语句和这条相似:
SELECT NULL AS table_cat, c.owner AS table_schem, c.table_name,
c.column_name, c.POSITION AS key_seq, c.constraint_name AS pk_name
FROM all_cons_columns c, all_constraints k
WHERE k.constraint_type = 'P'
AND k.table_name = :1
AND k.owner LIKE :2 ESCAPE '/'
AND k.constraint_name = c.constraint_name
AND k.table_name = c.table_name
AND k.owner = c.owner
ORDER BY column_name
给出的解释是:当JAVA程序中通过DatabaseMetaData#getPrimaryKeys()调用,将会执行这个SQL,如果不及时关闭,则还可能产生严重的内存占用。
看了些其它的资料,也是说的java在调用某些函数的使用应用到该语句,所以呢,最好还是让开发排查一下。
但事情往往推到开发那,就需要耗费大量口舌了。。。
---------------------------------------------------------------------------------------
后续补充2015-08-03
在metalink上面找到这样一篇文章(Doc ID 1492529.1)
导致出现大量执行上述sql的原因是:
This is caused by C3PO, a 3rd party connection pooling library used in the Liferay product.
These queries represent a "ping" to the database to test connection availability, and will do so with a lightweight query that returns no results.
解决方法:
修改portal-ext.properties文件中的参数jdbc.default.idleConnectionTestPeriod的值为一个较大的值,默认是60