 |  | Ver este articulo en Español |
Hi folks!
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
Read the popular 2008 article Who is using your UNDO space?
Interested on MySQL Performance? Read Book Review - High Performance MySQL Subscribe to Oracle Database Disected by Email
Bookmark this on Delicious
