Oracle 12c: index treedump
oracle 12C的index treedump比11G有了一点小变化, 这里简短的记录一下.
VERSION ---------- 11.2.0.3.0 SQL> create table obj as select object_id, object_name from dba_objects where rownum<=1000; Table created. SQL> create index idx_obj on obj(object_id); Index created. SQL> @o idx_obj owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME ------- -- ------------ -- ------------------ --------- ---------- ---------- ----------------- ----------------- SYS IDX_OBJ INDEX VALID 9927620 9927620 20170228 17:17:22 20170228 17:17:22 SQL> alter session set events 'immediate trace name treedump level 9927620'; Session altered. # trace file ----- begin tree dump branch: 0x1bd189a1 466717089 (0: nrow: 3, level: 1) leaf: 0x1bd189a2 466717090 (-1: nrow: 484 rrow: 484) leaf: 0x1bd189a3 466717091 (0: nrow: 477 rrow: 477) leaf: 0x1bd189a4 466717092 (1: nrow: 39 rrow: 39) ----- end tree dump
NOTE:
这个索引有3个Leaf Blocks, 第一个记录是索引的root块,下面是3个Leaf Block的信息, 记录了索引条目的数量(nrow),和实际的索引条目[no-deleted]的数量(rrow), 更多信息可以google Richard Foote 的<<index-internals-rebuilding-the-truth>>或 index dump
# 12c
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 SQL> create table obj as select object_id, object_name from dba_objects where rownum<=1000; Table created. SQL> @tab obj Show tables matching condition "%obj%" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS -------------------- --------------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------------------------------------- -------- ANBOB OBJ TAB 1000 7 0 0 20 2017-02-28 16:12:12 1 DISABLED 1 row selected. SQL> select column_name, num_distinct, density, histogram, notes from dba_tab_col_statistics where table_name='OBJ' COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NOTES ------------------------------ ------------ ---------- --------------- --------------------------- OBJECT_ID 1000 .001 NONE STATS_ON_LOAD OBJECT_NAME 994 .001006036 NONE STATS_ON_LOAD 2 rows selected. SQL> create index idx_obj on obj(object_id); Index created. SQL> @o idx_obj owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------------- ------------------- ANBOB IDX_OBJ INDEX VALID 73203 73203 2017-02-28 16:12:32 2017-02-28 16:12:32 1 row selected.
Note:
这里顺便提一下12C的另一个新特性统计信息的自动收集, 可以看到上面创建表和索引后统计信息都有自动收集.有以下情况:
1, 先创建的表后 insert without append 表上不会有统计信息, 创建索引后,索引会自动收统计信息
2, 先创建的表和索引 , insert append后表上会自动收集统计信息, 索引不会更新
3, CTAS 表上会自动收集统计信息
4, dba_tab_col_statistics视图的 STATS_ON_LOAD列会有状态
SQL> alter session set events 'immediate trace name treedump level 73203';
Session altered.
SQL> oradebug tracefile_name
ORA-01031: insufficient privileges
SQL> select * from v$diag_info;
INST_ID NAME VALUE CON_ID
---------- ----------------------------- ----------------------------------------------------------------------
...
1 Default Trace File /u02/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_4674.trc 0
1 Active Problem Count 0
# trace file
----- begin tree dump
branch: 0x300252b 50341163 (0: nrow: 3, level: 1) # 3 leaf blocks , level 1
leaf: 0x300252c 50341164 (-1: row:485.485 avs:823)
leaf: 0x300252d 50341165 (0: row:479.479 avs:816)
leaf: 0x300252e 50341166 (1: row:36.36 avs:7457)
----- end tree dump
Note:
从treedump traces可以看到12c比之前的版本有一点小不同, leaf block的信息原来的nrow,rrow 变成了 row:[nrow].[rrow] 合并成了一列, 并且增加了leaf block中上可用空间avs(free space)值. 下面delete一些行记录,查看treedump的变化.
SQL> delete obj where rownum<=500;
500 rows deleted.
SQL> commit;
Commit complete.
SQL> alter session set events 'immediate trace name treedump level 73203';
Session altered.
*** 2017-02-28T16:27:05.981662+08:00 (PDBANBOB(3))
----- begin tree dump
branch: 0x300252b 50341163 (0: nrow: 3, level: 1)
leaf: 0x300252c 50341164 (-1: row:485.0 avs:823)
leaf: 0x300252d 50341165 (0: row:479.464 avs:816)
leaf: 0x300252e 50341166 (1: row:36.36 avs:7457)
----- end tree dump
Note:
注意到之前的rrow[no-deleted]也就是row的第二个数值与实际相符,第一个和第二个索引条目录no-deleted都发生了改变, 第一个leaf索引条目已全被删除,第二个leaf索引条目部分删除, 但是nrow列和avs可用空间都没有变化.其实当下次我们在相应的leaf块上再做任何一个DML时,这些的记录就会更新了.
SQL> delete obj where rownum<=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter session set events 'immediate trace name treedump level 73203';
Session altered.
----- begin tree dump
*** 2017-02-28T16:32:01.754141+08:00 (PDBANBOB(3))
branch: 0x300252b 50341163 (0: nrow: 3, level: 1)
leaf: 0x300252c 50341164 (-1: row:485.0 avs:823)
leaf: 0x300252d 50341165 (0: row:464.463 avs:1040)
leaf: 0x300252e 50341166 (1: row:36.36 avs:7457)
----- end tree dump
Note:
注意到第二次删除的记录已经更新了第二个leaf block的rrow,nrow,和更加重要的avs可用空间已经清理,但是第一个leaf中已经全部deleted的block信息还在, 其实数据库会自动的reuse该block, 下面我们再尝试做DML,因为索引是有序存放, 下面我们要insert一条更小的值到索引条目的左边.
SQL> insert into obj values(1,'ANBOB');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set events 'immediate trace name treedump level 73203';
Session altered.
*** 2017-02-28T16:36:12.870629+08:00 (PDBANBOB(3))
----- begin tree dump
branch: 0x300252b 50341163 (0: nrow: 3, level: 1)
leaf: 0x300252c 50341164 (-1: row:1.1 avs:7982)
leaf: 0x300252d 50341165 (0: row:464.463 avs:1040)
leaf: 0x300252e 50341166 (1: row:36.36 avs:7457)
----- end tree dump
Note:
这次的DML更新了第一个leaf block, 并且清理了之前deleted的条目, leaf block被重用,并且avs可用空间已释放(8K block size).
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。