首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 12c: index treedump

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).

打赏

, ,

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