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.

  • In 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 default temporary tablespace, which means it won’t generate redo. As an interesting side effect this also means that you can do DML on temporary objects in a physical standby database. Currently the undo segment shows as type “UNDEFINED” in v$sort_usage. A detail to watch out for, though – it looks as if each session may get its own temporary undo segment – so be careful about specifying the extent size and tempfile size for the database default temporary tablespace.
  • In 11g you can specify a tablespace (though not a tablespace group) in the ‘create global temporary table’ statement, which means you 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 – and 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.
  • 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, factored subqueries et. al.) will go.

This combination 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. 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 work is coming from if you’r ever in the position where your single temporary tablespace is subject to a lot of I/O.

Footnote:

In the past I’ve advised DBAs to set up a small number of tablespaces (or 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.

1 Comment »

  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.
    
    rajesh@PDB1>
    

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,306 other followers