All About Temporary Tablespace Part IV

Monitoring Temporary Space Usage :

We can monitor temporary space usage in the database in real time. At any given time, Oracle can tell us about all of the database’s temporary tablespaces, sort space usage on a session basis, and sort space usage on a statement basis. All of this information is available from v$ views, and the queries shown in this section can be run by any database user with DBA privileges.

Temporary Segments :

The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

SQL> SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D 
WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; 

The query displays for each sort segment in the database the tablespace the segment resides in, the size of the tablespace, the amount of space within the sort segment that is currently in use, and the amount of space available. Sample output from this query is as follows:

TABLESPACE     MB_TOTAL     MB_USED       MB_FREE
-----------                ------------          ------------         ---------
TEMP                     10000                   9                    9991

This example shows that there is one sort segment in a 10,000 Mb tablespace called TEMP. Right now, 9 Mb of the sort segment is in use, leaving a total of 9,991 Mb available for additional sort operations. (Note that the available space may consist of unused blocks within the sort segment, unallocated extents in the TEMP tablespace, or a combination of the two.)

Sort Space Usage by Session :

The following query displays information about each database session that is using space in a sort segment. Although one session may have many sort operations active at once, this query summarizes the information by session. 

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks)* TBS.block_size/1024/1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr  AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace  ORDER BY sid_serial;

The query displays information about each database session that is using space in a sort segment, along with the amount of sort space and the temporary tablespace being used, and the number of sort operations in that session that are using sort space.Sample output from this query is as follows: 

SID_SERIAL  USERNAME OSUSER SPID MODULE PROGRAM   MB_USED TABLESPACE SORT_OPS
----------   -------- ------ ---- ------ --------- ------- ---------- --------
33,16998    RPK_APP    rpk  3061   inv   httpd@db1    9       TEMP       2

This example shows that there is one database session using sort segment space. Session 33 with serial number 16998 is connected to the database as the RPK_APP user. The connection was initiated by the httpd@db1 process running under the rpk operating system user, and the Oracle server process has operating system process ID 3061. The application has identified itself to the database as module “inv.” The session has two active sort operations that are using a total of 9 Mb of sort segment space in the TEMP tablespace.

Sort Space Usage by Statement :
The following query displays information about each statement that is using space in a sort segment.

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid ;

The query displays information about each statement using space in a sort segment,including information about the database session that issued the statement and the temporary tablespace and amount of sort space being used. 

Conclusion : 

When an operation such as a sort, hash, or global temporary table instantiation is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary tablespaces are a shared resource in the database, and we can’t set quotas to limit temporary space used by one session or database user. If a sort operation runs out of space, the statement initiating the sort will fail. It may only take one query missing part of its WHERE clause to fill an entire temporary tablespace and cause many users to encounter failure because the temporary tablespace is full. It is easy to detect when failures have occurred in the database due to a lack of temporary space. With the setting of a simple diagnostic event, it is also easy to see the exact text of each statement that fails for this reason. There are also v$ views that DBAs can query at any time to monitor temporary tablespace usage in real time. These views make it possible to identify usage at the database, session, and even statement level. Oracle DBAs can use the techniques outlined in this paper to diagnose temporary tablespace problems and monitor sorting activity in a proactive way.

   

(Ref : Roger Schrag Database Specialists )

Enjoy    :-)