Deep Analysis of database hotspot Blocks

 

Hotspot block Definition

The hotspot block of the database, in simple terms, is to access a small amount of data blocks too frequently in a very short period of time. The definition seems simple, but in the database, we need to observe or determine the hot block issue, but it is not that simple. To deeply understand how a database expresses hotspot blocks through some data features, we need to understand the features of some database processing mechanisms in this respect.

Data Buffer Structure

We all know that at the beginning of the query, the process first goes to the data buffer to find whether the data block required for the query exists. If not, it goes to the disk to read the data block to the memory. In this process, LRU chain management is involved in the data buffer (8i starts to measure the hot and cold Buffer Based on the touchpoint count to determine whether the buffer is on the cold or hot end of LRU ), for this part of content, we can get detailed documents from Oracle concepts. I am not going to discuss this part of content, which is not the focus of this article. Now our focus is on how the process quickly locates the block you want, or how to quickly determine that the block you want is not in the memory for physical read.

Let's think about it. With the development of hardware, the memory is getting bigger and bigger, and the cache buffer is getting bigger and bigger. How can we quickly locate the block we want in a large amount of memory? You cannot traverse all the buffers! This database introduces the concept of Hash (Oracle always uses the hash algorithm to quickly locate information. For example, it uses hash value to quickly locate whether SQL exists in the shared pool size, that is to say, objects in the shared pool size are also managed through the hash table. You can understand the basic knowledge of the data structure. An important feature of hash is quick search. For the simplest example, assume that we have a hash table that is a two-dimensional array a [200] [100] And now has 1000 unordered numbers, we need to find out whether a value exists from these 1000 numbers, or when we receive a number, we must determine whether it already exists. Of course, we can traverse these 1000 numbers, however, the efficiency is very low. But now we consider this method, that is, dividing 1000 numbers by 200, based on the remaining number, put it in a [200] [100] (assuming the maximum number of the same remainder cannot exceed 100), the remainder is the subscript of the array. In this way, on average, an array a [I] may contain about five numbers. When determining whether a number exists, divide the number by 200 (this is the simplest hash algorithm ), search for the array a [I] based on the remainder I as the subscript. after about five queries, you can determine whether the query already exists, in this way, the memory space a [200] [100] is opened in exchange for time (of course, the selection of the hash algorithm and the size of the hash table are a key issue ).

After understanding the basic hash principle, let's look at the management of Oracle block. The database also opens up hash tables for these blocks. If it is A, the number of blocks in one dimension is composed of parameters._ Db_block_hash_bucketsThat is, the existence of hash table [_ Db_block_hash_buckets], Starting from Oracle8i,_ Db_block_hash_buckets= Db_block_buffers * 2. The bucket in which a block is put is the file number and block number of the block (x $ BH. dbarfl, x $ BH. dbablk corresponds to the number of the block file in the tablespace and the number of the block in the file. x $ BH is the header information of all cache buffers, which can be queried in the form of tables) the hash algorithm is used to determine which bucket to store, And the bucket stores the buffers addresses. In this way, when we want to access data, we can obtain the extent of the segment (you can check it through dba_extents. The detailed information is not discussed here ), naturally, you know the number of the file to be accessed and the number of the block. Based on the file and block number, you can use the hash algorithm to calculate the hash bucket, and then you can find the buffer corresponding to the block in the hash bucket.

To maintain access to and changes to these blocks, Oracle also provides a latch to protect these blocks. Because it is necessary to avoid concurrent modification and access to these blocks by different processes at will, which may damage the structure of the block. Latch is a low-level lock provided by the database to maintain the internal structure. latch has a very short life cycle (below microseconds ), after the process adds latch, it quickly performs an access or modification action and then releases latch (there are no more latch details, but it may need another article to be clear ). The number of latch Parameters_ Db_block_hash_latchesTo define, one latch protects Multiple Buckets. From 8i, the default rule for this parameter is:

When cache buffers is less than 2052 Buffers

_ Db_block_hash_latches= Power (2, trunc (log (2,Db_block_buffers-4)-1 ))

When cache buffers exceeds 131075 buffers

_ Db_block_hash_latches= Power (2, trunc (log (2,Db_block_buffers-4)-6 ))

When cache buffers is between 2052 and 131075 buffers

_ Db_block_hash_latches= 1024

Through this rule, we can see that a latch can maintain about 128 buffers. Because latch makes block operations serialized (9i has been improved, reading and reading can be parallel, but reading and writing, writing and writing still need to be serialized), it is clear that we can think of a truth, if a large number of processes operate on the same block process, it will inevitably lead to competition on these latches, that is, latch waits. This is a system-level wait at a macro level. Understanding these principles laid the foundation for the diagnosis in the database below.

How to determine hotspot objects

If we often pay attention to the statspack report, we will find that sometimes cache buffer chains wait. This cache buffer chains is_ Db_block_hash_latchesThe general term of the defined latch can also be obtained by querying v $ latch:

Select "> sys @ OCN> select latch #, name, gets, misses, sleeps from V $ latch where name like 'cache buffer % ';

Latch # name gets misses sleeps
----------------------------------------------------------------------
93 cache buffers LRU chain 54360446 21025 238
98 cache buffers chains 6760354603 1680007 27085
99 cache buffer handles 554532 6 0

In this query result, we can see the latch status of all the cahce buffer chains since the database was started. Gets indicates that there are so many requests in total, misses indicates the number of failed requests (the lock fails), and sleeps indicates the number of sleep requests failed. Through sleeps, We can generally know whether latch competition in the database is serious, this indirectly represents whether the problem of hotspot blocks is serious. Because V $ latch is an aggregate information, we cannot obtain which blocks may be frequently accessed. Let's look at another view, that is, the address recorded by V $ latch_children, V $ latch_children.addr is the latch address.

Select "> sys @ OCN> select ADDR, latch #, child #, gets, misses, sleeps from V $ latch_children
2 Where name = 'cache buffers chains' and rownum <21;

ADDR latch # Child # Gets misses sleeps
----------------------------------------------------------
91b23b74 98 1024 10365583 3957 33
91b23374 98 1023 5458174 964 25
91b22b74 98 1022 4855668 868 15
91b22374 98 1021 5767706 923 22
91b21b74 98 1020 5607116 934 31
91b21374 98 1019 9389325 1111 25
91b20b74 98 1018 5060207 994 31
91b20374 98 1017 18204581 1145 18
91b1fb74 98 1016 7157081 920 23
91b1f374 98 1015 4660774 922 22
91b1eb74 98 1014 6954644 976 32
91b1e374 98 1013 4881891 970 19
91b1db74 98 1012 5371135 971 28
91b1d374 98 1011 5154497 990 26
91b1cb74 98 1010 5013796 936 18
91b1c374 98 1009 5667446 939 25
91b1bb74 98 1008 4673421 883 14
91b1b374 98 1007 4589646 986 17
91b1ab74 98 1006 10380781 1020 20
91b1a374 98 1005 5142009 1110 19

20 rows selected.

At this point, we can associate v $ latch_child.addr with the corresponding x $ BH. hladdr (this is the latch address of the Current Buffer recorded in the buffer header). The file number and block number of the block can be obtained through x $ BH.

Select "> sys @ OCN> select dbarfil, dbablk
From x $ BH
Where hladdr in
(Select ADDR
From (select ADDR
From v $ latch_children
Order by sleeps DESC)
Where rownum <11 );

Dbarfil dbablk
--------------------
4 6498
40 14915
15 65564
28 34909
40 17987
1 24554
8 21404
39 29669
28 46173
28 48221

........................

Therefore, we can break through the relationship between cache buffers chains and specific blocks. Then, we can continue to understand the blocks and what segments we need to know. This can be obtained through dba_extents.

Select distinct A. Owner, A. segment_name from
Dba_extents,
(Select dbarfil, dbablk
From x $ BH
Where hladdr in
(Select ADDR
From (select ADDR
From v $ latch_children
Order by sleeps DESC)
Where rownum <11) B
Where a. relative_fno = B. dbarfil
And a. block_id <= B. dbablk and A. block_id + A. Blocks> B. dbablk;

Owner segment_name segment_type
------------------------------------------------------------------------------
Alibaba biz_searcher table
Alibaba cmnty_user_message table
Alibaba cmnty_visitor_info_pk Index
Alibaba company_amid_ind Index
Alibaba company_draft table
Alibaba feedback_post table
Alibaba im_blacklist_pk Index
Alibaba im_group table
Alibaba im_group_assist_ind Index
Alibaba member table
Alibaba member_pk Index
Alibaba mlog $ _ Sample Table

........................

We have another method.

Select object_name
From dba_objects
Where data_object_id in
(Select OBJ
From x $ BH
Where hladdr in
(Select ADDR
From (select ADDR
From v $ latch_children
Order by sleeps DESC)
Where rownum <11 ));

Object_name
------------------------------------
I _ccol2
Resource_plan $
Dual
Fga_log $
Av_transaction
Company_draft
Member
Sample
Sample_group
Vertical_component
Member_pk
Sample_group_pk
Im_blacklist_pk
Im_contact
Im_group
Cmnty_user_message
Cmnty_visitor_info_pk
Im_offlinemsg_tid_ind
Offer
Offer_pk
Offer_email_ind
Offer_draft
Cmnty_user_message_td_bsm_ind
Cmnty_message_num_pk
Biz_express_member_id_ind

........................

 

 

Here, we can basically find the object corresponding to the hotspot block pair. But there is actually another way to obtain this information, that is, a method related to x $ BH. Tch. For the 8i Oracle, touch count is provided as a sign that blocks are hot and cold. when certain conditions are met, touch count is increased by one when the block is accessed by the process, after a standard is reached, it is moved to the LRU hot end (touch count is not described in detail here, it will be another article ). In a short period of time, the large block of touch count may imply that there are many accesses in the current period.

Select distinct A. Owner, A. segment_name, A. segment_type from
Dba_extents,
(Select dbarfil, dbablk
From (select dbarfil, dbablk
From x $ BH order by tch DESC) Where rownum <11) B
Where a. relative_fno = B. dbarfil
And a. block_id <= B. dbablk and A. block_id + A. Blocks> B. dbablk;

Owner segment_name segment_type
------------------------------------------------------------------------------
Alibaba cmnty_user_message table
Alibaba member_pk Index
Alibaba offer_draft_gmdfy_ind Index

This method is the same as above.

Select object_name
From dba_objects
Where data_object_id in
(Select OBJ
From (select OBJ
From x $ BH order by tch DESC) Where rownum <11 );
Object_name
---------------------------------------------------
Dual
Member_pk
Sample_group_pk
Cmnty_user_message_td_bsm_ind
Offer_draft_mid_gmdfy_ind
Offer_mid_gpost_ind
Offer_draft_pk
Member_gllogin_ind
Offer_mid_stat_gexpire_ind
Sample_mid_stat_ind

10 rows selected.

 

At this point, we have finished searching for hotspot blocks and hotspot objects, but we have not solved the problem yet.

 

Solution to hot issues

We have found both the hotspot block and the hotspot object, but how can we solve this problem? Generally, hotspot blocks will cause cache buffers chains to compete and wait, but it does not mean that cache buffer chains must start with hotspot blocks. In particular, it may be caused by latch quantity problems, that is, a large number of buffers managed by latch leads to fierce competition. However, we generally do not set the latch quantity easily. This is a hidden parameter of oracle.

In fact, the most effective way is to start with optimizing SQL statements. Poor SQL statements often lead to a large amount of unnecessary access, which is the root cause of hotspot blocks. For example, the query that should have passed the full table scan takes the range scan of the index, which will lead to a large number of repeated access to the block. This forms a hot issue. Or, for example, if the nested loops table is not locally connected, it may also cause a large number of repeated accesses to the non-driving table. At this time, our goal is to find these SQL statements and try to optimize them. In the statspack report, based on the SQL list in the report, if we determine the hotspot object through dba_extents rather than through dba_objects, we can convert the hotspot segment to the corresponding table through the searched hotspot segment, for non-partitioned indexes, index_name is segment_name. You can easily find the corresponding table_name through dba_indexes. For partitioned tables and partition indexes, you can also find the corresponding relationship between segment and table through dba_tab_partition and dba_ind_partitions. Use these tables to find related SQL statements in the statspack report.

Select SQL _text
From stats $ sqltext,
(Select distinct A. Owner, A. segment_name, A. segment_type from
Dba_extents,
(Select dbarfil, dbablk
From (select dbarfil, dbablk
From x $ BH order by tch DESC) Where rownum <11) B
Where a. relative_fno = B. dbarfil
And a. block_id <= B. dbablk and A. block_id + A. Blocks> B. dbablk) B
Where a. SQL _text like '%' | B. segment_name | '%' and B. segment_type = 'table'
Order by A. hash_value, A. Address, A. piece;

SQL _text
----------------------------------------------------------------
Select seq_sms_transaction.nextval from dual
Select seq_biz_express.nextval from dual
Select bizgroup. seq_grp_post.nextval from dual
Select seq_sample.nextval from dual
Select bizgroup. seq_grp_user.nextval from dual
Select seq_biz_searcher.nextval from dual
Select seq_offer_draft.nextval from dual
Select seq_company_draft.nextval from dual
Select seq_sample_group.nextval from dual
Select seq_cmnty_user_message.nextval from dual
Select sysdate from dual
Select seq_news_forum.nextval from dual
Select seq_sms_user.nextval from dual
Select seq_biz_member.nextval from dual
Select seq_pymt_managing.nextval from dual
E = '+ 08:00' nls_dual_currency = '$ 'nls_time_format = 'hh. Mi. ssx
Select seq_company_draft.nextval from dual
Select 1 from dual
Select seq_offer_draft.nextval from dual
Select seq_biz_express_category.nextval from dual

20 rows selected.

Of course, this is from the stats $ sqltext collected by statspack (you can find it in the statspack text report). In fact, we can find these SQL statements directly in the current database v $ sqlarea or V $ sqltext, and then try to optimize them.

Select SQL _text
From v $ sqltext,
(Select distinct A. Owner, A. segment_name, A. segment_type from
Dba_extents,
(Select dbarfil, dbablk
From (select dbarfil, dbablk
From x $ BH order by tch DESC) Where rownum <11) B
Where a. relative_fno = B. dbarfil
And a. block_id <= B. dbablk and A. block_id + A. Blocks> B. dbablk) B
Where a. SQL _text like '%' | B. segment_name | '%' and B. segment_type = 'table'
Order by A. hash_value, A. Address, A. piece;
SQL _text
----------------------------------------------------------------
Select null from dual for update Nowait
Select seq_sms_transaction.nextval from dual
Select seq_biz_express.nextval from dual
Select seq_im_group.nextval from dual
Select seq_sample.nextval from dual
= 'Dd-MON-RR HH. Mi. ssxff am tzr 'nls_dual_currency =' $ 'nls_comp ='
Select seq_biz_searcher.nextval from dual
Select seq_offer_draft.nextval from dual
Select seq_sample_group.nextval from dual
DD-MON-RR HH. Mi. ssxff am tzr 'nls_dual_currency = '$ 'nls_comp = 'bi
Select seq_cmnty_user_message.nextval from dual
Select sysdate from dual
Select seq_sms_user.nextval from dual
Imestamp_tz_format = 'dd-MON-RR HH. Mi. ssxff am tzr 'nls_dual_curre
Select seq_company_draft.nextval from dual
Select 1 from dual
Select User from dual
Select decode ('A', 'A', '1', '2') from dual

18 rows selected.

In addition to optimizing SQL statements, we can consider cache in the memory if hotspot tables or indexes are small, this may reduce the speed of physical reads and increase the SQL Running Speed (this does not reduce the number of accesses to the cache buffer chains). For sequences, we can set more caches. If it is an index object in the parallel server environment, and this index is a series of incremental types, we can consider reverse indexes (here we will not introduce reverse indexes here ).

Other symptoms of hotspot Blocks

There may also be some other hotspot block symptoms in the database. We can see some clues by waiting for V $ waitstat, V $ waitstat is based on the types of blocks in the data buffer (x $ BH. class.

Select "> sys @ OCN> select * from V $ waitstat;

Class count time
--------------------------------------
Data Block 1726977 452542
Sort Block 0 0
Save undo Block 0 0
Segment header 40 11
Save undo header 0 0
Free List 0 0
Extent map 0 0
1st Level BMB 611 112
2nd Level BMB 42 13
3rd Level BMB 0 0
Bitmap Block 0 0
Bitmap index Block 0 0
File Header Block 13 92
Unused 0 0
System undo header 111 28
System undo Block 7 0
Undo header 2765 187
Undo block 633 156

For example, before the emergence of assm tablespaces, due to the existence of freelist, if the table is frequently used by concurrent processes DML, there may be a large number of data block waits or free list waits. At this time, we find that we need to consider increasing the number of freelist after such a segment. For example, if a DML table is frequently accessed for a long time, too many accesses to the block in the rollback segment may occur. At this time, the Undo block may wait a lot. We may need to control the DML duration or find a solution to solve the problem from the application. If the Undo header has a lot of waiting time and does not use the Undo tablespace, you may need to increase the number of rollback segments.

Summary

Starting from the principle of hotspot blocks, this article introduces the generation and performance characteristics of hotspot blocks in detail from the internal structural features of oracle. This section describes how to diagnose hotspot objects and find SQL statements that cause hotspot objects. It also provides solutions to hot issues.