[20211105]索引分裂 塊清除 日誌增加.txt
[20211105]索引分裂 塊清除 日誌增加.txt
--//題目起的有點怪，只是我昨天在測試時遇到的怪問題，我透過測試環境演示出來。
--//當非主鍵索引發生分裂時發生的情況。
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.首先確定索引分裂發生的位置：
SCOTT@book> create table t1 (id number,vc varchar2(100));
Table created.
SCOTT@book> create index i_t1_id on t1(id);
Index created.
SCOTT@book> insert into t1 select rownum,rpad(rownum,100,'x') from dual connect by level<=1e3;
1000 rows created.
SCOTT@book> commit ;
Commit complete.
--//分析略。注意不要遺漏這步，避免查詢取樣問題的影響。
$ cat treedump.sql
column object_id new_value m_index_id
select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX';
alter session set events 'immediate trace name treedump level &m_index_id';
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
329447
Session altered.
--//檢視轉儲檔案：
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
leaf: 0x10002b6 16777910 (-1: nrow: 540 rrow: 540)
leaf: 0x10002b7 16777911 (0: nrow: 460 rrow: 460)
----- end tree dump
--//檢查轉儲，可以發現分裂發生在插入id=541值的情況。
3.開始測試：
--//truncate table t1;
SCOTT@book> insert into t1 select rownum,rpad(rownum,100,'x') from dual connect by level<=540;
540 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60241_0001.trc
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
329447
Session altered.
--//檢視轉儲檔案：
----- begin tree dump
leaf: 0x10002b3 16777907 (0: nrow: 540 rrow: 540)
----- end tree dump
--//插入資料，為了實現50-50分裂，我不插入最大值。而是中間值100,注意不要提交。
SCOTT@book> insert into t1 select 100,rpad(100,100,'x') from dual ;
1 row created.
SCOTT@book> select rowid,id from t1 where id in (1,100,540,539);
ROWID ID
------------------ ----------
AABQbpAAEAAAAIkAAA 1
AABQbpAAEAAAAIlAAh 100
AABQbpAAEAAAAK9AAM 100
AABQbpAAEAAAAK9AAK 539
AABQbpAAEAAAAK9AAL 540
--//可以看出id =1,id = 100記錄在不同塊中，後面id=539,540以及插入id=100的記錄在同一塊中。
--//開啟新的會話：
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60301_0001.trc
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
329447
Session altered.
--//檢視轉儲檔案：
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
leaf: 0x10002b6 16777910 (-1: nrow: 279 rrow: 279)
leaf: 0x10002b7 16777911 (0: nrow: 262 rrow: 262)
----- end tree dump
--//可以發生了索引塊分裂，一塊佔279條(鍵值id=1-279)，另外一塊262條。也就是id=100插入發生在dba=0x10002b6塊中。
--//開啟新的會話session 1：
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 5405 60300 DEDICATED 60301 28 172 alter system kill session '58,5405' immediate;
--//記下sid=58.
$ cat viewsessx.sql
column name format a70
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
FROM v$sesstat a, v$statname b
WHERE lower(b.NAME) like lower('%&1%') AND a.statistic# = b.statistic# and a.sid='&&2'
and a.value>0;
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
--------- ---------- ---------- ----------
redo size 194 752 58
--//session 1:
SCOTT@book> select * from t1 where id=540;
ID VC
---------- ----------------------------------------------------------------------------------------------------
540 540xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//session 2:
SCOTT@book> @viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
---------- ---------- ---------- ----------
redo size 194 1004 58
--//可以發現日誌增加 1004-752 = 252.
--//session 1:
SCOTT@book> select * from t1 where id=540;
ID VC
---------- ----------------------------------------------------------------------------------------------------
540 540xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
--------- ---------- ---------- ----------
redo size 194 1112 58
--//可以發現日誌會再次增加 1112-1004 = 108.
--//session 1:
SCOTT@book> select rowid from t1 where id=540;
ROWID
------------------
AABQbpAAEAAAAK9AAL
SCOTT@book> @ rowid AABQbpAAEAAAAK9AAL
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
329449 4 701 11 0x10002BD 4,701 alter system dump datafile 4 block 701 ;
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
--------- ---------- ------------ ------------
redo size 194 1112 58
--//透過索引定位不回表並沒有產生日誌，可以看出日誌的產生與探察相應資料塊有關。
--//session 1:
SCOTT@book> select /*+ full(t1) */ rowid from t1 where id=540;
ROWID
------------------
AABQbpAAEAAAAK9AAL
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
---------- ---------- ------------ ------------
redo size 194 1284 58
--//可以看出只要訪問到相應資料塊就會出現產生日誌。
4.看看日誌轉儲內容。
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
-----------
13382392662
sqlplus scott/book <<EOF
$ (seq 1000 | xargs -IQ echo 'select vc from t1 where id=539;')
EOF
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
-----------
13382394178
SCOTT@book> @ logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 1193 52428800 512 1 YES INACTIVE 13377207280 2021-11-04 22:00:26 13377254274 2021-11-05 08:29:41
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 1194 52428800 512 1 YES INACTIVE 13377254274 2021-11-05 08:29:41 13382389878 2021-11-05 09:52:40
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 1195 52428800 512 1 NO CURRENT 13382389878 2021-11-05 09:52:40 2.814750E+14
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//當前日誌是/mnt/ramdisk/book/redo03.log.
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60316_0001.trc
SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo03.log' scn min 13382392662 scn max 13382394178;
System altered.
--//檢查轉儲：
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo03.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0003.1da71b56 (13382392662) thru scn: 0x0003.1da72142 (13382394178)
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=50532=0xc564, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000001195, SCN 0x00031da71076-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000004ab hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.1da71076 (13382389878) 11/05/2021 09:52:40
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.1da71076 (13382389878) 11/05/2021 09:52:40
Disk cksum: 0x6b9e Calc cksum: 0x6b9e
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 3d4b0b67edc7ae87867f8a8a286fd4
redo log key flag is 5
Enabled redo threads: 1
...
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253a.0090 LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da72128 SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da72127 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da72128 ver: 0x01 opt: 0x01, entries follow...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253a.00d0 LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da72129 SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da72128 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da72129 ver: 0x01 opt: 0x01, entries follow...
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253a.0110 LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da7212a SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da72129 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da7212a ver: 0x01 opt: 0x01, entries follow...
...
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253b.01c4 LEN: 0x0060 VLD: 0x01
SCN: 0x0003.1da7212c SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0003.1da7212b SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0021 sqn: 0x00002e8a srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c04739.038a.09 ext: 2 spc: 7316 fbi: 0
REDO RECORD - Thread:1 RBA: 0x0004ab.00002545.0010 LEN: 0x0084 VLD: 0x05
SCN: 0x0003.1da72136 SUBSCN: 1 11/05/2021 10:26:43
(LWN RBA: 0x0004ab.00002545.0010 LEN: 0001 NST: 0001 SCN: 0x0003.1da72135)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 4841Kb in 0.14s => 33.77 Mb/sec
Total redo bytes: 5119Kb Longest record: 16Kb, moves: 2/5518 moved: 0Mb (0%)
Longest LWN: 621Kb, reads: 2569
Last redo scn: 0x0003.1da72142 (13382394178)
Change vector header moves = 732/9665 (7%)
$ grep -B1 'Block cleanout record' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60316_0001.trc | grep OBJ:329449|wc
1001 12012 99099
--//奇怪怎麼多了1次。
$ grep -B1 'Block cleanout record' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60316_0001.trc | grep -A1 OBJ:329449|head -7
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71abe SEQ:1 OP:4.1 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block cleanout record, scn: 0x0003.1da71d42 ver: 0x01 opt: 0x01, entries follow...
--
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71d42 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da71d43 ver: 0x01 opt: 0x01, entries follow...
--
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71d43 SEQ:1 OP:4.1 ENC:0 RBL:0
--//0x010002bd = set dba 4,701 = alter system dump datafile 4 block 701 = 16777917
SCOTT@book> select * from dba_objects where data_object_id=329449;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ---------- - - - ------------ ------------
SCOTT T1 329446 329449 TABLE 2021-11-05 10:04:02 2021-11-05 10:08:48 2021-11-05:10:04:02 VALID N N N 1
--//塊清除發生在表塊上 dba=0x010002bd。參考前面select rowid from t1 where id=540;的輸出。
--//0x010002bd = set dba 4,701 = alter system dump datafile 4 block 701 = 16777917
5.換另外的方式看看日誌轉儲內容。
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60316_0003.trc
SCOTT@book> ALTER SYSTEM DUMP LOGFILE '/mnt/ramdisk/book/redo03.log' DBA MIN 4 701 DBA MAX 4 701 scn min 13382392662 scn max 13382394178;
System altered.
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo03.log'
Opcodes *.*
DBAs: (file # 4, block # 701) thru (file # 4, block # 701)
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0003.1da71b56 (13382392662) thru scn: 0x0003.1da72142 (13382394178)
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=50532=0xc564, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000001195, SCN 0x00031da71076-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000004ab hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.1da71076 (13382389878) 11/05/2021 09:52:40
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.1da71076 (13382389878) 11/05/2021 09:52:40
Disk cksum: 0x6b9e Calc cksum: 0x6b9e
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 3d4b0b67edc7ae87867f8a8a286fd4
redo log key flag is 5
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x0004ab.000024b8.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0003.1da71d42 SUBSCN: 1 11/05/2021 10:26:35
(LWN RBA: 0x0004ab.000024b8.0010 LEN: 0133 NST: 0001 SCN: 0x0003.1da71d42)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71abe SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da71d42 ver: 0x01 opt: 0x01, entries follow...
REDO RECORD - Thread:1 RBA: 0x0004ab.000024b8.007c LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da71d43 SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71d42 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da71d43 ver: 0x01 opt: 0x01, entries follow...
REDO RECORD - Thread:1 RBA: 0x0004ab.000024b8.00bc LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da71d44 SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da71d43 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da71d44 ver: 0x01 opt: 0x01, entries follow...
....
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253a.00d0 LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da72129 SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da72128 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da72129 ver: 0x01 opt: 0x01, entries follow...
REDO RECORD - Thread:1 RBA: 0x0004ab.0000253a.0110 LEN: 0x0040 VLD: 0x01
SCN: 0x0003.1da7212a SUBSCN: 1 11/05/2021 10:26:35
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329449 SCN:0x0003.1da72129 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn: 0x0003.1da7212a ver: 0x01 opt: 0x01, entries follow...
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 5119Kb in 0.06s => 83.32 Mb/sec
Total redo bytes: 5119Kb Longest record: 16Kb, moves: 2/5518 moved: 0Mb (0%)
Longest LWN: 621Kb, reads: 2569
Last redo scn: 0x0003.1da72142 (13382394178)
Change vector header moves = 732/9665 (7%)
----------------------------------------------
6.再看看看索引分裂另外一塊的情況：
SCOTT@book> select rowid from t1 where id=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
SCOTT@book> @ rowid AABQbpAAEAAAAIkAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
329449 4 548 0 0x1000224 4,548 alter system dump datafile 4 block 548 ;
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137472 58
--//session 1:
SCOTT@book> select rowid from t1 where id=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137580 58
--//可以發現這次redo再次增加，137580-137472 = 108,注意我的查詢僅僅訪問索引。換成全表掃描呢？
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137688 58
--//session 1:
SCOTT@book> select /*+ full(t1) */ rowid from t1 where id=1 and rownum=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
--//注：加入條件rownum=1;避免掃描全部塊，這樣會掃描到id=540的塊，測試出現偏差。
--//session 2:
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137688 58
--//日誌沒有增加。也就是導致日誌增加的情況發生在索引上。
--//看看日誌內容:
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
------------
13382419257
SCOTT@book> select rowid from t1 where id=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
------------
13382419277
SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo03.log' scn min 13382419257 scn max 13382419277;
System altered.
--//檢視轉儲：
*** 2021-11-05 16:04:44.952
REDO RECORD - Thread:1 RBA: 0x0004ab.0000c576.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0003.1da7834a SUBSCN: 1 11/05/2021 16:04:22
(LWN RBA: 0x0004ab.0000c576.0010 LEN: 0001 NST: 0001 SCN: 0x0003.1da7834a)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002b6 OBJ:329448 SCN:0x0003.1da77e55 SEQ:1 OP:4.1 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block cleanout record, scn: 0x0003.1da7834a ver: 0x01 opt: 0x01, entries follow...
END OF REDO DUMP
--//0x010002b6 = set dba 4,694 = alter system dump datafile 4 block 694 = 16777910
SCOTT@book> select * from dba_objects where data_object_id=329448;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT I_T1_ID 329447 329448 INDEX 2021-11-05 10:04:24 2021-11-05 10:08:48 2021-11-05:10:04:24 VALID N N N 4
--//塊清除發生在索引上。你可能問為什麼，一些細節我自己也說不上來。
6.簡單總結：
--//我給出我自己的理解：
--//首先索引分裂是一個遞迴事務這個操作已經提交，不會回滾。
--//當查詢select * from t1 where id=540;時，透過索引定位資料塊，注意索引分裂已經發生，但是對應該索引塊的事務已經提交不會
--//再回滾。另外我設計插入的id=100,這個事務不發生在該分裂索引塊中，該索引塊不會重構，而探查表時插入id=100與id＝540的記錄
--//在同一塊中，該資料塊需要重構，我不理解為什麼oracle會在這樣的情況下做一次塊清除操作，而且如果該事務不提交，每次都會做
--//一次塊清除。
--//當查詢select rowid from t1 where id=1時，訪問的是分裂索引塊的另外一塊，該索引塊分裂後還做一個事務就是插入id=100,
--//這樣該索引塊重構，再次出現一次塊清除操作，但是這次發生在索引塊中。
--//你可以做一個想像，當dml插入記錄導致索引塊分裂時，如果有應用大量透過索引訪問涉及到對應表塊以及索引塊時有可能出現大量
--//塊清除日誌，只要事務不提交，塊清除日誌不斷出現。
--//我不知道oracle為什麼要這樣設計，當索引分裂時，會出現Block cleanout record操作,那位給一些建議。
7.補充：
--//如果提交後這樣的情況就不會出現。提交事務看看。
SCOTT@book> insert into t1 select 100,rpad(100,100,'x') from dual ;
1 row created.
SCOTT@book> commit ;
Commit complete.
--//session 2：
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137688 58
SCOTT@book> select rowid from t1 where id=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 137688 58
--//redo沒有增加。
SCOTT@book> select * from t1 where id=540;
ID VC
---------- ----------------------------------------------------------------------------------------------------
540 540xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 137688 58
--//redo沒有增加。
--//有機會看看主鍵或者唯一索引的情況。
--//再插入1次不提交。
SCOTT@book> insert into t1 select 100,rpad(100,100,'y') from dual ;
1 row created.
--//session 2：
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 137688 58
SCOTT@book> select * from t1 where id=540;
ID VC
---------- ----------------------------------------------------------------------------------------------------
540 540xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> select rowid from t1 where id=1;
ROWID
------------------
AABQbpAAEAAAAIkAAA
SCOTT@book> select * from t1 where id=100;
ID VC
---------- ----------------------------------------------------------------------------------------------------
100 100xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
100 100xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 137688 58
--//日誌沒有增加。
--//也就是這樣的Block cleanout record僅僅發生在索引塊分裂的時候。實際上就產生疑問，oracle在掃描資料塊時知道發生了索引塊
--//分裂,為什麼每次touch 對應資料塊時要發生一次Block cleanout record，這樣設計的道理何在，那位給出合理的解析。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2840853/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211105]索引分裂塊清除日誌增加(唯一索引).txt索引
- [20211108]索引分裂塊清除日誌增加(唯一索引)2.txt索引
- [20210603]如何跟蹤索引分裂.txt索引
- [20210604]索引分裂與 itl ktbitflg.txt索引
- Oracle索引塊分裂split資訊彙總Oracle索引
- [20181225]如何清除註冊的線上日誌.txt
- [20180626]延遲塊清除與只讀表.txt
- metaspolit下UAC提權以及日誌清除
- 日誌追蹤：log增加traceId
- [20210317]如何知道索引塊地址2.txt索引
- windows10系統如何清除事件日誌Windows事件
- [20181217]ogg抽取日誌分析.txt
- go fiber: 增加訪問日誌accesslogGo
- [20150409]只讀表空間與延遲塊清除.txt
- 資料庫索引分裂 問題分析資料庫索引
- [20180829]減少日誌生成量.txt
- ELK日誌定期清理 ES索引資料索引
- 物化檢視日誌無法正常清除的解決方法
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- 筆記 mongo查詢慢日誌,建立索引筆記Go索引
- [20190910]索引分支塊中TERM使用什麼字元表示.txt索引字元
- [20181116]18c DML 日誌優化.txt優化
- [20180625]簡單計算日誌生成率.txt
- 20211105BouncyCastleAST
- ELK日誌保留7天-索引生命週期策略索引
- MySQL 事務、日誌、鎖、索引學習總結，MySql索引
- 塊清除（block clean out）BloC
- Oracle Block Cleanouts 塊清除OracleBloC
- ORACLE 12c索引分裂引起的會話夯Oracle索引會話
- 如何清除建立失敗的索引索引
- TXT文字Log日誌分割工具(附工具連結)
- [20221121]rman刪除歸檔日誌問題.txt
- [20220826]顯示alert日誌檔案全路徑.txt
- [20210623]完善清除aud指令碼.txt指令碼
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- PG 12-2 B-Tree 索引 分析 分裂 level = 1索引
- [20181112]11g 日誌傳輸壓縮模式.txt模式