之前曾经写过一篇Sybase ASE在JDBC - Statement和PreparedStatement两种SQL执行方式下执行效率和监控指标的对比文章，本文沿用类似的方法，在Oracle 11gR2数据库中继续验证两种方式在SQL效率和数据库指标上的差异。
一、 Oracle SQL执行过程
借用《Pro Oracle SQL》书中对Oracle中SQL语句执行过程的截图：
[attach]91082[/attach]
AskTom网站对hard parse、soft parse和fast parse（soft soft parse）的解释如下：
HARD parse -- the query has never been seen before, isn't in the shared pool. We must parse it, hash it, look in the shared pool for it, don't find it, security check it, optimize it, etc lots of work).
SOFT parse -- the query has been seen before, is in the shared poo. We have to parse it, hash it, look in the shared pool for it and find it less work then a hard parse but work none the less)
a kinder, softer SOFT parse -- you are using session_cached_cursors search this site for that word for info). We take your query, look in the sessions cursor cache -- find the cursor 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解析进行验证。
试验中使用Oracle 11gR2，创建一个多字段的表，插入50w条随机数据。与Oracle的JDBC连接访问使用LoadRunner Java Vuser协议调用Oracle提供的ojdbc6.jar驱动，SQL语句的提交通过LoadRunner的参数化尽量实现动态。压力场景采用10并发（进程模式）执行5分钟，Oracle AWR Snapshot采集场景执行1分钟和4分钟左右的结果。
二、 数据库环境
1. 数据库基本配置
Oracle数据库相关配置参数如下：
optimizer_features_enable：11.2.0.1
optimizer_mode：ALL_ROWS
CPU count：4
memory_max_target：1232 MB
memory_target：1232 MB
sga_max_size：744 MB
sga_target：0 MB（Oracle自动管理）
session_cached_cursors：50
open_cursors：300
cursor_space_for_time：FALSE
cursor_sharing：EXACT
2. 创建数据库表
CREATE TABLE HR.LOT_TEST
(
"LOT_DATE" INTEGER NOT NULL,
"RED_1" INTEGER NOT NULL,
"RED_2" INTEGER NOT NULL,
"RED_3" INTEGER NOT NULL,
"RED_4" INTEGER NOT NULL,
"RED_5" INTEGER NOT NULL,
"RED_6" INTEGER NOT NULL,
"BLUE" INTEGER NOT NULL
)
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 PROCEDURE HR.PROC_LOT_INSERT
AS
I INT;
BEGIN
I:=1;
WHILE I<50000 LOOP
INSERT INTO HR.LOT_TEST VALUES(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;
//组建动态SQL
String SQLQuery = "select red_1,blue from hr.lot_test where red_1 = ? and red_2 = ? and red_3 = ? and red_4 = ? and red_5 = ? and red_6 = ? and blue = ?";
String SQLUpdate = "update hr.lot_test set red_2 = ?,red_3 = ?,red_4 = ?,red_5 = ?,red_6 = ? where blue = ? and red_1 = ?";
String SQLInsert = "insert into hr.lot_test values(hr.s_lot_date.nextval,?,?,?,?,?,?,?)";
DB Time per sec：由于限制了SQL语句每秒提交的次数，因此该值差别不大。
Parses per sec：由于该值包含了hard parses，因此Statement方式下该值远高于PreparedStatement方式；
Hard parses per sec：Statement方式下该值要远高于PreparedStatement方式。
Library Hit%：Statement方式下该值要低于PreparedStatement方式，该值的目标值是100%（本例中PreparedStatement方式下只有94%左右，一般可以理解为该值不应低于90%）。
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+）。
Parse CPU to Parse Elapsd%：Statement方式下该值小于PreparedStatement方式，但相差不大，理论上该值越高越好。
Soft Parse%：Statement方式下该值远低于PreparedStatement方式，该值越高越好（本例中该值不算很高，但实验过程中其他轮次时PreparedStatement方式下该值可以达到95+）。
DB Time per sec：由于不限制SQL语句每秒提交的次数，因此资源消耗较大，PreparedStatement方式下总TPS较Statement方式下高，而DB Time per sec反而小。
Parses per sec：由于该值包含了hard parses，因此Statement方式下该值远高于PreparedStatement方式；
Hard parses per sec：Statement方式下该值要远高于PreparedStatement方式。
Library Hit%：Statement方式下该值要低于PreparedStatement方式，该值的目标值是100%。
Execute to Parse %：Statement方式下该值远低于PreparedStatement方式，Execute to Parse %=100*（1-Parses/Executions），表示SQL语句执行与解析的比例，该值越高越好，本例中Statement方式下差不多执行10条SQL就要解析9.7条，而PreparedStatement方式下基本执行10条SQL就解析0.2条。
Parse CPU to Parse Elapsd%：Statement方式下该值大于PreparedStatement方式，理论上该值越高越好，但从多轮实验结果来看，PreparedStatement方式下该值确实较低，AWR报告中latch也没有特别高，目前尚不知如何解释。
Soft Parse%：Statement方式下该值远低于PreparedStatement方式，该值越高越好。
3. 其他数值对比
在Oracle AWR报告的“Complete List of SQL Text”部分也能看到，PreparedStatement方式下截取的SQL语句是带变量的：
[attach]91098[/attach]
执行SQL语句：select t.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 'insert into hr.lot%';查看SQL语句解析次数，可以看到所有SQL语句均解析且仅执行了一次：
[attach]91099[/attach]
而Statement方式下截取的SQL语句则不带变量，根据每次提交的查询条件不同而不同：
[attach]91100[/attach]
执行SQL语句：select t.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 'insert into hr.lot%';查看SQL语句解析次数，可以看到select、update、insert语句仅解析了一次，而执行了多次：
[attach]91101[/attach]
使用Spotlight查看自动管理模式下SGA里各种缓存的分布，Statement方式下shared_pool要比PreparedStatement方式占用更多：
Statement方式下shared_pool分配了500M+的空间，而占用率400M+：
[attach]91102[/attach]
PreparedStatement方式下shared_pool同样分配了500M+，但仅占用了100M+：
[attach]91103[/attach]
racle:thin
192.168.1.11:1521