对Oracle 语句缓存设置和JDBC PreparedStatement的试验

dionysus

之前曾经写过一篇Sybase ASE在JDBC - Statement和PreparedStatement两种SQL执行方式下执行效率和监控指标的对比文章，本文沿用类似的方法，在Oracle 11gR2数据库中继续验证两种方式在SQL效率和数据库指标上的差异。
一、        Oracle SQL执行过程
借用《Pro Oracle SQL》书中对Oracle中SQL语句执行过程的截图：

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;

插入50w条记录，其中个字段随机取值的范围不同。
三、        LoadRunner JDBC脚本
编写LoadRunner JDBC连接Oracle的脚本，Run-time settings的Classpath中加入Oracle 11gR2提供的JDBC包（本例中使用的是ojdbc6.jar），同时在本机安装1.6的JVM。查询LOT_TEST表中的列，对查询列的值进行随机参数化。
1.        &#83;tatement方式
import java.io.*;   
import java.sql.*;
import java.util.*;

import lrapi.lr;
public class Actions
{
        Connection conn = null;
    Statement stmts = null;
        Statement stmtu = null;
        Statement stmti = null;
    ResultSet rs = null;  
        int irs;

        String url = "jdbcracle:thin192.168.1.11:1521rcl" ;
        String username = "system";
        String paswd = "Linux1111";
       
        public int init() throws Throwable {
            
        lr.start_transaction("Create Connection");
               
            try{

                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(url, username, paswd);
                stmts = conn.createStatement();
                stmtu = conn.createStatement();
                stmti = conn.createStatement();

            }catch(Exception ex){

                System.out.println("Init Error: "+ex.toString());
            }
            
        lr.end_transaction("Create Connection", lr.AUTO);

                return 0;
        }//end of init
        public int action() throws Throwable {

            try {
               
                lr.start_transaction("Create SQL");

                //组建动态SQL
                //String SQLQuery = "select lot_date from hr.lot_test where rownum < 10";
                String SQLQuery = "select red_1,blue from hr.lot_test where red_1 = " + "<SQLQuery_1>" + " and red_2 = " + "<SQLQuery_2>" + " and red_3 = " + "<SQLQuery_3>" + " and red_4 = " + "<SQLQuery_4>" + " and red_5 = " + "<SQLQuery_5>" + " and red_6 = " + "<SQLQuery_6>" + " and blue = " + "<SQLQuery_b>";
                String SQLUpdate = "update hr.lot_test set red_2 = " + "<SQLQuery_2>" + ",red_3 = " + "<SQLQuery_3>" + ",red_4 = " + "<SQLQuery_4>" + ",red_5 = " + "<SQLQuery_5>" + ",red_6 = " + "<SQLQuery_6>" + " where blue = " + "<SQLQuery_b>" + " and red_1 = " + "<SQLQuery_1>";
                String SQLInsert = "insert into hr.lot_test values(hr.s_lot_date.nextval," + "<SQLQuery_1>," + "<SQLQuery_2>," + "<SQLQuery_3>," + "<SQLQuery_4>," + "<SQLQuery_5>," + "<SQLQuery_6>," + "<SQLQuery_b>)";
               
                lr.output_message("the SQLQuery is: " + SQLQuery);
                lr.output_message("the SQLUpdate is: " + SQLUpdate);
                lr.output_message("the SQLInsert is: " + SQLInsert);
                               
                lr.end_transaction("Create SQL", lr.AUTO);
               
                lr.start_transaction("SQLQuery");

                rs = stmts.executeQuery(SQLQuery);

                while(rs.next())
                 {
                    //避免显示过多，对返回结果仅显示OK
                    lr.output_message("OK");
                    //lr.output_message("T.EMPLOYEE_ID = "+ rs.getInt(1));

                 }
               
                lr.end_transaction("SQLQuery", lr.AUTO);

                lr.start_transaction("SQLUpdate");

                irs = stmtu.executeUpdate(SQLUpdate);

                lr.end_transaction("SQLUpdate", lr.AUTO);

                lr.start_transaction("SQLInsert");

                irs = stmti.executeUpdate(SQLInsert);

                lr.end_transaction("SQLInsert", lr.AUTO);


                if(rs != null)
                {
                    rs.close();
                }
               
            } catch (Exception ex) {

                System.out.println("Action Error: "+ex.toString());
            }
                return 0;
        }//end of action
        public int end() throws Throwable {
            
        lr.start_transaction("Destroy Connection");

            try{
                if(stmts != null)
                {
                    stmts.close();
                }
                if(stmtu != null)
                {
                    stmtu.close();
                }
                if(stmti != null)
                {
                    stmti.close();
                }
                if(conn != null)
                {
                    conn.close();
                }
            }catch(Exception ex){
                System.out.println("End Error: "+ex.toString());
            }
            
        lr.end_transaction("Destroy Connection", lr.AUTO);

                return 0;
        }
}

2.        Prepared&#83;tatement方式
import java.io.*;   
import java.sql.*;
import java.util.*;  

import lrapi.lr;
public class Actions
{
        Connection conn = null;
    PreparedStatement pstmts = null;
        PreparedStatement pstmtu = null;
        PreparedStatement pstmti = null;
    ResultSet rs = null;
        int irs;

        String url = "jdbcracle:thin192.168.1.11:1521rcl" ;
        String username = "system";
        String paswd = "Linux1111";
       
        public int init() throws Throwable {

            try{
                lr.start_transaction("Create Connection");

                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(url, username, paswd);
               
                //组建动态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,?,?,?,?,?,?,?)";

                pstmts = conn.prepareStatement(SQLQuery);
                pstmtu = conn.prepareStatement(SQLUpdate);
                pstmti = conn.prepareStatement(SQLInsert);
       
                lr.end_transaction("Create Connection", lr.AUTO);
               
            }catch(Exception ex){

                System.out.println("Init Error: "+ex.toString());
            }

                return 0;
        }//end of init
        public int action() throws Throwable {

            try {
                lr.start_transaction("Create SQL");
               
                //lr.output_message("SQLQuery is: " + SQLQuery);
                //预编译向查询条件中赋值
                pstmts.setInt(1,Integer.parseInt("<SQLQuery_1>"));
                pstmts.setInt(2,Integer.parseInt("<SQLQuery_2>"));
                pstmts.setInt(3,Integer.parseInt("<SQLQuery_3>"));
                pstmts.setInt(4,Integer.parseInt("<SQLQuery_4>"));
                pstmts.setInt(5,Integer.parseInt("<SQLQuery_5>"));
                pstmts.setInt(6,Integer.parseInt("<SQLQuery_6>"));
                pstmts.setInt(7,Integer.parseInt("<SQLQuery_b>"));

                //预编译Update语句
                pstmtu.setInt(1,Integer.parseInt("<SQLQuery_2>"));
                pstmtu.setInt(2,Integer.parseInt("<SQLQuery_3>"));
                pstmtu.setInt(3,Integer.parseInt("<SQLQuery_4>"));
                pstmtu.setInt(4,Integer.parseInt("<SQLQuery_5>"));
                pstmtu.setInt(5,Integer.parseInt("<SQLQuery_6>"));
                pstmtu.setInt(6,Integer.parseInt("<SQLQuery_b>"));
                pstmtu.setInt(7,Integer.parseInt("<SQLQuery_1>"));

                //预编译Insert语句
                pstmti.setInt(1,Integer.parseInt("<SQLQuery_1>"));
                pstmti.setInt(2,Integer.parseInt("<SQLQuery_2>"));
                pstmti.setInt(3,Integer.parseInt("<SQLQuery_3>"));
                pstmti.setInt(4,Integer.parseInt("<SQLQuery_4>"));
                pstmti.setInt(5,Integer.parseInt("<SQLQuery_5>"));
                pstmti.setInt(6,Integer.parseInt("<SQLQuery_6>"));
                pstmti.setInt(7,Integer.parseInt("<SQLQuery_b>"));

                lr.end_transaction("Create SQL", lr.AUTO);

                lr.start_transaction("SQLQuery");

                ResultSet rs = pstmts.executeQuery();

                while(rs.next())
                 {
                    //避免显示过多，对返回结果仅显示OK
                    //lr.output_message("OK");

                 }
                lr.end_transaction("SQLQuery", lr.AUTO);

                lr.start_transaction("SQLUpdate");

                irs = pstmtu.executeUpdate();

                lr.end_transaction("SQLUpdate", lr.AUTO);

                lr.start_transaction("SQLInsert");

                irs = pstmti.executeUpdate();

                lr.end_transaction("SQLInsert", lr.AUTO);

                if(rs != null)
                {
                    rs.close();
                }
               
            } catch (Exception ex) {

                System.out.println("Action Error: "+ex.toString());
            }
                return 0;
        }//end of action
        public int end() throws Throwable {
            
        lr.start_transaction("Destroy Connection");

            try{
                if(pstmts != null)
                {
                    pstmts.close();
                }
                if(pstmtu != null)
                {
                    pstmtu.close();
                }
                if(pstmti != null)
                {
                    pstmti.close();
                }
                if(conn != null)
                {
                    conn.close();
                }
            }catch(Exception ex){

                System.out.println("End Error: "+ex.toString());
            }
            
        lr.end_transaction("Destroy Connection", lr.AUTO);

                return 0;
        }
}

四、        测试结果–限制迭代间隔
第一个测试场景限制了LoadRunner Action的迭代间隔，在相同TPS下观察Oracle AWR中关于解析方面的各指标值，设置10 VUsers，执行5分钟，脚本pacing为fixed 1秒。
场景执行到1分钟左右时手工生成第一个Oracle SnapShot，4分钟左右生成第二个SnapShot，AWR取两个SnapShot之间的结果：
1.        JDBC Statement


TPS：


响应时间：

2.        JDBC PreparedStatement


TPS：


响应时间：

从测试结果可以看出，由于限制了脚本间隔，因此两种方式下TPS、响应时间相差甚微。
五、        测试结果–不限制迭代间隔
第二个测试场景不限制LoadRunner Action的迭代间隔，在资源消耗最大的情况下观察Oracle AWR中关于解析方面的各指标值，设置10 VUsers，执行5分钟，脚本pacing为fixed 1秒。
场景执行到1分钟左右时手工生成第一个Oracle SnapShot，4分钟左右生成第二个SnapShot，AWR取两个SnapShot之间的结果：
1.        JDBC Statement


TPS：


响应时间：

2.        JDBC PreparedStatement

TPS：


响应时间：

从结果可以看出，由于不限制脚本迭代的间隔，因此各场景TPS均非常高，但PreparedStatement方式下总TPS要高于Statement方式。
由于两种方式下TPS均很高，测试表操作频繁，因此随着场景执行，总TPS呈下降趋势。
六、        测试结论
1.        限制迭代间隔
在限制LoadRunner脚本迭代间隔场景时， Statement和PreparedStatement的AWR数值对比如下：


        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+）。

2.        不限制迭代间隔
在不限制LoadRunner脚本迭代间隔场景时，LoadRunner及系统指标对比如下：


由于不限制脚本迭代间隔，因此SQL语句满负荷消耗系统资源，CPU利用率相差无几，但PreparedStatement方式下省去了SQL语句的解析过程，压力都转移到SQL的执行、结果集返回和log记录上，Disk的利用率明显更高。

Statement和PreparedStatement的AWR数值对比如下：


        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语句是带变量的：

执行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语句均解析且仅执行了一次：


而&#83;tatement方式下截取的&#83;QL语句则不带变量，根据每次提交的查询条件不同而不同：

执行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语句仅解析了一次，而执行了多次：

使用Spotlight查看自动管理模式下SGA里各种缓存的分布，Statement方式下shared_pool要比PreparedStatement方式占用更多：
Statement方式下shared_pool分配了500M+的空间，而占用率400M+：

PreparedStatement方式下shared_pool同样分配了500M+，但仅占用了100M+：


最后测试结果也受Oracle版本、测试环境、测试工具和笔者的理解能力所限，可能存在错误，仅供参考！

gaha

通常都是用PreparedStatement测试存储过程，帖子很详细，很有参考价值！！