Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 26 March 2013

Identifying Statements that Fail Due to Lack of Temporary Space

When a sort operation is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary space is a resource shared by multiple sessions on the database, and quotas cannot be set to limit how much temporary space can be used by an individual user or session. If a sort operation runs out of space, the statement initiating the sort will fail with error: ORA-01652: unable to extend temp segment by 128 in tablespace temp. 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.
Note: It is important to note that not all ORA-1652 errors indicate temporary tablespace issues. For example, moving a table to a different tablespace with the ALTER TABLE…MOVE statement will cause an ORA-1652 error if the target tablespace does not have enough space for the table.
Instead of waiting for a temporary tablespace to fill and for statements to fail, you can monitor temporary space usage in the database in real time. The following query displays information about all sort segments in the database.
--Monitoring Temporary Space Usage while statement is running
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, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM v$tablespace B, v$tempfile C
         WHERE B.ts#= C.ts#
         GROUP BY, C.block_size
         ) D
WHERE    A.tablespace_name =
GROUP by A.tablespace_name, D.mb_total;
-------------  ---------- --------   ----------
TEMP           15000      143        14857
The above output indicating the total of 15000 MB temporary space in which 143 MBis currently in use and 14857 is free. From the use of below query you can find the description of each database session that is using space in sort segment. Although one session may have many sort operation active at once.
--Sort Space Usage by Session
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;
---------- --------   ------- ---- ------ --------- ------- ---------- --------
45,13948   it_shahid  s_ahmed 2065 orafin orafin@db1   143    TEMP          2
This example shows that there is one database session 45 with a serial number 13948 using sort segment space. The connection was initiated by the orafin@db1 process running under the s_ahmed OS user, and the Oracle server process has operating system process ID 2065. The session has two active sort operations that are using a total of 143 MB of sort segment space in the TEMP tablespace.
The below 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.
--Sort Space Usage by Statement 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; SID_SERIAL USERNAME MB_USED TABLESPACE ADDRESS HASH_VALUE SQL_TEXT ----- -------- ------- ---------- -------- ---------- --------------------------- 45,13948 IT_SHAHID 64 TEMP 000000038865B058 3641290170 select sys_desc from gen_system where sys_maj = 700 and sys_min = :b1 45,13948 IT_SHAHID 79 TEMP 00000003839FFE20 1874671316 select option_text,program_type from tree_menu where application_id = 'acc' and program_type != 1 and command_line = :b1 and rownum <= 1
This example shows that session 45 with serial number 13948, connected to the database as the IT_SHAHID user, has two statements currently using sort segment space in the TEMP tablespace. One statement is currently using 64 Mb of sort segment space, while the other is using 73 Mb. The text of each statement, along with its hash value and address in the shared SQL area are also displayed.


Post a Comment