Choose a Temporary Tablespace for a User

Temporary tablespaces are used for operations like a large sort that requires disk space on a short-term basis. Assigning a user to a temporary tablespace allows you to segregate this type of I/O activity, and place it on a set of disks that are not being used for other purposes. If you do not assign a user to a temporary tablespace, then sorts and similar operations will use the SYSTEM tablespace. That can have a disastrous impact of performance, because the SYSTEM tablespace contains the data dictionary objects and already is heavily used. Compare the two situations shown in the following diagram.

1. Question: I have a performance tuning problem using (GTT) global temporary tables and the WITH clauses in a vendor application. Once created, I cannot see the temporary table rows for a specific user session and I cannot see how to manipulate the SQL against the temporary tables to force my best explain plan.
2. Question: What are the ways to index, monitor and tune global temporary tables^[1]?
   Answer: Oracle SQL provides GTT (global temporary tables) and the SQL-99 WITH clause to improve the performance of complex queries that want to use permanent storage to hold intermediate results for a problem.
   Oracle 12c note: Starting in 12c, Oracle will allow private optimizer statistics for global temporary tables, allowing you to invoke session-level dbms_stats to gather statistics specific to your own global temporary table. Prior to 12c, statistics were shared from a master copy of the CBO statistics.

Cost Based Optimizer (CBO) Statistics in Oracle 19c

1. The Tom Kyte is experiencing plenty of I/O throughput to and from the TEMP tablespace.
   His sort is progressing rapidly.
2. Larry Ellison is experiencing limited throughput because his large sort is using the SYSTEM tablespace, which other users also are using heavily.
3. For the DBA, a storm is brewing over the DBA who can expect to receive some phone calls from irate users.
4. Larry Ellison's sort is hurting everyone's performance. Where is that DBA, anyway?

When the user named Tom Kyte issues a query requiring a sort, no other database users are affected unless they too are using the TEMP tablespace. On the other hand, when Larry Ellison requires a sort, it places a heavy I/O burden on the SYSTEM tablespace, and could have a drastic effect on the performance experienced by all database users. Most databases are constructed with at least one or more temporary tablespaces. In the case of the COIN database, the temporary tablespace is named TEMP.

Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk. The temporary tablespace is where this takes place. Each database should have one temporary tablespace that is created when the database is created. You create, drop and manage tablespaces with

1. create temporary tablespace,
2. drop temporary tablespace and
3. alter temporary tablespace commands,

each of which is similar to its create tablespace counterpart.
The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles. Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command as you can see in these examples:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE ?/ora01/oracle/oradata/booktst_temp_01.dbf? SIZE 50m;
DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tempfiles are a bit different than datafiles in that you may not immediately see them grow to the size that they have been allocated (this particular functionality is platform dependent). Hence, do not panic if you see a file that looks too small.

col allocated_bytes format 999,999,999,999,999
col free_bytes format 999,999,999,999,999

select  
   a.tablespace_name,  
   a.file_name,  
   a.bytes c3,  
   b.free_bytes  
FROM 
   dba_temp_files a, 
   (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b
WHERE  
a.file_id=b.file_id
ORDER BY 
 a.tablespace_name;

Here is a script that will display the contents of the TEMP tablespace.

set pagesize 60 linesize 132 verify off  
break on file_id skip 1 
 
column file_id heading "File|Id"  
column tablespace_name for a15  
column object          for a15 
column owner           for a15 
column MBytes          for 999,999 

select tablespace_name, 
'free space' owner, /*"owner" of free space */  
' ' object,         /*blank object name */ 
file_id, /*file id for the extent header*/ 
block_id, /*block id for the extent header*/  
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_free_space 
where tablespace_name like '%TEMP%' 
 union 
select tablespace_name, 
substr(owner, 1, 20), /*owner name (first 20 chars)*/ 
 substr(segment_name, 1, 32), /*segment name */ 
file_id, /*file id for extent header */  
block_id, /*block id for extent header */ 
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_extents 
where tablespace_name like '%TEMP%'  
order by 1, 4, 5  
/ 

You can check for held TEMP segments with this query:

select  
   srt.tablespace,  
   srt.segfile#,  
   srt.segblk#,  
   srt.blocks,  
   a.sid,  
   a.serial#,  
   a.username,  
   a.osuser,  
   a.status  
from 
    see code depot for full scripts 
   v$session    a,
   v$sort_usage srt  
where  
   a.saddr = srt.session_addr  
order by  
   srt.tablespace, srt.segfile#, srt.segblk#,  
   srt.blocks;

[1] Temporary Tables: Temporary tables in Oracle serve as transient storage for data within a single database session or transaction.