New Temp Tablespace Group

I know I owe a few folks a blog on temp tablespace groups-  THANK YOU for your patience, I know these last couple months I’ve been way too busy and haven’t been able to allocate the time I would have liked here…

Temp tablespace groups ROCK–  plain and simple..  This is a feature I have been working with extensively and have seen great performance improvement with in my datawarehouse environments when implemented correctly.  My main focus has been striping temp tablespace groups across fusion I/O cards, but the improvement moving from a standard temp tablespace to a temp tablespace group on standard hardware shows significant improvement as well for systems with high read and write waits on temp.

For anyone who has huge temp waits for read and writes, it’s painful to watch, especially if you have the ability to view it through OEM. The bands of blue and yellow wait lines that show up in I/O waits or in “OTHER” can be frustrating.

If you run an ASH Report, you will see waits, similar to what I experienced for the process that I will use for my example further into this post:

There are three things to remember when planning to move to a temp tablespace group:
1.  Plan your temp tablespace group carefully-  the tablespaces should be of the same size, have the same amount of files with the tempfiles each the same size.
2.  Inconsistent sizes across the tablespaces can affect the efficiency of the striping-  no auto-extend..
3.  The temp work will stripe across the tablespaces quite close to evenly, not use one files in one tablespace at a time, so keep this in mind when looking at temp tablespace usage.
4.  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.
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.  Do not use temp tablespace groups as a substitute for good ol’ fashion 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.

The testing process below is based a process that is the largest consumer of temp tablespace in one of our databases.  The process utilizes 720GB of temp tablespace, had an execution time of 16 hrs and my goal was to do the following:

• 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.

Create Temp Tablespace Group, three temp tablespaces total, 200GB each—-
CREATE TEMPORARY TABLESPACE TEMP_STDRD1 TEMPFILE ‘/u00/oradata/sid1/temp_stdrd1_01.dbf’ SIZE 51200M,
 
‘/u10/oradata/sid1/temp_stdrd1_02.dbf’ SIZE 51200M,
‘/u20/oradata/sid1/temp_stdrd1_03.dbf’ SIZE 51200M,
‘/u30/oradata/sid1/temp_stdrd1_04.dbf’ SIZE 51200M
TABLESPACE GROUP STDRD_TEMP;

CREATE TEMPORARY TABLESPACE TEMP_STDRD2 TEMPFILE ‘/u30/oradata/sid1/temp_stdrd2_01.dbf’ SIZE 51200M,
‘/u20/oradata/sid1/temp_stdrd2_02.dbf’ SIZE 51200M,
‘/u10/oradata/sid1/temp_stdrd2_03.dbf’ SIZE 51200M,
‘/u00/oradata/sid1/temp_stdrd2_04.dbf’ SIZE 51200M;
ALTER TABLESPACE TEMP_STDRD2 TABLESPACE GROUP STDRD_TEMP;


CREATE TEMPORARY TABLESPACE TEMP_STDRD3 TEMPFILE ‘/u30/oradata/martd/temp_stdrd3_01.dbf’ SIZE 51200M,
‘/u10/oradata/martd/temp_stdrd3_02.dbf’ SIZE 51200M,
‘/u00/oradata/martd/temp_stdrd3_03.dbf’ SIZE 51200M,
‘/u20/oradata/martd/temp_stdrd3_04.dbf’ SIZE 51200M;
ALTER TABLESPACE TEMP_STDRD3 TABLESPACE GROUP STDRD_TEMP;

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

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 8 as 
SELECT /*+ full(c) parallel (c 8) */ <--Note Parallel here, all orders for all time! 
c.member_id, ... PERCENT_RANK() 
OVER (ORDER BY c.orders_by_amt) pct_rnk 
FROM all_transactions c WHERE trans_type_cd != 'F'; 

Table created. 
Elapsed: 03:42:41.38

Estimated time run was 16 hrs old way

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

Index created.
Elapsed: 01:12:49.62

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=>6);

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 8
 as
 SELECT /*+ parallel(o,2) */
 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, hard to believe there could be so much... :)
 FROM
 (
 SELECT -- /*+ parallel(c,2)  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:36:21.12

Drop “work” Table/Indexes post final table creation.

DROP TABLE ORPT_TEST PURGE;

NEW PROCESS COMPLETE…

Did I meet my goal?
Largest amount of temp space used:  178GB,  (Old way: 721GB)
Time to complete process:  7hrs, 31 min., (Old way: 16 hrs)

Temp Tablespace Group Benefits to this entire tuning process:
Temp space usage without temp tablespace group, new process: 423GB, (over twice as much!)
Process completion time of new process without temp tablespace group: 12hrs, (4 1/2 hrs longer!)
Temp Tablespace Waits Eliminated with temp tablespace groups from original process:
Reads-  62%
Writes- 27%

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.