Performance issue with RuntimeService.getVariable(procInstId, varName) on Oracle 11

Hi,

We encounter performance issues on the call of RuntimeService.getVariable(procInstId, varName) after some months of use in production.
The problem comes suddenly and the response time increased from lesser than 0.1 seconds to upper than 9 seconds !

We use Oracle 11g database and the table ACT_HI_VARINST contains 1831235 records.
We have only STRING variable type.

Oracle AWR report returns the following SQL request :
select * from ( select a., ROWNUM rnum from ( select distinct RES. from ACT_HI_VARINST RES WHERE RES.PROC_INST_ID_ = :1 and RES.NAME_ = :2 and (RES.STATE_ != ‘DELETED’ or RES.STATE_ is null) order by RES.ID_ asc ) a where ROWNUM < :3 ) where rnum >= :4

After analysis, we conclude that the problem comes from a change of the execution plan… for an unkown reason.

The following permits to resolve (temporary) performance issue
exec dbms_stats.gather_schema_stats(‘CAMUNDA’,options=>‘GATHER AUTO’);

The bad execution plan use the following index ACT_IDX_HI_PROCVAR_NAME_TYPE

We have created an additional index :
create index ACT_IDX_HI_VAR_INST_001 on ACT_HI_VARINST (NAME_, PROC_INST_ID_) ;

It works ! But after several months, the execution plan has changed to use the index ACT_IDX_HI_PROCVAR_NAME_TYPE with bad performance.

Someone can you give us recommendation to optimize requests from this table ?

Regards

Hi @regis.amis.sopra

I don’t think that this is an unknown reason. It’s very Oracle specific and needs Oracle expertise to get a valid answer on this questions.

In my former projects this happened sometimes that the database decides to take other access paths to fetch the data and the very well qualified database admins had an answer to this.

I think you did the right thing adding an index.

But the question I quoted above could better be answered in an Oracle forum.

Hope this helps, Ingo