Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to evaluate the size of undo tablespaces. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

How to estimate the size of the UNDO tablespaces required by the Oracle database:

How To Size UNDO Tablespace For Automatic Undo Management (document ID 262066.1)

To determine the size of the UNDO tablespace required by Oracle, you need the following three pieces of information:

UR UNDO_RETENTION in seconds

Number of restore blocks generated by UPS per second

DBS db_block_size

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

UNDO_RETENTION is a parameter that controls the amount of restore data reserved to provide read consistency, defined in seconds, and can be set in the initialization file or dynamically modified using the ALTER SYSTEM command.

SQL > ALTER SYSTEM SET UNDO_RETENTION=900

SQL > show parameter undo_retention

NAME TYPE VALUE

-

Undo_retention integer 900

If the value is 900, you can keep the restored data for 15 minutes, which of course requires enough storage space.

How to calculate the number of restore blocks generated per second can be queried through the values of the begin_time, end_time and undoblks fields of the v$undostat view. The calculated SQL statement is as follows:

SQL > SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM (undoblks) / SUM (end_time-begin_time) * 86400)) AS UPS FROM v$undostat), (SELECT value AS DBS FROM v$parameter WHERE name =' db_block_size')

Bytes

-

445814.844

Detailed explanation:

Generally speaking, the calculation should be carried out at the time of the day when the database load is heaviest.

The definition of UNDO tablespace size requires consideration of the UNDO_RETNETION parameter, the generated UNDO BLOCKS/ seconds, and the size of the UNDO BLOCK. Undo_retention: if the datafile property of the UNDO tablespace is autoextensible, the undo_retenion parameter must be set, and the UNDO information will be retained at least within the value set by the undo_retention parameter, but the UNDO tablespace will be automatically expanded. For fixed UNDO tablespaces, UNDO information is maximized through the remaining space in the tablespaces. If the FIXED UNDO tablespace does not GUARANTEE (alter tablespace xxx retention guarantee;) the retention time, the undo_retention parameter will not work. (warning: if the UNDO tablespace is set to retention guarantee, unexpired data will not be overwritten, and insufficient tablespaces will cause DML operations to fail or transation to hang)

Oracle 10g has automatic Automatic Undo Retention Tuning feature. The undo_retention parameter set is only a guide value, and Oracle automatically adjusts the Undo (across the time set by undo_retention) to ensure that there are no Ora-1555 errors. By querying the tuned_undoretention field of V$UNDOSTAT (this view records the UNDO tablespace usage within 4 days, and the DBA_HIST_UNDOSTAT view can be queried for more than 4 days) (this field is only available in 10G version, 9i is not available), Oracle automatically calculates the best retenton time after sampling based on the transaction volume (if the file is not scalable, the remaining space will be considered). This creates a potential problem for a database with an uneven distribution of transactions-Undo may run out during batch processing, and this state will persist and will not be released.

SQL query tuned_undoretention:

Select to_char (begin_time,'DD-MON-RR HH24:MI') begin_time,to_char (end_time,'DD-MON-RR HH24:MI') end_time,tuned_undoretention from v$undostat order by end_time

Check the average UNDO BLOCK generated per second in a day

Select (sum (undoblks) / sum ((end_time-begin_time) * 86400) from v$undostat

The resulting result is UNDO BLOCK, and if you need to calculate the actual size, you need to multiply it by db_block_size (checked through show parameter db_block_size)

How to calculate the appropriate UNDO tablespace size:

Select (UR* (UPS*DBS)) + (DBS*24) as "bytes" from (select value as UR from v$parameter where name='undo_retention'), (select (sum (undoblks) / sum (end_time-begin_time) * 86400) as ups from v$undostat), (select value as DBS from v$parameter where name='db_block_size')

After reading the above, do you have any further understanding of how to evaluate the size of undo tablespaces? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.