[20211105]索引分裂塊清除日誌增加(唯一索引).txt
[20211105]索引分裂塊清除日誌增加(唯一索引).txt
--//連結http://blog.itpub.net/267265/viewspace-2840853/ 測試了索引分裂時遇到的奇怪現象。
--//看看唯一索引發生分裂時發生的情況。
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 unique 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
----------
329453
Session altered.
--//檢視轉儲檔案：
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
leaf: 0x10002b6 16777910 (-1: nrow: 578 rrow: 578)
leaf: 0x10002b7 16777911 (0: nrow: 422 rrow: 422)
----- end tree dump
--//可以發現唯一索引每塊插入的記錄更多，這是因為唯一索引rowid部分(不包括data_object_id資訊)6位元組在鍵值前面，沒有長度指示
--//器,這樣每條記錄節約1個位元組，能容納更多鍵值。可以看出插入id=579時出現分裂。
3.開始測試：
--//truncate table t1;
SCOTT@book> insert into t1 select rownum,rpad(rownum,100,'x') from dual connect by level<=578;
578 rows created.
SCOTT@book> commit;
Commit complete.
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_2257_0001.trc
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
329453
Session altered.
--//檢視轉儲檔案：
----- begin tree dump
leaf: 0x10002b3 16777907 (0: nrow: 578 rrow: 578)
----- end tree dump
--//注意不要提交。注感覺應該保留1箇中間值作為插入，快下班了。先這樣測試看看。
SCOTT@book> insert into t1 select 579,rpad(579,100,'y') from dual ;
1 row created.
--//注意不要提交。
SCOTT@book> select rowid,id from t1 where id in (1,578,579);
ROWID ID
------------------ ----------
AABQbvAAEAAAAIkAAA 1
AABQbvAAEAAAAK9AAx 578
AABQbvAAEAAAAK9AAy 579
--//可以看出id =1與後面插入的id=579,578記錄在不同一塊中。
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_2257_0002.trc
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
329453
Session altered.
--//檢視轉儲檔案：
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
leaf: 0x10002b6 16777910 (-1: nrow: 578 rrow: 578)
leaf: 0x10002b7 16777911 (0: nrow: 1 rrow: 1)
----- end tree dump
--//可以發現發生了索引塊分裂，一塊佔578條(鍵值id=1-5789)，另外一塊1條，也就是id=579插入發生在dba=0x10002b7塊中。
--//開啟新的會話session 1：
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 5407 2335 DEDICATED 2336 28 173 alter system kill session '58,5407' 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;
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 768 58
SCOTT@book> select * from t1 where id=578;
ID VC
---------- ----------------------------------------------------------------------------------------------------
578 578xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 768 58
--//日誌沒有增加。
SCOTT@book> select * from t1 where id=579;
no rows selected
--//看不見正常，因為沒有提交。
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 876 58
--//日誌增加，876-768 = 108.
SCOTT@book> select * from t1 where id=579;
no rows selected
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 984 58
--//日誌增加，984-876 = 108
SCOTT@book> select * from t1 where id=679;
no rows selected
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 984 58
SCOTT@book> select * from t1 where id=580;
no rows selected
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 984 58
--//從前面的測試可以確定似乎這個日誌產生與索引段相關。
--//僅僅謂詞條件id=579時才會產生日誌。
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 1092 58
SCOTT@book> select rowid from t1 where id=579;
no rows selected
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
redo size 194 1200 58
--//日誌增加,也可以確定在索引段中。
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3432 58
SCOTT@book> select * from t1 where id=578;
ID VC
------------ ----------------------------------------------------------------------------------------------------
578 578xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3432 58
--//日誌沒有增加。
SCOTT@book> select /*+ full(t1) */ * from t1 where id=578;
ID VC
------------ ----------------------------------------------------------------------------------------------------
578 578xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3540 58
--//日誌增加，3540 - 3432 = 108，全表掃描時出現。
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3540 58
SCOTT@book> select /*+ full(t1) */ * from t1 where id=1 and rownum=1;
ID VC
------------ ----------------------------------------------------------------------------------------------------
1 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3540 58
SCOTT@book> select /*+ full(t1) */ * from t1 where id=1 ;
ID VC
------------ ----------------------------------------------------------------------------------------------------
1 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ viewsessx 'redo size' 58
NAME STATISTIC# VALUE SID
------------------------------ ------------ ------------ ------------
redo size 194 3648 58
--//日誌增加。
--//測試到這裡有點亂,疏理一下思路。
--//首先查詢select * from t1 where id=:N; N=1-578 不會產生日誌，感覺這是唯一索引帶來的一點點好處。
--//:N=579會產生日誌，我想當然認為日誌的產生在索引段中。而後面的select /*+ full(t1) */ * from t1 where id=1 ;會產生日誌
--//，說明日誌的產生與表段相關. 亂。
--//仔細想一下當執行select * from t1 where id=579時，首先定位索引塊，透過undo重構索引塊沒有查詢到id=579的記錄，不用回表。
--//而全表掃描涉及全部資料塊，而且唯一索引的情況非常特殊select * from t1 where id=:N; N=1-578 不會產生日誌。
--//問題的源頭還是在於，oracle在掃描資料塊或者索引段如何知道索引塊發生了分裂,為什麼一些特殊情況下touch 對應資料塊以及索
--//引塊時要發生一次Block cleanout record，這樣設計的道理何在，那位給出合理的解析。
4.看看日誌轉儲內容。
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> select current_scn from v$database;
CURRENT_SCN
------------
13382426274
SCOTT@book> select rowid from t1 where id=579;
no rows selected
SCOTT@book> select /*+ full(t1) */ * from t1 where id=1 ;
ID VC
------------ ----------------------------------------------------------------------------------------------------
1 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
------------
13382426296
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_2336_0002.trc
SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo03.log' scn min 13382426274 scn max 13382426296;
System altered.
--//檢查轉儲：
*** 2021-11-05 17:30:18.019
REDO RECORD - Thread:1 RBA: 0x0004ab.0000ef2e.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0003.1da79eaa SUBSCN: 1 11/05/2021 17:29:17
(LWN RBA: 0x0004ab.0000ef2e.0010 LEN: 0001 NST: 0001 SCN: 0x0003.1da79eaa)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002b7 OBJ:329454 SCN:0x0003.1da79c9b SEQ:1 OP:4.1 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block cleanout record, scn: 0x0003.1da79eaa ver: 0x01 opt: 0x01, entries follow...
REDO RECORD - Thread:1 RBA: 0x0004ab.0000ef36.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0003.1da79eb3 SUBSCN: 1 11/05/2021 17:29:26
(LWN RBA: 0x0004ab.0000ef36.0010 LEN: 0001 NST: 0001 SCN: 0x0003.1da79eb3)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002bd OBJ:329455 SCN:0x0003.1da79e5b SEQ:1 OP:4.1 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block cleanout record, scn: 0x0003.1da79eb3 ver: 0x01 opt: 0x01, entries follow...
END OF REDO DUMP
--//0x010002b7 = set dba 4,695 = alter system dump datafile 4 block 695 = 16777911
SCOTT@book> select * from dba_objects where data_object_id=329454
2 @ prxx
==============================
OWNER : SCOTT
OBJECT_NAME : I_T1_ID
SUBOBJECT_NAME :
OBJECT_ID : 329453
DATA_OBJECT_ID : 329454
OBJECT_TYPE : INDEX
CREATED : 2021-11-05 16:56:44
LAST_DDL_TIME : 2021-11-05 17:01:04
TIMESTAMP : 2021-11-05:16:56:44
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 4
EDITION_NAME :
PL/SQL procedure successfully completed.
--//塊清除發生在索引上。
SCOTT@book> select * from dba_objects where data_object_id=329455
2 @ prxx
==============================
OWNER : SCOTT
OBJECT_NAME : T1
SUBOBJECT_NAME :
OBJECT_ID : 329446
DATA_OBJECT_ID : 329455
OBJECT_TYPE : TABLE
CREATED : 2021-11-05 10:04:02
LAST_DDL_TIME : 2021-11-05 17:01:04
TIMESTAMP : 2021-11-05:10:04:02
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 1
EDITION_NAME :
PL/SQL procedure successfully completed.
5.總結:
--//唯一索引分裂時估計估計影響小一點。
--//快下班了，測試沒有設計好，下個星期測試唯一索引插入中間，出現50－50分裂的情況。
--//還是非唯一索引測試索引分裂的疑問，oracle為什麼要這樣設計。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2841004/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- [20211108]索引分裂塊清除日誌增加(唯一索引)2.txt索引
- Oracle索引塊分裂split資訊彙總Oracle索引
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- 索引分裂的enq索引ENQ
- MongoDB之索引（唯一索引）MongoDB索引
- 唯一索引索引
- Oracle索引分裂（Index Block Split）Oracle索引IndexBloC
- MySQL ＜唯一索引＞MySql索引
- [20210604]索引分裂與 itl ktbitflg.txt索引
- 如何優雅的向MySQL唯一索引列增加值MySql索引
- 唯一索引和非唯一索引ROWID儲存的區別索引
- 資料庫索引分裂 問題分析資料庫索引
- MySQL實戰45講——普通索引和唯一索引MySql索引
- zt_如何確定index root block split索引塊分裂的時間點IndexBloC索引
- 唯一索引，可以在索引列插入多個null嗎索引Null
- 清除SqlServer日誌SQLServer
- mysql唯一索引是什麼MySql索引
- 筆記 mongo查詢慢日誌,建立索引筆記Go索引
- 如何選擇普通索引和唯一索引《死磕MySQL系列 五》索引MySql
- MYSQL中的普通索引,主健,唯一,全文索引區別MySql索引
- 轉載-treedump索引讀取索引儲存的資料值--非唯一性索引索引
- ORACLE 12c索引分裂引起的會話夯Oracle索引會話
- 關於唯一性索引造成堵塞和非唯一性索引造成堵塞的區別索引
- MySQL 事務、日誌、鎖、索引學習總結，MySql索引
- ELK日誌保留7天-索引生命週期策略索引
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（上）索引IndexORM
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（中）索引IndexORM
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（下）索引IndexORM
- Oracle主鍵、唯一鍵與唯一索引的區別Oracle索引
- 【Mongo】mongos shard 唯一索引的問題Go索引
- 主鍵和唯一索引的區別索引
- 唯一性索引優化實踐索引優化
- 唯一索引操作可能產生的鎖索引
- PG 12-2 B-Tree 索引 分析 分裂 level = 1索引
- 索引分裂造成的index contention等待事件的診斷索引Index事件
- [20210317]如何知道索引塊地址2.txt索引
- mysql清除binlog日誌MySql