Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand oracle index block splitting". In daily operation, I believe many people have doubts about how to understand oracle index block splitting. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to understand oracle index block splitting"! Next, please follow the editor to study!

B*tree index is the most common index structure in databases including oracle. It is an index similar to a tree structure, and the lowest block is called a leaf block. The leaf node layer is actually a two-way linked list. If it is a B*tree index with a height (height) of 3, then the layer above the leaf block is called the branch block. The top layer is called the root block. The root block is the entrance to the index. But in fact, the root block can also be thought of as a branch block, so the B*tree index actually contains only two kinds of blocks: branch blocks and leaf blocks. Branch blocks are used for search, and leaf blocks are used to store key values.

What is index block splitting?

To put it simply, index block splitting means that when the addition, deletion and modification of a transaction needs to maintain the index, if the index block does not have enough space to accommodate the new data, it is necessary to add an index block, and the old index block data is divided into two. Move some of the data to the new index block. The split here is not necessarily an equal division, it may be a 50-50 split, or a 90-10 split. Enough space here does not necessarily mean the free space of the block, or there may be insufficient ITL transaction slots.

The wait event associated with index block splitting is enq: TX-index contention, which may accompany row lock waits.

Index block splitting is divided into the following four situations:

L leaf node 90-10 split

L leaf node 50-50 split

L branch node splitting

L root node splitting

Among them, root node splitting can be regarded as a special branch node split.

When the leaf node space is not enough to split, one more leaf block will come out; the leaf block will continue to increase, and when the branch block space is not enough, the branch block will also split; when the branch block continues to increase and the root block space is insufficient, the root block will also split. The splitting of the root block will increase the height (height) of the B*tree index. If this increase is unnecessary (the height does not match the size of the index), it will increase unnecessary IO, resulting in index performance degradation. This situation should be avoided, and the height can be reduced by rebuilding the index.

When 90-10 split occurs, the transaction concurrency of the table is low, and the key value of the index is monotonously increasing. In this case, only the larger part of the index key value will be moved to the new block, so that there is more free space on the new block. Meet the subsequent incremental check insertion. The 50-50 split occurs in the case of high concurrency, and the manipulation of the index key value is disordered, but the inserted value will still be smaller than the maximum value on the block, so that there will be half of the free space on each split block. in this way, it will be cheaper to maintain the index with high concurrency and disorder.

Imagine that if an index is maintained in a high concurrency disorder and the index is split in a 90-10 way, then the free space of the new block is large enough, but the old block is almost full and cannot be maintained, which will drag down the entire index maintenance behavior. If the behavior of low concurrency ordered index manipulation is split in the way of 50-50, then the data on the old block will no longer be inserted because of the ordering of key values, which will result in a waste of space, and the increase in the number of index blocks will lead to a decline in index efficiency, which is also undesirable.

The following simulates the split of leaf nodes 90-10:

Create a test table:

SQL > create table tab91 (id number,name varchar2, created date)

Create an index, where you specify that the pctfree parameter is 0, that is, no space is reserved on a block:

SQL > create index idx_tab91_01 on tab91 (id) pctfree 0

Here is a brief introduction to a diagnostic event of oralce: 10224. This event can dump the delimiting and deletion of indexes. Usage:

Enable:

ALTER SESSION SET EVENTS '10224 trace name context forever, level 1'

ALTER SYSTEM SET EVENTS '10224 trace name context forever, level 1'

Close:

ALTER SESSION SET EVENTS '10224 trace name context off'

ALTER SYSTEM SET EVENTS '10224 trace name context off'

Then briefly introduce a statement that dumps the index structure:

Alter session set events' immediate trace name treedump level n'

N: object_id of the index

Select object_id from dba_objects where object_name=upper ('idx_tab91_01')

Low concurrency, the behavior of maintaining indexes when inserting data normally:

SQL > ALTER SESSION SET EVENTS '10224 trace name context forever, level 1'

Declare

Begin

For i in 1.. 10000 loop

Insert into tab91 values (iMagneMingshuoqian Sysdate)

End loop

Commit

End

/

SQL > ALTER SESSION SET EVENTS '10224 trace name context off'

Check the index information:

SQL > analyze index idx_tab91_01 validate structure

Index analyzed.

SQL > set line 200

SQL > col NAME for A20

SQL > select height,blocks,name,lf_rows,lf_blks,pct_used from index_stats

HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED

--

2 24 IDX_TAB91_01 10000 19 94

It can be seen that the number of leaf blocks is 19.

The trace file generated by the 10224 event:

Splitting leaf,dba 0x01c0059b,time 03:03:15.462

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c0059b recording time 03purl 03purl 15.559

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c0059b,time 03:03:15.559

Kdisnew_bseg_srch_cbk using block,dba 0x01c0059f,time 03:03:15.560

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c0059f recording time 03purl 03purl 15.560

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c0059f,time 03:03:15.560

Kdisnew_bseg_srch_cbk using block,dba 0x01c0059c,time 03:03:15.560

Splitting leaf,dba 0x01c0059c,time 03:03:15.597

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c0059c recording time 03purl 03purl 15.597

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c0059c,time 03:03:15.597

Kdisnew_bseg_srch_cbk using block,dba 0x01c0059d,time 03:03:15.597

Splitting leaf,dba 0x01c0059d,time 03:03:15.622

Kdisnew_bseg_srch_cbk using block,dba 0x01c0059e,time 03:03:15.622

Splitting leaf,dba 0x01c0059e,time 03:03:15.633

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c0059e recording time 03purl 03purl 15.633

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c0059e,time 03:03:15.633

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c0059dMagneTime 03bank 03purl 15.633

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c0059d,time 03:03:15.633

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ab,time 03:03:15.633

Splitting leaf,dba 0x01c005ab,time 03:03:15.644

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005abrect time 03purl 03purl 15.644

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ab,time 03:03:15.644

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ac,time 03:03:15.644

Splitting leaf,dba 0x01c005ac,time 03:03:15.654

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005ac recorder time 03purl 03purl 15.669

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ac,time 03:03:15.669

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ad,time 03:03:15.669

Splitting leaf,dba 0x01c005ad,time 03:03:15.696

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005adre time 03purl 03purl 15.696

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ad,time 03:03:15.696

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ae,time 03:03:15.696

Splitting leaf,dba 0x01c005ae,time 03:03:15.706

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005aeMagnical time 03purl 03purl 03purl 15.706

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ae,time 03:03:15.706

Kdisnew_bseg_srch_cbk using block,dba 0x01c005af,time 03:03:15.706

Splitting leaf,dba 0x01c005af,time 03:03:15.715

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005afMagneTime 03purl 03purl 15.715

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005af,time 03:03:15.715

Kdisnew_bseg_srch_cbk using block,dba 0x01c005a8,time 03:03:15.715

Splitting leaf,dba 0x01c005a8,time 03:03:15.727

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005a8 recorder time 03purl 03purl 15.727

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005a8,time 03:03:15.727

Kdisnew_bseg_srch_cbk using block,dba 0x01c005a9,time 03:03:15.727

Splitting leaf,dba 0x01c005a9,time 03:03:15.738

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005a9 recorder time 03purl 03purl 15.738

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005a9,time 03:03:15.738

Kdisnew_bseg_srch_cbk using block,dba 0x01c005aa,time 03:03:15.738

Splitting leaf,dba 0x01c005aa,time 03:03:15.747

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005aaMagneTime 03purl 03purl 15.747

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005aa,time 03:03:15.747

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c6,time 03:03:15.747

Splitting leaf,dba 0x01c005c6,time 03:03:15.757

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005c6 Magi time 03purr 03purl 15.757

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005c6,time 03:03:15.757

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c7,time 03:03:15.757

Splitting leaf,dba 0x01c005c7,time 03:03:15.768

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005c7 Magistrate time 03purr 03purl 15.768

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005c7,time 03:03:15.768

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c1,time 03:03:15.768

Splitting leaf,dba 0x01c005c1,time 03:03:15.778

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005c1Magic time 03purr 03purl 15.778

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005c1,time 03:03:15.778

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c2,time 03:03:15.778

Splitting leaf,dba 0x01c005c2,time 03:03:15.789

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c3,time 03:03:15.789

Splitting leaf,dba 0x01c005c3,time 03:03:15.799

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005c3 magical time 03purl 03purl 03purl 15.799

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005c3,time 03:03:15.799

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c4,time 03:03:15.799

Splitting leaf,dba 0x01c005c4,time 03:03:15.807

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005c4 recorder time 03purl 03purl 15.807

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005c4,time 03:03:15.807

Kdisnew_bseg_srch_cbk using block,dba 0x01c005c5,time 03:03:15.807

Query:

SQL > select s.SID, n.NAME, s.VALUE

2 from v$sesstat s, v$statname n

3 where s.STATISTIC# = n.STATISTIC#

4 and sid in (select userenv ('SID') from dual)

5 and n.NAME like'% split%'

SID NAME VALUE

1 leaf node splits 19

1 leaf node 90-10 splits 18

1 branch node splits 0

1 root node splits 0

1 queue splits 0

It can be seen that there are 18 leaf node 90-10 divisions, so there are exactly 19 leaf blocks.

Dump index structure:

SQL > select object_id from dba_objects where object_name=upper ('idx_tab91_01') and owner='SCOTT'

OBJECT_ID

-

77956

SQL > alter session set events' immediate trace name treedump level 77956'

Dump Information:

-begin tree dump

Branch: 0x1c0059b 29361563 (0: nrow: 19, level: 1)

Leaf: 0x1c0059f 29361567 (- 1: nrow: 540 rrow: 540)

Leaf: 0x1c0059c 29361564 (0: nrow: 533 rrow: 533)

Leaf: 0x1c0059d 29361565 (1: nrow: 533 rrow: 533)

Leaf: 0x1c0059e 29361566 (2: nrow: 533 rrow: 533)

Leaf: 0x1c005ab 29361579 (3: nrow: 533 rrow: 533)

Leaf: 0x1c005ac 29361580 (4: nrow: 533 rrow: 533)

Leaf: 0x1c005ad 29361581 (5: nrow: 533 rrow: 533)

Leaf: 0x1c005ae 29361582 (6: nrow: 533 rrow: 533)

Leaf: 0x1c005af 29361583 (7: nrow: 533 rrow: 533)

Leaf: 0x1c005a8 29361576 (8: nrow: 533 rrow: 533)

Leaf: 0x1c005a9 29361577 (9: nrow: 533 rrow: 533)

Leaf: 0x1c005aa 29361578 (10: nrow: 533 rrow: 533)

Leaf: 0x1c005c6 29361606 (11: nrow: 533 rrow: 533)

Leaf: 0x1c005c7 29361607 (12: nrow: 533 rrow: 533)

Leaf: 0x1c005c1 29361601 (13: nrow: 533 rrow: 533)

Leaf: 0x1c005c2 29361602 (14: nrow: 533 rrow: 533)

Leaf: 0x1c005c3 29361603 (15: nrow: 533 rrow: 533)

Leaf: 0x1c005c4 29361604 (16: nrow: 533 rrow: 533)

Leaf: 0x1c005c5 29361605 (17: nrow: 399 rrow: 399)

-end tree dump

Because the previously set pctfree is 0, a leaf block can be thought of as capable of storing about 533 rows.

The following simulates the split of 50-50 leaf nodes:

Create a test table:

SQL > create table tab55 (id number,name varchar2, created date)

Create an index, where you specify that the pctfree parameter is 0, that is, no space is reserved on a block:

SQL > create index idx_tab55_01 on tab55 (id) pctfree 0

The act of maintaining an index when inserting data in reverse order:

SQL > ALTER SESSION SET EVENTS '10224 trace name context forever, level 1'

Declare

Begin

For i in 1.. 10000 loop

Insert into tab55 values (10001muri, fumingshuohua, Sysdate)

End loop

Commit

End

/

SQL > ALTER SESSION SET EVENTS '10224 trace name context off'

Check the index information:

SQL > analyze index idx_tab55_01 validate structure

Index analyzed.

SQL > set line 200

SQL > col NAME for A20

SQL > select height,blocks,name,lf_rows,lf_blks,pct_used from index_stats

HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED

--

2 48 IDX_TAB55_01 10000 38 49

It can be seen that the number of leaf blocks is 38.

The trace file generated by the 10224 event:

Splitting leaf,dba 0x01c005e3,time 03:36:47.553

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e3 authoring time 03purl 36purl 47.701

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e3,time 03:36:47.701

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e7,time 03:36:47.703

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e7 Magistrate time 03purl 36purl 47.703

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e7,time 03:36:47.703

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e4,time 03:36:47.703

Splitting leaf,dba 0x01c005e7,time 03:36:47.723

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e4 magical time 03purl 36purl 47.723

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e4,time 03:36:47.723

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e5,time 03:36:47.723

Splitting leaf,dba 0x01c005e7,time 03:36:47.734

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e6,time 03:36:47.734

Splitting leaf,dba 0x01c005e7,time 03:36:47.744

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e6 magical time 03purl 36purl 47.745

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e6,time 03:36:47.745

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e5 authoring time 03purl 36purl 47.745

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e5,time 03:36:47.745

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ef,time 03:36:47.746

Splitting leaf,dba 0x01c005e7,time 03:36:47.757

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005efjfjt time 03purl 36purl 47.757

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ef,time 03:36:47.757

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e8,time 03:36:47.757

Splitting leaf,dba 0x01c005e7,time 03:36:47.766

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e8 recorder time 03purl 36purl 47.766

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e8,time 03:36:47.766

Kdisnew_bseg_srch_cbk using block,dba 0x01c005e9,time 03:36:47.766

Splitting leaf,dba 0x01c005e7,time 03:36:47.774

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ec,time 03:36:47.784

Splitting leaf,dba 0x01c005e7,time 03:36:47.789

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005ecgonomics time 03purl 36purl 47.790

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ec,time 03:36:47.790

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ed,time 03:36:47.790

Splitting leaf,dba 0x01c005e7,time 03:36:47.796

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005edrecoverytime 03purl 36purl 47.796

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ed,time 03:36:47.796

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ee,time 03:36:47.796

Splitting leaf,dba 0x01c005e7,time 03:36:47.801

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005ee recalculated time 03purl 36purl 47.801

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ee,time 03:36:47.802

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005e9 recorder time 03purl 36purl 47.802

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005e9,time 03:36:47.802

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ea,time 03:36:47.802

Splitting leaf,dba 0x01c005e7,time 03:36:47.806

Kdisnew_bseg_srch_cbk using block,dba 0x01c005eb,time 03:36:47.806

Splitting leaf,dba 0x01c005e7,time 03:36:47.813

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005ebMagazine time 03Franco 36purl 47.813

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005eb,time 03:36:47.813

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005eaMagazine time 03purl 36purl 47.813

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ea,time 03:36:47.813

Kdisnew_bseg_srch_cbk using block,dba 0x01c005fc,time 03:36:47.814

Splitting leaf,dba 0x01c005e7,time 03:36:47.819

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005fcMagneTime 03purl 36purl 47.819

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005fc,time 03:36:47.819

Kdisnew_bseg_srch_cbk using block,dba 0x01c005fd,time 03:36:47.819

Splitting leaf,dba 0x01c005e7,time 03:36:47.903

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005fdMagazine time 03Franco 36purl 47.903

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005fd,time 03:36:47.903

Kdisnew_bseg_srch_cbk using block,dba 0x01c005fe,time 03:36:47.903

Splitting leaf,dba 0x01c005e7,time 03:36:47.908

Splitting leaf,dba 0x01c005e7,time 03:36:47.909

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005felegentiary time 03purl 36purl 47.909

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005fe,time 03:36:47.909

Kdisnew_bseg_srch_cbk using block,dba 0x01c005ff,time 03:36:47.909

Splitting leaf,dba 0x01c005e7,time 03:36:47.914

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005ffrect time 03purl 36purl 47.914

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005ff,time 03:36:47.914

Kdisnew_bseg_srch_cbk using block,dba 0x01c005f9,time 03:36:47.914

Splitting leaf,dba 0x01c005e7,time 03:36:47.920

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005f9 recorder time 03purl 36purl 47.920

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005f9,time 03:36:47.920

Kdisnew_bseg_srch_cbk using block,dba 0x01c005fa,time 03:36:47.920

Splitting leaf,dba 0x01c005e7,time 03:36:47.926

Kdisnew_bseg_srch_cbk using block,dba 0x01c005fb,time 03:36:47.926

Splitting leaf,dba 0x01c005e7,time 03:36:47.932

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005fbMagneTime 03purl 36purl 47.932

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005fb,time 03:36:47.932

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x01c005fajimentin time 03purl 36purl 47.932

Kdisnew_bseg_srch_cbk rejecting block, dba 0x01c005fa,time 03:36:47.932

Kdisnew_bseg_srch_cbk using block,dba 0x0300008f,time 03:36:47.932

Splitting leaf,dba 0x01c005e7,time 03:36:47.938

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008f recorder time 03purl 36purl 47.938

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008f,time 03:36:47.938

Kdisnew_bseg_srch_cbk using block,dba 0x03000088,time 03:36:47.938

Splitting leaf,dba 0x01c005e7,time 03:36:47.943

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x03000088 recorder time 03purl 36purl 47.943

Kdisnew_bseg_srch_cbk rejecting block, dba 0x03000088,time 03:36:47.943

Kdisnew_bseg_srch_cbk using block,dba 0x03000089,time 03:36:47.943

Splitting leaf,dba 0x01c005e7,time 03:36:47.948

Kdisnew_bseg_srch_cbk using block,dba 0x0300008c,time 03:36:47.949

Splitting leaf,dba 0x01c005e7,time 03:36:47.954

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008c recorder time 03purl 36purl 47.954

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008c,time 03:36:47.954

Kdisnew_bseg_srch_cbk using block,dba 0x0300008d,time 03:36:47.954

Splitting leaf,dba 0x01c005e7,time 03:36:47.960

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008d recorder time 03purl 36purl 47.960

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008d,time 03:36:47.960

Kdisnew_bseg_srch_cbk using block,dba 0x0300008e,time 03:36:47.960

Splitting leaf,dba 0x01c005e7,time 03:36:47.965

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008eReclamation time 03purl 36purl 47.965

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008e,time 03:36:47.965

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x03000089 Magnum time 03purl 36purl 47.965

Kdisnew_bseg_srch_cbk rejecting block, dba 0x03000089,time 03:36:47.965

Kdisnew_bseg_srch_cbk using block,dba 0x0300008a,time 03:36:47.965

Splitting leaf,dba 0x01c005e7,time 03:36:47.972

Kdisnew_bseg_srch_cbk using block,dba 0x0300008b,time 03:36:47.973

Splitting leaf,dba 0x01c005e7,time 03:36:47.981

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008b recorder time 03purl 36purl 47.981

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008b,time 03:36:47.981

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300008a recorder time 03purl 36purl 47.981

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300008a,time 03:36:47.981

Kdisnew_bseg_srch_cbk using block,dba 0x0300009c,time 03:36:47.982

Splitting leaf,dba 0x01c005e7,time 03:36:47.987

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009c recorder time 03purl 36purl 47.987

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009c,time 03:36:47.987

Kdisnew_bseg_srch_cbk using block,dba 0x0300009d,time 03:36:47.987

Splitting leaf,dba 0x01c005e7,time 03:36:47.993

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009d recorder time 03purl 36purl 47.993

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009d,time 03:36:47.993

Kdisnew_bseg_srch_cbk using block,dba 0x0300009e,time 03:36:47.993

Splitting leaf,dba 0x01c005e7,time 03:36:47.999

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009eReclamation time 03purl 36purl 47.999

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009e,time 03:36:47.999

Kdisnew_bseg_srch_cbk using block,dba 0x0300009f,time 03:36:47.999

Splitting leaf,dba 0x01c005e7,time 03:36:48.06

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009f recorder time 03lane 3648.06

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009f,time 03:36:48.06

Kdisnew_bseg_srch_cbk using block,dba 0x03000099,time 03:36:48.06

Splitting leaf,dba 0x01c005e7,time 03:36:48.13

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x03000099 telegraphic time 03purl 3648.13

Kdisnew_bseg_srch_cbk rejecting block, dba 0x03000099,time 03:36:48.13

Kdisnew_bseg_srch_cbk using block,dba 0x0300009a,time 03:36:48.13

Splitting leaf,dba 0x01c005e7,time 03:36:48.20

Kdisnew_bseg_srch_cbk using block,dba 0x0300009b,time 03:36:48.20

Splitting leaf,dba 0x01c005e7,time 03:36:48.25

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009b recorder time 03lane 3648.25

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009b,time 03:36:48.25

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x0300009a recorder time 03lane 3648.25

Kdisnew_bseg_srch_cbk rejecting block, dba 0x0300009a,time 03:36:48.25

Kdisnew_bseg_srch_cbk using block,dba 0x030000af,time 03:36:48.26

Splitting leaf,dba 0x01c005e7,time 03:36:48.31

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x030000aft recorder time 03mm 3648.31

Kdisnew_bseg_srch_cbk rejecting block, dba 0x030000af,time 03:36:48.31

Kdisnew_bseg_srch_cbk using block,dba 0x030000a8,time 03:36:48.31

Splitting leaf,dba 0x01c005e7,time 03:36:48.36

Kdisnew_bseg_srch_cbk reject block-mark full,dba 0x030000a8 recorder time 03lane 3648.36

Kdisnew_bseg_srch_cbk rejecting block, dba 0x030000a8,time 03:36:48.36

Kdisnew_bseg_srch_cbk using block,dba 0x030000a9,time 03:36:48.36

Splitting leaf,dba 0x01c005e7,time 03:36:48.43

Kdisnew_bseg_srch_cbk using block,dba 0x030000ac,time 03:36:48.43

Query:

SQL > select s.SID, n.NAME, s.VALUE

2 from v$sesstat s, v$statname n

3 where s.STATISTIC# = n.STATISTIC#

4 and sid in (select sid from v$mystat)

5 and n.NAME like'% split%'

6

SID NAME VALUE

35 leaf node splits 37

35 leaf node 90-10 splits 0

35 branch node splits 0

35 root node splits 0

35 queue splits 0

It can be seen that there are 37 leaf node 50-50 divisions, so there are exactly 38 leaf blocks.

Dump index structure:

SQL > select object_id from dba_objects where object_name=upper ('idx_tab55_01') and owner='SCOTT'

OBJECT_ID

-

77958

SQL > alter session set events' immediate trace name treedump level 77958'

Dump Information:

-begin tree dump

Branch: 0x1c005e3 29361635 (0: nrow: 38, level: 1)

Leaf: 0x1c005e7 29361639 (- 1: nrow: 289 rrow: 289)

Leaf: 0x30000ac 50331820 (0: nrow: 262rrow: 262)

Leaf: 0x30000a9 50331817 (1: nrow: 262rrow: 262)

Leaf: 0x30000a8 50331816 (2: nrow: 262rrow: 262)

Leaf: 0x30000af 50331823 (3: nrow: 262rrow: 262)

Leaf: 0x300009b 50331803 (4: nrow: 262rrow: 262)

Leaf: 0x300009a 50331802 (5: nrow: 266 rrow: 266)

Leaf: 0x3000099 50331801 (6: nrow: 262rrow: 262)

Leaf: 0x300009f 50331807 (7: nrow: 262rrow: 262)

Leaf: 0x300009e 50331806 (8: nrow: 262rrow: 262)

Leaf: 0x300009d 50331805 (9: nrow: 262rrow: 262)

Leaf: 0x300009c 50331804 (10: nrow: 262rrow: 262)

Leaf: 0x300008b 50331787 (11: nrow: 262rrow: 262)

Leaf: 0x300008a 50331786 (12: nrow: 262rrow: 262)

Leaf: 0x300008e 50331790 (13: nrow: 266 rrow: 266)

Leaf: 0x300008d 50331789 (14: nrow: 262rrow: 262)

Leaf: 0x300008c 50331788 (15: nrow: 262rrow: 262)

Leaf: 0x3000089 50331785 (16: nrow: 262 rrow: 262)

Leaf: 0x3000088 50331784 (17: nrow: 262rrow: 262)

Leaf: 0x300008f 50331791 (18: nrow: 262rrow: 262)

Leaf: 0x1c005fb 29361659 (19: nrow: 262rrow: 262)

Leaf: 0x1c005fa 29361658 (20: nrow: 262rrow: 262)

Leaf: 0x1c005f9 29361657 (21: nrow: 266 rrow: 266)

Leaf: 0x1c005ff 29361663 (22: nrow: 262rrow: 262)

Leaf: 0x1c005fe 29361662 (23: nrow: 262rrow: 262)

Leaf: 0x1c005fd 29361661 (24: nrow: 262rrow: 262)

Leaf: 0x1c005fc 29361660 (25: nrow: 262rrow: 262)

Leaf: 0x1c005eb 29361643 (26: nrow: 262 rrow: 262)

Leaf: 0x1c005ea 29361642 (27: nrow: 262rrow: 262)

Leaf: 0x1c005ee 29361646 (28: nrow: 262rrow: 262)

Leaf: 0x1c005ed 29361645 (29: nrow: 267rrow: 267)

Leaf: 0x1c005ec 29361644 (30: nrow: 262rrow: 262)

Leaf: 0x1c005e9 29361641 (31: nrow: 262rrow: 262)

Leaf: 0x1c005e8 29361640 (32: nrow: 262rrow: 262)

Leaf: 0x1c005ef 29361647 (33: nrow: 262rrow: 262)

Leaf: 0x1c005e6 29361638 (34: nrow: 262rrow: 262)

Leaf: 0x1c005e5 29361637 (35: nrow: 262 rrow: 262)

Leaf: 0x1c005e4 29361636 (36: nrow: 262rrow: 262)

-end tree dump

You can see that a 50% index block stores about 262 rows of data.

Now compare the two indexes:

Index name

Number of blocks

The number of leaf blocks

Index size / M

Idx_tab91_01

twenty-four

nineteen

one hundred and ninety two

Idx_tab55_01

forty-eight

thirty-eight

three hundred and eighty four

Solution:

Re-index, hash partitioned index or build reverse key index (reverse key index).

Rebuilding the index can reduce the height of the index tree, and the reverse key index can put an end to the index hot block competition caused by the insertion operation, but there are advantages and disadvantages, and the reverse key index will affect the efficiency of index range scanning.

The idea of hash partitioned index is the same as that of reverse key index, which is a hot block of decentralized index. In some OLTP environments, there are monotonously increasing columns that are inserted only to the right side of the index block, which becomes hotspot because of some index pages, contention such as buffer,latch, and some additional index maintenance activities. The hash partition index breaks up the index blocks to avoid performance degradation in the above cases.

L change sequence cach value

There is a monotonously increasing column value that is populated with sequence values. Set a large cach value, and different instances will use different noorder sequence values, so that different instances are much less likely to insert data into the same block when inserting data. The occurrence of hot blocks is avoided. Using the idea of physical limit, if the cache is as small as 1, then instance 1 insert 1 and instance 2 insert 2 are likely to be on the same block. If the cach value is 100 million, then instance 1 inserts 100 million and instance 2 inserts 120 million pits on a block.

L rebuild or shrink index after a large amount of data is deleted

L increase the PCT_ free value of the index

A high PCT_ free value prevents insufficient ITL slots. This parameter is mainly useful for update systems.

At this point, the study on "how to understand oracle index block splitting" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.