[bug]: Drastically increased query time

[jobaja]

Flows for APEX version

5.1.1

Oracle Database version

21c

Oracle APEX version

24.1

Have you used the BPMN-Linter in Flow Designer to Validate your Diagram?

Yes

What happened?

In the last released version the FLOW_INSTANCE_DETAILS_VW view was altered to add the user_task this increased our query time on the view from 0.06 seconds to 37 seconds in our infrastructure.

Steps to reproduce

No response

Expected behaviour

No response

[rallen2010]

Could you confirm which Flows for APEX version you are now running- the report above says 5.1.1. Have you just upgraded to 24.1??

[rallen2010]

Please contact me & we can set up a Zoom call to diagnose.. Richard at Flowquest dot net.

[rallen2010]

The view flow_instance_details_vw is intended to be used to get all of the information required about a single process instance for the BPMN Viewer plugin to do its work. In 24.1, this was extended in two ways: Firstly, to retrieve a clickable URL for any UserTask that is a current task (I order for the diagrams to become clickable to launch the task, and secondly, for Enterprise Edition only, to retrieve information for all of the iterations in an instance so that you can choose which iteration you want to drill down into.
We only need to worry about the first issue here - because Enterprise Edition has its own copy of this view.
We suspect that the performance issue comes when we add the user task info, and you then use it for many rows. The UserTask_URL column evaluates whether the current user is able to start a task before returning a URL for the task. So it's a fairly expensive view - which is OK if you want to query it for a single row, but expensive if you look at lots of rows in SQL Dev / SQL workshop, etc.
We would like you to try changing the view definitions for flow_instance_details_vw and flow_P0008_vw.sql - where we are shifting the expensive part out of flow_instance_details_vw and into the view that is designed specifically to support the viewer in the Flows for APEX application (the 'engine app').

Then please let us know how you get on... We are also going to do some performance testing while you do this.

Can you tell us how many:

• how many instances in in your live system (select count(*) from flow_processes)
• how many running instances (select count(*) from flow_processes where prcs_status = 'running')
• how many sub flows (select count(*) from flow_subflows )
  to give us some idea of how big your system is. Email me if you don't want to post....

Thanks.

-- Flows for APEX Issue 775 possible Fix
-- installs 2 new definitions for views flow_instance_details_vw and flow_p0008_vw.sql
-- 
-- not yet a released fix - 
/* 
-- Flows for APEX - flow_instance_details_vw.sql
-- 
-- (c) Copyright Oracle Corporation and / or its affiliates, 2022.
-- (c) Copyright MT AG, 2021-2022.
--
-- Created    Nov-2020 Moritz Klein,  MT AG
-- Edited  16-Mar-2022 Richard Allen, Oracle Corporation
-- Edited  21-Jun-2024 Dennis Amthor, Hyand Solutions GmbH
--
-- NOTE THAT THIS VIEW HAS AN ENTERPRISE EDITION VERSION WHICH INCLUDES COMPLEX
-- PROCESSING TO GENERATE THE ITERATION_DATA COLUMN.
-- ANY CHANGES TO THIS VIEW SHOULD ALSO UPDATE THE ENTERPRISE EDITION VERSION IN ADDITION.
*/
create or replace view flow_instance_details_vw
as
with completed_objects as (
        select distinct sflg.sflg_prcs_id as prcs_id
                      , sflg.sflg_dgrm_id  as dgrm_id
                      , sflg.sflg_diagram_level as diagram_level
                      , sflg.sflg_objt_id as objt_id
          from flow_subflow_log sflg
         where sflg.sflg_objt_id not in (   
                                          select sbfl.sbfl_current
                                            from flow_subflows sbfl
                                           where sbfl.sbfl_prcs_id = sflg.sflg_prcs_id
                                             and sbfl.sbfl_diagram_level = sflg.sflg_diagram_level
                                             and sbfl.sbfl_current is not null
                                        )
), all_completed as ( 
    select prcs_id, dgrm_id, diagram_level,
           listagg( objt_id, ':') within group (order by objt_id) as bpmn_ids
    from completed_objects
    group by prcs_id, dgrm_id, diagram_level
), all_current as (
  select sbfl_prcs_id as prcs_id
       , sbfl_dgrm_id as dgrm_id
       , sbfl_diagram_level as diagram_level 
       , listagg(sbfl_current, ':') within group ( order by sbfl_current ) as bpmn_ids
    from flow_subflows
   where sbfl_current is not null  
group by sbfl_prcs_id, sbfl_dgrm_id, sbfl_diagram_level
), all_errors as (
  select sbfl_prcs_id as prcs_id
       , sbfl_dgrm_id as dgrm_id
       , sbfl_diagram_level as diagram_level
       , listagg(sbfl_current, ':') within group (order by sbfl_current) as bpmn_ids
    from flow_subflows
   where sbfl_current is not null
     and sbfl_status = 'error'
group by sbfl_prcs_id, sbfl_dgrm_id, sbfl_diagram_level
)
  select prcs.prcs_id
       , prcs.prcs_name
       , prdg.prdg_id
       , prdg.prdg_prdg_id
       , prdg.prdg_diagram_level as diagram_level
       , prdg.prdg_calling_dgrm as calling_dgrm
       , prdg.prdg_calling_objt as calling_objt
       , dgrm.dgrm_name || case when prdg.prdg_diagram_level is not null then ' ( Level: ' || prdg.prdg_diagram_level || ' )' else '' end as breadcrumb
       , 1 as drilldown_allowed
       , dgrm.dgrm_id
       , dgrm.dgrm_name
       , dgrm.dgrm_version
       , dgrm.dgrm_status
       , dgrm.dgrm_category
       , dgrm.dgrm_content
       , ( select acomp.bpmn_ids
             from all_completed acomp
            where acomp.prcs_id = prcs.prcs_id
              and acomp.dgrm_id = dgrm.dgrm_id
              and acomp.diagram_level =  prdg.prdg_diagram_level
         ) as all_completed
       , null as last_completed     -- remove in v22.1
       , ( select acurr.bpmn_ids
             from all_current acurr
            where acurr.prcs_id = prcs.prcs_id
              and acurr.dgrm_id = dgrm.dgrm_id
              and acurr.diagram_level =  prdg.prdg_diagram_level
         ) as all_current
       , ( select aerr.bpmn_ids
             from all_errors aerr
            where aerr.prcs_id = prcs.prcs_id
              and aerr.dgrm_id = dgrm.dgrm_id
              and aerr.diagram_level =  prdg.prdg_diagram_level
         ) as all_errors
       , to_clob(null) as user_task_urls
       , to_clob(null) as iteration_data
       , prov.prov_var_vc2 as prcs_business_ref
    from flow_processes prcs
    join flow_instance_diagrams prdg
      on prdg.prdg_prcs_id = prcs.prcs_id
    join flow_diagrams dgrm
      on prdg.prdg_dgrm_id = dgrm.dgrm_id
    left join flow_process_variables prov
      on prov.prov_prcs_id  = prcs.prcs_id
     and prov.prov_var_name = 'BUSINESS_REF'
     and prov.prov_var_type = 'VARCHAR2'
     and prov.prov_scope    = 0
with read only;

create or replace view flow_p0008_vw
as
  with user_tasks as (
    select process_id as prcs_id
         , json_objectagg
           (
               key current_obj
               value details_link_target
               absent on null
               returning clob
           ) as user_task_urls
      from flow_apex_my_combined_task_list_vw
     group by process_id
)
  select dgrm_content
       , fid.prcs_id
       , all_completed
       , all_errors
       , all_current
       , dgrm_id
       , calling_dgrm
       , calling_objt
       , breadcrumb
       , drilldown_allowed
       , iteration_data
       , prdg_id
       , prdg_prdg_id
       , usta.user_task_urls
    from flow_instance_details_vw fid
    left join user_tasks usta
      on fid.prcs_id = usta.prcs_id
with read only;