Undo_retention
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Undo_retention

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Undo_retention

    Hi,

    On what basis,appropriate value for UNDO_RETENTION is calculated?

    Thanks,
    Malru

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    this will tell you
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Apr 2006
    Posts
    23
    This could be of help to you.

    /*This view displays a histogram of statistical data to show how well the datbase is working.
    Each row in the view keeps statistics collected in the instance for a 10-minute interval.
    Estimates the amount of undo required for current workload.*/

    SELECT end_time,begin_time,undoblks from v$UNDOSTAT;

    /* UNDO SIZE =UNDO RETENTION * DB_BLOCK_SIZE* UNDO_BLK_PER_SEC
    The QUERY GIVES you the space required for UNDO SIZE .Run this after database is
    Up and running for good amount of time and activity. */

    SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
    (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
    g.undo_block_per_sec) / (1024*1024)
    "NEEDED UNDO SIZE [MByte]"
    FROM (
    SELECT SUM(a.bytes) undo_size
    FROM v$datafile a,
    v$tablespace b,
    dba_tablespaces c
    WHERE c.contents = 'UNDO'
    AND c.status = 'ONLINE'
    AND b.name = c.tablespace_name
    AND a.ts# = b.ts#
    ) d,
    v$parameter e,
    v$parameter f,
    (
    SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
    undo_block_per_sec
    FROM v$undostat
    ) g
    WHERE e.name = 'undo_retention'
    AND f.name = 'db_block_size'

    SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
    FROM (
    SELECT SUM(a.bytes) undo_size
    FROM v$datafile a,
    v$tablespace b,
    dba_tablespaces c
    WHERE c.contents = 'UNDO'
    AND c.status = 'ONLINE'
    AND b.name = c.tablespace_name
    AND a.ts# = b.ts#
    ) d,
    v$parameter e,
    v$parameter f,
    (
    SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
    undo_block_per_sec
    FROM v$undostat
    ) g
    WHERE e.name = 'undo_retention'
    AND f.name = 'db_block_size'
    /


    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width