c3p0连接oracle 配置 C3P0 默认测试连接引起的问题

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，减少对数据库资源的占用。