Calculating the optimal size for the oracle undo tablepsace

oracleoracle-12ctablespaces

To size the undo tablespace I query v$UNDOSTAT

SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN from v$UNDOSTAT;

BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYLEN
------------------- ------------------- ---------- -----------
14.10.2016 10:38:57 14.10.2016 10:48:13        911         244
14.10.2016 10:28:57 14.10.2016 10:38:57        977        1120
14.10.2016 10:18:57 14.10.2016 10:28:57       1031        2266
14.10.2016 10:08:57 14.10.2016 10:18:57       1018        2365 <--

To find out the blocksize of the undo tablespace, I query

SELECT TO_NUMBER(value) DB_BLOCK_SIZE FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE
-------------
         8192

Now I can calculate the undo tablespace size. I have to divide the UNDOBLKS by 600 to get the rate of undo generation in blocks per second, and multiply by the blocksize to get the figure in bytes. Multiply by the largest figure for MAXQUERYLEN to find the space needed if the highest rate of undo generates coincided with the longest query and then divide by a billion to get the answer in gigabytes

1018/600 * 8192*2365=328713557,33
328713557,33/1000000000=0.3287GB

Is this a correct calculation? Should I consider other metrics to calcualte the size of the undo tablepsace?

Best Answer

You may use the calculation mentioned in this site:

https://taliphakanozturken.wordpress.com/tag/actual-undo-size/

Related Solutions

Oracle delete technique for table’s segment size (data compactness)

How do you define the "tablespace size"? Are you interested in the total size of the data files on disk that comprise the tablespace? Or are you interested in the total size of all the segments that are part of the tablespace?

Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under either definition. Both the size of the table's segment and the size of the tablespace's data files will remain constant. Of course, there will now be additional free space in many of the table's blocks that can be used by subsequent INSERT and UPDATE operations.

Issuing a TRUNCATE, on the other hand, will decrease the size of the table's segment. That won't affect the size of the tablespace's data files. But it will affect the total size of all the segments that are part of the tablespace. So there may be a difference depending on your definition of the size of a tablespace. A TRUNCATE, being DDL, will not be transactional so it cannot be rolled back. Assuming that you are deleting a large fraction of the rows in the table, it will also tend to be much more efficient than issuing a DELETE because it generates much less UNDO and REDO.

If you are stating in your last paragraph that the size of the segment is increasing much faster than the rate at which new data is being added, assuming that the new rows are roughly the same size as the old rows and that the old rows are not growing over time due to updates, is it possible that the new rows are being added via direct-path inserts which will always go above the current high-water mark for the segment and will thus never reuse the space in blocks that is freed up by a DELETE? If so, is that intentional? If the table is small, you might see similar differences because of the granularity of extent allocation-- you might insert 100 rows without requiring a new extent, the 101st insert requires Oracle to allocate a new extent, and that new extent might be sufficient for thousands of new rows to be added, but you'll only see the size of the segment change after the 101st insert. But that is less likely if this is a reasonably large table unless you've chosen a particularly large extent size.

Oracle Undo tablespace users

This shows you the amount of undo size used by each session that is currently active.

select s.sid, 
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username;

Related Question