Data write performance problem on RDB schema 8.20

[sanikval]

Good day.
We updated WinCC OA from 3.14 version to 3.15 and oracle schema from 8.15 version to 8.20. After which, we found performance problem of writing data into database and increasing the count of RDB buffer data files.
We executed tracing of RDB oracle sessions and found this:

Execution time of one merge statement elapsed about 3 seconds. The reason for slow execution is unindexed condition TO_CHAR(h.ts,'…') = TO_CHAR(s.partner, '…').
On our test lab the problem reproduce after increasing count of rows in ALERTHISTORY_######## table more than 6 millions rows.
For restore the process of writing data into database, we changed the condition after which whole RDB buffer data files was written into database.
We hope that ETM will accept this issue for detailed analysis.

[leoknipp]

For a detailed analysis of this issue please get in contact with our WinCC OA Consulting team.
Doing Oracle analysis via the WinCC OA Portal is not the best approach.

Best Regards
Leopold Knipp
Senior Support Specialist

[zheleschikovav]

We have already analyzed:

• in 3.14, the function UpdateAlertPartner used the index for h.ts
• in 3.15, the function UpdateAlertPartner not used the index h.ts because you are converting h. ts to TO_CHAR.

In the real plant(3.15) RDB manager stop writing values to Oracle if ALERTHISTORY_000000123 have 6.000.000 alerts because run UpdateAlertPartner to slow

Decision:

• create index TO_CHAR(h.ts, ''YYYY.MM.DD HH24:MI:SS.FF3'') on ALERTHISTORY_000000123
• optimize the query

Diff UpdateAlertPartner from 3.14 to 3.15

[leoknipp]

If you want to have detailed information for your questions please get in contact with the WinCC OA consulting team.
Maybe you can get rid of the problem by switching the alert tables in RDB to create a new "alert history table".

Best Regards
Leopold Knipp
Senior Support Specialist