1、AWR检查,发现两个小时内SQL执行了536w次,此SQL非常异常,具体SQL如下:
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
2、经分析该SQL并非业务系统使用,通过翻阅资料发现是由jboss c3p0的默认测试连接所致,也就是通过DatabaseMetaData.getTABLES()操作调用执行的
3、经测试,在c3p0-service.xml配置文件设置PreferredTestQuery修改测试连接的Oracle SQL,具体内容如下:
select 1 from dual
总结:修改后不会再频繁执行原先复杂的sql,减少对数据库资源的占用。