2 Replies Latest reply on Aug 25, 2009 9:02 AM by Ronald van Kuijk

    Solution: Large numbers of jobs in JOB table causes major sl

    Shane Paul Newbie

      Environment:
      Oracle with jbpm3, db message queue and async processing.

      Problem:
      During high volume processing (such as our overnight soak test) the job queue can fill up.
      The job executor synchronises the code in the method "acquireJobs" which in turn executes the query:

      select job from org.jbpm.job.Job as job
      where ( (job.lockOwner is null) or (job.lockOwner = :lockOwner) )
       and job.retries > 0
       and job.dueDate <= :now
       and job.processInstance = :processInstance
       and job.isExclusive = true
       and job.isSuspended != true
       order by job.dueDate asc
      


      Unfortunately duedate is not indexed and job.dueDate <= :now appears to insure that oracle will fail to optimise the query as ":now" constantly changes. (obviously query plan culls jobs first, then sorts)

      Because this is all synchronised it slows the executor pool down and thus more jobs pool and the whole thing begins to crawl. Someone here decribed it as "degrade disgracefully" which is now my personal life long goal as well.

      Solution:
      Index duedate:
      create index IDX_JOB_DUEDATE on JBPM_JOB (DUEDATE_);

      Otherwise your DB will be constantly resorting that table.