查询挂起oracle 10g

Question

我的软件遇到了这个奇怪的问题。 它已经投入生产 5 年了，我们没有遇到这样的问题...

问题：

我们有一个 spring 作业（调度程序），它通过 hibernate 进行查询、检索对象并修改它们。

嗯，这已经有效了好几年，但一个月前查询每天挂起 5-10 次（查询每 10 分钟调用一次）。当它挂起时，我们必须重新启动服务。

以下代码执行查询：

@SuppressWarnings("unchecked")
public List<Delivery> 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 连接池，但查询更频繁地挂起。 ）：

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
    <beans>
      <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="connectionProperties">
         <props>
          <prop key="tcp.nodelay">yes</prop>
          <prop key="delayRowPrefetch">20</prop>
          <prop key="defaultBatchSize">5</prop>
         </props>
        </property>
      </bean>
    </beans>

使用的软件：

• spring 1.2.7
• hibernate 3.0.5
• oracle 10.2.0.1 (RAC)
• oracle jdbc 10.1.0.2
• Red Hat 3 EL
• Java 1.5_06

到目前为止我尝试过的：

• 使用oracle连接池作为数据 来源->挂起失败 连接
• 使用 oracle jdbc 10.2.0.5 ->我以为我已经解决了它......几个小时后它再次挂起:(

据我所知，oracle 上没有数据库锁......

可能是什么问题？

更新：

在 oracle EM：

ADDM 发现： 发现 SQL 语句消耗大量数据库时间。 该查询消耗大量数据库时间。影响81%。 用户 I/O 等待 97%。

• 负责的单个 SQL 语句 对于重要的用户 I/O 等待是 成立。
• 单独的数据库段 负责重要的用户 I/O 等待被发现。
• I/O 子系统的吞吐量 明显低于 预期的。

更新：（2011 年 3 月 15 日）

目前该服务可以运行近 48 小时而不会挂起。

我怀疑这是否能解决问题，但我对代码做了一些更改：

删除了 decode(delivered,null,0,1) = 0 和 decode(" + sqlCustAlias + ".deleteDate,null,1,0) = 1 查询中的函数，并将其替换为 is null 语句。
传递的字段已建立索引，但索引不能在 decode 函数中使用。

你认为这只是巧合吗？

更新：（2011 年 3 月 16 日）

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 秒！

Answer 1

首先，当此查询挂起时，检查 V$SESSION_WAIT 以查看会话正在等待什么。

第二个观察结果：上面显示的代码似乎会忽略 max 参数，除非 benefitType 参数为非空。这是故意的吗？是否只有当 BenefitType 参数为 null 时查询才会“挂起”？

抱歉，我以为您有某种方法可以识别 Oracle 中的正确会话。尝试这样的查询：

select v2.sid,
       v2.module,
       substr(v1.sql_text,1,180) sql_text,
       v1.rows_processed,
       v2.event,
       v2.seq#
from v$sqlarea v1, v$session v2
where v1.users_executing > 0
  and v2.sql_address (+) = v1.address;

这将显示当前正在执行的所有 SQL，如果可能的话，还会显示相关的会话 ID 以及它正在等待的事件。您应该能够使用 SQL 文本来识别您感兴趣的会话。