Use examples to describe the scenarios of hot blocks & Latch in index data blocks, and provide solutions

Introduction: indexing hot blocks are actually very different from the hot blocks of data blocks. They are also caused by a large number of sessions accessing the same index block. Our solutions include reverse indexing, partition indexes. Let's talk about any one

Introduction: indexing hot blocks are actually very different from the hot blocks of data blocks. They are also caused by a large number of sessions accessing the same index block. Our solutions include reverse indexing, partition indexes. Let's talk about any one

Introduction: indexing hot blocks are actually very different from the hot blocks of data blocks. They are also caused by a large number of sessions accessing the same index block. Our solutions include reverse indexing, partition indexes. We say that any method is not perfect. If it has advantages, it will inevitably have disadvantages. We split the index blocks containing index key values from sequential arrangement to unordered arrangement, Hong Kong Space, this reduces latch contention and increases the number of oracle scan blocks. In actual use, we test each other to improve the overall performance of the system.

LEO1 @ LEO1> create table leo1 (id number, name varchar2 (200); creates a leo1 table

Table created.

LEO1 @ LEO1> insert into leo1 (id, name) select object_id, object_name from dba_objects; copy the first two fields of dba_objects to the leo1 table.

71966 rowscreated.

LEO1 @ LEO1> select id, name from leo1 where rownum <10; finished

ID NAME

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

673 CDC_CHANGE_SOURCES $

674 I _CDC_CHANGE_SOURCES $

675 CDC_CHANGE_SETS $

676 I _CDC_CHANGE_SETS $

677 CDC_CHANGE_TABLES $

678 I _CDC_CHANGE_TABLES $

679 CDC_SUBSCRIBERS $

680 I _CDC_SUBSCRIBERS $

681 CDC_SUBSCRIBED_TABLES $

LEO1 @ LEO1> create index leow.index on leo1 (id); create an index in the id column of leo1 table

Index created.

LEO1 @ LEO1> execute dbms_stats.gather_table_stats ('leo1 ', 'leo1', cascade => true); perform an analysis on tables and indexes, Hong Kong Space, cascade => true refers to the analysis of the indexes in the cascade table.

PL/SQL proceduresuccessfully completed.

LEO1 @ LEO1> create table leo2 (id number, name varchar2 (200); create a leo2 table

Table created.

LEO1 @ LEO1> insert into leo2 (id, name) select object_id, object_name from dba_objects; insert row 71968

71968 rowscreated.

Why is there two rows more than the leo1 table, that is, two more objects, leo1 and leo1_index, which we just created.

LEO1 @ LEO1> create index leo2_index on leo2 (id) reverse; create a reverse index

Index created.

LEO1 @ LEO1> execute dbms_stats.gather_table_stats ('leo1 ', 'leo2', cascade => true); analyze

PL/SQL proceduresuccessfully completed.

LEO1 @ LEO1> select index_name, index_type, table_name, status from dba_indexes wheretable_name in ('leo1 ', 'leo2 ');

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS

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

LEO1_INDEX NORMAL LEO1 VALID

LEO2_INDEX NORMAL/REV LEO2 VALID

LEO2_INDEX is a reverse index. We use it to reverse the ordered index block into an unordered index block storage, so that when we query a range, the index key value falls on the discontinuous index block to prevent hot blocks and reduce the competition for "latch linked list. This may be the only use case of reverse indexes. The reverse index does not support the index range scan function. It only supports full index scan of index full scan. How can this problem be solved? Vm, for a simple example, reverse indexing cannot help you retrieve rows with id> 1 and id <10, but it can help you retrieve rows with id = 10, that is, the range scanning efficiency is low, the scanning efficiency is very high.

LEO1 @ LEO1> set autotrace on; start the execution plan

LEO1 @ LEO1> select count (*) from leo1 whereid <100; this is the B-TREE index execution plan

COUNT (*)

----------

98

Execution Plan

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

Plan hash value: 423232053

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

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

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

| 0 | select statement | 1 | 5 | 2 (0) | 00:00:01 |

| 1 | sort aggregate | 1 | 5 |

| * 2 | index range scan | leow.index | 96 | 480 | 2 (0) | 00:00:01 |

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

Index range scanning, because we query index key values are stored in consecutive index blocks, so only two consistent reads are available, it only scans the index blocks that meet the conditions to find the corresponding records.

PredicateInformation (identified by operation id ):

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

2-access ("ID" <100)

Statistics

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

0 recursive cballs

0 db block gets

2 consistent gets

0 physical reads

0 redo size

526 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1 @ LEO1> select count (*) from leo2 whereid <100; reverse index execution plan

COUNT (*)

----------

98

Execution Plan

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

Plan hash value: 1710468575

Bytes ------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes ------------------------------------------------------------------------------------