How Can Temporary Segment Usage Be Monitored Over Time ?

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2 and above

Goal:

Suggest a method by which temporary segment usage be monitored over time

Such a process would aid greatly in investigating

ORA-1652 "unable to extend temp segment by %s in tablespace %s"

Solution:
EXAMPLE of how to monitor Temporary Segment Usage over time
1) Login as SYS AS SYSDBA
NOTE: If SYS is unacceptable then use a schema that has access of each of the DBA views below
2) Create a table to hold your temporary space monitoring
EXAMPLE:

CREATE TABLE TEMP_TEMP_SEG_USAGE(
DATE_TIME DATE,
USERNAME VARCHAR2(30),
SID VARCHAR2(6),
SERIAL# VARCHAR2(6),
OS_USER VARCHAR2(30),
SPACE_USED NUMBER,
SQL_TEXT VARCHAR2(1000));

3) Create a job in the job queue to insert into your monitoring table

EXAMPLE:

CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into TEMP_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*8192 > 1024;
COMMIT;
END;
/

SQL> SELECT JOB FROM DBA_JOBS;

JOB
----------
19

BEGIN
DBMS_JOB.ISUBMIT(JOB => 20,
WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

4) Periodically query your monitoring table _TEMP_SEG_USAGE. Also monitor space usage of the table as it could grow very fast depending on job interval;
delete rows or truncate table as appropriate.

EXAMPLE:

SQL> select * from temp_temp_seg_usage;

DATE_TIME USERNAME SID SERIAL
--------- --------------------- ----- ----
OS_USER SPACE_USED
------------------------------ ----------
SQL_TEXT
-----------------------------------------
29-JUN-07 SYS 158 13
sygaw-ca\sygaw 768
select * from dba_objects order by object_id, object_name


SQL> select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb  from dba_segments where segment_name='TEMP_TEMP_SEG_USAGE';

SEGMENT_NAME
------------------------------------------
TABLESPACE_NAME USEDMB
------------------------------ ----------
TEMP_TEMP_SEG_USAGE
SYSTEM .0625

SQL> truncate table temp_temp_seg_usage;
Table truncated.

No comments:

Post a Comment