• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1896
  • Last Modified:

Estimate undo tablespace size

Hi,

I have seen oracle document to estimate undo tablespace in oracle 10g/any version of oracle is given below.

Undospace = (Undo Retention Period * Undo Blocks Per Second) + Overhead
The Overhead should account for metadata such as bitmaps.


Here i have a question.
Undo Retention period is set 30mints for my database and i would like to know how to find UNDO Blocks Per seconds and Overhead information.

Could you please any one give me detailed information.


Thanks & Regards,
Sreeni
0
bhsrao
Asked:
bhsrao
  • 2
1 Solution
 
MohanKNairCommented:
The V$UNDOSTAT view displays statistical data to show how well a database is performing. Each row in the view represents statistics collected for a 10-minute interval. The following query gives the number of undo blocks generated per second. This value needs to be multiplied by the size of an undo block – the same size as the database block defined by the DB_BLOCK_SIZE parameter.


The END_TIME and BEGIN_TIME columns are DATE datatypes.

-- UNDO per second
SELECT (SUM(undoblks))/SUM((end_time-begin_time)*86400) FROM v$undostat;

Value of DATA BLOCK SIZE
SELECT value FROM v$parameter WHERE lower(name) = 'db_block_size';

-- Value for UNDO RETENTION
SELECT value FROM v$parameter WHERE lower(name) = 'undo_retention';

Multiplying the value from the three queries above gives the UNDO tablespace required.
0
 
bhsraoAuthor Commented:
Thanks MohanNair,

Again i have a questions here.

1)Why 86400 value is multiplying to Sum(end_time-begin_time).

Suppose : i have only one record in v$undostat means 10mints and undo generate blocks (undoblk) are 20000. For getting UNDO per second is

10 M= 20000
1M=20000/10
1Sec = 20000/10*60 . Am i rite, please correct me here.

2) Is the Db_block_size equal to Overhead.
  Could you please explain me below statement.
  (The Overhead should account for metadata such as bitmaps. )

3) This is formual/calc is for all ready existing database. Suppose i want to calucate UNDO tablespace (estimated) space  for new database(OLTP). What are the things i need to consider.

Thanks a lot for your reply.

Thanks & Regards,
Sreeni

0
 
MohanKNairCommented:
>> Why 86400 value is multiplying to Sum(end_time-begin_time).
end_time-begin_time gives the difference in number of days,  ie 86400 seconds

>> Suppose : i have only one record in v$undostat means 10mints and undo generate blocks (undoblk) are 20000. For getting UNDO per second is
The UNDO generated is 20000/(10*6) blocks per sec

>> The Overhead should account for metadata such as bitmaps
Overhead varies based on extent and file size.

>> Suppose i want to calucate UNDO tablespace (estimated) space  for new database(OLTP). What are the things i need to consider.
The amount of UNDO generated depends on the number of transactions and also the kind of transactions.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now