Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Calculating the UNDO SIZE and Sizing the Undo Tablespace

  1. #1
    Administrator
    Join Date
    Oct 2011
    Posts
    13

    Calculating the UNDO SIZE and Sizing the Undo Tablespace

    Run @ sql
    ------------------

    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'
    /

    For me the O/P is
    ACTUAL UNDO SIZE
    ------------------------
    200

    [MByte] UNDO RETENTION [Sec]
    ------------------------
    900

    NEEDED UNDO SIZE [MByte]
    ------------------------
    209.765625

    >show parameter
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    tracefile_identifier string
    transactions integer 272
    transactions_per_rollback_segment integer 5
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS
    use_indirect_data_buffers boolean FALSE
    user_dump_dest string /u01/accounts/b13prade/myora/1
    1g/diag/rdbms/prod/prod/trace
    utl_file_dir string
    workarea_size_policy string AUTO

    so if query return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.
    Last edited by Hemant; 01-03-2012 at 07:29 PM.

  2. #2

    Red face Undo tablespace

    Optimum Size for your UNDO TABLESPACE ?

    Generally this question is vaguely answered saying that sizing comes with experience (of DBA) or looking at load on server
    or sometimes even by the number of ORA-1555 or out of space errors. The size of UNDO tablespace can be optimized by using a simple formula.
    While designing an application, generally it is tough to know about the number of transactions and subsequently number of
    rows changed per second. So I suggest having a “big undo tablespace” to start with and based on load, after doing some calculations
    and resize your UNDO tablespace. In my case one of the applications was going to production (live), and I had no idea that how many
    transactions will happen against this database. All what I was told that there will be optimum (transactional) activity on this database.
    The word “optimum” itself is vague. So I started with UNDO tablespace with size of 2GB and datafiles with autoextend “on”. And after
    a month, I noticed the activity from V$undostat.

    Here is the step by step approach:

    Step 1: Longest running query.

    SQL> select max(maxquerylen) from v$undostat;

    MAX(MAXQUERYLEN)
    ----------------
    1793

    This gives you ideal value for UNDO_RETENTION. To be on the safer size you should add few more seconds to get the right value. So in my case, the size of undo retention should be say 2000 secs.

    Step 2: Size of UNDO tablespace.

    Size of UNDO needed = UNDO_RETENTION x [UNDO block Generation per sec x DB_BLOCK_SIZE] + Overhead(30xDB_BLOCK_SIZE)

    Out of these we know UNDO_RETENTION and DB_BLOCK_SIZE. All we need is to find out “UNDO Blocks per second”. Which can be easily fetched from v$undostat

    SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UPS" FROM v$undostat;

    UPS
    ------------------------------
    8.11985583

    V$undostat stores data for every 10 mins and begin/end times are start/end time of those intervals. We multiplied it with 24*60*60 because the difference between two dates will be in days and to get to seconds, we need it to multiply with 24hrs*60mins*60secs

    So now we have all the values needed.

    Undo size needed = [8.12 x 2000 x 8192] + [30 x 8192] = 133283840 bytes = 127.11 MB

    UNDO TABLESPACE shoaibansari

+ Reply to Thread

Similar Threads

  1. how to know the size of cf,df,rf?
    By saioracleappsdba in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 05-11-2012, 05:50 PM
  2. how to know which user is using max undo tablespace in oracle 10g?
    By saioracleappsdba in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 05-10-2012, 07:53 PM
  3. Using logminer to undo incorrectly committed changes in Oracle 10g.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-30-2012, 10:18 PM
  4. Oracle Database Undo Tablespace Mangement.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-12-2012, 11:08 PM
  5. Undo Utilization for Tablespace and for Session
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 11-02-2011, 08:13 PM

Tags for this Thread

Posting Permissions

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

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics