关于用java模拟soft soft parse场景的问题
本帖最后由 zl293 于 2015-8-25 00:02 编辑由于公司的研发人员对ORACLE的内部机制不怎么了解，容易写出不规范的代码。因此我专门写了几个java代码来模拟各种场景，类似http://t.askmaclean.com/thread-2729-1-1.html中的场景。
其中我在模拟soft soft parse时出现了无法解释的问题，即在session_cached_cursors不为0的情况下，oracle并没有parse三次后便不再parse，而是execute次数和parse次数一样。请论坛中高手们为我解惑，谢谢。下附java代码，oracle版本是11.2.0.1，jdk1.6
public static void main(String[] args)
{
String sqlinsert = "insert into uo_inner select seq,object_name,object_id from uo where seq = ?"; // 这里用问号
String sqlcount = "select count(*) from uo";
int i;
PreparedStatement st = null;
PreparedStatement stcount = null;
ResultSet rscount = null;
Connection conn = null;
try
{
conn = DBUtils.getConnection();
conn.setAutoCommit(false);
Statement ddl = conn.createStatement();
ddl.executeUpdate("alter system flush buffer_cache");
ddl.executeUpdate("alter system flush shared_pool");
ddl.executeUpdate("alter session set session_cached_cursors=100");
stcount = conn.prepareStatement(sqlcount);
rscount = stcount.executeQuery();
rscount.next();
for (i=1;i<=rscount.getInt(1);i++)
{
st = conn.prepareStatement(sqlinsert);
st.setInt(1,i); // 这里将问号赋值
st.executeUpdate();
if (i%1000==0)
{
conn.commit();
System.out.println("已插入"+i+"条数据");
}
st.close();
}
conn.commit();
执行select s.SQL_TEXT,s.EXECUTIONS,s.PARSE_CALLS,s.LOADS from v$sqlarea s where s.SQL_TEXT like '%uo_inner%'后的结果为
SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
insert into uo_inner select seq,object_name,object_id from uo where seq = :1 125841 125841 1
PS：这是我在单位电脑上执行的结果，晚上回家用家里电脑（oracle11.2.0.3 jdk1.8）跑同样的代码，执行出的结果为
SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
insert into uo_inner select seq,object_name,object_id from uo where seq = :1 125841 3 1
请到dbdao贴吧提问 单位的版本是多少?
页:
[1]