[20211011]跟蹤freespace空間的變化情況.txt

[20211011]跟蹤freespace空間的變化情況.txt

--//上個星期做了[20211009]8K資料庫最大行號補充.txt的測試，我發現利用我寫的指令碼可以測試freespace的情況。
--//我想測試pctfree＝10的情況下，oracle會保留多少空間為dml操作。
--//但是我測試遇到一個我有點無法理解的情況.透過例子說明:

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 t2 (a varchar2(1)) SEGMENT CREATION IMMEDIATE pctfree 10;
Table created.

$ cat test2.txt
select systimestamp from dual;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;

declare
 v_newrowid rowid;
begin
 for i in 1..&&1 loop
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
 end loop;
end;
/

declare
 v_newrowid rowid;
begin
 for i in 1..&&2 loop
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
   execute immediate 'alter system checkpoint';
   sys.dbms_lock.sleep(0.2);
 end loop;
 insert into t2 values(NULL);
 commit;
end;
/
select systimestamp from dual;
alter system checkpoint;

3.測試：
SCOTT@book> @ test2.txt 2014 0
SYSTIMESTAMP
---------------------------------------------------------------------------
2021-10-11 08:59:44.647636 +08:00
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SYSTIMESTAMP
---------------------------------------------------------------------------
2021-10-11 08:59:44.926981 +08:00
System altered.

SCOTT@book> select rowid,t2.* from t2;
ROWID              A
------------------ -
AAAW5vAAEAAAALkAfe

SCOTT@book> @ rowid AAAW5vAAEAAAALkAfe
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     93807          4        740       2014  0x10002E4           4,740                alter system dump datafile 4 block 740 ;

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system checkpoint ;
System altered.

--//透過bbed觀察
BBED> set dba 4,740
        DBA             0x010002e4 (16777956 4,740)

BBED> map
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740                                   Dba:0x010002e4
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2015]                             @118
 ub1 freespace[9]                           @4148
 ub1 rowdata[4031]                          @4157
 ub4 tailchk                                @8188

--//很明顯有一種感覺PCTFREE設定無效.因為freespace空間等於9.
--//也就是在一個事務裡面空間回收時不做這樣的監測,還是我做的例子很特殊.

4.繼續測試:
--//drop table t2 purge;
SCOTT@book> create table t2 (a char(5)) SEGMENT CREATION IMMEDIATE pctfree 10;
Table created.
--//注意欄位型別char(5).

--//修改指令碼如下,取消delete操作.
$ cat test3.txt
select systimestamp from dual;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;

declare
 v_newrowid rowid;
begin
 for i in 1..&&1 loop
   insert into t2 values('a') returning rowid into v_newrowid;
-- delete from t2 where rowid=v_newrowid;
-- commit;
 end loop;
end;
/

declare
 v_newrowid rowid;
begin
 for i in 1..&&2 loop
   insert into t2 values('b') returning rowid into v_newrowid;
-- delete from t2 where rowid=v_newrowid;
-- commit;
   execute immediate 'alter system checkpoint';
   sys.dbms_lock.sleep(0.2);
 end loop;
 insert into t2 values('c');
 commit;
end;
/
select systimestamp from dual;
alter system checkpoint;

--//如果不註解delete,測試與前面一樣.大家可以自行測試.
--//我測試過插入1條時freespace=8058,8058+11 = 8069,8069*.9 = 7262.1,8069*.9
--//7262/11 = 660.18181818181818181818

--//開啟另外視窗執行如下,裡面的dba 4,740可以先註解前面指令碼的sleep等內容執行1次來確定.然後刪除表重建，
--//我的測試環境僅僅我一個人操作，只要回話沒有退出，插入的dba地址不會變。
--//先執行如下,然後在執行test3.txt指令碼，
$ seq 40 | xargs -IQ echo "echo map dba 4,740 | bbed  parfile=bbed.par cmdfile=cmd.par | grep freespace | ts.awk; sleep 0.2"| bash | tee /tmp/test3.txt

SCOTT@book> @ test3.txt 650 20

$ seq 40 | xargs -IQ echo "echo map dba 4,740 | bbed  parfile=bbed.par cmdfile=cmd.par | grep freespace | ts.awk; sleep 0.2"| bash | tee /tmp/test3.txt
[2021-10-11 09:53:37]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:37]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:38]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:38]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:38]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:38]  ub1 freespace[8058]                        @120
[2021-10-11 09:53:38]  ub1 freespace[909]                         @1420
[2021-10-11 09:53:39]  ub1 freespace[898]                         @1422
[2021-10-11 09:53:39]  ub1 freespace[887]                         @1424
[2021-10-11 09:53:39]  ub1 freespace[876]                         @1426
[2021-10-11 09:53:39]  ub1 freespace[865]                         @1428
[2021-10-11 09:53:40]  ub1 freespace[854]                         @1430
[2021-10-11 09:53:40]  ub1 freespace[843]                         @1432
[2021-10-11 09:53:40]  ub1 freespace[832]                         @1434
[2021-10-11 09:53:40]  ub1 freespace[821]                         @1436
[2021-10-11 09:53:40]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:41]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:41]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:41]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:41]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:42]  ub1 freespace[810]                         @1438
[2021-10-11 09:53:42]  ub1 freespace[810]                         @1438
--//剩餘810位元組不再插入.

5.增加INITRANS數量呢?

--//drop table t2 purge;
create table t2 (a char(5)) SEGMENT CREATION IMMEDIATE pctfree 10 INITRANS 10;

--//重複測試:
SCOTT@book> @ test3.txt 630 40

$ seq 40 | xargs -IQ echo "echo map dba 4,740 | bbed  parfile=bbed.par cmdfile=cmd.par | grep freespace | ts.awk; sleep 0.2"| bash | tee /tmp/test3.txt
[2021-10-11 10:08:17]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:18]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:18]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:18]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:18]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:18]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:19]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:19]  ub1 freespace[937]                         @1572
[2021-10-11 10:08:19]  ub1 freespace[915]                         @1576
[2021-10-11 10:08:19]  ub1 freespace[904]                         @1578
[2021-10-11 10:08:20]  ub1 freespace[893]                         @1580
[2021-10-11 10:08:20]  ub1 freespace[882]                         @1582
[2021-10-11 10:08:20]  ub1 freespace[871]                         @1584
[2021-10-11 10:08:20]  ub1 freespace[860]                         @1586
[2021-10-11 10:08:20]  ub1 freespace[849]                         @1588
[2021-10-11 10:08:21]  ub1 freespace[838]                         @1590
[2021-10-11 10:08:21]  ub1 freespace[827]                         @1592
[2021-10-11 10:08:21]  ub1 freespace[816]                         @1594
[2021-10-11 10:08:21]  ub1 freespace[805]                         @1596
[2021-10-11 10:08:22]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:22]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:22]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:22]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:22]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:23]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:23]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:23]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:23]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:24]  ub1 freespace[794]                         @1598
[2021-10-11 10:08:24]  ub1 freespace[794]                         @1598
--//剩餘794位元組不再插入.
--//也就是ITL槽數量影響會改變保留自由空間的大小,但是影響不大.

BBED> map dba 4,740
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740                                   Dba:0x010002e4
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 264 bytes                    @20
 struct kdbh, 14 bytes                      @292
 struct kdbt[1], 4 bytes                    @306
 sb2 kdbr[644]                              @310
 ub1 freespace[794]                         @1598
 ub1 rowdata[5796]                          @2392
 ub4 tailchk                                @8188

SCOTT@book> drop table t2 purge;
Table dropped.

SCOTT@book> create table t2 (a char(5)) SEGMENT CREATION IMMEDIATE pctfree 10 INITRANS 10;
Table created.

SCOTT@book> @ test3.txt 0 0

BBED> map dba 4,740
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740                                   Dba:0x010002e4
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 264 bytes                    @20
 struct kdbh, 14 bytes                      @292
 struct kdbt[1], 4 bytes                    @306
 sb2 kdbr[1]                                @310
 ub1 freespace[7867]                        @312
 ub1 rowdata[9]                             @8179
 ub4 tailchk                                @8188

--//7867+11 = 7878
--//7878*.1 = 787.8
--//與前面比較8069-7878 = 191,前面預設保留2個ITL槽.
--//191/(10-2) = 23.875 ,差1個位元組正好等於24,基本符合,也就是1個ITL槽佔用24位元組.
--//也就是pctfree=10的情況下,大約保留800位元組為DML操作.