Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 2 November 2014

How to Find Database Optimal and Needed Undo Size

Since oracle 9i, rollback segments are re-named undo logs, traditionally it were stored in rollback segments until a commit or rollback segments were issued.
Automatic undo management allows the DBA how long information should be retained after commit. The larger your undo tablespace the more you can hold for long running DML operation (Preventing "snapshot to old error on long running queries).
You can choose to allocate a specific size for the UNDO tablespace and then set the optimal UNDO_RETENTION according to UNDO size. This is specially useful when you disk space is limited and you do not want to allocate more space than required UNDO size.

OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE *       UNDO_BLOCK_PER_SEC)

Find Actual Undo Size:
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#;

UNDO_SIZE
-----------
  7948206080

Find Undo Blocks per Second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
        6.47
Find Undo Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
--------------------
                8192

OPTIMAL UNDO RETENTION = 7948206080 /(8192 * 6.47)
149959.8145 [sec]
Using below query you can find all those information collectively:

Script: To Find calculated Optimal Undo retention:
Using below query you can find all those above information collectively.
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';

ACTUAL UNDO SIZE [MByte]   UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
----------------------    ---------------- -----------------------------
7580 10800 149960 
Script: Planning to Calculate Needed Undo size for database:
In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, thus the setting for these are determined by the level of DML activity in the database:
1. If you are using heavy DML operation make sure there is enough sized rollback segments.
2. If you expect heavy DML load then must have multiple undo tablespace.
3. Try to limit the number of simultaneous users per UNDO to four.
4. For large batch transactions create special large extent rollback segments in a separate tablespace from the other rollback segments,
only bring them online when needed and use SET TRANSACTION command to assign them to specific transactions.
5. Try to avoid running large batch transactions simultaneously with OLTP or smaller transactions.
UNDO_SIZE = UNDO_RETENTION * DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC

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

ACTUAL UNDO SIZE [MByte]    UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
-----------------------     ----------------         -----------------------
7580 10800 545.90

12 comments:

  1. nice information

    http://eapplynew.in/

    ReplyDelete
  2. I have a doubt regarding "set transaction", is it session specific or transaction specific. Please clearify... thanks in advance

    ReplyDelete
  3. Wow! It was the best article , actually you have posted something useful than others, because I read many articles related to this basics of programming, but I only get impressed with your post only, keep posting.
    Regards,
    oracle dba course in chennai

    ReplyDelete
  4. Best Java Training Institute In ChennaiThis information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  5. Nice article
    Thanks for sharing the informative blog.

    CCNA Training In Bangalore

    ReplyDelete
  6. Awesome resources!
    Thanks for sharing such good article.

    Staffing services in Bangalore

    ReplyDelete
  7. Great post.
    It really helped me to learn something new. So thanks.
    E-commerce Development Comapny In Bangalore

    ReplyDelete
  8. I would like to appreciate your work for good accuracy and got informative knowledge from here.

    Oracle Fusion financial

    ReplyDelete
  9. Quantum Binary Signals

    Get professional trading signals delivered to your mobile phone every day.

    Follow our signals NOW and profit up to 270% per day.

    ReplyDelete