Shulou(Shulou.com)06/01 Report--

Undo tablespace is a very important tablespace in Oracle database, and its utilization is also the focus of DBA, but in busy production systems, it is easy to see that undo tablespace utilization is very high, even up to 100%. So, how big should the undo tablespace be set? How are undo blocks allocated? What should I do when the utilization of undo tablespaces is high? This paper explains the above problems.

I. undo tablespace size setting

1. First of all, preset the size of a undo table space according to the database and business situation.

2. After the database has been running for a period of time, you can estimate the reasonable undo tablespace size as follows:

Calculation formula: UR* (USP*DBS)

UR represents the undo_retention parameter value

UPS represents the number of undo blocks generated per second

DBS represents the database block size

SELECT (UR * (UPS * DBS)) AS "Bytes"

FROM (select max (tuned_undoretention) AS UR from v$undostat)

(SELECT undoblks / (end_time-begin_time) * 86400) AS UPS

FROM v$undostat

WHERE undoblks = (SELECT MAX (undoblks) FROM v$undostat))

(SELECT block_size AS DBS

FROM dba_tablespaces

WHERE tablespace_name =

(SELECT UPPER (value)

FROM v$parameter

WHERE name = 'undo_tablespace'); II. Automatically managed undo block allocation algorithm

1. If there are free blocks in the current extent, the next free block in this extent will continue to be used when space is needed.

2. After the current extent is finished, if the next extent is expired, jump to the first data block of the next extent

3. If the next extent is not expired, apply for space from the undo table space. If there is free space in the undo table space, assign a new extent to join the undo.

Segment, and then jump to the first block of the new undo extent

4. If there is no free space in the undo tablespace, steal (steal) the extent of expired from the undo segment of offine, and change the extent of the undo segment of offine

Expired's extent is assigned to the current undo segment and jumps to the first block of the newly added extent

5. If you do not have expired's extent in offine's undo segment, steal expired's extent from online's undo segment and add it to the current undo

Segment and jump to the first free block of the newly added extent

6. If there is no extent for expired in online's segment, extend the undo tablespace data file (if automatic extension is enabled) and add a new extent to the current

Undo segment

7. If the undo tablespace data file cannot be expanded, lower the retention value by 10%, and then steal the undo extent that is now expired

8. Steal unexpired's extent from any offine's undo segment

9. Try to reuse the extent of unexpired in the current segment. If all extent is in currently busy (transaction is not committed), go to step 10.

10. Try to reuse the unexpired extent in the undo segment of any online

11. If all of the above steps fail, throw ORA-30036 unable to extend segment by% s in undo tablespace'% s'.

As can be seen from the above undo block allocation algorithm, when the utilization rate of undo table space is very high, it is not necessary to increase the size of undo table space, because oracle will reuse expired.

Extent of or extent of unexpired. Even if the undo tablespace usage reaches 100%, the database does not necessarily report an error.

The utilization of undo tablespaces can be queried in the following ways:

SELECT / * + RULE * / df.tablespace_name "Tablespace"

Df.bytes / (1024 * 1024) "Size (MB)"

SUM (fs.bytes) / (1024 * 1024) "Free (MB)"

Nvl (Round (SUM (fs.bytes) * 100 / df.bytes), 1) "% Free"

Round ((df.bytes-SUM (fs.bytes)) * 100 / df.bytes) "% Used"

FROM dba_free_space fs

(SELECT tablespace_name,SUM (bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT / * + RULE * / df.tablespace_name tspace

Fs.bytes / (1024 * 1024)

SUM (df.bytes_free) / (1024 * 1024)

Nvl (Round ((SUM (fs.bytes)-df.bytes_used) * 100 / fs.bytes), 1)

Round ((SUM (fs.bytes)-df.bytes_free) * 100 / fs.bytes)

FROM dba_temp_files fs

(SELECT tablespace_name,bytes_free,bytes_used

FROM v$temp_space_header

GROUP BY tablespace_name,bytes_free,bytes_used) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

ORDER BY 4 DESC; however, if undo tablespace usage is very high and encounters ORA-01555 or ORA-30036, analysis is required. First of all, to analyze whether the size setting of the undo tablespace is reasonable, you can use the algorithm above to estimate the reasonable size of the undo tablespace. If the undo tablespace is large enough, but still encounters ORA-01555 or ORA-30036, you need to troubleshoot from the following aspects:

1. For ORA-01555, you need to check whether the running time of the query statement is too long and whether the undo_retention setting is too small.

2. For ORA-30036, you need to check whether the transaction is too large and whether the undo_retention setting is too large.

3. For ORA-30036, you need to pay attention to the following information:

-- check to see if there are undo blocks that have been occupied for a long time

Select begin_time

End_time

Undoblks

Tuned_undoretention

Maxquerylen

Maxqueryid

From v$undostat

If there are undo blocks that have been occupied for a long time, you need to pay attention to Bug 5387030 in 10g version.

-- query the percentage of undo tablespaces being used

SELECT

(SELECT (NVL (SUM (bytes), 0))

FROM dba_undo_extents

WHERE tablespace_name=''

AND status IN ('ACTIVE','UNEXPIRED')) * 100) /

(SELECT SUM (bytes)

FROM dba_data_files

WHERE tablespace_name='')

"PCT_INUSE"

FROM dual

-- View the status of the extent assigned to the undo tablespace

SELECT DISTINCT STATUS, SUM (BYTES) / 1024ax 1024, COUNT (*)

FROM DBA_UNDO_EXTENTS where tablespace_name =\ 'UNDOTBS1\' GROUP BY STATUS

-- query the transactions that are using undo segments and the size of the undo tablespaces used

Select start_time

Username

S.MACHINE

S.OSUSER

R.name

Ubafil

Ubablk

T.status

(used_ublk * p.value) / 1024 blk,-- use the size of the undo tablespace

Used_urec

S1.SQL_ID

S1.SQL_TEXT

From v$transaction t, v$rollname r, v$session s, v$parameter pcent vault SQL S1

Where xidusn = usn

And s.saddr = t.ses_addr

And p.name = 'db_block_size'

And s.SADDR=s1.ADDRESS (+)

Order by 1; for ORA-01555, the best solution is to shorten the running time of query statements, which needs to optimize sql. For ORA-30036, try to split large transactions and commit them in batches.

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.