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

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

The standard index structure of oracle is B × tree structure, and a B × tree structure is composed of three kinds of block.

Root block (root block): there is only one block in B × tree, where all access indexes start, and there are many child blocks under root block.

Branch block (Branch blocks): this is the middle tier, branch block has no restrictions, it increases with the increase of leaf block, branch block is usually 4 layers, if more than 4 layers, it will affect the performance. Branch block is not deleted when we delete a row.

Leaf block: the leaf block is at the bottom, with index entries and rowid stored on it

Index and table data are cascaded, and index entries are automatically deleted when table data is deleted, so in index leaf

Block will produce fragmentation, that is, it is not recommended to create a large number of indexes on tables with a large number of updates on the OLTP system, which will affect performance.

Some people say that the deleted index entry space will not be reused because there will no longer be the same data as insert in the application. Actually, this.

This is not entirely true, except for half-empty leaf blocks, other deleted index space can be reused.

Eg:

All the experiments in this paper are tested on the following platforms:

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Prod SQL > create table test_idx as select seq_test.nextval id,2000 syear, a.* from dba_objects a

The table has been created.

SQL > insert into test_idx select seq_test.nextval id,2001 syear, a.* from dba_objects a

50780 rows were created.

SQL > insert into test_idx select seq_test.nextval id,2002 syear, a.* from dba_objects a

50780 rows were created.

SQL > commit

The submission is complete.

SQL > desc test_idx

SQL > create unique index idx_test on test_idx (syear,id)

The index has been created.

SQL > select segment_name, bytes/1024/1024, blocks, tablespace_name, extents

From dba_segments

Where segment_name = 'IDX_TEST'

SQL > select object_name, object_id, data_object_id

From dba_objects

Where object_NAME='IDX_TEST'

OBJECT_NAM OBJECT_ID DATA_OBJECT_ID

IDX_TEST 59545 59545

-Used to join X$BH table (query cache blocks from x$bh, use DATA_OBJECT_ID)

SQL >

Check how much the system now caches, which will be executed by sysdba users.

SQL > select count (*) from x$bh where obj=59545

COUNT (*)

-

seventeen

View the execution plan:

SQL > set autot trace exp

SQL > select syear,id from test_idx where syear > = 2000 and syear

Execute a query to have oracle cache the corresponding index block

SQL > set autot trace statis

SQL > select syear,id from test_idx where syear > = 2000 and syear

At this time, let's see how much oracle caches.

SQL > select count (*) from x$bh where obj=59545

COUNT (*)

-

four hundred and thirty eight

Increased from 17 to 438.

SQL > analyze index idx_test validate structure

The index has been analyzed

SQL > select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows From index_stats

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS

2 512 418 152340 1 417 0

SQL >

A total of 418 leaf blocks in the index idx_test have been cached, as expected. 1/3 of the data is deleted below.

SQL > delete from test_idx where syear=2001

SQL > commit

Clear the data cache

SQL > alter system flush buffer_cache

SQL > alter system flush buffer_cache

SQL > alter system flush buffer_cache

Query again, and found that the number of caches has decreased, from 438 to 396

SQL > select count (*) from x$bh where obj=59545

COUNT (*)

-

396 execute the query again to cache the index block

SQL > set autot trace stat

SQL > select syear,id from test_idx where syear > = 2000 and syear

This time, the number of query caches suddenly increased, from 438 to 774.

SQL > select count (*) from x$bh where obj=59545

COUNT (*)

-

seven hundred and seventy four

The sudden increase is presumed to be because the deleted empty index blocks need to be reloaded from disk into buffer cache, so

Cached will suddenly increase, with alter system flush buffer_cache can not completely clear data cache, I reboot below

Database, check it again.

Restart the database to completely empty the cached index

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 574619648 bytes

Fixed Size 1297944 bytes

Variable Size 192938472 bytes

Database Buffers 373293056 bytes

Redo Buffers 7090176 bytes

The database is loaded.

The database is already open.

Execute the query to cache the index

SQL > select syear,id from test_idx where syear > = 2000 and syear select count (*) from x$bh where obj=59545

COUNT (*)

-

four hundred and twenty five

I can see from the query results that the cache result 425 and the pre-deletion 438 have not changed much, while I have deleted 1/3 of the

Data, according to theory, there are very few tables that should be cached. We are looking at how many leaf blocks are now.

SQL > analyze index idx_test validate structure

The index has been analyzed

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

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS

2512 418 152340 1 417 50780 from the result, the leaf block is the same as before deletion, which further proves that although the index leaf block has been deleted, it has not

Free up space, and the query statement does not skip these deleted index blocks, so these fragments have a lot of impact on performance.

So how to completely delete the index leaf block?

SQL > alter index idx_test rebuild nologging online

The index has changed.

SQL > analyze index idx_test validate structure

The index has been analyzed

SQL > select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows fr

Om index_stats

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS

2 384 276 101560 1 275 0

SQL >

SQL > select count (*) from x$bh where obj=59545

COUNT (*)

-

one hundred and thirty nine

SQL >

From the above results, we can see that the space of the deleted index leaf block has been freed.

In the insert2003 year after the deletion of 2001

SQL > insert into test_idx select seq_test.nextval id,2003 syear, a.* from dba_objects a

50781 rows were created.

SQL > commit

The submission is complete.

SQL > select segment_name, bytes/1024/1024

2 blocks, tablespace_name, extents

3 from dba_segments

4 where segment_name = 'IDX_TEST'

SEGMENT_NAME BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS

-

IDX_TEST 4 512 USERS 19

SQL > analyze index idx_test validate structure

The index has been analyzed

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

HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS

2 512 403 152341 1 402 0

SQL >

From the query results, the total number of blocks in the index is 512, which does not grow after delete and insert, indicating the space for index deletion.

It has been reused.

What is a half-empty leaf block (Half Empty Leaf Blocks)

A Leaf Block is initialized with index key values, and when some key values are deleted, the leaf block contains the deleted

The index key value, which also contains the undeleted index key value, is called "Half Empty Leaf Blocks".

Let's take test_idx as an example

SQL > insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_objects a

50781 rows were created.

SQL > insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_objects a

50781 rows were created.

SQL > insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_objects a

50781 rows were created.

SQL > insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_objects a

50781 rows were created.

SQL > commit

The submission is complete. SQL > select segment_name, bytes/1024/1024

Blocks, tablespace_name, extents

From dba_segments

Where segment_name = 'IDX_TEST'

SEGMENT_NAME BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS

-

IDX_TEST 1152 USERS 24 SQL > delete from test_idx where syear=2005 and mod (id,2) = 0

101562 lines have been deleted.

SQL > commit

The submission is complete.

Before reinserting 101562 rows of data

SQL > insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_objects a

50781 rows were created.

SQL > insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_objects a

50781 rows were created.

SQL > select segment_name, bytes/1024/1024

Blocks, tablespace_name, extents

From dba_segments

Where segment_name = 'IDX_TEST'

SEGMENT_NAME

BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS

-

IDX_TEST

11 1408 USERS 26

SQL >

Deleted 101562 rows of data, and then re-added 101562 rows of data, but the index block increased by 1408-1152 million 256 blocks, so it is said that the half-empty block

The index is not reused. You can also see from the trace below

SQL > select object_id from dba_objects where object_name='IDX_TEST'

OBJECT_ID

-

59545

The command to get tree's dump is as follows

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

The session has changed.

Then look at the corresponding trace file, as follows:

Branch: 0x100972c 16815916 (0: nrow: 3, level: 2)

Branch: 0x1007fe5 16809957 (- 1: nrow: 511, level: 1)

Leaf: 0x100972d 16815917 (- 1: nrow: 378rrow: 378)

Leaf: 0x100972e 16815918 (0: nrow: 378rrow: 378)

.

.

.

Leaf: 0x1007ff2 16809970 (14: nrow: 400 rrow: 400)

Leaf: 0x1007ff6 16809974 (15: nrow: 400 rrow: 332)

Leaf: 0x1007ffa 16809978 (16: nrow: 400 rrow: 200)

Leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)-Half empty blocks

Leaf: 0x1008002 16809986 (18: nrow: 400 rrow: 200)

.

.

.

Leaf: 0x1009f86 16818054 (19: nrow: 400 rrow: 200)

Leaf: 0x1009f4b 16817995 (20: nrow: 400 rrow: 400)

.

.

.

Leaf: 0x1009f4f 16817999 (21: nrow: 400 rrow: 400)

Leaf: 0x100a15f 16818527 (nrow: 56 rrow: 56)

Leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)

Explanation: leaf block contains 400 rows, and this block has deleted 200 rows of key values

Identify whether the index is fragmented

To get information about the index, use the following command

Analyze index index_name validate structure or validate index index_name

Analyze index index1 validate structure: is used to analyze whether the data block of the index has bad blocks, and to determine whether the index needs to be re-established based on the analyzed data (stored in index_stats).

After running the command, then query in the view index_stats, this view records some information about the index, this view has only one record, so only one index can be analyzed at a time.

1. If the number of rows deleted accounts for 30% of the total number of rows, that is, del_lf_rows / lf_rows > 0.3, then consider index defragmentation

two。 If "hight" is greater than 4, you can consider defragmentation

3. If the number of rows in the index (LF_rows) is much less than 'LF_BLKS', then there is a large delete action that needs to be defragmented

Index defragmentation method

1. Recreate index

2. Alter index skate.idx_test rebuild nologging parallel 4 online

3. If the leaf block is half-empty or near empty, you can run "alter index coalesce" to merge

At this point, the study on "oracle index page fragment analysis" 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.