| optimal size [message #59879] |
Tue, 30 December 2003 22:24  |
ashokmote
Messages: 56
Registered: December 2003
|
Member |
|
|
i couldn't not understand the use of optimal size.
were it is used.were can i fine the vaule of optimal size
|
|
|
|
| Re: optimal size [message #59885 is a reply to message #59879] |
Wed, 31 December 2003 02:56  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ashok,
before anyone advises you to use OPTIMAL, let me just say 'DONT USE OPTIMAL' .. details later(as I am headed out and brb)
-Thiru
|
|
|
|
| Re: optimal size [message #59886 is a reply to message #59879] |
Wed, 31 December 2003 06:53 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ok,here's an extract from the manual about OPTIMAL size of Rollback segment
"When you create or alter a rollback segment, you can use the storage parameter OPTIMAL (which applies only to rollback segments) to specify the optimal size of the segment in bytes. If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, Oracle compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger than its optimal size, and if the extents immediately following the extent just filled are inactive, then Oracle deallocates consecutive nonactive extents from the rollback segment until the total size of the rollback segment is equal to or close to, but not less than, its optimal size. Oracle always frees the oldest inactive extents, as these are the least likely to be used by consistent reads.
A rollback segment's OPTIMAL setting cannot be less than the combined space allocated for the minimum number of extents for the segment. For example:
(INITIAL + NEXT + NEXT + ... up to MINEXTENTS) bytes
"
To find out the optimal size of rollback segments,
you can
select n.name,r.optsize from v$rollname n,v$rollstat r where n.usn=r.usn;
The problem with using OPTIMAL(especially inappropriate values) are following:
1.Performance penalty during wraps(ie when the transaction moves from one extent to another).
Everytime the extent boundary is crossed, Oracle has to compare the rollback segment size to the OPTIMAL size and
this operation(recursive sql) does induce penalty.
2.When the transaction wraps into the next extent and if the size is greater than the OPTIMAL,
then it has to wait for the Rollback segment to shrink all the way down to its OPTIMAL size
and this is an unnecessary wait affecting its response time.
3.Usage of OPTIMAL increases the chances of hitting the ORA-1555 snapshot too old error.
The inactive extents might be needed to present a read consistent image for other queries,
but when the OPTIMAL setting causes the rollback segment to shrink(deallocating extents beyond OPTSIZE),
the queries that needed these inactive extents will fail with 'ORA-1555' errors.
Inorder to get away with not using OPTIMAL,you will need to make sure you size the rollback segments based on your regular larger(or largest if possible) transactions. You could always shrink those one-off large rollback segments back manually ,when it has extended too much. SMON will shrink them every 12 hours too.
Having said these,if you are running out of disk space and you cannot afford to have large rollback segment tablespaces,you might be OK with using OPTIMAL but you increase the chances of hitting the 'snapshot too old error' and the performance penalties I mentioned earlier.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
