L3 - Default Description
Scenario:
- Oracle database
- ~ 20.717.999 rows in ACT_RU_METER_LOG table. (The table contains the expected indexes - CREATE INDEX ACT_IDX_METER_LOG ON ACT_RU_METER_LOG(NAME_, TIMESTAMP_);
- Trigger execute metrics in Admin webapp to generate the annual report for Flow Nodes Instances (FNI) and Executed Decision Elements (EDE)
Expected behavior:
A successful result of the query in a reasonable time.
Observed behavior:
Sql exception due to long running query:
java.sql.SQLRecoverableException: Getrennte Verbindung
An example of the query is:
SELECT Sum(RES.value_) FROM act_ru_meter_log RES WHERE RES.name_ = 'activity-instance-start' AND RES.timestamp_ between to_timestamp('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2019-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
Hints:
Full table access is performed even though ACT_IDX_METER_LOG is existing. This can happen on Oracle db as the Oracle optimizer might decide to execute a full table scan if it considers that it is cheaper than the cost of using an index.
Considerations:
- We can investigate if we can add a parallel-hint to the query on Oracle:
SELECT /*+ FULL(RES) PARALLEL(RES 6) */ SUM (RES.VALUE_) FROM ACT_RU_METER_LOG RES WHERE RES.TIMESTAMP_ >= TO_DATE ('2019/01/01', 'YYYY/MM/DD') AND RES.TIMESTAMP_ < TO_DATE ('2020/01/01', 'YYYY/MM/DD');
The parallelization might be applicable for some of the users but we need to consider all of the scenarios when introducing such change. Such parallelization might be implemented as configurable.