Read consistency analysis: undo, read consistency: undo

Read consistency analysis: undo, read consistency: undo
The following uses the consistent read Analysis of undo:
[Oracle @ localhost ~] $ Lsb_release-
LSB Version: core-3.1-ia32: core-3.1-noarch: graphics-3.1-ia32: graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage

SQL> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

SQL> create table t (id number, name varchar2 (10); the table has been created.
Used time: 00: 00: 00.15
SQL> set timing off;
SQL> show user;
The USER is "HR"
SQL> insert into t values (1, 'A ');
One row has been created.

SQL> insert into t values (2, 'B ');
One row has been created.
SQL> commit;
Submitted.

SQL> update t set name = 'C' where id = 1;

1 row updated.
SQL> select * from t;


ID NAME
--------------------
1 c
2 B



Note that the task is not submitted.
Re-open a session: SQL> select * from t;


ID NAME
--------------------
1
2 B

At this time, we still read the data before the modification. Here a is read from the undo, and the following dump analyzes the process:
SQL> select t. *, rowid from t;


ID NAME ROWID
--------------------------------------
1 c AAASunAAEAAABuuAAA
2 B AAASunAAEAAABuuAAB

We can use the packages provided by oracle to obtain the data file number and block number of the first data:
SQL> show user;
USER is "SYS"
SQL> select dbms_rowid.rowid_relative_fno (rowid) fno, dbms_rowid.rowid_block_number (rowid) bno from hr. t;


FNO BNO
--------------------
4 7086
4 7086
In this case, we can dump 7,086th blocks of file 4:
SQL> alter system dump datafile 4 block 7086;


The system has been changed.
The following is an excerpt from some dump files: *** 15:26:34. 151
Block header dump: 0x01001bae
Object id on Block? Y
Seg/obj: 0x12ba7 csc: 0x00. 6658e2 itc: 2 flg: E typ: 1-DATA
Brn: 0 bdba: 0x1001ba8 ver: 0x01 opc: 0
Inc: 0 exflg: 0
 
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003. 017.0000098a 0x00c009ac. 02f0. 24 C --- 0 scn 0x0000. 0066582c
0x02 0x0008. 00d. 00000a84 0x00c02e17. 0258.28 ---- (transaction lock) 1 (lock a piece of data) fsc 0x0000.00000000 => the 0x2 column has been locked
Bdba: 0x01001bae uba: undo block address
Data_block_dump, data header at 0xa18264
====================
Tsiz: 0x1f98
Hsiz: 0x16
Pbl: 0x00a18264
76543210
Flag = --------
Ntab = 1
Nrow = 2
Frre =-1
Fsbo = 0x16
Fseo = 0x1f88
Avsp = 0x1f70
Tosp = 0x1f70
0xe: pti [0] nrow = 2 offs = 0
0x12: pri [0] offs = 0x1f90
0x14: pri [1] offs = 0x1f88
Block_row_dump:

Tab 0, row 0, @ 0x1f90
Tl: 8 fb: -- H-FL -- lb (Lock tag): 0x2 cc: 2 the second column of the First row has been locked by a transaction. Lb: lock byte
Col 0: [2] c1 02 => This column is read from the 7086 block of file 4
Col 1: [1] 63 63 indicates c, which has been changed

Tab 0, row 1, @ 0x1f88
Tl: 8 fb: -- H-FL -- lb: 0x0 cc: 2
Col 0: [2] c1 03
Col 1: [1] 62

End_of_block_dump
End dump data blocks tsn: 4 file #: 4 minblk 7086 maxblk 7086

In the second session, the second column in the first row is a, which is read from the 7086 blocks of file 4. However, the second column is locked and cannot be read.
We can use the address provided by uba to obtain the location where data a was stored after modification:

Uba:
0x00c02e17. 0258.28
First, convert the hexadecimal number to the decimal number. The decimal package provided by oracle can obtain the file number and block number:
SQL> select to_number ('00c02e17 ', 'xxxxxxxxxxxxxxxxxxxxxxxx') from dual;


TO_NUMBER ('00c02e17 ', 'xxxxxxxxxxxxxxxxxxxxxxxx ')
------------------------------------------
12594711

SQL> select dbms_utility.data_block_address_file (12594711) from dual;


DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (12594711)
----------------------------------------------
3

SQL> select dbms_utility.data_block_address_block (12594711) from dual;


DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (12594711)
-----------------------------------------------
11799

In the second session, oracle prompts that reading the second column of the First row must be read on block 11,799th of file 3:
SQL> alter system dump datafile 3 block 11799;

The system has been changed.
The following are partial dump files:
Uba: 0x00c02e17. 0258.25 ctl max scn: 0x0000.00665307 prv tx scn: 0x0000.00665325
Txn start scn: 0x0000. 006658a2 logon user: 91
Prev brb: 12594708 prev bcl: 0
KDO undo record:
KTB Redo
Op: 0x03 ver: 0x01
Compat bit: 4 (post-11) padding: 1
Op: Z
Array Update of 1 rows:
Tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 191
Ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
X type: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01001bae hdba: 0x01001baa
Itli: 2 ispac: 0 maxfr: 4858
Vect = 3
Col 1: [1] 61
 
End dump data blocks tsn: 2 file #: 3 minblk 11799 maxblk 11799

So we read 61 (representing a) through undo)
Let's check what type of data file 3 is:
Select * from dba_data_files where file_id = 3;

Is an undo data file.
SQL> show parameter undo;


NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1

The default value is the undotbs1 tablespace. Many undo tablespaces can be created, but only one undo tablespace is used for an instance.
The undo tablespace is composed of undo segments. Check the number of undo segments:
Select * from dba_rollback_segs;

Note that in the above owner column, if it is public, the undo segment created by this instance can be used by other database instances, but sys represents the private undo segment, it can only be used by the creator of the undo segment.
The status of the undo segment is missing. Now the default undo tablespace is undotbs1, so the undo segments are all online, and the undo segments of the undo_w tablespace are all offline.
You can set the default undo tablespace by modifying the undo_tablespace parameter.
Oracle monitors undo segments online. view the following in view v $ rollstat:
 
There are a total of 11 above, and usn is the undo segment number
A transaction uses an undo segment
Run the following thing:
SQL> update t set name = 'C' where id = 1;


1 row updated.

Select * from v $ transaction;

Note that the XIDUSN column indicates the undo segment number. In this case, the transaction uses the No. 10 undo segment.
View the undo Section 10:
Select * from v $ rollstat;
The XACTS column indicates the number of active transactions in the undo segment on the 10 th.
Modify the default undo tablespace:
SQL> alter system set undo_tablespace = undo_w;


The system has been changed.


SQL> show parameter undo;


NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDO_W
Check the status of the undo segment:
Select * from dba_rollback_segs;
The undo segment in undotbs1 is offline except for the 10th segment because it is used. After the transaction ends, it automatically becomes offline.
View the View:
Select * from v $ rollstat;

The status of the undo segment on the 10th is pending offline (pending is waiting... Period)
I doubt how many transactions can be used in each rollback segment?
SQL> show parameter roll


NAME TYPE VALUE
-----------------------------------------------------------------------------
Fast_start_parallel_rollback string LOW
Rollback_segments string
Transactions_per_rollback_segment integer 5

It can be used by five transactions, but this is before the undo tablespace is not automatically managed, since the undo tablespace is automatically managed, this parameter does not work.
An undo segment can only be used by one transaction. If undo is used up, oracle background process smon automatically creates an undo segment.
If a rollback segment is used by multiple transactions, the undo segment header will wait, affecting the concurrency. We can view the wait event through view V $ WAITSTAT:
Select * from v $ waitstat;

You can execute multiple transactions, simulate smon to automatically create undo segments, and simulate the experiment on your own. (The undo segment created by smon will not be reclaimed because the transaction ends)
Next, let's take a look at a very important parameter: the unit of undo_retention is second (indicating the time when the data in the undo is retained after the transaction is committed)
SQL> show parameter undo;


NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDO_W
SQL> show parameter roll
A very famous oracle error: ORA-01555 (snapshot too old)
In actual cases, the query takes place before modification, but is relatively small.
This error may occur:
The undo tablespace is too small.
Data Query takes too long (poor SQL query performance)
Undo_partition ention is too small
We use the dba_tablespaces view to produce a parameter:
SELECT TABLESPACE_NAME, retention from DBA_TABLESPACES;

In the retention column, the default value of the undo tablespace is NOGUARANTEE, but we can modify this parameter to retain it forcibly.
SQL> alter tablespace undotbs1 retention GUARANTEE;


The tablespace has been changed.

900 seconds will be retained.
When the undo_rentention can be modified

SQL & gt; alter system set undo_retention = 1200;


The system has been changed.
The three situations that appear in the ORA-01555 listed above have a relationship between the three, such as the increase of undo_tablespace ention, thus requiring more undo tablespaces.
Oracle provides the undo advisor. Based on this, we can balance these relationships and enter the OEM:
[Oracle @ localhost ~] $ Emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996,200 9 Oracle Corporation. All rights reserved.
Https: // diy_ OS: 1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control .......

After entering em, this figure is provided on the automatic management interface:

Determine the size of the required undo tablespace based on the size of the undo_tablespace ention. You can change the analysis period and provide a graphic interface.
How is a graph made?
There is a formula:
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
UR: undo_assistention in seconds
UPS: number of undo data blocks generated per second
DBS: overhead varies based on extent and file size (db_block_size) is actually the block size: show parameter db_block;
Based on an oracle view: v $ undostat;

Calculate the number of undo tasks every ten minutes. You can draw the above image based on this view.