首页 » Cloud, ORACLE 9i-23ai » PCTFRR for index and HOW to find index blocks by key value?

PCTFRR for index and HOW to find index blocks by key value?

  • 2022/11/10
  • Cloud, ORACLE 9i-23ai
  • 188 views
  • PCTFRR for index and HOW to find index blocks by key value?已关闭评论

pctfree常用于控制block中预留的空间，对于table insert是发现超过pctfree后分析新空间，但是对于index对象PCTFREE只是在创建索引时(create or rebuild)生效，平时的DML后并不会保留该空间甚至会耗尽做index block split, 之前写过一篇Oracle 12c: index treedump， 这里再延伸一篇，如果根据index key entry查看index block.

— TOM

pctfree only counts when BUILDING an index. It reserves X% of the block for subsequent inserts into that block (inserts into the block can arise from inserts or UPDATES to the base table).

1， 验证PCTFREE

SQL> create table t_pctfree (id int, name varchar2(20));
Table created.

SQL> create index idx_t_pctfree on t_pctfree(id);
Index created.

SQL> insert into t_pctfree select rownum,rownum||'weejar' from dual connect by level<=1e4; 10000 rows created. SQL> commit;
Commit complete.

SQL> analyze index idx_t_pctfree validate structure;

SQL> @printtab "select * from index_stats"
HEIGHT                        : 2
BLOCKS                        : 32
NAME                          : IDX_T_PCTFREE
PARTITION_NAME                :
LF_ROWS                       : 10000
LF_BLKS                       : 19
LF_ROWS_LEN                   : 149801
LF_BLK_LEN                    : 7996
BR_ROWS                       : 18
BR_BLKS                       : 1
BR_ROWS_LEN                   : 198
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 0
DEL_LF_ROWS_LEN               : 0
DISTINCT_KEYS                 : 10000
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 159952
USED_SPACE                    : 149999
PCT_USED                      : 94
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 3
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 0
OPT_CMPR_PCTSAVE              : 0
DEL_LF_CMP_ROWS               : 0
PRG_LF_CMP_ROWS               : 0
LF_CMP_ROWS                   : 0
LF_CMP_ROWS_LEN               : 0
LF_UNCMP_ROWS                 : 10000
LF_UNCMP_ROWS_LEN             : 149801
LF_SUF_ROWS_LEN               : 0
LF_CMP_ROWS_UNCMP_LEN         : 0
LF_CMP_RECMP_COUNT            : 0
LF_CMP_LOCK_VEC_LEN           : 0
LF_CMP_BLKS                   : 0
LF_UNCMP_BLKS                 : 19
-----------------

PL/SQL procedure successfully completed.

----- begin tree dump
branch: 0x3006743 50358083 (0: nrow: 19, level: 1)
   leaf: 0x3006746 50358086 (-1: row:540.540 avs:0)
   leaf: 0x3006747 50358087 (0: row:533.533 avs:6)
   leaf: 0x3006744 50358084 (1: row:533.533 avs:7)
   leaf: 0x300674e 50358094 (2: row:533.533 avs:6)
   leaf: 0x300674f 50358095 (3: row:533.533 avs:6)
   leaf: 0x3006748 50358088 (4: row:533.533 avs:7)
   leaf: 0x3006749 50358089 (5: row:533.533 avs:6)
   leaf: 0x300674c 50358092 (6: row:533.533 avs:6)
   leaf: 0x300674a 50358090 (7: row:533.533 avs:7)
   leaf: 0x300674b 50358091 (8: row:533.533 avs:6)
   leaf: 0x3006765 50358117 (9: row:533.533 avs:6)
   leaf: 0x3006766 50358118 (10: row:533.533 avs:7)
   leaf: 0x3006767 50358119 (11: row:533.533 avs:6)
   leaf: 0x3006761 50358113 (12: row:533.533 avs:6)
   leaf: 0x3006762 50358114 (13: row:533.533 avs:7)
   leaf: 0x3006763 50358115 (14: row:533.533 avs:6)
   leaf: 0x3006776 50358134 (15: row:533.533 avs:6)
   leaf: 0x3006777 50358135 (16: row:533.533 avs:7)
   leaf: 0x3006770 50358128 (17: row:399.399 avs:2015)
----- end tree dump

默认情况下一个index block可以装533个左右key记录， 下面删除索引，重建一个pctfree 98的索引

SQL> drop index IDX_T_PCTFREE;
Index dropped.

SQL> create index IDX_T_PCTFREE on t_pctfree(id) PCTFREE 98;
Index created.

SQL> analyze index idx_t_pctfree validate structure;
Index analyzed.

SQL> @printtab "select * from index_stats"
HEIGHT                        : 3
BLOCKS                        : 10240
NAME                          : IDX_T_PCTFREE
PARTITION_NAME                :
LF_ROWS                       : 10000
LF_BLKS                       : 10000
LF_ROWS_LEN                   : 149801
LF_BLK_LEN                    : 7996
BR_ROWS                       : 9999
BR_BLKS                       : 15
BR_ROWS_LEN                   : 109789
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 0
DEL_LF_ROWS_LEN               : 0
DISTINCT_KEYS                 : 10000
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 80080420
USED_SPACE                    : 259590
PCT_USED                      : 1
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 4
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 1
OPT_CMPR_PCTSAVE              : 15
DEL_LF_CMP_ROWS               : 0
PRG_LF_CMP_ROWS               : 0
LF_CMP_ROWS                   : 0
LF_CMP_ROWS_LEN               : 0
LF_UNCMP_ROWS                 : 10000
LF_UNCMP_ROWS_LEN             : 149801
LF_SUF_ROWS_LEN               : 0
LF_CMP_ROWS_UNCMP_LEN         : 0
LF_CMP_RECMP_COUNT            : 0
LF_CMP_LOCK_VEC_LEN           : 0
LF_CMP_BLKS                   : 0
LF_UNCMP_BLKS                 : 10000
-----------------

PL/SQL procedure successfully completed.

SQL> alter session set events 'immediate trace name treedump level 79910';
Session altered.

[oracle@oel7db1 ~]$ grep -A 10 " begin tree dump" /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_5652.trc
----- begin tree dump
branch: 0x3006743 50358083 (0: nrow: 14, level: 2)
   branch: 0x3006afa 50359034 (-1: nrow: 740, level: 1)
      leaf: 0x3006744 50358084 (-1: row:1.1 avs:7982)  <<<<<<<<<<<
      leaf: 0x3006745 50358085 (0: row:1.1 avs:7982)
      leaf: 0x3006746 50358086 (1: row:1.1 avs:7982)
      leaf: 0x3006747 50358087 (2: row:1.1 avs:7982)
      leaf: 0x3006748 50358088 (3: row:1.1 avs:7982)
      leaf: 0x3006749 50358089 (4: row:1.1 avs:7982)
      leaf: 0x300674a 50358090 (5: row:1.1 avs:7982)
      leaf: 0x300674b 50358091 (6: row:1.1 avs:7982)

Note：
看到每个index leaf block 上1行记录。 因为索引是有序结构，我们往最小的值再插入100条记录，看是否会继续参考pctfree生成新块还是不受pctfree影响，继续写入原index leaf block?

SQL> insert into t_pctfree select 1,rownum||'weejar' from dual connect by level<=1e2; 
100 rows created. 
SQL> commit;
Commit complete.
 
SQL> alter session set events 'immediate trace name treedump level 79910';
Session altered.

[oracle@oel7db1 ~]$ grep -A 10 " begin tree dump" /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_5652.trc

----- begin tree dump
branch: 0x3006743 50358083 (0: nrow: 14, level: 2)
   branch: 0x3006afa 50359034 (-1: nrow: 740, level: 1)
      leaf: 0x3006744 50358084 (-1: row:101.101 avs:6582)   <<<<<<<<<<<
      leaf: 0x3006745 50358085 (0: row:1.1 avs:7982)
      leaf: 0x3006746 50358086 (1: row:1.1 avs:7982)
      leaf: 0x3006747 50358087 (2: row:1.1 avs:7982)
      leaf: 0x3006748 50358088 (3: row:1.1 avs:7982)
      leaf: 0x3006749 50358089 (4: row:1.1 avs:7982)
      leaf: 0x300674a 50358090 (5: row:1.1 avs:7982)
      leaf: 0x300674b 50358091 (6: row:1.1 avs:7982)

Note:
可以看到insert 后index block并未受PCTFREE影响，而是继续写入了原leaf block.

2， 查看索引block
通过上面看到index treedump是个key entry范围，如果根据一个index key值找索引block呢？

SQL> alter session set events 'immediate trace name treedump level 79910';
Session altered.

[oracle@oel7db1 ~]$ fgrep -i "leaf:" /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_15538.trc|head
      leaf: 0x3006744 50358084 (-1: row:101.101 avs:6582)
      leaf: 0x3006745 50358085 (0: row:1.1 avs:7982)
      leaf: 0x3006746 50358086 (1: row:1.1 avs:7982)
      leaf: 0x3006747 50358087 (2: row:1.1 avs:7982)
      leaf: 0x3006748 50358088 (3: row:1.1 avs:7982)
      leaf: 0x3006749 50358089 (4: row:1.1 avs:7982)
      leaf: 0x300674a 50358090 (5: row:1.1 avs:7982)
      leaf: 0x300674b 50358091 (6: row:1.1 avs:7982)
      leaf: 0x300674c 50358092 (7: row:1.1 avs:7982)

-- 以index key value 2为例
SQL>select * from (
select row_number() over (order by t.id) rn,t.id  from anbob.t_pctfree t
  3  ) where id=2;

        RN         ID
---------- ----------
       102          2

[oracle@oel7db1 ~]$ fgrep -i "leaf:" /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_15538.trc|awk '{sub(/[\.]+/," ",$5);print $0}'|awk '{a=a+$6}{if(a>=102){print $0;exit}}'
leaf: 0x3006745 50358085 (0: row:1 1 avs:7982)

-- index leaf block
SQL> select dbms_utility.data_block_address_file(50358085) file_id, dbms_utility.data_block_address_block(50358085) block_id from dual;
   FILE_ID   BLOCK_ID
---------- ----------
        12      26437


Block header dump:  0x03006745
 Object id on Block? Y
 seg/obj: 0x13826  csc:  0x00000000025ad3e8  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x3006740 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x00000000025ad3e8
Leaf block dump
===============
header address 140271725400164=0x7f938e5d5064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8020=0x1f54
kdxcoavs 7982
kdxlespl 0
kdxlende 0
kdxlenxt 50358086=0x3006746
kdxleprv 50358084=0x3006744
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 03               <<<<< index key value
col 1; len 6; (6):  03 00 67 3b 00 01   <<<<< non-uk B-Tree index stored the ROWID and the index key value in a tree structure. ----- end of leaf block Logical dump ----- ----- end of leaf block dump ----- End dump data blocks tsn: 5 file#: 12 minblk 26437 maxblk 26437 SQL> SELECT utl_raw.cast_to_number(replace('c1 03',' ')) value FROM dual;
     VALUE
----------
         2

-- 转换index rowid为block address
SQL> @hex 0300673b
                                DEC                  HEX
----------------------------------- --------------------
                    50358075.000000              300673B

SQL> select dbms_utility.data_block_address_file(50358075) file_id, dbms_utility.data_block_address_block(50358075) block_id from dual;
   FILE_ID   BLOCK_ID
---------- ----------
        12      26427

-- table块地址和index rowid指向块地址匹配
SQL>select /*+full(t)*/t.id,t.rowid ,
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
dbms_rowid.rowid_block_number(rowid)blockno
from anbob.t_pctfree t where id=2;

        ID ROWID                 REL_FNO    BLOCKNO
---------- ------------------ ---------- ----------
         2 AAATgkAAMAAAGc7AAB         12      26427

— over —
Note:
上面的window 取key value顺序是个Full table Scan 对于大表性能不佳。

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：