[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操作.