Monitor Temporary Tablespace

To monitor the temporary Segment Usage

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;

To monitor the temporary storage usage per session

SELECT S.sid , S.serial#, S.username, S.osuser, P.spid,
S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) statements
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,
P.program, TBS.block_size, T.tablespace
ORDER BY sid, serial#;

To monitor the Temporary storage usage per statement

SELECT S.sid , S.serial#, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddrAND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Thanks

Switching Undo Tablepace in RAC

Switching undo tablespace is similar to single instance. If you want to switch the tablespace for instance 1, then issue the following statement from that instance

SQL> Alter system set undo_tablespace = undotbs10 scope = both;

At the time of the switch, the instances maintain control of both the old and new undo tablespaces.The old undo tablespace is marked as pending-offline until all the transactions using it are completed.

Thanks

Undo Tablespace in RAC

In RAC there must be one UNDO tablepsace for each instance. The entry in pfile must be (for 2 node instance prod1 and prod2)


prod1.undo_tablespace=UNDOTBS1

prod2.undo_tablespace=UNDOTBS2

• Each instance can use only one undo tablespace at a time (store undo information for that instance only).


• All instances can read block from any or all undo tablespaces for the purpose of Read consistency.


• Undo management mode must be same for all instances. Either Auto or Manual.

Thanks

Undo Advisor

Undo Advisor is used to do the following,

1. Set the low threshold value for undo retention.

2. Estimate the size of undo tablespace you'll need for various undo retention setting.

3. Use different analysis time periods to get the recommendations, in the form of graph, about the right undo tablespace size for varying undo retention length.

Thanks

Tuning Undo - Part 2

The V$UNDOSTAT provides useful information in tuning the undo tablespace.

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view represents statistics across instances, thus each begin time, end time, and statistics value will be a unique interval per instance.

Important columns

UNXPSTEALCNT - The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests.

UNXPBLKRELCNT - The number of unexpired blocks removed from undo segments to be used by other transactions

UNXPBLKREUCNT - The number of unexpired undo blocks reused by transactions.

EXPSTEALCNT - The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests

EXPBLKRELCNT - The number of expired extents stolen from other undo segments to satisfy a space request

EXPBLKREUCNT - The number of expired undo blocks reused within the same undo segments

SSOLDERRCNT - The number of ORA-1555 errors that occurred during the interval

NOSPACEERRCNT - The number of Out-of-Space errors

Inference:

1. When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

2. If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

3. If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

Thanks

Tuning Undo - Part 1

Execute the following query,

SELECT inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT "Unexpired Stolen", EXPSTEALCNT "Expired Reused",
SSOLDERRCNT "Snapshot err", NOSPACEERRCNT "Out of space err",
MAXQUERYLEN, tuned_undoretention
FROM gv$undostat
WHERE begin_time between to_date('','MM/DD/YYYY HH24:MI:SS')and to_date('','MM/DD/YYYY HH24:MI:SS')
ORDER BY inst_id, begin_time;

Problem 1: Out of space error and unexpired extents are stolen
Solution : Increase the undo Tablespace size.

Problem 2: Snapshot error and Expired extents are reused
Solution: Increase the undo Retention

Thanks

Undo Extents

In Undo Segments there are three types of extents, they are

Unexpired Extents - Undo data whose age is less than the undo retention period.
Expired Extents - Undo data whose age is greater than the undo retention period.
Active Extents - Undo data that is part of the active transaction.

The sequence for using extents is as follows,

1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.

2. If this fails because of no available free extents and we cannot autoextend the datafile, then Oracle tries to steal an expired extent from another undo segment.

3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.

4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.

5. If all the above fails, an Out-Of-Space error will be reported.

Thanks

ORA - 01555 Snapshot Too Old

ORA - 01555 Snapshot Too Old Error

In case of long running queries, if data is changed while the query is running, undo segments created for that change are needed to make a read consistent image for the long running query. When these undo segments are reused while these were still need by the query, this error occurs, because oracle cannot provide the read consistent image of the data.

To avoid this error you have to postpone the reuse of the undo segments by

i) Increase UNDO_RETENTION value.

ii) Increase the size of Undo Tablespace.

Note: Committing less often would be other solution. As until transaction is committed undo segment can not be reused thus reducing the chances of a snapshot too old error.

Thanks

Sizing Undo Tablespace

Sizing Undo Tablespace

Sizing an Undo Tablespace requires 3 pieces of data.

(UR) UNDO_RETENTION in seconds. (init parameter)
(UPS) Number of undo data blocks generated per second (query v$undostat)
(DBS) Overhead varies based on extent and file size (init parameter db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

To Calculate UPS,

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

The following query calculates the size of undo tablespace (Mega bytes) needed:

SQL> SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 "Mega Bytes"
FROM (SELECT value UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) UPS FROM v$undostat),
(select block_size DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

Thanks

Retention Guarantee

Retention Guarantee

If you specify the RETENTION GUARANTEE clause for theUNDO tablespace, then the database will never overwrite undo data whose age is less than the undo retention period. This functionality is new in Oracle 10g. By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if itmeans risking failure of currently active DML operations. The clause can be specified either during undo tablespace creation time or later using an ALTER TABLESPACE command. The default setting is RETENTION NOGUARANTEE.

SQL> Alter Tablespace undotbs RETENTION GUARANTEE;

SQL>Alter Tablespace undotbs RETENTION NOGUARANTEE;

Thanks

Automatic Undo Retention

Undo Retention (Oracle 9i)

The minimum amount of time (in secs) that undo data should be retained in undo tablespace to satisfy the read consistency. If the database requires space then the undo data will be over written even if the undo data has not been retained for UNDO_RETENTION duration.

Automatic Undo Retention (Oracle 10g)

If UNDO_MANAGEMENT=AUTO (no matter the setting for UNDO_RETENTION) automatic undo tuning will be used. MMON will calculate the MAXQUERYLEN every 30 sec interval. Based upon the MAXQUERYLEN, MMON decides the TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

If UNDO_RETENTION is set > 0, the value specified acts as a minimum setting.

Note: Undo information for LOBs are stored in the segment itself and not in the undo tablespace. If space becomes low, the unexpired LOB undo information may be overwritten. Hence Automatic undo retention is not supported for LOBs.

Thanks

Drop Undo Tablespace

To drop the undo tablespace (undotbs) issue the following statement,

SQL> Drop tablespace undotbs including contents and datafiles;

The statement will return error if the undo tablespace have any active transactions or any unexpired extents.

Thanks

Switching Undo Tablespace

Oracle Database can have more than one Undo tablespace, but only one undo tablespace will be online per instance. To switch the undo tablespace,

SQL> Alter system set Undo_Tablespace = 'undotbs2' scope=both;

Note: The active transactions will continue to use the old undo tablespace. The status of the old undo tablespace will be PENDING OFFLINE. The undo tablespace cannot be dropped when its status is pending offline. When all the active transactions are completed then the staus will be changed to offline. The new transactions will use the new undo tablespace.

Thanks

Enable Automatic Undo Management

To enable Automatic Undo Management feature, all you have to do is set up two initialization parameters.

UNDO_TABLESPACE= undotbs1 -- Name of the undo tablespace
UNDO_MANAGEMENT= Auto -- Default is manual. Not a dynamic parameter.
UNDO_RETENTION= time_in_secs -- Default 900 secs.

From Oracle 11g,

The default value for UNDO_MANAGEMENT is AUTO (Manual in earlier releases). Also note that a null value for this parameter sets it to Automatic undo management in 11g and later, but defaults to manual in earlier releases. Hence while upgrading from previous version double check this parameter value.

Thanks

Create Undo Tablespace

Syntax:

CREATE UNDO TABLESPACE tbs_name
DATAFILE 'filename' SIZE {KMG}
[AUTOEXTEND {OFF ON} [NEXT int {KM}] [MAXSIZE int {KM}] ]
[EXTENT MANAGEMENT {DICTIONARY LOCAL {AUTOALLOCATE UNIFORM [SIZE int K M]} }]
[RETENTION NOGUARANTEE GUARANTEE];

Note: The Autoextend MAXSIZE will default to UNLIMITED if not specified.


Example:

CREATE UNDO TABLESPACE undotbs1
DATAFILE 'oracle/prod/datafile/undotbs1' SIZE 5000M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
RETENTION NOGUARANTEE;

When you start the instance, the database automatically selects the first available undo tablespace. If no undo tablespace is available, the instance stores the undo records in the system tablespace.

If the tablespace mentioned in the UNDO_TABLESPACE parameter does not exist, then the startup command will fail.

Thanks