Who is using your UNDO space?

Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC 

Good luck with your UNDO-eating monsters...


Who is using your UNDO space? Improved Script"

I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176; 216 if your database is 11gR1... or use the following version independent script!!! (Hope we don't change the statistic name).

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND b.name = 'undo change vector size'
AND a.value > 0
ORDER BY a.value DESC

http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html

Starting and Stopping Oracle Enterprise Manager 11g Grid Control

This document will detail the steps required to stop and start Oracle Enterprise Manager 11g Grid Control and all its components. For information on installing Oracle Enterprise Manager 11g Grid Control see the post Install Oracle Enterprise Manager Grid Control 11gR1 on Linux.

In this document I will be making references to the OMS_HOME and AGENT_HOME. If you do not happen to know what those locations are you can find them in the /etc/oratab file.

[oracle@gc bin]$ grep -E 'oms|agent' /etc/oratab
*:/u02/app/oracle/product/weblogic/oms11g:N
*:/u02/app/oracle/product/weblogic/agent11g:N
[oracle@gc bin]$

So in this example the OMS_HOME would be /u02/app/oracle/product/weblogic/oms11g and AGENT_HOME would be/u02/app/oracle/product/weblogic/agent11g.

NOTE: The Oracle Enterprise Manger 11g Grid Control install process puts a script called gcstartup in /etc/init.dthat will stop and start the Oracle Management Service and Agent on OS startup/shutdown. It does not start or stop the repository database. If you would like to stop the automated startup/shutdown of Grid Control services place comments in front of the OMS and AGENT home directories in the /etc/oratab file or remove the script /etc/init.d/gcstartup.

Stopping Oracle Enterprise Manager 11g Grid Control

Stop the Oracle Management Service

From the OMS_HOME directory run the following to stop the OMS and WebTier services.

OMS_HOME/bin/emctl stop oms –all

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/oms11g/bin/emctl stop oms -all
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
[oracle@gc ~]$

Note if you do not include the –all flag the HTTP services for the WebLogic Server will not be shutdown.

Stop the Oracle Management Agent

From the AGENT_HOME directory run the following to stop the Agent.

AGENT_HOME/bin/emctl stop agent

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/agent11g/bin/emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
[oracle@gc ~]$

Stop the repository database

[oracle@gc ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 11 11:41:21 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc ~]$

Stop the Listener

[oracle@gc ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2010 11:42:03

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gc)(PORT=1521)))
The command completed successfully
[oracle@gc ~]$

That is it. Oracle Enterprise Manager 11g Grid Control and all associated services are now shutdown.

Starting Oracle Enterprise Manager 11g Grid Control

Start the Listener

[oracle@gc ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2010 12:58:01

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u02/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/gc/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gc.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gc)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-MAY-2010 12:58:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u02/app/oracle/diag/tnslsnr/gc/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gc.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@gc ~]$

Start the repository database

[oracle@gc ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 11 12:58:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size                  1336260 bytes
Variable Size             805309500 bytes
Database Buffers          452984832 bytes
Redo Buffers               12582912 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc ~]$

Start the Oracle Management Service

From the OMS_HOME directory run the following to start the OMS and WebTier services

OMS_HOME/bin/emctl start oms

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@gc ~]$

Start the Oracle Management Agent

From the AGENT_HOME directory run the following to start the Agent.

AGENT_HOME/bin/emctl start agent

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/agent11g/bin/emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ........ started.
[oracle@gc ~]$

Enterprise Manager 11g Grid Control and all associated services and can be accessed at https://servername.domain:7799/em.

http://www.oracledistilled.com/grid-control/starting-and-stopping-oracle-enterprise-manager-11g-grid-control/