Hello everyone
I’m trying to understand a problem that happens regarding Bonita 5.10 platform
Oracle DBA discovered low performance more than twenty seconds for the result of monitoring query below
select
internalac0_.DBID_ as DBID1_121_, internalac0_.ACT_INST_UUID_ as ACT2_121_, internalac0_.PROCESS_UUID_ as PROCESS3_121_, internalac0_.INST_UUID_ as INST4_121_, internalac0_.SUB_INST_UUID_ as SUB5_121_, internalac0_.ROOT_INST_UUID_ as ROOT6_121_, internalac0_.ACTIVITY_STATE_ as ACTIVITY7_121_, internalac0_.READY_DATE as READY8_121_, internalac0_.START_DATE as START9_121_, internalac0_.END_DATE as END10_121_, internalac0_.LAST_UPD as LAST11_121_, internalac0_.START_BY as START12_121_, internalac0_.END_BY as END13_121_, internalac0_.EXPEN_DATE as EXPEN14_121_, internalac0_.IS_HUMAN_ as IS15_121_, internalac0_.ACT_DEF_UUID_ as ACT16_121_, internalac0_.TYPE_ as TYPE17_121_, internalac0_.PRIORITY as PRIORITY121_, internalac0_.NAME as NAME121_, internalac0_.DESCR as DESCR121_, internalac0_.LABEL as LABEL121_, internalac0_.DYN_DESCR as DYN22_121_, internalac0_.DYN_LABEL as DYN23_121_, internalac0_.DYN_EXECUTION_SUMMARY as DYN24_121_, internalac0_.USERID_ as USERID25_121_, internalac0_.ITERATION_ID_ as ITERATION26_121_, internalac0_.ACT_INST_ID_ as ACT27_121_, internalac0_.LOOP_ID_ as LOOP28_121_
from BN_ACT_INST internalac0_
where internalac0_.INST_UUID_=:1 and internalac0_.IS_HUMAN_=1
the suggestion to increase the performance is to use Oracle HINT
through this way the solution could be :
SELECT /*+ index (internalac0_ IDX_UUID_ISHUM_DBID) */
internalac0_.DBID_ as DBID1_121_, internalac0_.ACT_INST_UUID_ as ACT2_121_, internalac0_.PROCESS_UUID_ as PROCESS3_121_, internalac0_.INST_UUID_ as INST4_121_, internalac0_.SUB_INST_UUID_ as SUB5_121_, internalac0_.ROOT_INST_UUID_ as ROOT6_121_, internalac0_.ACTIVITY_STATE_ as ACTIVITY7_121_, internalac0_.READY_DATE as READY8_121_, internalac0_.START_DATE as START9_121_, internalac0_.END_DATE as END10_121_, internalac0_.LAST_UPD as LAST11_121_, internalac0_.START_BY as START12_121_, internalac0_.END_BY as END13_121_, internalac0_.EXPEN_DATE as EXPEN14_121_, internalac0_.IS_HUMAN_ as IS15_121_, internalac0_.ACT_DEF_UUID_ as ACT16_121_, internalac0_.TYPE_ as TYPE17_121_, internalac0_.PRIORITY as PRIORITY121_, internalac0_.NAME as NAME121_, internalac0_.DESCR as DESCR121_, internalac0_.LABEL as LABEL121_, internalac0_.DYN_DESCR as DYN22_121_, internalac0_.DYN_LABEL as DYN23_121_, internalac0_.DYN_EXECUTION_SUMMARY as DYN24_121_, internalac0_.USERID_ as USERID25_121_, internalac0_.ITERATION_ID_ as ITERATION26_121_, internalac0_.ACT_INST_ID_ as ACT27_121_, internalac0_.LOOP_ID_ as LOOP28_121_
from BN_ACT_INST internalac0_
where internalac0_.INST_UUID_=:1 and internalac0_.IS_HUMAN_=1
Now I’m looking for if this solution is possible and where is situated the mechanism of building dynamic string query.
Alternatively if there is another way to increase perform about BN_ACT_INST table - class InternalActivityInstance.
Many many thanks in advance
Fausto