Oracle 10g and later versions have a new feature that automatically adjusts the undo retention time, which is much simpler to manage, for auto-scaling (Autoextend on) undo tablespace, Parameters Undo_ The retention is set as the lowest threshold for Oracle Auto-tuning the undo retention. For non-auto-extended (autoextend off), non-guarantee undo Tablespace, Oracle will have historical information based on the undo tablespace size and V$undostat (whether the statistical undo information is by the implied parameter _collect_undo_ Stats, the default is true) the maximum possibility to retain the undo information. To maximize the reduction of errors like ORA-01555 occur. In this case, the undo retention is basically useless. The UNDO auto-optimization feature is turned on by default _undo_autotune =true. The optimized undo retention can be seen in V$undostat Tuned_undoretention, where Oracle writes a Unod table space usage record to v$undostat every 10 minutes, including tuned_ Undoretention.
Of course, this feature is controlled by the implicit parameter _undo_autotune, which is set to True by default, and in some special cases it is set to false, such as startup upgrade.
If the parameter is set to False,oracle does not adjust the undo retention size by itself, such as the size of the table space, the Undo retention setting hours is prone to ora-01555 errors. For example, the table space is large enough, but there will still be ora-01555.
The V$undostat view can be used in the Oracle 10g release to monitor the use of the undo table space by the current transaction in the instance. Each row in the view lists the statistics collected from the instance every 10 minutes. Each row represents a statistical snapshot of the usage of the undo tablespace, the amount of transactions, and the length of the query over the past 10-minute hours.
The usage of the undo tablespace varies depending on the transaction volume, and we generally refer to the average usage and peak usage of the undo table space at the same time as the calculation.
The following SQL statement is used to calculate the average usage of the undo tablespace in the last 7 * 24 hours:
Col undo_retention for A15
Col db_block_size for A15
Select ur undo_retention, dbs db_block_size, ((Ur * (UPS * DBS)) + (DBS *))/1024/1024 as "M_bytes"
From (select Value as ur from v$parameter where name = ' undo_retention '),
(Select (Sum (undoblks)/SUM (((end_time-begin_time) * 86400)), UPS from V$undostat),
(select Value as DBS from V$parameter where name = ' db_block_size ');
The following SQL statement calculates the amount of space required for the undo Tablespace as a peak:
Col undo_retention for A15
Col db_block_size for A15
Select ur undo_retention, dbs db_block_size, ((Ur * (UPS * DBS)) + (DBS *))/1024/1024 as "M_bytes"
From (select Value as ur from v$parameter where name = ' undo_retention '),
(Select (Undoblks/((end_time-begin_time) * 86400)) ups from V$undostat where undoblks on (select Max (undoblks) from v$ Undostat)),
(select Value as DBS from V$parameter where name = ' db_block_size ');
It is important to note that there are 2 undo table spaces in the case of RAC, depending on the actual situation, the above query is executed in 2 instances respectively.
In general, in order to maintain the normal operation of the daily business as much as possible, we recommend estimating and allocating the size of the undo table space according to peak conditions, although there is a waste of storage space, but you can avoid the problem caused by the lack of undo table space.
We can also use the dba_undo_extents view to monitor the use of the UNDO table space in real time:
Select sum (bytes)/1024/1024 MB, status, Tablespace_name
From Dba_undo_extents
Group BY status, Tablespace_name order by 3, 2;
The query returns the amount of space used to roll back information in each state grouped by status, which typically has three status states: Expired,unexpired,active. Active represents a transaction-related rollback information that is still active, unexpired indicates that although the transaction has ended but the rollback information has not been retained longer than the value set by the instance parameter undo_retention, expired indicates that the rollback information has been retained for more than Undo_ The value set by the retention.
In the case where the undo tablespace is not enabled with the Guarantee option (current usage), the rollback space allocation for the new transaction follows the following criteria:
A) find a rollback segment that does not have an active interval, and if not, create a new rollback segment that returns an error if the space does not allow a new segment to be generated.
b) If a rollback segment is selected, but the idle space is not sufficient to store the rollback information for the transaction, then it will attempt to create the interval, and if there is no space on the tablespace, it will go to the next step.
c) If you fail to create a new interval, it will search for the expired interval in the other rollback segments and reuse it.
d) If no expired interval is available in the other rollback segments, it will continue to search for the unexpired interval in the other rollback segments and reuse it, noting that the transaction does not reuse the unexpired interval in this rollback segment, so unexpired rollback space can only be partially reused for Oracle or return an error if you still don't get what you want.
When we observe that the active rollback information takes up a large amount of space, it indicates that the system is currently running a busy transaction. Because the guarantee option for the Undo tablespace is not enabled at this time, the rollback space of all expired and the unexpired part of the roll-back space can be used for Oracle multiplexing, where the active state rollback information is mainly observed when monitoring in realtime.
In the case of a system-dependent business, we can maximize the configuration of the undo table space by calculating the peak usage of the undo table space, and when the system is in the business tuning phase, such as when new business joins or business hours are adjusted, further real-time monitoring of undo tablespace usage is required. To meet dynamic adjustment requirements.
The following is a script that monitors undo.
V$undostat
Select inst_id, To_char (Begin_time, ' Yyyy/mm/dd HH24:MI:SS ') "BEGIN time", End_time, Undoblks, Txncount, unxpblkrelcnt, Activeblks, Unexpiredblks, expiredblks
From Gv$undostat order by 2;
See which undo segment a transaction is using
Select S.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 MB
From Dba_rollback_segs R, V$rollstat v,v$transaction t,v$session s
Where r.segment_id = V.usn and V.usn=t.xidusn and T.ADDR=S.TADDR
Order BY Segment_name;
Col undo_retention for A15
Col db_block_size for A15
Select ur undo_retention, dbs db_block_size, ((Ur * (UPS * DBS)) + (DBS *))/1024/1024 as "M_bytes"
From (select Value as ur from v$parameter where name = ' undo_retention '),
(Select (Sum (undoblks)/SUM (((end_time-begin_time) * 86400)), UPS from V$undostat),
(select Value as DBS from V$parameter where name = ' db_block_size ');
Col undo_retention for A15
Col db_block_size for A15
Select ur undo_retention, dbs db_block_size, ((Ur * (UPS * DBS)) + (DBS *))/1024/1024 as "M_bytes"
From (select Value as ur from v$parameter where name = ' undo_retention '),
(Select (Undoblks/((end_time-begin_time) * 86400)) ups from V$undostat where undoblks on (select Max (undoblks) from v$ Undostat)),
(select Value as DBS from V$parameter where name = ' db_block_size ');
The situation of various extent in UNDO
Select sum (bytes)/1024/1024 MB, status, Tablespace_name
From Dba_undo_extents
Group BY status, Tablespace_name order by 3, 2;
View undo Segemnts Size Dba_segments
Col segment_name for A30
Col OWNER for A8
Col Tablespace_name for A20
Select Tablespace_name, owner, Segment_name, bytes/1024/1024 mb
From dba_segments where tablespace_name like ' undotbs% ';
View undo Segemnts Size V$rollstat
Select Segment_name, v.rssize/1024/1024 MB
From Dba_rollback_segs R, V$rollstat V
Where r.segment_id = V.USN (+)
Order BY Segment_name;
Undo Segemnt Extent Info
Select Segment_name, Tablespace_name, R.status,
(initial_extent/1024) Initialextent, (next_extent/1024) nextextent,
Max_extents, V.curext curextent
From Dba_rollback_segs R, V$rollstat V
Where r.segment_id = V.USN (+)
Order BY Segment_name;
To view the undo situation for a transaction
Select S.sid, s.serial#, T.xidusn, T.status, t.used_ublk
From V$transaction T, V$session s
where t.addr = s.taddr;
View undo details for a transaction
Set Lines 199
Col STATUS for A8
Col USERNAME for A6
Col name for A25
Col substr (s.program,1,78) for A30
SELECT R.name,
D.tablespace_name,
S.sid,
s.serial#,
S.username,
T.status,
T.cr_get,
T.phy_io,
T.USED_UBLK,
T.noundo,
SUBSTR (s.program,1,78)
From Sys.v_$session s,sys.v_$transaction T,sys.v_$rollname r,dba_rollback_segs D
WHERE t.addr=s.taddr
and T.xidusn=r.usn
and D.segment_name= R.name
ORDER by T.cr_get,t.phy_io;
