查询挂起oracle 10g [英] query hangs oracle 10g

问题描述

我的软件有这个奇怪的问题。
正在生产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（）{$ b $ 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 Simple日期格式（ YYYY / MM / DD）; 
日期startDate = new Date（）; 
尝试{
 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））;如果（inType！= null）{
 for（INType i：INType.values（））
 if（i.toString（）。equals（inType））{
 
 b $ b criteria.add（Expression.eq（i.inType，i））; 
休息; 
 
 
 
 criteria.add（Expression.eq（p.active，true））; 
 $ b $ 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（）; < =====挂在这里
} 
}）; 
 
 
 
 $ b 
数据源被定义为这个（我知道这不应该是在生产中，但这是唯一的方法 - 我试图使用oracle连接池，但随后查询更频繁地挂起..）：
 
 
 <？xml version =1.0encoding =UTF-8？> 
<！DOCTYPE bean PUBLIC -  // SPRING // DTD BEAN // ENhttp://www.springframework.org/dtd/spring-beans.dtd\"> 
< beans> 
< bean id =dataSourceclass =org.springframework.jdbc.datasource.DriverManagerDataSourcedestroy-method =close> 
< property name =driverClassNamevalue =$ {jdbc.driverClassName}/> 
< property name =urlvalue =$ {jdbc.url}/> 
< property name =usernamevalue =$ {jdbc.username}/> 
< property name =passwordvalue =$ {jdbc.password}/> 
< property name =connectionProperties> 
<道具> 
< prop key =tcp.nodelay>是< / prop> 
< prop key =delayRowPrefetch> 20< / prop> 
< prop key =defaultBatchSize> 5< / prop> 
< /道具> 
< / 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连接池作为数据
 source - >挂起
连接失败
 
 
使用oracle jdbc 10.2.0.5  - >我以为我已经解决了它......几个小时后，它又被绞死了：（
 
 
 
 
 
在oracle上没有数据库锁，因为我可以看到... 
 
 
 
更新：
 
 
 
在oracle EM中：
 
 
 
 ADDM的发现：
发现消耗大量数据库时间的SQL语句
查询消耗大量数据库时间影响81％。
用户I / O等待97 ％。
 
 
 
 
 
负责
的单个SQL语句用于显式t用户I / O等待发现
。
 
发现了个别数据库段
，负责重要用户I / O 
等。
 
 
 I / O子系统
的吞吐量显着低于预期的
。
 
  
 
 < h1>更新：（15.03.2011） 
 
 
目前该服务可以在不停工的情况下工作近48小时。 
 
 
 
我怀疑这会解决问题，但我对代码做了一些更改： 
 
 
已删除解码（deliver，null，0,1）= 0 和 decode（+ sqlCustAlias +.deleteDate，null，1,0）= 1 函数在查询中，并用为空语句替换它们。
 
已传递字段已编入索引，但索引不能用于解码函数。
 
 
 
你认为这只是巧合？
 
 
 
更新：（16.03.2011）
 
 
 
 alert.log现在显示许多条目： 
 
 pre $ 由以下SQL语句导致的ORA-01555（SQL ID：affkpm4j7azc4，查询持续时间= 232624秒，SCN：0x0003.dca70559）：
 Tue Mar 15 17:43 ：06 2011 
 select * from（选择this_.id作为id5_，this_.deliverAt作为deliverAt68_5_，this_.delivered已交付68_5_，this_.inDelivery inDelivery68_5_，this_.lastDeliveryTry作为lastDeli5_68_5_，this_.tries作为try68_5_，this_。发送为sentAt68_5_，this_.sent如sent68_5_，this_.retry如retry68_5_，this_.inStatus如inStatus68_5_，this_.errorMessage如errorMe11_68_5_，this_.inCvsDelivery如inCvsDe12_68_5_，this_.cvsDelivered如cvsDeli13_68_5_，this_.cvsLastDeliveryTry如cvsLast14_68_5_，this_.cvsTries如cvsTries68_5_， this_.collectedPoints如collect16_68_5_，this_.smsMessage如smsMessage68_5_，this_.inOldStatus如inOldSt18_68_5_，this_.replacedDate如replace19_68_5_，this_.oldMsisdn如oldMsisdn68_5_，this_.deletedDate如deleted21_68_5_，this_.addManualDate如addManu22_68_5_，this_.stornoPromiseDate如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秒！

解决方案

首先，挂起，检查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＃$ b $ v from v $ sqlarea v1，v $ session v2 
 where v1.users_executing> 0 
和v2.sql_address（+）= v1.address; 
  

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

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!

解决方案

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.

这篇关于查询挂起oracle 10g的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！