Warp Speed with Temp Tablespace Groups

The introduction of the temp tablespace was a crucial step in the RDBMS to separating  the “work area” for sorting and specific join operations from permanent segments.   To eliminate the performance challenges of sorts and hash against the I/O threshold involved with performing this word in the temp tablespace, Oracle 9i introduced the Program Global Area, (PGA) to complete sorts and hashes within memory.

DBA’s spent considerable time tuning the PGA, attempting to continually ensure that as more complex sorting and join features emerged, that these processes would complete their work within the PGA and not require such a large work area to force a, “swap” to the temp tablespace, again returning to an I/O bound process.

Throughout the years of 10g release 2, larger databases, required more sorting and hash operations, which in turn required larger work areas that for particular requirements, no PGA could meet.  This would be seen in PGA cache hit graphs without a size recommendation to meet a 90% or better hit ratio.  These sorting and/or hash “heavy” processes would then become burdensome on the database, often encompassing the primary utilization of temporary “swap” and extended wait time.

When a DBA or Developer observes high waits on tempfile reads and writes, the challenge is often not understood.  Unlike a standard tablespace, that with high read and writes can have the I/O intensive demands spread across more area by adding multiple datafiles to write to simultaneously, a temp tablespace, when observed, will appear very different.

A simple ASH report can show how impacting this type of performance challenge can be to a VLDB:

Temp tablespace groups have received little press and less discussion since their introduction in Oracle 10g.  When utilizing temp tablespace groups, a DBA can offer impressive performance increases by spreading the I/O across multiple files vs. one tempfile.   As tempfiles are utilized differently than datafiles, a temp tablespace group enables a process to consume temp space from multiple tablespaces.

Before the feature is implemented though, there are many considerations that need to be examined to ensure that benefits are seen and that complications are not introduced.

Myths about temptablespace groups:

Myth 1.  A temp tablespace group must have more than 1 tablespace.

Answer-  Untrue.  The tablespace group is created with the initial temp tablespace defined with the temp tablespace group clause.

Myth 2.  There is a limit to how many temp tablespaces can be added to a temp tablespace group.

Answer-  There is no explicit limit on the maximum number of tablespaces that can be allocated to a tablespace group.

Myth 3.  A temporary tablespace can belong to more than one temporary tablespace group.

Answer-  It can belong to only one group.

Myth 4.  Temporary tablespace groups improve all sorting issues.

Answer-  Windows sort rank often perform worse when utilizing temp tablespace groups and parallel.

The feature can be allocated to one user as its temporary tablespace or to an entire database to increase performance.

To create a temporary tablespace group is quite simple:

CREATE TEMPORARY TABLESPACE <tablespace_name>

TEMPFILE <ASM DISKGROUP >

SIZE <SIZE REQ>

TABLESPACE GROUP <group_name>;

CREATE TEMPORARY TABLESPACE TEMP_G1

TEMPFILE '+DATA'

SIZE 20000M

TABLESPACE GROUP TEMP_G;

desc dba_tablespace_groups

SELECT *

FROM dba_tablespace_groups;

ALTER TABLESPACE <tablespace_name>

TABLESPACE GROUP <group_name>;

ALTER TABLESPACE DM_DATA1 TABLESPACE GROUP TEMP_G;

ALTER USER DM_USER TEMPORARY TABLESPACE TEMP_G;

SELECT TEMPORARY_TABLESPACE FROM DBA_USERS

WHERE USERNAME='DM_USER';

ALTER SYSTEM TEMPORARY TABLESPACE TEMP_G;

Planing to implement temp tablespace group(s)
1.  Plan your temp tablespace group with careful consideration-  the tablespaces should be of the same size, have the same amount of files with the tempfiles each the same size.
2.  Keep in mind- inconsistent sizes across the tablespaces can affect the efficiency of the striping when using a non-ASM managed environment.
3.  The temp work will stripe across the tablespaces quite evenly.  When using a temp tablespace group, it will no longer simply use one temp tablespace at a time, so keep this in mind when monitoring temp tablespace usage.
4.  If in a non-ASM environments, stripe your files in differing orders to avoid having the same # file on the same drive as another temp tablespace from the temp tablespace group-  this will eliminate more I/O “hot spots”.
4.  You should estimate the required size of each tablespace to be approximately 40-60% or your current temp tablespace needs, depending on your database usage, (parallel processing, rowid range scans and types of sorts…)
5.  Temp tablespace groups should not be a replacement for SQL tuning. Larger than acceptable temp tablespace usage in correlation to the data set size the code is working with is a clear indicator that the code should be examined for tuning opportunities.

6.   Match your quantity of temp tablespaces contained in a tablespace group to the amount of parallel you will be hinting in your statements using it.

7.   Poor performance from statistics issues is only aggravated by temp tablespace groups.  Sort and hash operations are two of the most sensitive tasks to poor statistics, so it is easy to understand that extensive performance issues should be expected if statistics are not addressed before temp tablespace group introduction.

Temp tablespace groups and parallelism

DBA’s who utilize parallel query efficiently in their environments often follow a strict set of guidelines on what scenarios, objects and design must be in place before parallel will offer a true benefit in performance.

• Partitioned Objects
• Where clause that utilizes a partitioning key.
• Large insert/update with defined variables.
• Non-stale statistics
• Setting the degree of parallel, (DOP) on the object.

Parallel can be utilized with temp tablespace groups to enhance performance to an incredible degree as well, but of the above “rules”, only one is preferable and it is most likely not the one most would guess from the above list.

Temp Tablespace groups should have the following for positive performance gains when utilizing parallel:

• Non-stale statistics
• Set the degree of parallel, (DOP) as a hint in the query, not at the object level.
• Combination works best with OLAP and DSS systems, rare occasion where an OLTP would benefit.
• Unless the insert and update is performing large sort or hash, it would offer little performance gain.
• Partitioned objects are not a requirement to receive performance benefit.

The last bullet point is often difficult to understand, but the mindset must be re-aligned from the idea that you are using parallel to benefit a “read or write on multiple objects” i.e partitioned objects and that we are using the parallel process to benefit “read and write to multiple tempfiles” which is the temp tablespace group.  Visualizing the temp tablespace group as a “partitioned object” may assist with comprehending the benefits.

Performance Example

The process example:

• utilizes 720GB of temp tablespace.
• has an execution time of 16 hrs.
• Source tables are 472GB in a 10TB datamart environment.

The Goal to attain:

• Cut the execution time in 1/2 to re-allocate the resources to other processes.
• Minimize temp usage by 60%.
• Use temp tablespace groups to eliminate temp read/write waits in existing process.

Note**  Second time documenting this test.  Current iteration has enhancements (i.e. Added fourth temp tablespaces and changing amount of parallel used in the statements, etc…)

Create Temp Tablespace Group, three temp tablespaces total, 150GB each-
 CREATE TEMPORARY TABLESPACE TEMP_G1 TEMPFILE ‘/u06/oradata/sid1/temp_g1_01.dbf’ SIZE 51200M,
 ‘/u10/oradata/sid1/temp_g1_02.dbf’ SIZE 51200M,
 ‘/u20/oradata/sid1/temp_g1_03.dbf’ SIZE 51200M
 TABLESPACE GROUP TEMP_G;

CREATE TEMPORARY TABLESPACE TEMP_G2 TEMPFILE ‘/u30/oradata/sid1/temp_g2_01.dbf’ SIZE 51200M,
 ‘/u20/oradata/sid1/temp_g2_02.dbf’ SIZE 51200M,
 ‘/u10/oradata/sid1/temp_g2_03.dbf’ SIZE 51200M;
 ALTER TABLESPACE TEMP_G2 TABLESPACE GROUP TEMP_G;

CREATE TEMPORARY TABLESPACE TEMP_G3 TEMPFILE ‘/u25/oradata/martd/temp_g3_01.dbf’ SIZE 51200M,
 ‘/u10/oradata/martd/temp_g3_02.dbf’ SIZE 51200M,
 ‘/u04/oradata/martd/temp_g3_03.dbf’ SIZE 51200M;
 ALTER TABLESPACE TEMP_G3 TABLESPACE GROUP TEMP_G;

CREATE TEMPORARY TABLESPACE TEMP_G4 TEMPFILE ‘/u20/oradata/martd/temp_g4_01.dbf’ SIZE 51200M,
 ‘/u15/oradata/martd/temp_g4_02.dbf’ SIZE 51200M,
 ‘/u06/oradata/martd/temp_g4_03.dbf’ SIZE 51200M;
 ALTER TABLESPACE TEMP_G4 TABLESPACE GROUP TEMP_G;

Switch over database and user to new temp tablespace group—
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_G;

Break up original process from one process with complex aggregating to two CTAS, less one process sorting:
 SQL> create table orpt_test
 tablespace tbl_data1 compress nologging parallel 4
 as
 SELECT /*+ full(c) parallel (c 4) */ <–Note Parallel here, same requested parallel as # of temp
 c.m_id,
 …
 PERCENT_RANK() OVER (ORDER BY c.orders_by_amt) pct_rnk
 FROM all_transactions c
 WHERE trans_type_cd != ‘F’;
 Table created.
 Elapsed: 02:56:46:22

Estimated time run was 16 hrs old way

SQL>CREATE INDEX orpt_tst_idx1
 on orpt_test(m_id, file_id) PARALLEL 4 nologging
 tablespace idx_data1;

Index created.
Elapsed: 01:42:15:43

Set table to unparallel and gather stats—
 ALTER TABLE orpt_test noparallel;
 exec dbms_stats.gather_table_stats( ownname=>’OWNER’,tabname=>’ORPT_TEST’, ESTIMATE_PERCENT=>.00001, METHOD_OPT=> ‘FOR ALL INDEXED COLUMNS’, GRANULARITY=>’ALL’,CASCADE=>TRUE, DEGREE=>4);

Create the secondary table that utilizes the index to ease into the hash and limit the temp usage on the amount of data it ends up having to perform in the sort if it was performed in one CTAS—

create table NEW_RPT_TBL

tablespace tbl_data2 compress nologging pctfree 0 parallel 4
 as
 SELECT /*+ parallel(o,4) */
 b_id,
 MIN(t_dt) this one,
 MAX(t_dt)that one,
 COUNT(*) ,
 SUM(o_amt) ,
 SUM(o_amt) this one,
 ….so on and so forth, more and more aggregation
 FROM
 (
 SELECT — /*+ parallel(c,4)  index(c,orpt_tst_idx1) use_hash(c cm mm m) */
 c.m_id,
 …
 FROM orpt_test c , < Here’s our new table, already much of the aggregation performed!
 m_tbl1 mm,
 crm_mtbl2 cm,
 files_form3 m
 where c.m_id = mm.m_id
 AND mm.m_cd = cm.m_cd
 AND c.file_id = m.file_id (+)
 AND c.m_id = m.m_id (+)
 ) o
 GROUP BY m_id,
 c_cd,
 m_cd,
 l_name,
 f_id,
 b_id;

Table created.

Elapsed: 02:12:32:56

A quick observations of the work area verifies how the temp sorts are spread across the temp tablespaces that encompass the temp tablespace group to eliminate waiting as we write to one tempfile at a time:

Drop “work” Table/Indexes post final table creation.
 DROP TABLE ORPT_TEST PURGE;

NEW PROCESS COMPLETE…

Was the goal met?
Largest amount of temp space used:  182GB,  (Old way: 721GB)
Time to complete process:  5hrs, 23 min., (Old way: 16 hrs)

Temp Tablespace Group Benefits to this entire tuning process:
Temp space usage without temp tablespace group, new process: 512GB, (over twice as much!)
Process completion time of new process without temp tablespace group: 11hrs, (5 hrs longer!)
Temp Tablespace Waits Eliminated with temp tablespace groups from original process:
Reads-  66%
Writes- 29%

Where are the improvements seen?
1.  I/O is distributed evenly across multiple datafiles instead of one datafile when presented with onle a single temp tablespace.

2.  Sorts in parallel performance increased, on average, over 20% for window and buffer sorts, (improvement was not seen for sorts involving rowid range scans…)
3.  Database overall improved as resources were re-allocated to all processes that may have been hindered by I/O bottleneck by heavy swapping to temp tablespace.

4.  For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.