对Oracle 语句缓存设置和JDBC PreparedStatement的试验
来源：互联网 发布：java安装包下载 编辑：程序博客网 时间：2024/05/22 00:37
笔者之前曾经写过一篇Sybase ASE在JDBC -Statement和PreparedStatement两种SQL执行方式下执行效率和监控指标的对比文章，本文沿用类似的方法，在Oracle 11gR2数据库中继续验证两种方式在SQL效率和数据库指标上的差异。
一、 Oracle SQL执行过程
借用《Pro OracleSQL》书中对Oracle中SQL语句执行过程的截图：
AskTom网站对hard parse、soft parse和fast parse（soft soft parse）的解释如下：
HARD parse -- the query has neverbeen seen before, isn't in the shared pool. We must parse it, hash it, look inthe shared pool for it, don't find it, security check it, optimize it, etc(lots of work).
SOFT parse -- the query has beenseen before, is in the shared poo. We have to parse it, hash it, look inthe shared pool for it and find it (less work then a hard parse but work nonethe less)
a kinder, softer SOFT parse -- youare using session_cached_cursors (search this site for that word forinfo). We take your query, look in the sessions cursor cache -- find thecursor and reuse it. Very very little work.
由此可见，一条SQL传入Oracle后，如果它从没有被执行过，则会经过语法、语义检查，分析并产生执行计划等多个步骤，耗时耗资源（hard parse）；
如果这条SQL语句已经被执行过，则相应的执行计划已被缓存，Oracle仅经过语法、语义检查即可，这条SQL的执行计划可以在library cache中找到并直接使用；
如果Oracle设置了SESSION_CACHED_CURSORS，则SQL的执行计划在PGA的private sql area中还好保留副本，且直接被使用，较soft parse更快。本次实验同使用JDBC –Statement和PreparedStatement两种SQL执行方式对SQL解析进行验证。
试验中使用Oracle11gR2，创建一个多字段的表，插入50w条随机数据。与Oracle的JDBC连接访问使用LoadRunner Java Vuser协议调用Oracle提供的ojdbc6.jar驱动，SQL语句的提交通过LoadRunner的参数化尽量实现动态。压力场景采用10并发（进程模式）执行5分钟，Oracle AWR SnapShot采集场景执行1分钟和4分钟左右的结果。
二、 数据库环境
1. 数据库基本配置
l Oracle数据库相关配置参数如下：
l optimizer_features_enable：11.2.0.1
l optimizer_mode：ALL_ROWS
l CPU count：4
l memory_max_target：1232 MB
l memory_target：1232 MB
l sga_max_size：744 MB
l sga_target：0 MB（Oracle自动管理）
l session_cached_cursors：50
l open_cursors：300
l cursor_space_for_time：FALSE
l cursor_sharing：EXACT
2. 创建数据库表
CREATE TABLE HR.LOT_TEST
(
"LOT_DATE" INTEGER NOT NULL,
"RED_1" INTEGER NOTNULL,
"RED_2" INTEGER NOTNULL,
"RED_3" INTEGER NOTNULL,
"RED_4" INTEGER NOTNULL,
"RED_5" INTEGER NOT NULL,
"RED_6" INTEGER NOTNULL,
"BLUE" INTEGER NOTNULL
)
ORGANIZATION HEAP
TABLESPACE USERS
NOLOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE(PCTINCREASE 0
BUFFER_POOL DEFAULT)
NOPARALLEL
CACHE
/
CREATE INDEX HR.IDX_LOT_BLUE
ON HR.LOT_TEST("BLUE")
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
PCTINCREASE 0
BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
ALTER TABLE HR.LOT_TEST
ADD CONSTRAINT PK_LOT_DATE
PRIMARY KEY ("LOT_DATE")
USING INDEX TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(PCTINCREASE 0
BUFFER_POOL DEFAULT)
ENABLE
VALIDATE
/
3. 创建SEQUENCE，用以向字段LOT_DATE中插入自增值
CREATE SEQUENCE HR.S_LOT_DATE
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
4. 创建插入随机数据的存储过程
CREATE OR REPLACE PROCEDUREHR.PROC_LOT_INSERT
AS
I INT;
BEGIN
I:=1;
WHILE I<50000 LOOP
INSERT INTO HR.LOT_TESTVALUES(HR.S_LOT_DATE.NEXTVAL,DBMS_RANDOM.VALUE(1,50000),DBMS_RANDOM.VALUE(1,50000),DBMS_RANDOM.VALUE(1,10000),DBMS_RANDOM.VALUE(1,10000),DBMS_RANDOM.VALUE(1,5000),DBMS_RANDOM.VALUE(1,5000),DBMS_RANDOM.VALUE(1,50));
I:=I+1;
END LOOP;
COMMIT;
END;
插入50w条记录，其中个字段随机取值的范围不同。
三、 LoadRunner JDBC脚本
编写LoadRunner JDBC连接Oracle的脚本，Run-time settings的Classpath中加入Oracle 11gR2提供的JDBC包（本例中使用的是ojdbc6.jar），同时在本机安装1.6的JVM。查询LOT_TEST表中的列，对查询列的值进行随机参数化。
1. Statement方式
LoadRunner脚本暂略，如有需要请和我联系。
2. PreparedStatement方式
LoadRunner脚本暂略，如有需要请和我联系。四、 测试结果–限制迭代间隔
第一个测试场景限制了LoadRunnerAction的迭代间隔，在相同TPS下观察Oracle AWR中关于解析方面的各指标值，设置10VUsers，执行5分钟，脚本pacing为fixed 1秒。
场景执行到1分钟左右时手工生成第一个Oracle SnapShot，4分钟左右生成第二个SnapShot，AWR取两个SnapShot之间的结果：
1. JDBC Statement
测试项
VUser数量
测试执行时间
（分钟）
Query平均TPS
（笔/秒）
Query平均响应时间
（秒）
Statement
10
5
10
0.013
Update平均TPS
（笔/秒）
Update平均响应时间
（秒）
Insert平均TPS
（笔/秒）
Insert平均响应时间
（秒）
10
0.008
10
0.003
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
0.1
36.7
30.9
69.36
6.71
55.88
15.77
TPS：
响应时间：
2. JDBC PreparedStatement
测试项
VUser数量
测试执行时间
（分钟）
Query平均TPS
（笔/秒）
Query平均响应时间
（秒）
PreparedStatement
10
5
10
0.011
Update平均TPS
（笔/秒）
Update平均响应时间
（秒）
Insert平均TPS
（笔/秒）
Insert平均响应时间
（秒）
10
0.007
10
0.004
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
0.1
7.6
0.8
94.03
84.90
65.96
89.39
TPS：
响应时间：
从测试结果可以看出，由于限制了脚本间隔，因此两种方式下TPS、响应时间相差甚微。
五、 测试结果–不限制迭代间隔
第二个测试场景不限制LoadRunnerAction的迭代间隔，在资源消耗最大的情况下观察OracleAWR中关于解析方面的各指标值，设置10 VUsers，执行5分钟，脚本pacing为fixed 1秒。
场景执行到1分钟左右时手工生成第一个Oracle SnapShot，4分钟左右生成第二个SnapShot，AWR取两个SnapShot之间的结果：
1. JDBC Statement
测试项
VUser数量
测试执行时间
（分钟）
Query平均TPS
（笔/秒）
Query平均响应时间
（秒）
Statement
10
5
263.314
0.012
Update平均TPS
（笔/秒）
Update平均响应时间
（秒）
Insert平均TPS
（笔/秒）
Insert平均响应时间
（秒）
263.314
0.012
263.314
0.012
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
5.0
795.4
773.2
66.98
2.83
94.07
2.80
TPS：
响应时间：
2. JDBC PreparedStatement
测试项
VUser数量
测试执行时间
（分钟）
Query平均TPS
（笔/秒）
Query平均响应时间
（秒）
PreparedStatement
10
5
296.263
0.011
Update平均TPS
（笔/秒）
Update平均响应时间
（秒）
Insert平均TPS
（笔/秒）
Insert平均响应时间
（秒）
296.263
0.011
296.263
0.009
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
4.8
19.8
0.6
99.90
97.73
30.77
96.98
TPS：
响应时间：
从结果可以看出，由于不限制脚本迭代的间隔，因此各场景TPS均非常高，但PreparedStatement方式下总TPS要高于Statement方式。
由于两种方式下TPS均很高，测试表操作频繁，因此随着场景执行，总TPS呈下降趋势。
六、 测试结论
1. 限制迭代间隔
在限制LoadRunner脚本迭代间隔场景时，Statement和PreparedStatement的AWR数值对比如下：
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
Statement
0.1
36.7
30.9
69.36
6.71
55.88
15.77
PreparedStatement
0.1
7.6
0.8
94.03
84.90
65.96
89.39
l DB Time per sec：由于限制了SQL语句每秒提交的次数，因此该值差别不大。
l Parses per sec：由于该值包含了hard parses，因此Statement方式下该值远高于PreparedStatement方式；
l Hard parses per sec：Statement方式下该值要远高于PreparedStatement方式。
l Library Hit%：Statement方式下该值要低于PreparedStatement方式，该值的目标值是100%（本例中PreparedStatement方式下只有94%左右，一般可以理解为该值不应低于90%）。
l Execute to Parse %：Statement方式下该值远低于PreparedStatement方式，Execute to Parse %=100*（1-Parses/Executions），表示SQL语句执行与解析的比例，该值越高越好，本例中Statement方式下差不多执行10条SQL就要解析9.4条，而PreparedStatement方式下基本执行10条SQL就解析1.5条（本例中该值不算很高，但实验过程中其他轮次时PreparedStatement方式下该值可以达到97+）。
l Parse CPU to Parse Elapsd%：Statement方式下该值小于PreparedStatement方式，但相差不大，理论上该值越高越好。
l Soft Parse%：Statement方式下该值远低于PreparedStatement方式，该值越高越好（本例中该值不算很高，但实验过程中其他轮次时PreparedStatement方式下该值可以达到95+）。
2. 不限制迭代间隔
在不限制LoadRunner脚本迭代间隔场景时，LoadRunner及系统指标对比如下：
测试项
VUser数量
总TPS
CPU利用率
Average Available Mbytes
Average %Disk Time
Average Disk Transfers/sec
Statement
10
1053.32
97.22%
6010.02
10.821
120.965
PreparedStatement
10
1185.117
96.426%
6447.369
32.056
242.898
由于不限制脚本迭代间隔，因此SQL语句满负荷消耗系统资源，CPU利用率相差无几，但PreparedStatement方式下省去了SQL语句的解析过程，压力都转移到SQL的执行、结果集返回和log记录上，Disk的利用率明显更高。
Statement和PreparedStatement的AWR数值对比如下：
DB Time per sec
Parses per sec
Hard parses per sec
Library Hit %
Execute to Parse %
Parse CPU to Parse Elapsd %
Soft Parse %
Statement
5.0
795.4
773.2
66.98
2.83
94.07
2.80
PreparedStatement
4.8
19.8
0.6
99.90
97.73
30.77
96.98
l DB Time per sec：由于不限制SQL语句每秒提交的次数，因此资源消耗较大，PreparedStatement方式下总TPS较Statement方式下高，而DB Time per sec反而小。
l Parses per sec：由于该值包含了hard parses，因此Statement方式下该值远高于PreparedStatement方式；
l Hard parses per sec：Statement方式下该值要远高于PreparedStatement方式。
l Library Hit%：Statement方式下该值要低于PreparedStatement方式，该值的目标值是100%。
l Execute to Parse %：Statement方式下该值远低于PreparedStatement方式，Execute to Parse %=100*（1-Parses/Executions），表示SQL语句执行与解析的比例，该值越高越好，本例中Statement方式下差不多执行10条SQL就要解析9.7条，而PreparedStatement方式下基本执行10条SQL就解析0.2条。
l Parse CPU to Parse Elapsd%：Statement方式下该值大于PreparedStatement方式，理论上该值越高越好，但从多轮实验结果来看，PreparedStatement方式下该值确实较低，AWR报告中latch也没有特别高，目前尚不知如何解释。
l Soft Parse%：Statement方式下该值远低于PreparedStatement方式，该值越高越好。
3. 其他数值对比
在Oracle AWR报告的“Complete List of SQL Text”部分也能看到，PreparedStatement方式下截取的SQL语句是带变量的：
执行SQL语句：selectt.sql_text,t.sql_id,t.PARSE_CALLS,t.EXECUTIONS from v$sql t where sql_text like'select red_1%' or sql_text like 'update hr.lot_test%' or sql_text like 'insertinto hr.lot%';查看SQL语句解析次数，可以看到所有SQL语句均解析且仅执行了一次：
而Statement方式下截取的SQL语句则不带变量，根据每次提交的查询条件不同而不同：
执行SQL语句：selectt.sql_text,t.sql_id,t.PARSE_CALLS,t.EXECUTIONS from v$sql t where sql_text like'select red_1%' or sql_text like 'update hr.lot_test%' or sql_text like 'insertinto hr.lot%';查看SQL语句解析次数，可以看到select、update、insert语句仅解析了一次，而执行了多次：
使用Spotlight查看自动管理模式下SGA里各种缓存的分布，Statement方式下shared_pool要比PreparedStatement方式占用更多：
Statement方式下shared_pool分配了500M+的空间，而占用率400M+：
PreparedStatement方式下shared_pool同样分配了500M+，但仅占用了100M+：
最后测试结果也受Oracle版本、测试环境、测试工具和笔者的理解能力所限，可能存在错误，仅供参考！
- 对Oracle 语句缓存设置和JDBC PreparedStatement的试验
- JDBC的Statement和PreparedStatement
- Oracle JDBC中的语句缓存
- 对JDBC中preparedStatement的解释
- JDBC oracle PreparedStatement
- Statement 和PreparedStatement 批量处理JDBC语句提高处理速度
- JDBC的Statement和PreparedStatement区别
- JDBC的Statement和PreparedStatement区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- jdbc中 PreparedStatement 和Statement的区别
- JDBC的Statement和PreparedStatement区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- JDBC中的Statement和PreparedStatement的区别
- java nio http服务器（1）读取http请求
- 61、实例 jQuery管理页面中的多个库
- Java面试题
- 计算出某年某月某日是星期几
- touch
- 对Oracle 语句缓存设置和JDBC PreparedStatement的试验
- SqlServer数据库还原或更新 脱机和联机操作
- Android 侧边栏快速索引（点击索引、滑动索引），通讯录样式
- 获取手机唯一标示符
- mac 下vim 配置文件
- Android应用发布渠道汇总
- 士兵杀敌（一）
- 嵌入式Linux学习笔记（基于S5PV210 TQ210）之裸机编程ddr
- IE中@responseBody出现文件下载的解决办法
