我的软件有这个奇怪的问题.
是在生产5年,我们没有这样的问题…
问题:
我们有一个spring job(调度程序),通过hibernate进行查询,检索对象并修改它们.
那么这个工作已经好几年了,但是一个月前,这个查询每天挂起5-10次(查询每10分钟调用一次).当它挂起时,我们必须重新启动服务.
以下代码执行查询:
@SuppressWarnings("unchecked")
public List findScheduledForDelivery(final String inType, final int max, final String benefitType ) {
//getHibernateTemplate().clear();
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws SQLException {
Criteria criteria = session.createCriteria(Delivery.class);
criteria.createAlias("reward","r");
criteria.createAlias("r.customer","c");
criteria.createAlias("c.inNe","i");
criteria.createAlias("r.promotion","p");
criteria.createAlias("benefit","b");
String sqlCustAlias = StringHelper.generateAlias("c", 2);
criteria.add(Expression.disjunction()
.add(Expression.eq("inStatus", INStatus.InterfaceFailure))
.add(Expression.eq("inStatus",INStatus.Initial)));
criteria.add(Expression.le("deliverAt", new Date()));
String dateString = "2000/01/01";
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
Date startDate = new Date();
try {
startDate = dateFormat.parse(dateString);
criteria.add(Expression.ge("deliverAt", startDate));
}
catch(ParseException e) {
e.printStackTrace();
}
String sqlEqual = "decode(delivered,null,0,1) = 0";
criteria.add(Expression.sql(sqlEqual));
sqlEqual = "decode(" + sqlCustAlias + ".deleteDate,null,1,0) = 1";
criteria.add(Expression.sql(sqlEqual));
if(inType != null ) {
for(INType i : INType.values())
if(i.toString().equals(inType)) {
criteria.add(Expression.eq("i.inType", i));
break;
}
}
criteria.add(Expression.eq("p.active", true));
if(benefitType != null) {
if(benefitType.equals("FREECREDIT"))
criteria.add(Expression.disjunction()
.add(Expression.eq("b.type", BenefitType.FREE_CREDIT))
.add(Expression.eq("b.type", BenefitType.FREE_CREDIT_FTAM)));
else if(benefitType.equals("NONFREECREDIT")) {
criteria.add(Expression.conjunction()
.add(Expression.ne("b.type", BenefitType.FREE_CREDIT))
.add(Expression.ne("b.type", BenefitType.OTHER))
.add(Expression.ne("b.type", BenefitType.VOUCHER)));
criteria.add(Expression.isNull("b.md3Profile"));
}
if(max != 0)
criteria.setMaxResults(max);
}
criteria.addOrder( Order.desc("p.priority") );
criteria.addOrder( Order.asc("deliverAt") );
return criteria.list(); <===== hangs here
}
});
}
数据源被定义为这个(我知道这不是在生产中,但这是唯一的工作方式 – 我尝试使用oracle连接池,但是查询更经常挂起..):
yes
20
5
使用的软件:
> spring 1.2.7
> hibernate 3.0.5
> oracle 10.2.0.1(RAC)
> oracle jdbc 10.1.0.2
红帽3 EL
> Java 1.5_06
到目前为止我已经尝试过
>使用oracle连接池作为数据
来源 – >失败了挂
连接
> used oracle jdbc 10.2.0.5 – >我以为我已经解决了,几个小时后,它再次挂起:(
在oracle上没有数据库锁,我可以看到…
可能是什么问题呢?
更新:
在oracle EM:
ADDM发现:
发现消耗大量数据库时间的SQL语句.
该查询消耗了大量的数据库时间.影响81%.
用户I / O等待97%.
>个人SQL语句负责
对于重要的用户I / O等待
找到.
个人数据库段
负责重要的用户I / O
等待被发现.
> I / O子系统的吞吐量
明显低于
预期.
更新:(15.03.2011)
现在服务工作了将近48小时,无需挂.
我怀疑这将解决问题,但我对代码做了一些修改:
在查询中删除了decode(deliver,null,0,1)= 0和decode(“sqlCustAlias”.deleteDate,null,1,0)= 1函数,并将其替换为null语句.
传递的字段被索引,但索引不能在解码功能中使用.
你认为这只是巧合吗?
更新:(16.03.2011)
alert.log现在显示许多条目,如下所示:
ORA-01555 caused by SQL statement below (SQL ID: affkpm4j7azc4, Query Duration=232624 sec, SCN: 0x0003.dca70559):
Tue Mar 15 17:43:06 2011
select * from ( select this_.id as id5_, this_.deliverAt as deliverAt68_5_, this_.delivered as delivered68_5_, this_.inDelivery as inDelivery68_5_, this_.lastDeliveryTry as lastDeli5_68_5_, this_.tries as tries68_5_, this_.sentAt as sentAt68_5_, this_.sent as sent68_5_, this_.retry as retry68_5_, this_.inStatus as inStatus68_5_, this_.errorMessage as errorMe11_68_5_, this_.inCvsDelivery as inCvsDe12_68_5_, this_.cvsDelivered as cvsDeli13_68_5_, this_.cvsLastDeliveryTry as cvsLast14_68_5_, this_.cvsTries as cvsTries68_5_, this_.collectedPoints as collect16_68_5_, this_.smsMessage as smsMessage68_5_, this_.inOldStatus as inOldSt18_68_5_, this_.replacedDate as replace19_68_5_, this_.oldMsisdn as oldMsisdn68_5_, this_.deletedDate as deleted21_68_5_, this_.addManualDate as addManu22_68_5_, this_.stornoPromiseDate as stornoP23_68_5_, this_.stornoINDate as stornoI24_68_5_, this_.activationCode as activat25_68_5_, this_.activationExpirationDate as activat26_68_5_, this_.rewardId as rewardId68_5_, this_.benefitId as b
这似乎是从3天前的会议.. 232624秒!