The Geek Diary

Oracle Database 12c New feature: Local Temporary Tablespaces

by

Local Temporary Tablespaces

To improve I/O operations, Oracle introduces a local temporary tablespace in Oracle Database 12c release 2 (12.2), so that spill-overs are written to the local disks on the reader nodes. It is still possible for SQL operations, such as hash aggregation, sort, hash join, creation of cursor-duration temporary tables for the WITH clause, and star transformation to spill over to disk (specifically to the global temporary tablespace on shared disks). Management of the local temporary tablespace is similar to that of the existing temporary tablespace.

When many read-only instances access a single database, local temporary tablespaces improve temporary tablespace management in read-only instances by:

  • Storing temp files in reader node private storage to take advantage of the I/O benefits of local storage.
  • Avoiding expensive cross-instance temporary tablespace management.
  • Increased addressability of temporary tablespace.
  • Improving instance warm-up performance by eliminating on-disk space metadata management. It stores temp file metadata common to all instances in the control file, and instance-specific metadata (for example, the bitmaps for allocation, current temp file sizes, and file status) in the SGA.

You cannot use local temporary tablespaces to store database objects, such as tables or indexes. This same restriction is also true for space for Oracle global temporary tables.

Local Temporary Tablespace Organization

The creation of a local temporary tablespace results in the creation of local temporary files on every instance and not a single file, as is currently true for shared global temporary tablespaces. You can create local temporary tablespaces for both read-only and read-write instances.

The LOCAL FOR LEAF option creates a local temporary tablespace whose files reside on local disks for read-only instances. 

SQL> CREATE LOCAL TEMPORARY TABLESPACE FOR LEAF local_temp_ts_leaf TEMPFILE 'temp_file_leaf.dbf' SIZE 1M AUTOEXTEND ON;

The LOCAL FOR ALL option creates a local temporary tablespace whose files reside on local disks for both read-write and read-only instances.

SQL> CREATE LOCAL TEMPORARY TABLESPACE FOR ALL temp_ts_all TEMPFILE 'temp_file_all.dbf' SIZE 5M AUTOEXTEND ON;

Temporary Tablespace Hierarchy

The allocation of temporary space for spilling to a local temporary tablespace differs between read-only and read-write instances.

For read-only instances:

  1. Allocate from a user’s local temporary tablespace.
  2. Allocate from the database default local temporary tablespace.
  3. Allocate from a user’s temporary tablespace.
  4. Allocate from the database default temporary tablespace.

For read-write instances:

  1. Allocate from a user’s shared temporary tablespace.
  2. Allocate from a user’s local temporary tablespace.
  3. Allocate from the database default shared temporary tablespace.
  4. Allocate from the database default local temporary tablespace.

When you define local temporary tablespace and shared (existing) temporary tablespace, there is a hierarchy in which they are used. To understand the hierarchy, remember that there can be multiple shared temporary tablespaces in a database, such as the default shared temporary tablespace for the database and multiple temporary tablespaces assigned to individual users. If a user has a shared temporary tablespace assigned, then that tablespace is used first, otherwise, the database default temporary tablespace is used.

Once a tablespace has been selected for spilling during query processing, there is no switching to another tablespace. For example, if a user has a shared temporary tablespace assigned and during spilling it runs out of space, then there is no switching to an alternative tablespace. The spilling, in that case, will result in an error. Additionally, remember that shared temporary tablespaces are shared among instances.

Note: In previous releases, the term temporary tablespace referred to what is now called a shared temporary tablespace.

Local Temporary Tablespace Considerations

Instances cannot share local temporary tablespace, hence one instance cannot take local temporary tablespace from another. If an instance runs out of temporary tablespace during spilling, then the statement resutls in an error.

– Local temporary tablespace support only one BIGFILE per tablespace. but the BIGFILE keyword is not required in the creation statement.
– To address contention issues arising from having only one BIGFILE-based local temporary tablespace, multiple local temporary tablespaces can be assigned to different users, as default. A database administrator can specify the default temporary tablespace for a user using ALTER USER syntax. For example:

ALTER USER MAYNARD LOCAL TEMPORARY TABLESPACE temp_ts_for_leaf;

– A user can be configured with two default temporary tablespaces:

  • One local temporary (created with the FOR LEAF option) when the user is connected to the read-only instance running on reader nodes.
  • One shared temporary tablespace to be used when the same user is connected on the read-write instances running on a Hub Node

Managing Local Temporary Tablespaces

You manage local temporary tablespaces and temporary files with either ALTER TABLESPACE, ALTER DATABASE, or both DDL commands. All DDL commands related to local temporary tablespace management and creation are run from the read-write instances. Running all other DDL commands will affect all instances in a homogeneous manner.

For example, the following command resizes the temporary files on all read-only instances:

ALTER TABLESPACE temp_ts_for_leaf RESIZE 1G;

For local temporary tablespaces, Oracle supports the allocation options and their restrictions currently active for temporary files.

To run a DDL command on a local temporary tablespace on a read-only instance, only (in particular, CREATE LOCAL TABLESPACE…FOR LEAF or ALTER TABLESPACE LOCAL TABLESPACE…FOR LEAF), there must be at least one read-only instance in the cluster. This restriction is not applicable when creating or altering local temporary tablespaces FOR ALL. Users can assign a default local temporary tablespace to the database with a DEFAULT LOCAL TEMPORARY TABLESPACE clause appended to the ALTER DATABASE command. Given below are some examples of local temporary space management using the ALTER DATABASE or ALTER TABLESPACE commands.

– To resize a local temporary file:

SQL> ALTER TABLESPACE temp_ts_for_leaf RESIZE 10g;

– To change auto-extension attributes of a local temporary file:

SQL> ALTER TABLESPACE temp_ts_for_leaf AUTOEXTEND ON NEXT 20G;

– To decrease the size of a local temporary tablespace:

SQL> ALTER TABLESPACE temp_ts_for_leaf SHRINK SPACE KEEP 20M;

– To take a local temporary tablespace offline:

SQL> ALTER DATABASE TEMPFILE 'temp_file_leaf' OFFLINE;

Local Temporary Tablespace Dictionary Views

All the diagnosability information related to temporary tablespaces and temporary files exposed through AWR, SQL monitor, and other utilities, is also available for local temporary tablespaces and local temporary files. This information is available with the existing dictionary views for temporary tablespaces and temporary files: DBA_TEMP_FILES, DBA_TEMP_FREE_SPACE.

The USER_TABLESPACES and DBA_TABLESPACES dictionary view are extended by a column, called SHARED, that indicates whether the temporary file is local (FOR LEAF or FOR ALL) or shared.

The DBA_TEMP_FILES dictionary view is extended by two columns: SHARED and INST_ID. The SHARED column indicates whether the temp file is local (FOR LEAF or FOR ALL) or shared. The INST_ID column contains the instance number. For shared temporary files, there is a single row per file, and the INST_ID is null. For local temporary files, this column contains information about temporary files per instance, such as the size of the file in bytes (BYTES column).

SQL> select tablespace_name, shared, inst_id from DBA_TEMP_FILES;
TABLESPACE_NAME                 SHARED       INST_ID
------------------------------ ------------- -----------
TEMP                            SHARED
TEMP_ALL                        LOCAL_ON_ALL   1
TEMP_ALL                        LOCAL_ON_ALL   2
TEMP_ALL                        LOCAL_ON_ALL   3
TEMP_ALL                        LOCAL_ON_ALL   4
TEMP_LEAF                       LOCAL_ON_LEAF  1
TEMP_LEAF                       LOCAL_ON_LEAF  2

The DBA_TEMP_FREE_SPACE dictionary view is extended by two columns: SHARED and INST_ID. The SHARED column indicates whether the temporary file is local (FOR LEAF or FOR ALL) or shared. The INST_ID column contains the instance number. For shared temporary files, there is a single row per file, and the INST_ID is null. For local temporary files, this column contains information about temporary files per instance, such as total free space available (FREE_SPACE column).

Summary

  • USER_TABLESPACES and DBA_TABLESPACES have a new column SHARED,indicating that the temp file is local or shared.
  • DBA_TEMP_FILES is extended by two columns: SHARED and INST_ID.
  • DBA_TEMP_FREE_SPACE is extended by two columns: SHARED and INST_ID.

You May Also Like