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

Undo_retention: determine the optimal undo retention time

Use the following formula to calculate the value of the undo_retention parameter:

Undo_retention=undo size/ (db_block_size * undo_block_per_sec)

-- you can calculate the actual undo data generated in the database by submitting the following query:

SQL > select sum (d.bytes/1024/1024) "undo_mb"

From v$datafile d, v$tablespace t, dba_tablespaces s

Where s.contents = 'UNDO'

And s.status = 'ONLINE'

And t.name = s.tablespace_name

And d.ts# = t.ts#

Undo_mb

-

102400

-- you can calculate the undo_block_per_sec with the following query:

SQL > select max (undoblks / (end_time-begin_time) * 3600 * 24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat

UNDO_BLOCK_PER_SEC

-

272.980265

SQL > show parameter db_block_size

NAME TYPE VALUE

-

Db_block_size integer 8192

-calculate the value of the undo_retention parameter in seconds:

SQL > select 102400 / (272.980265 / 8192) from dual

102400 1024024 / (272.98026510192)

-

48015.1926

The v$undostat view contains statistics on how the database uses the undo space, as well as the longest-running queries. You can use this information to calculate the amount of undo space for the workload handled by the current database. Each line in the v$undostat view displays undo statistics for the ten-minute interval. The table contains up to 576 rows of data, each recording ten minutes. Therefore, you can view the undo space usage for up to four days.

BEGIN_TIME: the start time of the time period.

END_TIME: the deadline for the time period.

UNDOBLKS: the number of undo blocks consumed by the database during the ten-minute interval.

TXNCOUNT: the number of transactions executed during the ten-minute interval.

MAXQUERYLEN: shows the time (in seconds) of the longest query executed on this instance during the ten-minute interval.

MAXQUERYID: identifier of the longest running SQL statement during the interval.

NOSPACEERRCNT: the number of times that the database is in the undo table space because the entire space is occupied by the active transaction, so there is not enough space to hold the new undo data.

TUNED_UNDORETENTION: the database will retain the revocation data (in seconds) after the transaction is committed and undone.

SQL > select to_char (begin_time, 'hh34:mi:ss') BEGIN_TIME

To_char (end_time, 'hh34:mi:ss') END_TIME

Maxquerylen

Nospaceerrcnt

Tuned_undoretention

From v$undostat

BEGIN_TI END_TIME MAXQUERYLEN NOSPACEERRCNT TUNED_UNDORETENTION

--

17:37:31 17:43:00 1281 0 2062

17:27:31 17:37:31 978 0 1759

17:17:31 17:27:31 372 0 1153

17:07:31 17:17:31 974 0 1755

16:57:31 17:07:31 368 0 1151

16:47:31 16:57:31 968 0 1809

16:37:31 16:47:31 363 0 1205

16:27:31 16:37:31 961 0 1805

16:17:31 16:27:31 358 0 1200

16:07:31 16:17:31 957 0 1799

15:57:31 16:07:31 353 0 1195

15:47:31 15:57:31 953 0 1794

15:37:31 15:47:31 349 0 1190

15:27:31 15:37:31 948 0 1790

15:17:31 15:27:31 342 0 1185

15 rows have been selected.

Oracle increases or decreases the undo retention time for a given period of time based on the query with the longest execution time.

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.