Forum Stats

  • 3,872,592 Users
  • 2,266,456 Discussions


Find if temporary objects created before TEMP_UNDO_ENABLED is set

Version : Oracle 12c ( - EE 2-node RAC, active DG

I'm trying to reduce REDO and archive logs generated for my application and measure using V$SYSSTAT and corresponding archive logs using DBA_HIST* views.

In my application code on DB side, I'm using the session level setting of TEMP_UNDO_ENABLED to direct UNDO for gtt into temporary tablespace. The specific feature noted here.


I see the documentation has this quote:

..if the session already has temporary objects using regular undo, setting this parameter will have no effect

If I use a pure database session, I can ascertain that since no other temporary tables have been created/used before setting the parameter, the REDO logs generated are minimal. I can use a simple (select value from V$SYSSTAT where name= 'redo size') to see the difference.

However the actual application (Java) triggers this code through a JDBC session. As such, I'm unable to ascertain if before the call to 'ALTER SESSION..' there were any GTT or other temporary objects previously created/used in the session. The consequence of this is, if say a GTT was already used, then the call to 'ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE' simply ignores the setting without an indication. The code will continue logging UNDO & REDO in the normal tablespace, which is unintended.

Is there any way to query if this parameter TEMP_UNDO_ENABLED is already set/unset within the session, so that before I do a ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE I'll know for sure this 'will' or 'will not' have an effect?

Thanks in advance for inputs.


Best Answers


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,920 Red Diamond


    If GTT doesn't have ON COMMIT PRESERVE ROWS we can also check GV$TRANSACTION for UBAFIL. It will be UNDO tablespace file id if UNDO is used or 0 if TEMP_UNDO is used. It will work for ON COMMIT PRESERVE ROWS if GTT changes aren't yet committed. However, if changes are committed there will be no open transaction and this method will not work.


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,115 Blue Diamond


    That's an interesting obsevation; but does it depend on the transaction doing nothing but manipulate the GTT? What if the transaction mixes ordinary DML and GTT DML? It's been some time since I did any experiments of that type.

    Reading the definition of the parameter there seem to be a number of questions (not even rocket-science questions) that need to be answered to clarify exactly what can happen - one example being this statement:

    Once the value of the parameter is set, it cannot be changed for the lifetime of the session.

    That sounds as if any DML that takes place as the session starts will fix the temp_undo_enabled parameter - so what if audit by logon is enabled? Does that transaction fix the setting? Then there's a note that:

    If database applications make use of temporary objects (using global temporary tables or temporary table transformations) ...

    But temp table transformations don't (seem to) generate undo beyond a tiny amount for the recursive transaction(s) that update the file and segment headers. So why is there a reference to them - and does that make my concern above about the distinction between GTTs and materialized CTEs irrelevant.

    I feel that there are probably a couple of dozen scenrios to investigate before we could have any confidence in any method for knowing that the "alter session" would work. (And half those scenarios might have to be about connection pooling anyway!)


    Jonathan Lewis

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,115 Blue Diamond
    edited Feb 2, 2021 11:42AM

    If you're going to post sample code you ought to test it before you post it; there are about 10 mistakes in your sample which mean it won't work, and one of them would give you a false positive result.

    On a specific detail, when you're testing strategies for this problem, make sure you start a new session after you've created the GTT or you may find that the act of creating it chnages the results you would otherwise get.

    I did a quick test on executing a query with a "with subquery" that was going to materialize into the temporary tablespace, and if it ran before the "alter session" command it blocked the change and the PL/SQL would generate ordinary undo. If the "alter session" ran first then it took effect and the pl/sql loop dumped undo the the temporary tablespace.

    The same test in seemed to behave more nicely - though I had to switch to manual workarea sizing to make sure that the materialized subquery was actually dumped to disc to be confident of that.

    The same test on reminded me that it's important to start a new session for each test when doing some of these things (and it can even be necessary to flush the shared pool, or force cursors out of memory). It also prompted me to go back to 12c and test again.

    It seems that when a "with subquery" materializes it won't necessarily spill to disc, and if it DOESN'T spill to disc (and this applies to both 12c and 19c in my tests) then you can "alter temp_undo_enabled = true" after the query has executed and find that you DO generate temp undo; but if the materialized WITH does spill to disc then you stuck and the parameter will show as changed, but you won't get temp undo.


    Jonathan Lewis