12c Temporary

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 (select value$ from props$ where name = ‘DEFAULT_TEMP_TABLESPACE’) which means it won’t generate redo. As an interesting consequence 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 default 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.

UPDATE: in 12.2 you can create a “local” temporary tablespace, and even specify a default local temporary tablespace. This appears to be a feature aimed at RAC systems (and flex clusters) that allows each instance to write to “local” files rather than having to share files and space management with other instances. Every instance has the same temporary tablespace, which is automatically a bigfile tablespace (i.e. one file) and the name of the associated tempfile has a numeric postfix identifying the instance which uses it. I have yet to check whether “temp undo” will be written to the database’s temporary or local temporary tablespace.

 

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 isolated 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, or include LOB columns, 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 caused 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 first when I want to examine what’s going on in the temporary tablespace(s) but you should probably use (or be aware of) the “modern” object v$tempseg_usage which is (still) just a synonym for 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 Database 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.

Update (April 2022)

Prompted by a discussion about using global temporary tables and enabling temp undo, I spent a few minutes searching for some notes that I thought I had written about the combination and the impact of materialized “with” subqueries. It’s another note I never got around to writing, but most of the material and ideas can be found in this thread on Oracle Developers’ Forum.

Critically, and subject to further testing, if you try to “alter session” to enable temp undo then it may have no effect if the session has already generated undo. The thread doesn’t examine all the possible ways in which this could happen, though; not does it examine the case where the parameter has been set at the system level or in the startup file. (Tests and blog note to follow – but not in the very short term.)