原来用oracle xe11访问速度很快，升级到oracle11g，很慢
调试发现只要preparedStatement 使用set方法传递参数，速度就很慢，盼高手指点
具体代码
贴出你的查询语句和代码。
你是怎么得到“preparedStatement使用set很慢”的，和什么对比的。
同一个sql,在xe11里就快，oracle11g就慢，而且发现不用set方法，两个速度
是一样的
具体sql
select * from ( select row_number() over(order by c.intime desc) rs,c.carid,c.carnumber,to_char(to_date(c.firstdate,'yyyy-mm-dd'),'yyyy-mm') firstdate,c.intime,getstatusnamebypid(c.status) statusname,c.status, nvl(c.flag,'0') flag, getcodenamebyid(c.maker) maker,getcodenamebyid(c.brand) brand,getcodenamebyid(c.model) model,c.licenseplates, getcodenamebyid(i.company) company,getcodenamebyid(i.companychild) companychild,i.linkman,(select orgname from org where orgid=c.orgid) orgname, (select distinct operation from business where intime=(select max(intime) from business t where t.carid= c.carid) and carid=c.carid) operation, (select distinct nexttime from business where intime=(select max(intime) from business t where t.carid= c.carid) and carid=c.carid) nexttime, (select final_price from carcheck where carid=c.carid) final_price, (select date_to_char(char_to_date(min(intime),6),3) from olog where relatedid = c.carid and status ='11') intime_sb, (select min(intime) from olog where relatedid = c.carid and status = '11') intime11 from carinfo c join carinsurance i on c.carid = i.carid join (select min(o.intime) intime, o.relatedid from olog o where status = '11' group by o.relatedid) o1 on o1.relatedid=c.carid where c.stype is null and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 order by c.carnumber desc ) tab where rs > (to_number('1')-1)*to_number('15') and rs <= to_number('1')*to_number('15')
String sql="select * from ( select row_number() over(order by c.intime desc) rs,c.carid,c.carnumber,to_char(to_date(c.firstdate,'yyyy-mm-dd'),'yyyy-mm') firstdate,c.intime,getstatusnamebypid(c.status) statusname,c.status, nvl(c.flag,'0') flag, getcodenamebyid(c.maker) maker,getcodenamebyid(c.brand) brand,getcodenamebyid(c.model) model,c.licenseplates, getcodenamebyid(i.company) company,getcodenamebyid(i.companychild) companychild,i.linkman,(select orgname from org where orgid=c.orgid) orgname, (select distinct operation from business where intime=(select max(intime) from business t where t.carid= c.carid) and carid=c.carid) operation, (select distinct nexttime from business where intime=(select max(intime) from business t where t.carid= c.carid) and carid=c.carid) nexttime, (select final_price from carcheck where carid=c.carid) final_price, (select date_to_char(char_to_date(min(intime),6),3) from olog where relatedid = c.carid and status ='11') intime_sb, (select min(intime) from olog where relatedid = c.carid and status = '11') intime11 from carinfo c join carinsurance i on c.carid = i.carid join (select min(o.intime) intime, o.relatedid from olog o where status = '11' group by o.relatedid) o1 on o1.relatedid=c.carid where c.stype is null and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 order by c.carnumber desc ) tab where rs > (to_number(?)-1)*to_number(?) and rs <= to_number(?)*to_number(?)";
Connection conn=dbutil.getConnection();
//conn.setAutoCommit(false);
//Savepoint sp=conn.setSavepoint();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1,"1");
preparedStatement.setString(2,"15");
preparedStatement.setString(3,"1");
preparedStatement.setString(4,"15");
ResultSet rs=preparedStatement.executeQuery();
//conn.commit();
CachedRowSet crs=new CachedRowSet();
crs.populate(rs);
如果我把参数直接写在sql里，执行都很快，就和pl/sql里一样，如果加了setString那几个参数，在xe11里还是很快，但是在11g或10g就慢
我试了所有的oracle数据库,从9i~11g,只有xe版本执行的速度快,太奇怪了
楼主解决了嘛 这个和sqlserver参数嗅探的问题很像