Oracle index merge coalesce operation

Oracle index merge coalesce operation

Index rebuild and rebuild online are common problems in the O & M environment. However, in either case, the rebuild of the big data index object is a large-scale operation that consumes resources. Time Window planning is required to avoid impact on online system operation.

This article describes another method to streamline the index operation: coalesce merge. We have learned from the previous discussions that the index structure is generally a constantly degraded balanced structure. If a new value is added, it may expand along with the leaf node, it even includes creating branch nodes. When a value is deleted and modified, the leaf node is marked as deleted and not merged or recycled. In this way, the indexes in the normal environment should be segments with "fragmented" and "slow expansion" leaves.

The basic motivation for administrators to consider rebuild is to recycle space and make leaf nodes more closely. The space occupied by new indexes is relatively small, and the retrieval speed is also fast. However, after the insertion, update, and deletion processes, the space allocation process is still lost. Therefore, I personally think: Maybe the healthy index structure should be "fragmented" and "slow expansion ". The Coalesce operation provides a logical re-indexing method, which only reassembles the index tree without data collection.

1. Environment Introduction

I chose 11gR2 for the experiment.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

Create a data table T and create an index.

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_id on t (object_id );

Index created

To simulate the effect, delete most of the data to form a dead node.

SQL> select max (object_id) from t;

MAX (OBJECT_ID)

--------------

164092

SQL> delete t where object_id <164092;

77405 rows deleted

SQL> commit;

Commit complete

Collect statistics again.

SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL> select count (*) from t;

COUNT (*)

----------

1

2. coalesce operation

The Delete operation neither recycles data segments nor recycles index segments. The segment information of the current data table T is as follows:

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner = 'sys 'and segment_name = 'T ';

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

--------------------------------------------------

0 1 86984 65536 8

1 1 86992 65536 8

2 1 87000 65536 8

3 1 87008 65536 8

4 1 87016 65536 8

5 1 87024 65536 8

6 1 87032 65536 8

7 1 88960 65536 8

8 1 88968 65536 8

9 1 88976 65536 8

10 1 88984 65536 8

11 1 88992 65536 8

12 1 89000 65536 8

13 1 89008 65536 8

14 1 90360 65536 8

15 1 91008 65536 8

16 1 89088 1048576 128

17 1 89216 1048576 128

18 1 89344 1048576 128

19 1 89472 1048576 128

 

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

--------------------------------------------------

20 1 89600 1048576 128

21 1 89728 1048576 128

22 1 89856 1048576 128

23 1 89984 1048576 128

 

24 rows selected

The index segment is as follows:

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner = 'sys 'and segment_name = 'idx _ T_ID ';

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

--------------------------------------------------

0 1 91704 65536 8

1 1 91712 65536 8

2 1 91720 65536 8

3 1 91728 65536 8

4 1 91736 65536 8

5 1 91744 65536 8

6 1 91752 65536 8

7 1 91760 65536 8

8 1 91768 65536 8

9 1 92544 65536 8

10 1 92552 65536 8

11 1 92560 65536 8

12 1 92568 65536 8

13 1 92576 65536 8

14 1 92584 65536 8

15 1 92592 65536 8

16 1 91776 1048576 128

17 rows selected

Multi-extent structure, indicating that the structure is not recycled. The following uses the analyze statement to analyze the index:

SQL> analyze index idx_t_id validate structure;

Index analyzed

SQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats;

Height blocks LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS DEL_LF_ROWS

--------------------------------------------------------------------------------------------

2 256 77406 172 1227792 7996 171 1 77405

The index tree has two-layer structure, including 256 databases, 77406 leaf nodes, and 77405 deleted nodes.

Enable the coalesce process for event tracking 10046.

SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE

--------------------------------------------------------------------------------

/Home/oracle/app/diag/rdbms/awpdb/trace/awpdb_ora_14931.trc

SQL> alter session set events '10046 trace name context forever, level 12 ';

Session altered.

SQL> alter index idx_t_id coalesce;

Index altered.

SQL> alter session set events '10046 trace name context off ';

Session altered.

After the operation, check the structure effect.

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner = 'sys 'and segment_name = 'idx _ T_ID ';

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

--------------------------------------------------

0 1 91704 65536 8

1 1 91712 65536 8

2 1 91720 65536 8

3 1 91728 65536 8

4 1 91736 65536 8

5 1 91744 65536 8

6 1 91752 65536 8

7 1 91760 65536 8

8 1 91768 65536 8

9 1 92544 65536 8

10 1 92552 65536 8

11 1 92560 65536 8

12 1 92568 65536 8

13 1 92576 65536 8

14 1 92584 65536 8

15 1 92592 65536 8

16 1 91776 1048576 128

17 rows selected

The storage allocation of the index segment remains unchanged, or 17 extent. However, the index logical structure has changed:

SQL> analyze index idx_t_id validate structure;

Index analyzed

SQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats;

Height blocks LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS DEL_LF_ROWS

--------------------------------------------------------------------------------------------

2 256 1 1 16 7996 0 1 0

The index height and the number of allocated blocks remain unchanged, but the leaf nodes are reorganized. The deleted data nodes are sorted and merged.

3. File Analysis 10046

From the analysis of event files in section 10046, we can see the following:

==================================

Parsing in cursor #139851695602760 len = 29 dep = 0 uid = 0 oct = 11 lid = 0 tim = 1427182487640740 hv = 4054144165 ad = 'aa2f2710 'sqlid = 'a88sghvsuap55'

Alter index idx_t_id coalesce

END OF STMT

PARSE #139851695602760: c = 17997, e = 56662, p = 9, cr = 117, cu = 0, mis = 1, r = 0, dep = 0, og = 1, plh = 0, tim = 1427182487640739

Based on the cursor number, you can locate the data retrieval and reading process.

WAIT #139851695602760: nam = 'db file sequential read 'ela = 8 file # = 1 block # = 91705 blocks = 1 obj # = 164093 tim = 1427182487878712

WAIT #139851695602760: nam = 'db file sequential read 'ela = 6 file # = 1 block # = 91706 blocks = 1 obj # = 164093 tim = 1427182487878751

WAIT #139851695602760: nam = 'db file sequential read 'ela = 8 file # = 1 block # = 91707 blocks = 1 obj # = 164093 tim = 1427182487878989

WAIT #139851695602760: nam = 'db file sequential read 'ela = 9 file # = 1 block # = 91708 blocks = 1 obj # = 164093 tim = 1427182487879576

WAIT #139851695602760: nam = 'db file sequential read 'ela = 9 file # = 1 block # = 91709 blocks = 1 obj # = 164093 tim = 1427182487879914

(Space reasons, omitted ......)

WAIT #139851695602760: nam = 'db file sequential read 'ela = 7 file # = 1 block # = 91821 blocks = 1 obj # = 164093 tim = 1427182487929761

A large number of single-block read actions are concentrated on objects numbered 164093 each time.

SQL> select object_name, owner from dba_objects where object_id = 164093;

OBJECT_NAM OWNER

----------------------------------------

IDX_T_ID SYS

Note: The merge operation reads the original index data and then merges the index.

4. Conclusion

Compared with rebuild, coalesce operations are rarely discussed. As the structure changes, the storage structure is not adjusted and recycled. Compared with rebuild, coalesce has several advantages:

1. It does not need to occupy nearly twice the disk storage space
2. Can be operated online
3. Instead of rebuilding the index structure, you can merge the index leaf blocks as soon as possible to avoid overhead of the system.