GLASTP1 Performance Improvements

Query Execution Plans recommended by Oracle:

select COUNT(1) from ProcessStatusCondition PSC join ProcessInstance PI on (PSC.Process = PI.Process) join Stream S on PI.Stream = S.Stream where RowNum = 1 and PSC.DependentProcess = :1 and PSC.ProcessingStatus <> PI.ProcessingStatus and (S.ParentStream = :2 or S.Stream = :3) and S.IsLatest = 1 and PI.IsLatest = 1

This guy is in SPProcessInstance.getProcessStatusConditionsOutstanding().  He's currently only spending 24% of his execution time on the CPU.  The rest of the time is spent in Concurrency Waits (70%) or "other" waits.

Oracle had no better plan to recommend.

select COUNT(1) from ProcessCompletionCondition PCC join ProcessInstance PI on (PCC.Process = PI.Process) join ProcessingStatus PS on (PI.ProcessingStatus = PS.ProcessingStatus) join Stream S on (PI.Stream = S.Stream) where RowNum = 1 and PCC.DependentProcess = :1 and PS.Final = 0 and (S.ParentStream = :2 or S.Stream = :3) and S.IsLatest = 1 and PI.IsLatest = 1

 This guy is in SPProcessInstance.getProcessingCompletionConditionsOutstinding().  He's currently spending 41.2% of his time on the CPU.  The rest is concurrency 7% and other.

Oracle has a better execution plan, I have accepted it.

{{select SUM(1) "ALL",

SUM(case when PROCESSINGSTATUS='WAITING' then 1 else 0 end) "WAITING",

SUM(case when PROCESSINGSTATUS='READY' then 1 else 0 end) "READY",

SUM(case when PROCESSINGSTATUS='QUEUED' then 1 else 0 end) "QUEUED",

SUM(case when PROCESSINGSTATUS='SUBMITTED' then 1 else 0 end) "SUBMITTED",

SUM(case when PROCESSINGSTATUS='RUNNING' then 1 else 0 end) "RUNNING",

SUM(case when PROCESSINGSTATUS='SUCCESS' then 1 else 0 end) "SUCCESS",

SUM(case when PROCESSINGSTATUS='FAILED' then 1 else 0 end) "FAILED",

SUM(case when PROCESSINGSTATUS='TERMINATED' then 1 else 0 end) "TERMINATED",

SUM(case when PROCESSINGSTATUS='CANCELED' then 1 else 0 end) "CANCELED",

SUM(case when PROCESSINGSTATUS='SKIPPED' then 1 else 0 end) "SKIPPED",

lev, lpad(' ',1+24*(lev -1),' ')||taskname taskname, task,
Initcap(ProcessType) type, processname, process,displayorder,max(stream) ProcessStream
from PROCESS
join (
SELECT task,taskname,level lev FROM TASK
start with Task=:1 connect by prior Task = ParentTask
) using (task)
join PROCESSINSTANCE using (PROCESS)
where isLatest=1 and PII.GetStreamIsLatestPath(stream)=1
and stream in (
SELECT stream
FROM stream
start with stream = :2
connect by prior stream = parentstream)
group by lev,task, taskname,process,PROCESSNAME,displayorder, processtype
order by task, process }}

This is from the front-end.  It's the query used to generate the Task Summary (main-page) view. 

Oracle has a recommendation to improve the execution plan.  I have accepted it.