Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-9581

Job acquisition query causes high CPU costs

    Details

      Description

      Scenario:

      • Oracle
      • ~ 1,000,000 jobs (whereby > 90% are timer jobs)
      • on average ~ 10 jobs are due when the job acquistion query runs

      Problem:

      • The job acquistion query performs a full table scan to get 3 jobs to execute. This results in a slow query and high CPU utilization
      • An index on column DUEDATE_ does not have any impact, because the job acquisition query does a null check (see RES.DUEDATE_ is null)
      • A composite index like (RETRIES_, SUSPENSION_STATE_, DUEDATE_) can be used by the optimizer to execute a index skip scan operation, which reduces the overall costs, but the CPU utilization is still quite high.

      How to improve the (CPU) costs?

      • Get rid of the null check (i.e. RES.DUEDATE_ is null)
      • For each created job a due date should be applied (in case of non-timer jobs it should be the current timestamp)
      • This should be configurable.

      What is the result of the improvement?

      • The index on DUEDATE_ or the composite index (RETRIES_, SUSPENSION_STATE_, DUEDATE_) can be used to run the job acquisition query.
      • Instead of an index skip scan an index range scan is executed. The index range scan is less expensive than the index skip scan.

      • The operation table access by ROWID (batched) still results in a high CPU consumption. So that the overall costs are still high. A high clustering factor (of the indexes) relative to the number of leaf blocks may increase the number of data blocks required to satisfy a range query based on the indexed column.

      Hint:

      • This report is based on "experimental" evidence.
      • The CPU costs of the query will most likely remain high.
      1. COMPOSITE_INDEX.png
        34 kB
      2. DUEDATE_INDEX.png
        27 kB
      3. WITH_NULL_CHECK_FULL_SCAN.png
        77 kB
      4. WITH_NULL_CHECK_SKIP_SCAN.png
        44 kB

        Issue Links

          Activity

          There are no comments yet on this issue.

            People

            • Assignee:
              Unassigned
              Reporter:
              roman.smirnov Smirnov Roman
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development