| sizing undo tablespace & setting Undo_retenion [message #60285] |
Fri, 30 January 2004 08:11  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
can any one help me to give some tips regarding sizing undo tablespace and undo_retention parameter.
we have to implement the database in production system with 40 users
but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the retenion parameter.pls give some valuable tips .i have gone thru oracle doc's and some related sites.i have an idea but expecting ur valuable advice its an ERP aplications that contains 20 modules .I am an new one to this dba level.pls hlp
susan
|
|
|
|
| Re: sizing undo tablespace & setting Undo_retenion [message #60293 is a reply to message #60285] |
Fri, 30 January 2004 12:13  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
you size the undo_tablespace based on your undo_retention requirement(flashback query requirement , longest running query , avoiding ORA-1555 ). Following from the documentation will help you size
"Calculating the Space Requirements For Undo Retention
Given a specific UNDO_RETENTION parameter setting and some system statistics, the amount of undo space required to satisfy the undo retention requirement can be estimated using the following formula:
UndoSpace = UR * UPS + overhead
where:
UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows:
(2 * 3600 * 200 * 4K) = 5.8GBs.
Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view.
"
With AUM,Oracle manages the size and number of the rollback segments based on your transaction volume. You basically need to find out how long you want to retain your undo,based on the things I mentioned earlier. If you dont have a specific requirement,then find out the time it takes for your longest query(MAXQUERYLEN
from v$undostat) after creating the undo tablespace on guess estimates and letting the users complete their cycle of transactions/queries.
Based on this you would be able to decide on your retention needs(to avoid ORA-1555) and hence undo_retention parameter and hence the undo space.
HTH
Thiru
|
|
|
|
|
|
| Re: sizing undo tablespace & setting Undo_retenion [message #60334 is a reply to message #60316] |
Mon, 02 February 2004 09:38 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi
to monitor rollback segments, use v$rollstat, v$undostat,dba_rollback_segs and to monitor tablespace growth use dba_free_space,dba_data_files and if autoextend is turned on,you need to monitor the underlying filesystem space too.
-Thiru
|
|
|
|
| sizing redologsize [message #60370 is a reply to message #60334] |
Tue, 03 February 2004 11:32 |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
hi
thanks for ur reply .Then i need a clarification about redolog sizing.
Now for testing db i put in 3 groups(2 each )in different groups disks.
so in prodcuction system how much space have to allocate we have large applcation(ERP) with around 20 modules.So normally lots of DML (inserts) happening (its not 24 * 7)oracle recomends checkpoint should happen aroun 20 to 30 minutes.so how i size the redologfiles.whether its size indirectly related to undomanagement even though both r different.will u give some deatiled explanation of redolog sizing and tunning(b'cos newbie to this field)
with regards
susan
|
|
|
|
| Re: sizing redologsize [message #60375 is a reply to message #60370] |
Tue, 03 February 2004 14:35 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi
See this thread on the effects of the size of redolog files on checkpointing -->
RedoLog_Checkpointing
Undo segment changes are also logged(ie go into redologs ) .So the more undo you generate ,the more redo you generate. But size of the redolog files doesnt have direct impact on undo management. However it can increase DBWR/CKPT activity and indirectly affect other things.
Basically you can size the redolog files large enough so as to cause the full checkpoints happen once every 15-30 minutes, but if your recovery requirements are more stringent,then you can enable incremental checkpoints via FAST_START_MTTR_TARGET .
-Thiru
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
|
|
|
|
| Re: sizing redologsize [message #471683 is a reply to message #471680] |
Mon, 16 August 2010 12:43 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
> how can v calculate the ups (undo blocks per second)
What will you do with this number after you obtain it?
How can this answer be put to practical use?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
