B - Production Support » BI FAST Support

We will perform enhancement on the below items to tackle for this problem>-

Fee Calculation batch job enhancement:-
1) Will enhance the Fee Calculation batch job to include the "Completed Date" as the search criteria

Dashboard module enhancement:-
1) To develop the new pre-calculation batch job on every hour
2) To enhance Dashboard module to use the new table and also the latest hour data
3) To add column name to the count('column name')
4) To add table clean up for the new table to the archive program

Reports Modules enhancement:
1) To enhance the Daily Report batch job to store the daily calculated data to a new table (7 new tables)
2) To enhance the Weekly and Monthly Reort batch job to query from the new table for the report generation (24 reports impacted)
3) To add table clean up for the new table to the archive program

Issue 1:
After implemented the DB partition, certain search query facing slowness and high CPU issue due to db scan

Suspect:
After implemented the table partition for the transaction table, suspect due to the query does not specify to which partition to get the data that cause Oracle DB to use more CPU on the query

Solution:
Add the "Completed Date", the Partition Key, as part of the search criteria. Oracle DB will have the specific partition date range to search from.

Action:
1) Will enhance the Fee Calculation batch job to include the "Completed Date" as the search criteria

Release Patch: TrnxDataChargesCalculatorMain-2.0.32-ga.jar

Issue 2: Suspect of high CPU contribution from Dashboard

Suspect:
Dashboard is one of the heavy query module that will enquire for today's transaction data during every user login. Due to getting more participants and more transaction data, during day time, this might contribute to heavy Transaction table query that resulted on higher CPU.

Solution:
Create a new batch job to perform pre-calculation on every hour per participant and save the result to a new table. When a participant login, the Dashboard will calculate based on the precalculated data from the new table. This will greatly lighten on the DB query on the fly. Of cause, we spread the DB usage into every hour and also reduce to by participant, not by user.

Action:
1) To develop the new pre-calculation batch job on every hour
2) To enhance Dashboard module to use the new table and also the latest hour data
3) To add column name to the count('column name')
4) To add table clean up for the new table to the archive program

Release Patch: ???

Issue 3:
Suspect of high CPU contribution from Daily report batch job

Suspect:
Monthly report is one of the heavy query at every month end, which will lead to higher CPU usage at DB at every month end.

Solution:
To enhance all the daily report module to store the daily calculated data to a separate table. Weekly and Monthly Report batch job will get the data from the new daily report table, which will greatly reduce the query volume to DB at month end.
All query will add the "Completed Date" as search criteria to comply with the table Partition Key

Action:
1) To enhance the Daily Report batch job to store the daily calculated data to a new table
2) To enhance the Weekly and Monthly Reort batch job to query from the new table for the report generation
3) To add table clean up for the new table to the archive program

Release Patch: ???

Development done on Dec 23th

Already went life in July 2023