_oracle System _ with the Tang notes 012-undo

The undo segment in the Undo table space is automatically generated, and Oracle automatically uses the undo segment of the Undo table space. As a high-level DBA, you need to understand how Oracle uses the Undo segment. This will solve the performance problem.1.Undo table space and management method

Show parameter undo_tablespaceshow parameter undo

Undo Segment

SELECT * from V$rollname;

Select Segment_name,blocks,extents from dba_segments where segment_name= ' _syssmu1$ ';

Select Segment_name,tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where Segment_name= ' _SYSSMU1 $ ';

Select owner, Segment_name, Tablespace_name from Dba_rollback_segs;

Select name from V$parameter where name is like '%undo% ';

Undo Tablespace space Usage and data file location

Select file_name,bytes/1024/1024 from Dba_data_fileswhere tablespace_name like '%undotbs% ';

Undo's three effects ① things rollback (rollback)
② read consistency, constructing CR blocks
③ instance recovery, rollback of uncommitted things
Four states of the central part of the undo segment
Free
Expired
Inactive
Active
Show Undo Area Information

SELECT extent_id, Bytes, status from Dba_undo_extentswhere segment_name= ' _syssmu1$ ';

Undo_retention (the state of the undo segment is the retention time of the inactive state) the following parameters function mainly for the overwrite of the state of the undo segment in the inactive state: Guarantee for forced retention Undo_ Retention the inactive state of the time zone

Alter TABLESPACE UNDOTBS1 retention guarantee;

Alter tablespace UNDOTBS1 retention noguarantee;

2. Diagram the operation flow of a thing:

Select XID, Xidusn, Xidslot, Xidsqn, Ubablk, ubafil from V$transaction;

----Summary: ITL UBA points to the last undo block address of Data block, which points between the undo blocks for rollback operations, the Data block header directly points to the undo block, in order to construct the CR block faster In ITL, XID points to the undo segment header for a variety of submission methods. After the beginning of a thing, at least two places to write things information: 1. The segment header block of the rollback segment Things Table2. The header of the data block to be modified things TroughPosition, find a thing slot and write the information about things.3.IMU mechanismThe traditional undo,oracle treats the undo table space as equal to the normal table space. Now, with the IMU technology, after the start of the thing, the need to rollback the block is no longer required to be transferred from the disk, directly in the shared pool in the IMU buffer read. The IMU significantly improves the speed of the construction of the CR block, which also increases the rate of CR reading. In RAC and stream environments, the IMU is banned. Moreover, IMU technology is not being touted by Oracle.

SELECT * from V$sysstat where name is like '%imu% ';

--The above results of SQL query can determine whether the IMU is open, focus on the query results IMU commits and imu flushes value, if it has been increasing, indicating that the database opened the IMU technology.4. Read consistencyFor example: 9:00 starts querying a table that returns records from a 1w bar. 9:01 other users deleted the 1w record and submitted it. 9:02 the query ends, should the result be 1w or 9,999? According to the previous knowledge, may be 9,999, but in fact, Oracle is returning 1w records, which is based on the actual requirements of the business. The introduction of new knowledge Points: The query will be the current state of the snapshot, recording a current SCN, that is, after the query process if it is found that the SCN number is greater than the current SCN, this block in the query process has been modified. ORA-015551.sql execution time is too long, 2.undo table space pressure is too large, 3.undo table space size settings, depending on the longest time to execute SQL, and need to flash back the window. Then according to these specific requirements in the EM undo table space in the proposed figure based on the time to find the corresponding undo table space should be set to how much reference value.