Monitoring TEMP & UNDO Space Usage

Monitoring TEMP Space Usage in Oracle

It's a common problem encountered by many DBAs on a daily basis; developers writing queries which run out of TEMP space. It can come at the worst of times, too. For example, you've run in the scripts many times overnight into a development environment and they worked fine. They are signed off and run into PROD. However, when running them into PROD they run out of TEMP space because some other processes were contending for TEMP space.

We use this query to monitor TEMP space. We have an OEM job which runs every 5 minutes during selected times and then alerts us when the TEMP space reaches a certain threshold.

SELECT ROUND(SUM( u.blocks * blk.block_size)/1024/1024,0)
FROM v$sort_usage u,
(SELECT block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY') blk;

It can be easy to forget about TEMP space usage, but when it goes wrong it usually goes wrong badly. After all, if your job is using that much TEMP space it's probably doing a lot of work!

If you are concerned about monitoring the TEMP space within your Oracle environment,

-----------------------------------------------------------------------------------------------------------------

How to Monitor UNDO Usage in Oracle 9i

To put it simply, there are two main ways to monitor UNDO usage within the Oracle database. Which one you decide to use will depend upon your requirements. The two ways are:

1. View currently active UNDO usage
2. Using current undo usage figures, estimate percentage of UNDO required based on UNDO_RETENTION

Method 1

Requires a query to look at the actively used blocks within the database. It will show you at any point in time how much UNDO is currently being used by actively running transactions.

SELECT (used_ublk * (SELECT block_size
FROM dba_tablespaces
WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction;

This query calculates your block size, providing that you have the UNDO tablespace set up correctly, and then outputs the amount of space being used by active transactions in the database in MBs. This query will display one line per session. If you would like to know the total amount used, you can just put a SUM at the beginning of the query

Method 2

Within most databases, the UNDO_RETENTION parameter will be set. The UNDO_RETENTION parameter specifies, in seconds, how long non-active UNDO, that is UNDO that is not currently being used by a session but was used recently, should be kept for. This UNDO is refered to as unexpired UNDO.

This setting provides the DBA with some control over what UNDO space is used first. The UNDO space which is expired - UNDO blocks which are older than the UNDO_RETENTION period - will be used first, followed by unexpired UNDO if necessary.

The database will try to honour this setting wherever possible but this is not always necessary. For example, a long running update from an active transaction will take precedence over UNDO that is unexpired.

The idea behind the UNDO_RETENTION parameter is for the DBA to provide a value for this so that the longest running SELECT statements can still be satisfied after X number of seconds of transactions happening in the database.

This brings us into 2nd method of monitoring UNDO usage, which requires a slightly more complex query with a couple more variables.

SELECT ROUND(((ur * (ups * dbs)) + (dbs * 24))/ut*100,0) AS "%"
FROM (SELECT VALUE AS ur
FROM v$parameter
WHERE NAME = 'undo_retention'),
(SELECT (SUM (undoblks) / SUM (((end_time - begin_time) * 25200))
) AS ups
FROM v$undostat),
(SELECT block_size AS dbs
FROM dba_tablespaces
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace')),
(SELECT sum(bytes) as ut
FROM dba_data_files
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace'));

There is a view in the database called V$UNDOSTAT, and this view shows the hostorical usage of the database. As with all the other V$ views, it is cumulative since instance startup. In the case of the V$UNDOSTAT view, it shows data for a maximum of 7 days prior to the point in time.

Our query above looks at the total amount of UNDO blocks used in that time, divides it by the timespan that is recorded in V$UNDOSTAT and then multiplies it by the UNDO_RETENTION period to give a value for the undo per second (ups).

Using this, some other variables and a few calulations it is possible to work out, based on the historical UNDO usage since instance startup, what percentage of the UNDO tablespace is used.

This query should be used if you have specified an UNDO_RETENTION paramter which should be adhered to all the time in order to allow long running queries to complete.

----------------------------------------------------------

source :-

http://www.ora00600.com/scripts/databasemonitoring/undo.html