DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

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:

 

Event

% Event

P1 Value, P2 Value, P3
Value

% Activity

Parameter 1

Parameter 2

Parameter 3

direct path read temp

43.27

“1001″,”3502″,”7″

0.01

file number first dba block cnt
PX Deq Credit: send blkd

26.18

“268501001″,”2″,”0″

1.50

sleeptime/senderid passes qref

“268501001″,”1″,”0″

1.18

“268501003″,”2″,”0″

1.06

direct path read

5.10

“50″,”11333″,”11″

0.01

file number first dba block cnt
direct path write temp

1.47

“1003″,”50949″,”7″

0.01

file number first dba block cnt

 

SQL ID

Planhash

% Activity

Event

% Event

SQL Text

dhytpraqah5c5

2880106315

100.00

direct path read temp

43.27

create table report_tbl1…
PX Deq Credit: send blkd

26.18

 

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:

SQL_TEXT SID TABLESPACE OPERATION_TYPE Temp MB
create table orpt_test tbl_data1

803

TEMP_G1 WINDOW (SORT)

10229

create table orpt_test tbl_data1

835

TEMP_G4 WINDOW (SORT)

13872

create table orpt_test tbl_data1

836

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

847

TEMP_G1 WINDOW (SORT)

12974

create table orpt_test tbl_data1

803

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

847

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

835

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

827

TEMP_G3 WINDOW (SORT)

11408

create table orpt_test tbl_data1

837

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

837

TEMP_G4 WINDOW (SORT)

11535

create table orpt_test tbl_data1

827

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

828

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

828

TEMP_G3 WINDOW (SORT)

15940

create table orpt_test tbl_data1

853

LOAD WRITE BUFFERS
create table orpt_test tbl_data1

836

TEMP_G2 WINDOW (SORT)

10807

create table orpt_test tbl_data1

853

TEMP_G2 WINDOW (SORT)

18028

 

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.

 

Print Friendly

facebook comments:

7 thoughts on “Warp Speed with Temp Tablespace Groups
  • [...] DBA Kevlar » Blog Archive » Warp Speed with Temp Tablespace … 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 …. 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 … http://dbakevlar.com/2011/08/w .. [...]

  • Uwe Hesse says:

    Kellyn,
    thank you for the effort you took to explain the benefits of Temporary Tablespace Groups! That seems indeed to be one of the less known & advertised 10g New Features, deserving some more attention.
    When I taught about it, I used to mention the particular advantage it brings for Parallel Operations, but couldn’t come up with such a nice Real-World example like you did – got no multi-TB Database at hand.
    From now on, I will be able to point to your posting whenever the topic raises again :-)

  • [...] Kellyn Pot’Vin has written an excellent blog post on getting the most out of temporary tablespace groups; it provides excellent information for those wanting to use temporary tablespace groups and those already doing so. [...]

  • Mark Burgess says:

    Kellyn,

    This is very good – do you know if Oracle has the same behaviour on 11.2.0.3?

    Mark

  • dbakevlar says:

    Depends on if you are on ASM or not. ASM is the big change factor on temp write usage. I have another post where I verified that simultaneous write on multiple tempfiles is possible with one tablespace via ASM.

  • On the flip side, there are couple of serious bugs reported when TTS feature in action:

    High LIO (Logical I/O) And CPU Consumption On TS$ When Using A “tablespace group” As TEMP TS (Doc ID 1469347.1)
    Bug 8223165 ORA-600 [ktsxtffs2] During Startup When Using Temporary Tablespace Group

  • dbakevlar says:

    I think we can all agree, until Oracle spends more time facing the performance challenges faced when attempting to aggregate data on the fly, DBAs and Developers are left with few solutions to address those processes that in no way will fit within PGA allocation. Temp is disk, disk is slow. Splitting it by parallel and across multiple temp tablespaces assist in speeding it up, pushing temp to SSD, speeds it up more, but face it, we need TEMP in memory or we need another solution for this challenge that I run into way too often.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  • Facebook
  • Google+
  • LinkedIn
  • Twitter