In addition, the column
SSOLDERRCNT indicates how many queries failed with a ???Snapshot too old??? error:
SQL> select to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
2> undoblks, ssolderrcnt from v$undostat;
END_TIME UNDOBLKS SSOLDERRCNT
---------------- ---------- -----------
2007-08-02 20:17 45 0
2007-08-02 20:07 116 0
2007-08-02 19:57 2763 0
2007-08-02 19:47 23 0
2007-08-02 19:37 45120 2
2007-08-02 19:27 119 0
2007-08-02 19:17 866 0
Between 19:27 and 19:37 we have a spike in undo usage, resulting in some failed queries.
As a rule of thumb, you can use the following calculations:
undo_tablespace_size = UR * UPS + overhead
In this formula, UR equals undo retention in seconds (from the initialization parameter
UNDO_RETENTION), UPS equals undo blocks used per second (maximum), and overhead
equals undo metadata, usually a very small number relative to the overall size. For example,
if a database has an 8K block size, and UNDO_RETENTION equals 43200 (12 hours), and we
Chapter 7: Managing Transactions with Undo Tablespaces 221
generate 500 undo blocks every second, all of which must be retained for at least 12 hours, our
total undo space must be:
undo_tablespace_size = 43200 * 500 * 8192 = 176947200000 = 177GB
Add about 10 to 20 percent to this calculation to allow for unexpected situations. Alternatively,
you can enable autoextend for the datafiles in the undo tablespace. Although this calculation is
useful as a starting point, Oracle 10g??™s and Oracle 11g??™s built-in advisors, using trending analysis,
can give a better overall picture of undo space usage and recommendations.
Pages:
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380