Since version 8.1.5, Oracle has provided what can be considered as a true temporary tablespace, utilizing sparse files (or, in Oracle-speak, tempfiles). These files are essentially ‘anchors’, at the operating system level, consuming a file name and an inode (on *nix-type operating systems) and information on how large they can grow. By now this should be common knowledge but sometimes what may be thought of as ‘common’ may not be, thus the explanation. Having that out of the way let’s go on and discuss how best to monitor and manage the ‘true’ temporary tablespace.
Temporary tablespace management begins with several V$ views:
V$TEMPFILE V$TEMPSTAT V$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL V$TEMP_SPACE_HEADER V$TEMPSEG_USAGE (Oracle 9i and later releases) V$SORT_USAGE (Oracle 8.1.7, 8.1.6 and 8.1.5)
Let’s look at each and see what information it provides.
V$TEMP_EXTENT_MAP reports all of the allocated extents in the temporary tablespace:
SQL> desc v$temp_extent_map
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
OWNER NUMBER
RELATIVE_FNO NUMBER
Notice there is no ‘ownership’ information here, just a catalog of the TEMPORARY extents allocated in each TEMPORARY tablespace. To see who ‘owns’ the segments one needs to query another view, V$TEMPSEG_USAGE (or V$SORT_USAGE, an older view that’s been redefined to match V$TEMPSEG_USAGE since versions older than 9i use that name to manage sort (read temp) segments:
SQL> desc v$tempseg_usage
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(8)
SESSION_NUM NUMBER
SQLADDR RAW(8)
SQLHASH NUMBER
SQL_ID VARCHAR2(13)
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER
The V$TEMP_EXTENT_POOL view reports on the extent usage as aggregate values by tablespace:
SQL> desc v$temp_extent_pool
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
FILE_ID NUMBER
EXTENTS_CACHED NUMBER
EXTENTS_USED NUMBER
BLOCKS_CACHED NUMBER
BLOCKS_USED NUMBER
BYTES_CACHED NUMBER
BYTES_USED NUMBER
RELATIVE_FNO NUMBER
The simple task of querying V$TEMPSEG_USAGE or V$SORT_USAGE over time is all it should require to monitor the temporary tablespace usage:
--
-- 9i and later
--
select segtype, extents, blocks
from v$tempseg_usage
where tablespace = ;
--
-- 8.1.5, 8.1.6, 8.1.7
--
select segtype, extents, blocks
from v$sort_usage
where tablespace = ;
No rows returned means no attention is needed by the DBA; that may be confusing as such a result can fly in the face of the results from V$TEMP_EXTENT_POOL but remember that V$TEMP_EXTENT_POOL reports only allocated extents and simply allocating extents in a temporary tablespace does not indicate a space problem exists. Even if the above query returns rows (and in an active database it most likely will) unless you are seeing allocation errors you need not worry about space.
The query below will produce a fairly useful allocation/usage report:
select tablespace_name,
file_id,
extents_cached extents_allocated,
extents_used,
bytes_cached/1024/1024 mb_allocated,
bytes_used/1024/1024 mb_used
from v$temp_extent_pool
/
Want a more detailed report? The following query returns the user name, the ‘offending’ query, the extents and blocks of temporary space consumed:
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
If you’re still using 9iR2 and/or 9iR1 that use true temporary tablespaces (and if you are, that begs the question ‘Why?’) the following modified query returns that same information:
select u.username, s.sql_text, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;
V$TEMP_SPACE_HEADER provides a less-detailed view of the available space; it lists allocated and non-allocated space in the tempfiles, by file. It’s important to remember that in a true temporary tablespace allocated extents are not necessarily used extents; a temporary tablespace that is 100% allocated is NOT a problem since extents are re-used by sessions as they are needed.
A commonly asked question is “How much space do I need in my temporary tablespace?” That, of course, would depend on how ‘active’ your system is, how many concurrent and active sessions there are, the sizes of those transactions and how much disk space you have available. It’s not unusual to increase the temporary tablespace size over time as the number of active sessions can increase, the size of the transactions can grow larger and query durations can get longer. Seeing ORA-01652 errors (unable to extend temp segment by 128 in tablespace TEMP, for example) will alert you to the need for more space; those can also alert you to the need to tune queries that are using considerable amounts of temporary space as the plans may not be optimal for the volume of data returned. The number reported by an ORA-01652 error is in blocks; converting that value is easy using the db_block_size value so you know how many bytes the temporary tablespace is ‘short’. Since there is no ‘rule of thumb’ to size a temporary tablespace each situation requires individual attention to provide sufficient space for the particular database being managed.
The current size of your temporary tablespace can be found by querying the DBA_TEMP_FILES view:
SQL> select tablespace_name, sum(bytes)/1024/1024 MB
2 from dba_temp_files
3 group by tablespace_name
4 /
TABLESPACE_NAME MB
------------------------------ ----------
TEMP 1024
SQL>
If, because of file corruption, you need to drop and recreate your TEMP tablespace you cannot perform that act when there are connected users so you’ll need to do the following:
Create a new temporary tablespace, make that the database default, then drop the original temporary tablespace.
If you’re attached to the old temporary tablespace name you can recreate it (since the default temporary tablespace has another name) and go through the process shown above to make IT the default temporary tablespace. Obviously, you’ll then need to drop the ‘interim’ temporary tablespace.
When tuning queries the V$SQL_PLAN view can provide information on estimated temporary space usage provided you have the SQL_ID of the query:
select temp_space from v$sql_plan where sql_id = '';
Temporary space usage can involve any number of areas for you to monitor: execution plans generated by DBMS_XPLAN.DISPLAY_CURSOR() are usually the best place to start as they can provide statistics that EXPLAIN PLAN and tkprof can’t (like estimated temporary space usage):
Plan hash value: 1887592732
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 2745 (100)| |
|* 1 | HASH JOIN ANTI | | 237 | 469K| | 2745 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | LF_HOTEL_TEMP | 237 | 466K| | 176 (4)| 00:00:01 |
| 3 | VIEW | | 478K| 5607K| | 2565 (4)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | HASH GROUP BY | | 478K| 5607K| | 2565 (4)| 00:00:01 |
| 6 | VIEW | | 478K| 5607K| | 2526 (2)| 00:00:01 |
| 7 | UNION-ALL | | | | | | |
| 8 | HASH UNIQUE | | 215K| 2525K| 4240K| 1136 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| LF_TS_ROOMTYPE_PROPERTIES | 215K| 2525K| | 140 (3)| 00:00:01 |
| 10 | HASH UNIQUE | | 125K| 1467K| 2464K| 683 (2)| 00:00:01 |
|* 11 | TABLE ACCESS FULL| LF_GTA_ROOMTYPE_PROPERTIES | 125K| 1467K| | 104 (2)| 00:00:01 |
| 12 | HASH UNIQUE | | 137K| 1614K| 2712K| 707 (2)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| LF_HB_ROOMTYPE_PROPERTIES | 137K| 1614K| | 70 (3)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."HOTEL_CODE"="B"."HOTEL_CODE")
2 - filter("A"."SERVICE_ID"=:P_SERVICE_ID)
4 - filter(COUNT(*)=3)
9 - filter("HOTEL_CODE" IS NOT NULL)
11 - filter("HOTEL_CODE" IS NOT NULL)
13 - filter("HOTEL_CODE" IS NOT NULL)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Armed with this information you can investigate why that amount of temp space is required and possibly reduce temp space usage by, say, adding an index to reduce the overall volume of data processed. Not all temporary space management involves modifying the temporary tablespace.
Hopefully this prepares you for examining, and possibly attacking, temporary tablespace problems when they exist and gives you the insight to know when a perceived ‘issue’ is not really an issue at all.
