15. Diagnosing and Recovering from TEMPFILE I/O Issues
Temporary tablespaces are often the bane of Oracle database technology: Oracle DBAs rarely think about them as a possible vector for database performance issues, but when they do cause poor performance, they tend to stick out in a crowd. This chapter briefly reviews the history of temporary tablespace features. It then discusses the benefits and drawbacks of using temporary tablespace to improve database performance. Finally, it looks at some of the symptoms of application performance when a temporary tablespace has been identified as the root cause of the performance problem.
Overview of Temporary Tablespaces
Temporary tablespaces have been available for the Oracle databases since Oracle Release 7.3. Table 15.1 lists the various features of temporary tablespaces and the database release as of which they were made available.
Read-Only Databases
A database that has been opened in read-only mode is the perfect host for a data warehouse or data mart that is blocked from accepting any data manipulation language (DML) modifications against the data segments in permanent storage. When the program global area (PGA)—where all SQL statements for a user’s session essentially do their work, whether that work involves sorting, aggregation, or hash joins—runs out of collective memory for all active user sessions, the only other place besides memory to complete that work is within a temporary segment housed in a temporary tablespace. However, if there were no alternative writeable location once all free memory in the PGA had been completely exhausted during query processing, then it would be impossible to process an application workload against a read-only database.
Locally Managed Temporary Tablespaces
Locally managed temporary tablespaces (LMTTs) were introduced in Oracle Database Release 8.1 in concert with the general release of locally managed tablespaces (LMTs). Just as with permanent tablespaces, LMTs provide a much more efficient space management mechanism for a tablespace’s underlying physical database files than the predecessor file management method called dictionary-managed tablespaces. Since an LMT is essentially self-aware in terms of from where the next file extent can be allocated, it effectively eliminated the recursive SQL (and especially the corresponding undo and redo entries) that were required to update the data dictionary tables that stored the information. Since an LMTT could need to expand dramatically any time that the PGA might be exhausted, LMTs thus dramatically increased I/O space management performance—in many cases, by one full order of magnitude (10X).
Temporary Tablespace Groups
It’s perfectly acceptable to create multiple temporary tablespaces for a single database, which allows an Oracle DBA to assign a temporary tablespace to particular application users on the basis of how those users’ sessions are expected to leverage temporary tablespaces. The use of multiple temporary tablespaces also tends to limit or even eliminate file contention between temporary segments. However, this technique may not necessarily help when an application user’s queries leverage large degrees of parallelism.
Introduced in Oracle 10g Release 1, temporary tablespace groups (TTGs) are designed to help parallel queries benefit from multiple temporary tablespaces when an application workload can benefit from employing multiple degrees of parallelism. It’s certainly possible to extend the size of a temporary tablespace by either adding more space to its TEMPFILE or adding new TEMPFILEs. However, each set of parallel server processes are assigned to a single temporary tablespace regardless of the number of TEMPFILEs allocated to that temporary tablespace.
Listings 15.1 and 15.2 show how to create a TTG and assign LMTTs to it.
Listing 15.1 Creating a TTG while Building New LMTTs
SQL> CREATE BIGFILE TEMPORARY TABLESPACE biglmtt1
TEMPFILE '+DATA'
SIZE 256M
AUTOEXTEND ON
TABLESPACE GROUP ttg_1;
Tablespace created.
Listing 15.2 Adding an Existing LMTT to an Existing TTG
SQL> CREATE BIGFILE TEMPORARY TABLESPACE biglmtt2
TEMPFILE '+DATA2'
SIZE 256M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64M;
Tablespace created.
ALTER TABLESPACE biglmtt2
TABLESPACE GROUP ttg_1;
Tablespace altered.
SQL> SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
----------- ---------------
TTG_1 BIGLMTT1
TTG_1 BIGLMTT2
Once a TTG has been successfully created, it can be assigned as the temporary tablespace for an existing Oracle database user account just as if it were a normal temporary tablespace:
ALTER USER ap TEMPORARY TABLESPACE ttg_1;
User altered.
Note
Even though a TTG usually encompasses multiple temporary tablespaces, a query still might exhaust the space required to complete the sorting, aggregation, or join activity. Once the TEMPFILEs for the TTG’s temporary tablespaces that are selected and assigned for use have exhausted all the available free space, the statement will still fail with an ORA-01652 error (“Unable to extend temp segment by < ... > in tablespace < ... >”) because the temporary segments will simply not be allowed to extend beyond the boundaries of the selected temporary tablespace’s TEMPFILEs.
Global Temporary Tables
Global temporary tables (GTTs) are an extremely useful option when an application needs to gather and retain data for an extremely brief duration. For example, it’s often necessary to gather data at the lowest level of necessary detail from an online transaction processing (OLTP) system to populate a data warehouse table with several different levels of rollup; a GTT is a perfect candidate for holding on to that data until the population is completed.
Another useful application is when each user of an application needs to gather and retain data for the duration of a transaction, but each session gathers a different set of data—perhaps for a different range of dates or a different data class—but afterwards, the data can be discarded immediately. GTTs are perfect for this situation because each user session can retain a completely isolated copy of the data without having to tokenize the data in the same table and clean it up via a DELETE operation afterwards. Fortunately, several new features available in the most recent Oracle database releases have made GTTs even more attractive. These features are discussed in the following sections.
Separate Temporary Tablespaces for GTTs
As of Oracle 11.2.0.1, a GTT can be placed into an LMTT that’s completely separate from other temporary segments needed for sorting, aggregation, or hash join processing, or even separated from other GTT types (for example, those used for extract-transform-load [ETL] processing versus those used for retaining data temporarily during query execution).
Listing 15.3 illustrates this capability with the creation of an LMTT tablespace, GTT_LMTT, that’s designed specifically for GTTs.
Listing 15.3 Creating a Separate LMTT for GTTs
SQL> CREATE TEMPORARY TABLESPACE gtt_lmtt
TEMPFILE '+DATA2'
SIZE 64M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;
Tablespace created.
Listing 15.4 shows the creation of a GTT that retains an interim set of data from the AP.INVOICES and AP.INVOICE_ITEMS tables for later insertion into the GL.REVENUES table that will reside within the GTT_LMTT tablespace.
Listing 15.4 Creating a GTT within a Separate LMTT
SQL> CREATE GLOBAL TEMPORARY TABLE ap.gtt_load_revenue(
acct_nbr NUMBER(5)
,acct_desc VARCHAR2(32)
,acct_balance NUMBER(8,2)
)
ON COMMIT PRESERVE ROWS
TABLESPACE gtt_lmtt;
Table created.
UNDO Deactivation for GTTs
Oracle Database Release 12.1.0.1 also eliminates one of the more pernicious problems with GTTs used within an LMTT: the retention of UNDO in the database’s currently assigned UNDO tablespace. Prior to this release, the creation of rows in any GTT required UNDO entries to be generated and retained whenever data was added to the table, even if the table was going to be used only to stage data for immediate loading into another permanent table.
It’s now possible to tell Oracle to shift retention of UNDO blocks into the temporary tablespace in which the GTT resides. This can be established for all GTTs by changing the setting for initialization parameter TEMP_UNDO_ENABLED to TRUE instead of its default value of FALSE. This setting can also be modified at the session level if the Oracle DBA desires to modify this behavior for only some GTTs, as shown in Listing 15.5.
Listing 15.5 Enabling Temporary UNDO Generation
SQL> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
INSERT INTO gl.gtt_load_revenue VALUES (12300, 'GL Account 12300', 123.00);
INSERT INTO gl.gtt_load_revenue VALUES (12301, 'GL Account 12301', 123.01);
INSERT INTO gl.gtt_load_revenue VALUES (12302, 'GL Account 12302', 123.02);
INSERT INTO gl.gtt_load_revenue VALUES (12303, 'GL Account 12303', 123.03);
INSERT INTO gl.gtt_load_revenue VALUES (12304, 'GL Account 12304', 123.04);
INSERT INTO gl.gtt_load_revenue VALUES (12305, 'GL Account 12305', 123.05);
INSERT INTO gl.gtt_load_revenue VALUES (12306, 'GL Account 12306', 123.06);
INSERT INTO gl.gtt_load_revenue VALUES (12307, 'GL Account 12307', 123.07);
INSERT INTO gl.gtt_load_revenue VALUES (12308, 'GL Account 12308', 123.08);
INSERT INTO gl.gtt_load_revenue VALUES (12309, 'GL Account 12309', 123.09);
. . .
<< many other transactions! >>
. . .
Listing 15.6 shows how a query against the new V$TEMPUNDOSTAT dynamic view will yield information about any temporary UNDO that is retained within the GTT_LMTT temporary tablespace.
Listing 15.6 Querying Against V$TEMPUNDOSTAT
SET LINESIZE 90
SET PAGESIZE 20000
COL begin_time FORMAT A20 HEADING "Begin Time"
COL end_time FORMAT A20 HEADING "End Time"
COL undotsn FORMAT 9999999999 HEADING "Undo|TSP #"
COL undoblkcnt FORMAT 9999999 HEADING "Undo|Blocks"
COL txncount FORMAT 9999999 HEADING "Trxn|Count"
COL maxquerylen FORMAT 9999999 HEADING "Max|Query|Length"
TTITLE "Temporary UNDO Segment Statistics|(from V$TEMPUNDOSTAT)"
SELECT
TO_CHAR(begin_time, 'mm-dd-yyyy hh24:mi:ss') begin_time
,TO_CHAR(end_time, 'mm-dd-yyyy hh24:mi:ss') end_time
,undotsn
,undoblkcnt
,txncount
,maxquerylen
FROM v$tempundostat
;
TTITLE OFF
Thu Jan 29 page 1
Temporary UNDO Segment Statistics
(from V$TEMPUNDOSTAT)
Max
Undo Undo Trxn Query
Begin Time End Time TSP # Blocks Count Length
-------------------- -------------------- ----------- -------- -------- --------
01-29-2015 19:57:00 01-29-2015 20:06:07 3 0 0 0
01-29-2015 19:47:00 01-29-2015 19:57:00 3 1 1 0
01-29-2015 19:37:00 01-29-2015 19:47:00 3 1 1 0
01-27-2015 20:57:00 01-29-2015 19:37:00 2147483647 0 0 0
Automatic Statistics Gathering for GTTs
Finally, as of Oracle Database Release 12.1.0.1, it is now possible to gather statistics automatically and individually for each session’s version of a GTT via the SET_GLOBAL_PREFS procedure of the DBMS_STATS package. The advantage of this new feature is that more accurate optimizer statistics provide potentially better execution plan generation when a GTT is joined directly to other permanent tables for which accurate statistics already exist.
It’s also possible to use the same procedure to capture statistics automatically from one representative session and then share those statistics with all other sessions that access the same GTT. This is particularly useful when the end result of populating a GTT varies only mildly between user sessions.
Listing 15.7 shows two examples of how to implement session-only and shared GTT statistics gathering, respectively.
Listing 15.7 Gathering Session-Only and Shared GTT Statistics
-----
-- Gather statistics specific to each GTT:
-----
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(
pname => 'GLOBAL_TEMP_TABLE_STATS'
,pvalue => 'SESSION'
);
END;
/
PL/SQL procedure completed successfully.
-----
-- Gather statistics and share them for all GTT iterations:
-----
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(
pname => 'GLOBAL_TEMP_TABLE_STATS'
,pvalue => 'SHARED'
);
END;
/
PL/SQL procedure completed successfully.
Correcting TEMPFILE I/O Waits
The good news is that there are only a few situations when temporary tablespaces become a problem, and in all cases, it’s only a small number of wait events and instance statistics that need to be monitored.
Undersized PGA
Since LMTTs are used only when there is insufficient space in the PGA, it’s not uncommon to see a sudden spike in the related I/O wait events for a TEMPFILE. In Listing 15.8, the PGA has been sized (via initialization parameter PGA_AGGREGATE_TARGET) to an absurdly small value of only 10 MB purely to demonstrate a corresponding spike in TEMPFILE I/O statistics and events when a query with large sort demands is issued.
Listing 15.8 Undersized PGA Resulting in TEMPFILE I/O Wait
SQL> ALTER SYSTEM SET pga_aggregate_target = 10M;
System altered.
SELECT /*+ MONITOR ReallyBadSorting */ DISTINCT
cust_id
,prod_id
,SUM(qty)
,SUM(amt)
FROM (SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 1 and 15000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 90000 and 105000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 20000 and 35000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 80000 and 95000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 30000 and 45000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 70000 and 85000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 40000 and 55000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id > 100000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 50000 and 65000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 10000 and 25000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
,prod_id
,SUM(quantity_sold) qty
,SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 60000 and 75000
GROUP BY cust_id, prod_id
)
GROUP BY ROLLUP(cust_id, prod_id)
ORDER BY
1 DESC
,2 ASC
,3 DESC
,4 ASC;
<< output from AUTOTRACE: >>
Statistics
----------------------------------------------------------
41 recursive calls
35 db block gets
352 consistent gets
4858 physical reads
0 redo size
8014678 bytes sent via SQL*Net to client
211026 bytes received via SQL*Net from client
19136 SQL*Net roundtrips to/from client
0 sorts (memory)
3 sorts (disk)
287014 rows processed
The queries and corresponding output shown in Listings 15.9, 15.10, and 15.11 respectively illustrate the I/O statistics, session-level statistics, and session-level wait events that correlate to such a severely undersized PGA.
Listing 15.9 TEMPFILE I/O Statistics
SET LINESIZE 130
SET PAGESIZE 20000
COL tablespace FORMAT A12 HEADING "Tablespace"
COL file# FORMAT 9999 HEADING "TEMP|File|#"
COL phyrds FORMAT 9999999 HEADING "Phys|Reads"
COL phywrts FORMAT 9999999 HEADING "Phys|Writes"
COL phyblkrd FORMAT 9999999 HEADING "Phys|Blocks|Read"
COL phyblkwrt FORAMAT 9999999 HEADING "Phys|Blocks|Written"
COL sbrs FORMAT 9999999 HEADING "Single|Block|Reads"
COL readtim FORMAT 9999999 HEADING "Read|Time"
COL writetim FORMAT 9999999 HEADING "Write|Time"
COL sbrtm FORMAT 99999 HEADING "Single|Block|Read|Time"
COL avgiotim FORMAT 99999 HEADING "Avg|I/O|Time"
COL lstiotim FORMAT 99999 HEADING "Last|I/O|Time"
COL miniotim FORMAT 99999 HEADING "Min|I/O|Time"
COL maxiortm FORMAT 99999 HEADING "Max|I/O|Read|Time"
COL maxiowtm FORMAT 99999 HEADING "Max|I/O|Write|Time"
TTITLE "TEMPFILE I/O Statistics"
SELECT
TSP.name tablespace
,TS.file#
,phyrds
,phywrts
,phyblkrd
,phyblkwrt
,singleblkrds sbrs
,readtim
,writetim
,singleblkrdtim sbrtm
,avgiotim
,lstiotim
,miniotim
,maxiortm
,maxiowtm
FROM v$tempstat TS, v$tempfile TF, v$tablespace TSP
WHERE TS.file# = TF.file#
AND TF.ts# = TSP.ts#
ORDER BY TS.file#
;
TTITLE OFF
TEMPFILE I/O Statistics
Single Max Max
TEMP Phys Phys Single Block Avg Last Min I/O I/O
File Phys Phys Blocks Blocks Block Read Write Read I/O I/O I/O Read Write
Tablespace # Reads Writes Read Written Reads Time Time Time Time Time Time Time Time
------------ ----- -------- -------- -------- -------- -------- ----- ----- ------ ------ ------ ------ ------ ------
TEMP 1 839 852 6340 6692 57 628 3171 38 3 1 0 20 289
GTT_LMTT 2 2 6 2 6 2 3 4 3 2 0 0 2 2
BIGLMTT1 3 0 0 0 0 0 0 0 0 7 7 0 0 0
BIGLMTT2 4 0 0 0 0 0 0 0 0 7 7 0 0 0
Listing 15.10 TEMPFILE I/O Session-Level Statistics
SET LINESIZE 60
SET PAGESIZE 20000
COL name FORMAT A45 HEADING "Statistic"
COL value FORMAT 99999999 HEADING "Value"
TTITLE "Temporary Tablespace - Current Session Instance Statistics"
SELECT SN.name, SS.value
FROM .
v$mystat SS
,v$statname SN
WHERE SS.statistic# = SN.statistic#
AND (SN.name LIKE '%physical reads direct temporary%'
OR SN.name LIKE '%physical writes direct temporary%')
;
TTITLE OFF
Temporary Tablespace - Current Session Instance Statistics
Statistic Value
--------------------------------------------- ---------
physical reads direct temporary tablespace 5060
physical writes direct temporary tablespace 5648
Listing 15.11 TEMPFILE I/O Session-Level Wait Events
SET LINESIZE 80
SET PAGESIZE 20000
COL event FORMAT A40 HEADING "System Wait Event"
COL total_waits HEADING "Total|Waits"
COL wait_secs HEADING "Total|Wait|Time|(s)"
COL avg_wait_secs HEADING "Avg|Wait|Time|(s)"
TTITLE "Temporary Tablespace - Current Session Wait Events"
SELECT
DISTINCT event
,total_waits
,(time_waited / 100) wait_secs
,(average_wait / 100) avg_wait_secs
FROM
v$session_event E
,v$mystat S
WHERE event LIKE '%temp%'
AND E.sid = S.sid
;
TTITLE OFF
Temporary Tablespace - Current Session Wait Events
Total Avg
Wait Wait
Total Time Time
System Wait Event Waits (s) (s)
---------------------------------------- ---------- ---------- ----------
direct path write temp 1 0 .0031
direct path read temp 278 1.79 .0064
Inappropriate TEMPFILE Extent Sizing
Oracle recommends the following best practices to avoid inappropriately sizing temporary tablespaces:
If the application workload is primarily a decision support system (DSS) that’s dominated by complex joins—especially hash joins between multiple row sources—as well as intensive sorts and aggregations, data will be written to temporary segments in 64 KB multiples. It’s therefore best to configure the corresponding temporary tablespace with an extent size that is a multiple of 64 KB; usually, an extent size of 1 MB is more than sufficient in this case.
Conversely, if a large number of temporary large objects (LOBs) are going to be used—usually present because the database is processing transient, semistructured, or unstructured data such as XML or binary large objects (BLOBs)—a larger extent size is recommended, no smaller than 1 MB but no larger than 10 MB.
If these two simple rules of thumb for sizing LMTT extents are followed, then unexpectedly high wait events related to TEMPFILE I/O should rarely be encountered.
Inappropriate Use of GTTs
Finally, be wary of applications that use GTTs inappropriately, especially when the application has been migrated from other RDBMSs such as Sybase or SQL Server. These databases offer the capability to use a scratch area to create structures that appear to be very much like GTTs because they can be populated via SQL, but in reality they are more analogous to a PL/SQL associative array.
In these situations, it often makes sense to encourage application developers to rewrite their application code to leverage appropriate PL/SQL in-memory structures such as VARRAYs or PIPELINEd table functions instead of GTTs. This strategy entirely avoids the unnecessary generation of UNDO as well as the overhead of the physical I/O required to write data to temporary segments.
My Oracle Support Reference Documents
MOS Note 1576956.1, “How to address high wait times for the ‘direct path’ write temp wait event,” contains valuable primary and ancillary information for additional reading and strengthening understanding of the myriad topics discussed in this chapter.
Summary
This chapter showed that without temporary tablespaces, it would be impossible to bring a database into read-only mode. Temporary tablespaces also make it possible to store large amounts of transient data in global temporary tables on a per-session basis. When a database instance exhausts its PGA memory and needs to complete a sort, aggregation, or hash join operation, temporary tablespaces can provide the necessary temporary repository for that information.
The most common I/O issues arise when a temporary tablespace is undersized, uses extents sized inappropriately for the temporary segments of the objects stored within, or is overloaded because of the misuse of GTTs.