Automic Workload Automation

 View Only
Expand all | Collapse all

AWA 12.1.5: Oracle - massive TEMP tablespace usage

  • 1.  AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 20, 2019 11:12 AM
    Edited by Basil Benning Aug 21, 2019 02:21 AM

    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|         95176097792
    TEMP_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
    ------------------------------


  • 2.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 01:08 AM
    Hi

    Didi you perform a cold start after the servicepack installation (12.1.3 to 12.1.5 is actually an SP install :-)  )?
    Was anything changed regarding DB settings in ucsrv.ini?

    If yes I would check the DB load log of the initialdata (DB) and the WP logs of the cold start.
    If you can not find anything open a support ticket at Automic Support.

    cheers, Wolfgang

    ------------------------------
    I know I do really know it!
    ------------------------------



  • 3.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 01:18 AM
    Hi.

    First off, OP, you do realize you posted the exact same question twice, right?​

    I will check what our temp space usage looks like when our DBA comes in later. But just a thought (and I'm no Oracle expert and have no DBA rights either, so maybe barking up the wrong tree here), but any chance of seeing what objects or tables are that are using up that space?

    There is a sample query here that promises to "display the contents of the TEMP tablespace" (search for that phrase on the page to find):
    http://www.dba-oracle.com/concepts/temporary_tablespaces.htm

    Maybe by identifying what is actually in there one can find out what's going wrong.

    Hth,


  • 4.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 02:53 AM
    Edited by Basil Benning Aug 21, 2019 02:55 AM
    Hi,

    thanks a lot for your replies!

    First, I also just noticed that my discussion occurs twice - accidenally. After submitting it yesterday morning it did not show up in the community within couple of hours. Hence I assumed it maybe got lost and composed this message again. I already edited the other question and described it as obsolete.

    @Wolfgang:
    All processes were shut down completely and cold start was enabled:
    ucsrv.ini:

    [GLOBAL]
    StartMode=COLD
    "ucsrv.ini" is similar to 12.1.3. We did also run 12.1.5 service pack with original 12.1.3 configuration files from previous installation to be on the safe side.

    We recently did downgrade to 12.1.3 on most servers (showing normal DB TEMP usage again), so we can compare WP and DB logs and 
    I will lookup both logs available to check for similarities and differences.

    ​@Carsten:​
    Thanks for your advise! According to our DBA TEMP usage is very heterogeneous, meaning lots of different tables and no single conspicous objects using all the ressources.
    Thanks a lot for your link. I am currently in close contact with our DBA digging deeper into this topic.

    I will contact Automic support as well and keep you updated.

    Cheers,
    Basil

    ------------------------------
    Senior Consultant
    setis GmbH
    ------------------------------



  • 5.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 02:46 AM
    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.


  • 6.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 03:16 AM
    Edited by Basil Benning Aug 21, 2019 03:18 AM
    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
    ------------------------------



  • 7.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 21, 2019 02:58 AM

    Hi Basil,

    > occurs twice - accidenally

    yeah, the new forum ​software is ... well, you could either say it's "AI driven" or "has a mind of it's own". I for one go with the later :)

    Also, cheers for the feedback and you're welcome. I hope this can get sorted out quickly.




  • 8.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 27, 2019 03:44 AM
    Hello,
    I opened a ticket with the vendor. Here's the latest news:

    Cause: There is a known issue with the TEMP tablespace growing on 12.1*. This was fixed with 12.2 and later versions. The fix is tentatively planned for 12.1.6.
    Solution: Upgrade the system to 12.2 or later.

    Thanks for your support and suggestions as well!

    Cheers,
    Basil

    ------------------------------
    Senior Consultant
    setis GmbH
    ------------------------------



  • 9.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 27, 2019 04:14 AM
    Edited by Carsten Schmitz Aug 27, 2019 04:15 AM
    Thanks for the update!

    I'm somewhat puzzled, as they say you are to update to 12.2 or later, but the fix is planned for 12.1.x ... meh, typo on their end I guess?

    (edit: or maybe they mean it's already fixed in the 12.2 branch, but will be packported into 12.1 with 12.1.6? Possible.)

    Best regards,
    Carsten


  • 10.  RE: AWA 12.1.5: Oracle - massive TEMP tablespace usage

    Posted Aug 28, 2019 08:56 AM
    Hi Carsten,

    I assume it's not a typo since I double checked with the support for further details. Currenlty 12.1.6 has no planned release date.

    As you already summarized in another discussion the latest versions 12.3 / 12.2 seem to be focused on most and sometimes have fixes already for knows issues.
    However, for older versions it's not foreseeable if and when they might be revised. I noticed that support often advises to upgrade to a newer version instead of referring waiting for service packs.

    Cheers,
    Basil


    ------------------------------
    Senior Consultant
    setis GmbH
    ------------------------------