Hi Carsten,
thanks a lot for your research and opinion!
This is exactly what we were used to before the upgrade. TEMP usage was minimal, if any then only a few MB.
My DBA is curious as well and eager to help since he and other UC4 admins never experiended such a behaviour.
Slightly increased TEMP usage might be justifieable after an update but it should adjust around a constant level and not increase slowly from x MB to xx GB within 1 day. Several servers did show the same symptoms directly after upgrading the service pack and TEMP usage grows constantly to its limit (90GB, 40GB).
I am no DB expert either but it seems that the application is not cleaning up TEMP after finishing its activities and freeing ressources again.
Interesting observation:
Although free TEMP space is literally 0, UC4 is still working pretty flawlessly, i.e. jobs are running fine, no abortions/cancellation, performance setbacks (AWI, executions, monitoring).
We did expect much more impact on automation processing and would actually not know about this issue without our DBA or the other applications using the same tablespace TEMP which were impacted unfortunatelly.
I'll keep you updated.
Cheers,
Basil
------------------------------
Senior Consultant
setis GmbH
------------------------------
Original Message:
Sent: 08-21-2019 02:45 AM
From: Carsten Schmitz
Subject: AWA 12.1.5: Oracle - massive TEMP tablespace usage
Okay, I quizzed our DBA. Our temp tablespace is 32 GB, and currently has no temporary data in it at all. It's usage ist literally zero.
We're on AE 12.2 with Oracle 12c.
So I'd figure there's something wrong with the massive consumption of temp space you are seeing.
Original Message:
Sent: 08-20-2019 06:56 AM
From: Basil Benning
Subject: AWA 12.1.5: Oracle - massive TEMP tablespace usage
Hello,
in our test environments we upgraded AWA components from 12.1.3 to 12.1.5 (release of 2019-07-24). This includes:
- automation engine
- service manager
- UNIX agent
- db
- utilities
- AWI
Database update was processed and ended with: "Application return code = 0".
cd [UC4_UTILITY_HOME]/utility/bin
./ucybdbld -X[UC4_HOME]/db/general/12.1/UC_UPD.TXT
AWA is starting successfully and processing jobs. However, since the upgrade TEMP tablespace usage has increased enormously - up to its limit of 90 GB.
Configuration (ucsrv.ini, uc4.smc, uc4.smd) and UC4 objects are similar to the previous 12.1.3 installation.
In order to prevent impacts on other application also using TEMP tablespace, our DBA has assigned its own tablespace TEMP_UC4 sized 40 GB which was fully used after 1 day.
2 DB sessions "AUTOMATION ENGINE JWP" were active and have occupied all of the volume in TEMP tablespace.
TABLESPACE_NAME | TABLESPACE_SIZE| ALLOCATED_SPACE| FREE_SPACE==============================|====================|====================|====================TEMP | 95193923584| 1891631104| 95176097792TEMP_UC4 | 42077257728| 42077257728| 0
Has anyone experienced similar effects on the database or ideas what else to check in detail?
Thanks a lot in advance!
Basil
------------------------------
Senior Consultant
setis GmbH
------------------------------