query hangs oracle 10g

Question

I have this strange issue with our software. Is is on production for 5 years and we had no such problems...

Problem:

We have a spring job (scheduler) which makes a query via hibernate, retrieves the objects and modifies them.

Well, this worked for several years but a month ago the query hangs 5-10 times a day (the query is invoked every 10 minutes). And when it hangs, we have to restart the service.

The following code does the query:

@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
        }
    });
}

Data source is defined as this (I know that this is not supposed to be on production but this is the only way it works - I tried to use oracle connection pool but then the query hangs more often..):

<?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>

Software used:

• 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

What I've tried so far:

• use oracle connection pool as data source -> failed with hanging connections
• used oracle jdbc 10.2.0.5 -> I thought I've solved it ... and after a few hours it hanged again :(

There are no database locks on oracle as far I can see...

What could be the problem?

UPDATE:

in oracle EM:

ADDM findings: SQL statements consuming significant database time were found. The query consumes significant database time. Impact 81%. User I/O waits 97%.

• Individual SQL statements responsible for significant user I/O wait were found.
• Individual database segments responsible for significant user I/O wait were found.
• The throughput of the I/O subsystem was significantly lower than expected.

UPDATE: (15.03.2011)

For now the service works for almost 48 hours without hanging.

I'm skeptical that this will solve the problem but I did some changes to the code:

Removed the decode(delivered,null,0,1) = 0 and decode(" + sqlCustAlias + ".deleteDate,null,1,0) = 1 functions in query and replaced them with is null statements.
The delivered field is indexed but indexing can't be used in decode functions.

Do you think that this is just coincidence?

UPDATE: (16.03.2011)

alert.log now shows many entries like this:

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

It seems to be from sessions 3 days before.. 232624seconds!

Answer 1

First thing, when this query hangs, check V$SESSION_WAIT to see what the session is waiting on.

Second observation: The code you've shown above appears to ignore the max parameter unless the benefitType parameter is non-null. Is this intentional? Is it possible that the query is "hanging" only when the benefitType parameter is null?

Sorry, I assumed you had some way of identifying the correct session within Oracle. Try a query like this:

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;

That will show all the SQL currently being executed, and if possible the related session ID and what event it is waiting on. You should be able to use the SQL text to identify the session you are interested in.