Oracle Scratchpad

March 14, 2014

12c Temporary

Filed under: 12c,Infrastructure,Oracle,undo — Jonathan Lewis @ 6:41 pm GMT Mar 14,2014

Just one of those little snippets to cover something new and remind of something old. A single session can now have three (or more) temporary tablespaces in use at the same time for different reasons.

12c

You can set parameter temp_undo_enabled to true, at which point the undo relating to global temporary tables (GTTs) will be written into the database’s default temporary tablespace (i.e. the one that SYS has as its default) which means it won’t generate redo. As an interesting side effect this also means that you can use DML on temporary objects in a physical standby database.

In 12c the temporary undo segment is reported with segtype = “UNDEFINED” in v$sort_usage/v$tempseg_usage; by 19c this has changed to “TEMP UNDO”.

A detail to watch out for: it looks as if each session gets its own temporary undo segment and the segment will not be released or shrink, until the session ends. Since the default definition for the temporary tablespace is uniform allocation with 1MB extents; you might want to think carefully about specifying the extent size and tempfile size for the database default temporary tablespace before you enable this feature.

11g

In 11g you can specify a tablespace (though – even in 19c – not a tablespace group) in the ‘create global temporary table’ statement, which means you can keep activity about GTTs separated from the I/O resulting from sorts and hash joins etc. spilling to disc. In fact you could specify a different temporary tablespace for every GTT if you wanted to – realistically, I could imagine a case for having a couple of different temporary tablespaces to accommodate GTTs with very different usage characteristics. (Unfortunately you still can’t specify a tablespace in the dbms_lob.create_temporary() function).

If you don’t specify a tablespace for a GTT it will go into the default temporary tablespace of the user who is using it (not the database default, and not the default for the definer of the GTT). If you create indexes on a GTT they will automatically go into the same tablespace as the table.

Generic

Finally, of course, there’s the default temporary tablespace for the user and this is where GTTs will go if they don’t have a tablespace specified, and where all the scratch data (sorts, hash tables, materialized “with” subqueries et. al.) will go.

Consequences

The range of possibilities means, of course, that you could manage to do a single “insert as select” writing a GTT to one temporary tablespace, with its undo going to a second temporary tablespace, and the spill from a sort or hash join in the select going to a third temporary tablespace. The flexibility probably won’t make much difference to performance (for most people), but it’s possible that it will make it easier to monitor where the I/O is coming from if you’re ever in the position where a single temporary tablespace would be subject to a lot of I/O.

Footnotes:

In the past I’ve advised DBAs to set up a small number of temporary tablespaces (or temporary tablespace groups) so that they can allocate different classes of users – typically grouped by business function – to different temporary tablespaces. The ability to allocate GTTs to temporary tablespaces allows a further degree of refinement to this strategy.

If you do use the view v$sort_usage to check what the temporary space allocation looks like it has a column session_num, which sounds like it ought to be the session sid; it isn’t, it’s the serial# (the v$sort_usage.session_addr will give you the link to the session via v$session.saddr).

Note also that the column v$sort_usage.sql_id identifies the statement currently being executed by the session, not (necessarily) the statement that cause the temporary space allocation, In 12c Oracle added the column sql_id_tempseg to report this rather important piece of information.

Old habits die hard – I tend to think of v$sort_usage but you should probably use (or be aware of) the “modern” object v$tempseg_usage. which is just a synonym of v$sort_usage.

Update (May 2018)

I’ve just discovered an undesirable side effect of enabling temporary undo. I’ve tried to do an “explain plan” on a distributed query in an instance of 12.2.0.1 (from a PDB) where temp_undo_enabled was set to true, and got the following Oracle error messages:

explain plan for select  /*+ driving_site(t2) */
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55525: cannot generate temporary undo in a distributed transaction
ORA-02063: preceding line from ORCL
ORA-02063: preceding 3 lines from ORCL@LOOPBACK

You’ll notice from the echoed first line of the call to explain plan that I’ve used the driving_site() hint in this example to make the remote database optimise the query, and that’s why the last two lines (ORA-02063) appear; if I remove the driving_site() hint then only the first two error messages appear.

Update (July 2020)

In a recent thread on the Datbase Admin forum on MOSC (needs a valid account) the point came up that 18.3 introduces data errors if you try to mix global temporary tables, distributed transactions and enable temp undo – but doesn’t necessarily report ORA-55525 when (apparently) it ought to (as we saw in the May 2018 update above).

Moreover the manuals for Oracle 19c still report that

  • Distributed transactions are not supported for temporary tables

This, despite the fact that they seem to have operated for many years with no problem – until you enable temp undo, that is.

 

 

2 Comments »

  1. Jonathan,

    I am on Multitenant Architeture, and seems to be temp_undo_enabled not working at session level. but fine at system level.

    Here is my test case to prove that.

    I dont see redo_size REDUCED when TEMP_UNDO_ENABLED=true.
    Is that Temp undo is not supported in multitenant Architecture?

    (looks like the parameter works at SYSTEM level but not at SESSION level)
    http://docs.oracle.com/cd/E16655_01/server.121/e17633/logical.htm#CNCPT89227

    rajesh@PDB1> create global temporary table gtt
      2  on commit delete rows
      3  as
      4  select * from all_objects
      5  where 1 = 0;
    
    Table created.
    
    rajesh@PDB1>
    rajesh@PDB1> alter session set temp_undo_enabled=false;
    
    Session altered.
    
    rajesh@PDB1>
    rajesh@PDB1> set autotrace traceonly statistics
    rajesh@PDB1> insert into gtt
      2  select * from all_objects ;
    
    73812 rows created.
    
    
    Statistics
    ----------------------------------------------------------
    
         482804  redo size
          73812  rows processed
    
    rajesh@PDB1>
    rajesh@PDB1> update gtt
      2  set object_name = lower(object_name);
    
    73812 rows updated.
    
    
    Statistics
    ----------------------------------------------------------
    
        7162396  redo size
          73812  rows processed
    
    rajesh@PDB1> set autotrace off
    rajesh@PDB1>
    rajesh@PDB1> commit;
    
    Commit complete.
    
    rajesh@PDB1>
    rajesh@PDB1>
    rajesh@PDB1> alter session set temp_undo_enabled=True;
    
    Session altered.
    
    rajesh@PDB1> set autotrace traceonly statistics
    rajesh@PDB1> insert into gtt
      2  select * from all_objects ;
    
    73812 rows created.
    
    
    Statistics
    ----------------------------------------------------------
         482848  redo size
          73812  rows processed
    
    rajesh@PDB1>
    rajesh@PDB1>
    rajesh@PDB1> update gtt
      2  set object_name = lower(object_name);
    
    73812 rows updated.
    
    
    Statistics
    ----------------------------------------------------------
    
        5648620  redo size
          73812  rows processed
    
    rajesh@PDB1> set autotrace off;
    rajesh@PDB1> commit;
    
    Commit complete.
    
    

    Comment by Rajeshwaran Jeyabal — March 20, 2014 @ 9:50 am GMT Mar 20,2014 | Reply

  2. […] Beside every new session will allocate a new “UNDEFINED” segments (Jonathan Lewis post :https://jonathanlewis.wordpress.com/2014/03/14/12c-temporary/) so watch out for space allocation.We may need to create a specific temporary tablespace for undo […]

    Pingback by 12C Temporary Undo : Under the hood | Hatem Mahmoud Oracle's blog — May 11, 2015 @ 10:03 am BST May 11,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.