Examples of scenarios where hot blocks and latch appear in an indexed block of data

The hot block of the index is actually different from the principle of the hot block of the data block, and it is caused by the large number of sessions accessing the same index block together, our solution has the reverse index, the partition index and so on. We say that any one way is not perfect, there are advantages must have disadvantages, we have indexed key values of the index block from the order to break out of the sequence, reduce latch contention, but also increase the number of Oracle scan block. We use multiple tests to improve the overall performance of the system as a goal.

Leo1@leo1>create table Leo1 (ID number, name VARCHAR2 (200)); A leo1 table was created

Table created.

Leo1@leo1>insert into Leo1 (id,name) Select Object_id,object_name from Dba_objects, copy the first 2 fields of dba_objects to the Leo1 table.

71966 rowscreated.

Leo1@leo1>select id,name from Leo1 where rownum<10; Okay, it's done.

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 leo1_index on leo1 (ID); Create an index on the ID column on the leo1 table

Index created.

Leo1@leo1>execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO1 ', cascade=>true); To do an analysis of tables and indexes, cascade=>true refers to the index on the cascading table to do the analysis together

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 71968 Lines

71968 rowscreated.

Why more than the Leo1 table 2 more lines, is more leo1 and leo1_index these 2 objects, we have just built.

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); Do analysis

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 order of the index block to the unordered index block storage, so we query an interval range, the index key value will fall on the discontinuous index block, prevent the production of hot block, reduce the "latch linked list" contention. This is probably the only case where a reverse index is used. Because the reverse index does not support the index range Scan feature, only the index full scan scan, how to understand, for example, the reverse index can not help you retrieve the id> 1 and ID < 10 rows, but can help you retrieve id=10 rows, also That is, the scanning efficiency of the range is low, and the equivalent scanning efficiency is very high.

Leo1@leo1> set autotrace on; Start 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 |