JDBC连接参数设置对Oracle数据库的影响分析

JDBC连接参数设置对Oracle数据库的影响分析

作者介绍

毛思平, 12年IT工作经验,7年数据库管理维护经验,现就职于中国农业银行软开中心,从事数据库应用研究。

一次数据库性能问题处理引发的JDBC参数设置思考

近期某环境下系统,出现大面积页面访问缓慢情况,每个页面交易响应时间2-5秒,严重超过平日访问阈值。

经排查分析,问题主要出现在数据库,生成AWR得到32C的数据库DBtime每小时采样达到4800。进一步分析发现主要等待事件library cache lock,library cache:mutex X。

JDBC连接参数设置对Oracle数据库的影响分析

进一步分析发现DB主要耗时在SQL解析及解析失败上。

JDBC连接参数设置对Oracle数据库的影响分析

生成10035事件,发现数据库日志中有大量解析失败错误,且解析失败的SQL全部带有ROWID列。

JDBC连接参数设置对Oracle数据库的影响分析

将该类SQL反馈研发部门,反馈未在SQL中添加ROWID。那么ROWID是从何而来的呢,查询相关资料最后定位到JDBC连接参数设置不合理。

那么JDBC连接有哪些参数,各个参数作用是什么,参数设置会对SQL解析产生什么样的影响呢?带着这些问题,笔者对JDBC参数设置对SQL解析影响做了针对性实验,大家跟着我来一探究竟吧。

JDBC连接参数说明

JDBC连接数据库主要有两个类分别是PreparedStatement和Statement,两个类可以带三个参数分别是:ResultSetType、ResultSetConcurrency、ResultSetHoldability。由于参数ResultSetConcurrency和ResultSetHoldability对数据库连接影响小,且使用限制较多,因此这里重点介绍参数:ResultSetType。

ResultSetType

ResultSetType的可选值有三个,每个值及对应的作用如下:

  • R esultSet.TYPE_FORWARD_ONLY: 为空时默认值,该值支持结果集前滚遍历操作。   

  • R esultSet.TYPE_SCROLL_INSENSITIVE: 该值支持结果集后滚,随机,取后,取 前等操作,当其它会话对数据 库中数据做出更改时,结果集不随之刷新。

  • ResultSet.TYPE_SCROLL_SENSITIVE: 该值支持结果集后滚,随机,取后,取前等操作,当其它会话对数据库中数据做出更改时,改更会刷新到本结果集中,使结果集中数据与数据库数据实时一致。

连接参数测试

测试环境情况如下表:

JDBC连接参数设置对Oracle数据库的影响分析

在测试数据库中建表psname,psdept,psobj并插入数据,表结构如下:

JDBC连接参数设置对Oracle数据库的影响分析

Oracle中每次程序调用前清空数据库缓存(alter system flush shared_pool清空缓存便于观察SQL执行情况),程序调用结束后查询V$SQL视图,查看解析执行的SQL。

参数设置对解析影响

ROWID是Oracle用于查询表中一行记录的地址,一旦查询中出现了SUM,DISTINCT等聚合或去重函数,获取的数据与单行记录地址不再一一对应,因此多表连接、单表查询带聚合函数、数据去重等SQL解析过程不会出现ROWID,如果出现ROWID则会报解析错误。

测试方法:

  1. 设置JDBC参数ResultSetType=ResultSet.TYPE_FORWARD_ONLY。

  2. 在数据库服务端执行:alter system set events ‘10035 trace name context forever,level 1’,跟踪解析SQL。

  3. 执行程序查看Oracle内部视图V$SQL执行解析结果并截图。

  4. 调整ResultSetType值为TYPE_SCROLL_SENSITIVE和TYPE_SCROLL_INSENSITIVE重复1、2步骤执行测试。

  5. 比对不同参数设置下Oracle中V$SQL显示结果,参数设置对数据库解析影响。

1)测试一,单表简单SQL1:select ID,NAME from psname。

测试结果如下表,执行过程后台未见解析报错(如图3-0),在测试过程中当ResultSetType为TYPE_SCROLL_SENSITIVE时数据库执行会自动加入rowid列。其中12C给rowid自动增加一个别名(如图4-1),11G则不会(如图4-2)。

JDBC连接参数设置对Oracle数据库的影响分析

图3-0 10035事件记录的日志,无错误输出

JDBC连接参数设置对Oracle数据库的影响分析

图3-1 V$SQL查询的最终执行结果

JDBC连接参数设置对Oracle数据库的影响分析

图4-1 V$SQL查询的最终执行结果

JDBC连接参数设置对Oracle数据库的影响分析

图4-2 V$SQL查询的最终执行结果

2)测试二,单表带聚合函数或去重SQL语句:

① select  count(ID) as SUM,dept_id from psname group by dept_id。

② select DISTINCT(OWNER) as OWNER, OBJECT_TYPE FROM TEST001.PSOBJ。 

经测试ResultSetType设置为TYPE_FORWARD_ONLY,TYPE_SCROLL_INSENSITIVE编译不报错,解析执行正常。连接参数ResultSetType设置为TYPE_SCROLL_SENSITIVE时,解析会报错,由于数据库自身编译纠错机制,最终数据库执行结果正常,该情况测试结果如下表:

JDBC连接参数设置对Oracle数据库的影响分析

JDBC连接参数设置对Oracle数据库的影响分析

图5-1 10035事件记录的报错日志

JDBC连接参数设置对Oracle数据库的影响分析

图5-2 V$SQL查询的最终执行结果

图6-1 10035事件记录的报错日志

JDBC连接参数设置对Oracle数据库的影响分析

图6-2 V$SQL查询的最终执行结果

3)测试三,表关联查询SQL3:select

n.id,n.name,d.dept_name from TEST001.PSNAME n ,TEST001.psdept d where n.dept_id=d.dept_id and d.dept_name=’Sales’。

经测试ResultSetType设置为TYPE_FORWARD_ONLY,TYPE_SCROLL_INSENSITIVE编译不报错,解析执行正常。连接参数ResultSetType设置为TYPE_SCROLL_SENSITIVE时,解析会报错,由于数据库自身编译纠错机制,最终数据库执行结果正常,该情况测试结果如下表:

JDBC连接参数设置对Oracle数据库的影响分析

图7-1 10035事件记录的报错日志

JDBC连接参数设置对Oracle数据库的影响分析

图7-2 V$SQL查询的最终执行结果

4)测试四,带条件全列查询SQL4:select * from psname where dept_id=2。

经测试ResultSetType设置为TYPE_FORWARD_ONLY,TYPE_SCROLL_INSENSITIVE编译不报错,解析执行正常。连接参数ResultSetType设置为TYPE_SCROLL_SENSITIVE时,解析会报错,由于数据库自身编译纠错机制,最终数据库执行结果正常,该情况测试结果如下表:

JDBC连接参数设置对Oracle数据库的影响分析

JDBC连接参数设置对Oracle数据库的影响分析

图8-1 10035事件记录的报错日志

JDBC连接参数设置对Oracle数据库的影响分析

图8-2 V$SQL查询的最终执行结果

5)结论:从以上测试情况看出,当ResultSetType等于TYPE_SCROLL_SENSITIVE时,所有SQL编译过程均会在查询结果集前增加ROWID列。当增加ROWID产生语法解析错误时(通过10035事件生产的日志可知),数据库通过内部纠错算法得到正确SQL再执行,于是在V$SQL表中看到不带ROWID的正确已执行的SQL。当JDBC参数ResultSetType等于TYPE_FORWARD_ONLY,TYPE_SCROLL_INSENSITIVE时SQL解析都不会增加ROWID,此两种情况下SQL执行前后完全一致,数据库日志无解析报错输出。

在测试过程中还通过调整不同的环境因素进行测试,发现数据库版本、驱动版本、JDK版本对数据库解析后ROWID产生无影响,对比分析如下表:

JDBC连接参数设置对Oracle数据库的影响分析

参数设置对查询更新数据影响

测试说明:根据参数解释当resultSetType为TYPE_SCROLL_SENSITIVE时,查询结果集会随着数据修改动态更新,其他值无此类特性因此此处测试只测该情况即可。

测试过程:

① 设置JDBC连接参数ResultSetType=ResultSet.TYPE_SCROLL_SENSITIVE。

② 调用程序执行查询。

③ 进入断点,更新psname表中dept_id字段值。   

④ 查看JAVA程序输出图,查看数据库表中结果值图,断点循环取值时查看resultset对应ID值。若JAVA输出结果集与数据库查询一致表明,数据动态更新。

测试结果如下表:

JDBC连接参数设置对Oracle数据库的影响分析

JDBC连接参数设置对Oracle数据库的影响分析

图9:JAVA程序输出结果

JDBC连接参数设置对Oracle数据库的影响分析

图10:数据库查询结果

结论:JDBC连接参数ResultSetType设置为TYPE_SCROLL_SENSITIVE均不会动态更新结果集。

参数设置对JVM影响

测试方法:调整JVM堆大小,查询数据库大表结果集来测试不同参数对查询结果集影响。

测试过程:

① 将JVM大小设置成-Xmx128m -Xms64m -Xmn32m -Xss16m。

② 执行SQL向表PSOBJ插入150万条数据,使表数据大于128M。

③ 设置参数ResultSetType=ResultSet.TYPE_FORWARD_ONLY,程序中执行如下查询:

JDBC连接参数设置对Oracle数据库的影响分析

④ 执行程序查看控制台输出结果并截图。

⑤ 调整ResultSetType值为TYPE_SCROLL_SENSITIVE和TYPE_SCROLL_INSENSITIVE重复1、2步骤执行测试。

测试结果如下表:

JDBC连接参数设置对Oracle数据库的影响分析

JDBC连接参数设置对Oracle数据库的影响分析

图13

JDBC连接参数设置对Oracle数据库的影响分析

图14

结论:当查询结果集大于JVM时,Oracle在ResultSetType为TYPE_FORWARD_ONLY时正常查询,其他值时内存异常;MySQL在所有情况下都会出现内存溢出的情况。

JDBC参数设置建议

从以上测试中我们不难发现,由于开发人员对JDBC连接参数设置不合理,导致了数据库解析SQL产生大量报错,引起了library cache lock,library cache:mutex X等待事件,造成数据库性能急剧下降,最终造成业务系统响应严重超时。

由此建议JDBC中ResultSetType参数设置:

  1. 若只做一般查询默认值即可;

  2. 若需要对结果集进行遍历、比较等相关操作可设置为TYPE_SCROLL_INSENSITIVE;

  3. 不推荐使用TYPE_SCROLL_SENSITIVE,因为该参数不会动态更新结果集,且该参数可能导致数据库SQL解析错误而执行二次解析消耗数据库资源;

  4. 若查询结果集过大,建议使用默认,避免结果集过大造成JVM内存溢出。

从过去40年至今,数据库的形态基本经历了传统商业数据库、开源数据库到原生数据库的演进过程。云时代下数据库将如何革新与创变?来 Gdevops全球敏捷运维峰会北京站 寻找答案:

  • 《All in Cloud 时代,下一代云原生数据库技术与趋势》 阿里巴巴集团副总裁/达摩院首席数据库科学家 李飞飞(飞刀)

  • 《AI和云原生时代的数据库进化之路》腾讯数据库产品中心总经理 林晓斌(丁奇)

  • 《ICBC的MySQL探索之路》 工商银行软件开发中心 魏亚东

  • 《民生银行在SQL审核方面的探索和实践》 民生银行资深数据库专家 李宁宁

  • 《OceanBase分布式数据库在西安银行的落地和实践》 蚂蚁金服P9资深专家/ OceanBase 核心负责人 蒋志勇

  • 金融行业MySQL高可用实践》 爱可生技术总监 明溪源

让我们 9月11日在北京 共同眺望数据库发展变革更长远的未来!

JDBC连接参数设置对Oracle数据库的影响分析

原文 

http://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650791042&idx=2&sn=89cc17fd7f82cd100ea1519f3eaf5c7d

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » JDBC连接参数设置对Oracle数据库的影响分析

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址