L3 - Default The job acquisition query performs an exists subquery to ensure that no other exclusive jobs are currently locked. This subquery uses select *. It should use select 1 instead, which saves a table access.
Query:
SELECT sub.*
FROM ( SELECT res.*,
Row_number( )
OVER (
ORDER BY res.priority_ DESC) rnk
FROM ( SELECT DISTINCT res.*
FROM ACT_RU_JOB res
WHERE ( res.retries_ > 0 ) AND
( res.duedate_ IS NULL OR
res.duedate_ <= @P0 ) AND
( res.lock_owner_ IS NULL OR
res.lock_exp_time_ < @P1 ) AND
res.suspension_state_ = 1 AND
( res.deployment_id_ IS NULL OR
( res.deployment_id_ IN ( @P2, @P3, @P4, @P5,
@P6, @P7, @P8, @P9,
@P10, @P11, @P12, @P13,
@P14, @P15, @P16, @P17,
@P18, @P19, @P20, @P21,
@P22, @P23, @P24 ) ) ) AND
( ( res.exclusive_ = 1 AND
NOT EXISTS
( SELECT j2.*
FROM ACT_RU_JOB j2
WHERE j2.process_instance_id_ = res.process_instance_id_ -- from the same proc. inst.
AND
( j2.exclusive_ = 1 ) -- also exclusive
AND
( j2.lock_owner_ IS NOT NULL AND
j2.lock_exp_time_ >= @P25 ) -- in progress
) ) OR
res.exclusive_ = 0 ) )res ) sub
WHERE sub.rnk >= @P26 AND
sub.rnk < @P27
ORDER BY sub.rnk
SELECT j2.* is where it is at.