APEX

hello,

i work on apex 5.1.4, oracle 12.1.0.2.0

i have a performance problem in classic report so i decide to execute the same query in apex sql workshop

1) i execute in apex sql worskshop-->sql commands the following query with bind variable :P67_ORGA

(i set in the dialog box for P67_ORGA the value FRA404)

WITH REFER as (

select unique substr(temps,1,4) annee

     ,e.employe

     ,r.rubsrh

     ,t.temps

     ,s.scenar

from EMPLOYE_NONE_TABLE e,RUBSRH_STANDARD_table r, TEMPS_STD_MOIS_TABLE t, SCENAR_NONE_TABLE s

where  t.L1 = '2018'

   and SCENAR = 'LE16_B17'

   and RUBSRH = 'ABS_AT_TR_MAL_PRO'

   and EMPLOYE in (select /*+ PARALLEL(nomemp_code_table,4) */ employe

                    from nomemp_code_table

                    where scenar = 'LE16_B17'

                    and temps in (select temps from TEMPS_STD_MOIS_TABLE where L1_MOD ='2018' )

                    and matpres_code = 1

                    and employe not in (select employe from employe_none_table where etre_OD_CODE = 1)

                    and matorg_code in (select orga from orga_standard_table where  instr(tous_niveau,'#'  || :P67_ORGA || '#') >0)

                    and matref1_code in (select axemet from axemet_standard_table where instr(tous_niveau,'#' || 'M0001' || '#') > 0)

)

)

select (select e.employe  from employe_none_table e where e.employe = emp.employe) EMP_LIB

     ,RUB_01

FROM (

select r.rubsrh

     ,r.annee

     ,r.scenar 

     ,r.employe

   ,sum( CASE WHEN r.temps = r.annee  ||'01'  THEN b.budgetsal ELSE null END)  RUB_01

from REFER r

left outer join budgetsal_table b on (  b.scenar=r.scenar and b.temps=r.temps and b.rubsrh=r.rubsrh and b.employe= r.employe)

group by r.rubsrh,r.annee,r.scenar,r.employe

     )emp

it returns 75 rows in 114 seconds

the explain plan is