How much undo is too much?

From: <rjqjunk_at_vt.edu>
Date: 14 May 2004 05:08:37 -0700
Message-ID: <329a64a5.0405140408.58c04335@posting.google.com>

I've read in this forum that you can't just go around allocating
'oodles' of undo space because the management of said space fills up
the buffer. So how do you tell when you've reached the 'oodles' stage and you should stop allocating?

I'm trying to balance the overall performance of the db with the needs of a couple of jobs that are periodically run.

ops$oracle_at_PROD SQL>select *
  2 from (select begin_time, end_time, maxquerylen/60/60 "Undo_Retention (hrs)",

  3                 undoblks * 8*1024/600 * maxquerylen/1024/1024
"Undo size (Mbs)"
  4            from v$undostat order by undoblks*maxquerylen desc)

  5 where rownum < 10;

BEGIN_TIME END_TIME Undo_Retention (hrs) Undo size (Mbs)

------------ ------------ -------------------- ----------------
10-MAY 00:36 10-MAY 00:46                    2            7,521
13-MAY 18:06 13-MAY 18:16                    3            7,096
10-MAY 00:26 10-MAY 00:36                    2            5,323
10-MAY 00:16 10-MAY 00:26                    2            4,826
13-MAY 14:36 13-MAY 14:46                    8            4,802
10-MAY 01:06 10-MAY 01:16                    3            4,675
10-MAY 00:46 10-MAY 00:56                    2            3,868
09-MAY 23:36 09-MAY 23:46                    1            3,022
10-MAY 00:56 10-MAY 01:06                    3            2,592

ops$oracle_at_PROD SQL>select sum(bytes)/1024/1024 as "Current undo size (Mbs)"

  2       from dba_data_files
  3      where tablespace_name = 'UNDO1'

  4 ;

Current undo size (Mbs)

---

                  5,024

ops$oracle_at_PROD SQL>/* See note Metalink Note:262066.1 */ ops$oracle_at_PROD SQL>SELECT ((UR*(UPS*DBS))+(DBS*24))/1024/1024 AS "Average undo size (Mbs)"
  2 FROM (SELECT value AS UR FROM v$parameter WHERE name =
'undo_retention'),

  3 (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS

  4            FROM v$undostat),
  5         (SELECT value AS DBS FROM v$parameter WHERE name =

'db_block_size')

  6 ;

Average undo size (Mbs)

---

                  1,015

Received on Fri May 14 2004 - 07:08:37 CDT