Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted November 20, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Monitoring Oracle's Temp Usage

By David Fitzjarrell

It appears that Oracle's temporary tablespace is still a mystery to some, possibly because it's not managed in the same way as a conventional tablespace. Extents are allocated and re-used so space management looks nothing like that for a traditional tablespace. Because of that space errors are managed differently. Let's look at how to manage a temporary tablespace and what tools are at the DBA's disposal.

Starting with version 8.1.5 Oracle has provided a 'true' temporary tablespace where extents are allocated and assigned to sessions as they are needed. The extents are never dropped while the database is running so a properly utilized temporary tablespace will always have 0 'free' space. In this sense 'free' means unallocated, and everything in the temporary tablespace will be allocated but that doesn't mean it's being used. This is why conventional space management has no place in the temporary tablespace.

So how IS the temporary tablespace managed? It's done through 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)

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

The most helpful views to manage temporary tablespaces are V$TEMP_EXTENT_POOL and V$TEMPSEG_USAGE/V$SORT_USAGE. V$TEMP_EXTENT_POOL lists not only the allocated extents, it also reports on all used extents in a temporary tablespace by tempfile:


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

V$TEMPSEG_USAGE (or, if you prefer, V$SORT_USAGE) shows the temporary segment usage, by user, for all tablespaces:


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 

[In 10.2.0.4 and later releases V$SORT_USAGE and V$TEMPSEG_USAGE have the same definition, with V$SORT_USAGE provided simply for backward compatibility.]

Temporary tablespace usage monitoring is a simple task of querying V$TEMPSEG_USAGE (V$SORT_USAGE, if that is preferred) over time:



select segtype, extents, blocks
from v$tempseg_usage
where tablespace  = 'TEMP';

Remember that such monitoring will produce no actionable data; unless the database is reporting errors allocating temp space the DBA really has nothing to do.

If you'd like to know how many extents are allocated and actually used per datafile the following query will produce a fairly useful 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
/

Again, no action needs to be taken based on such a report; it's merely for informational purposes.

Who's using your temp space, what queries are they executing and how much of that space is each one consuming? That's also a fairly easy task to complete:


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;

The V$TEMP_SPACE_HEADER view provides a less granular view of the consumed and available space; it lists the allocated and non-allocated space in the tempfiles, by file. Keep in mind that allocated does not mean used; there will likely be large differences between what V$TEMP_SPACE_HEADER reports and what V$TEMPSEG_USAGE provides.

I will quote myself (from somewhere else on the web) with regard to proper sizing of temporary tablespaces: "So how much space do you need in your temporary tablespace? That would depend upon how active your system is, how many concurrent active sessions there are, the size of the transactions and how much disk space you have. It isn't a disgrace to increase your TEMP tablespace size over time as usage patterns, number of users and data volumes change. Oracle will also inform you that the temporary tablespace needs to be increased by issuing ORA-01652 errors (unable to extend temp segment by 128 in tablespace TEMP, for example). [ORA-01652 errors can occur for ANY tablespace in the database, and the affected resource will be listed in the error text: "ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM" indicates that the SYSTEM tablespace is needing to be increased. Again, ANY tablespace in the database can be listed in an ORA-01652, not just the TEMP tablespace. And the transaction which threw the error was rolled back because of it, thereby freeing the space it had already consumed when the error condition was encountered. An interesting side note: simply because an ORA-01652 error is displayed for a non-temporary tablespace doesn't mean that there are temporary objects created there as when tables/indexes are created or extended the extents allocated are listed as temporary until the DDL completes at which time the created extents are given their 'permanent' object name. When regular tablespaces throw this error then there is insufficient space to extend tables and/or indexes and inserts/updates will fail. So, when an ORA-01652 error appears look carefully at the tablespace listed in the error text as it may not be a temporary tablespace issue.] The number reported in an ORA-01652 error is in blocks, not bytes, so you'll need to convert that using the db_block_size value to know how many bytes the temporary tablespace needed to complete the transaction generating the error. There is no 'rule of thumb' to size a temporary tablespace because such rules usually create situations where the only tool becomes a hammer and every task ends up as a nail, and more often than not, you hit that thumb with the only 'tool' you've been given."

DBA_TEMP_FILES will tell you how large the temporary tablespace is:


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>

It can also report which files are associated with your temporary tablespace:


SQL> select tablespace_name, file_name, bytes
  2  from dba_temp_files
  3  order by tablespace_name, file_name
  4  /

TABLESPACE_NAME FILE_NAME                                                    BYTES
--------------- ------------------------------------------------------- ----------
TEMP            /u2/orawiz/parlopnett/temp01.dbf                        1073741824

SQL>

If you want, or need, to decrease the size of your TEMP tablespace you need to shut the database down, open it in restricted mode, resize the tempfile (or tempfiles) smaller then shutdown and startup the database. Adding space isn't nearly as involved, as a simple 'alter database tempfile resize ;' increases the space.

What happens if the temporary tablespace doesn't exist or isn't available? Oracle will display this:


SQL> select * from mybigtable order by mycoolkey;
select * from mybigtable order by mycoolkey
*
ERROR at line 1:
ORA-01129: user's default or temporary tablespace does not exist 


SQL>

If you see this error message check to see if the temporary tablespace exists and is online. Correct any issues you find (create the missing tablespace or put the tempfiles back online).

If the tablespace exists but no tempfiles are assigned to it (which can occur after a database restore/recover from a backup) then Oracle reports:


SQL> select * from mybigtable order by mycoolkey;
select * from mybigtable order by mycoolkey
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty 

SQL>

So, the task is simply to issue 'alter temporary tablespace ... add tempfile ...' commands to make the tablespace no longer empty.

Managing the temporary tablespace isn't rocket science, but it does differ (sometimes considerably) from the 'normal' tablespace management procedures employed for regular data files. Knowing that a 'full' temporary tablespace is not an issue (remember that segments are allocated then reused) can make the task a bit easier as Oracle will tell you (with ORA-01652 errors) if you've run out of space. Which means the DBA can spend time on useful tasks, like password resets.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM