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