Hot block definition
The database's hot block, from simple, is a very frequent access to a small amount of data blocks in a very short period of time. Definitions seem to be very simple, but actually in the database, we have to observe or determine the problem of hot blocks, but it is not that simple. It is necessary to understand how the database is how to represent hotspots through some data characteristics, and we need to understand the characteristics of some databases in this area.
Data buffer structure
We all know that when the query begins, the process first goes to find the data block required to query the query. If not, read the data block to memory on the disk. In this process, the management of the LRU chain in the data buffer (8i starts with the contact point count as the standard measure Buffer cold heat to determine whether the buffer is in the cold end of the LRU or the hot end), about this part of the content, from Oracle Concepts I can get a detailed document, I am not prepared to discuss this part, this is not the focus of this article. Now our focus is how to quickly locate the block you want, or how to quickly determine that the desired Block is not physically read.
We think about it carefully. With the development of hardware, the memory is getting bigger and bigger, and Cache Buffer is getting bigger and bigger. How can we quickly locate it in a large number of BLOCKs? You can't go to all Buffers to traverse! In this database, the concept of Hash (the fast positioning information in Oracle is always passed through the Hash algorithm, such as fast positioning SQL is located in shared pool size is to position through Hash Value, that is, the object of Shared pool size is also passed through Hash table is managed, understanding the basic knowledge of a point of data structure, knowing that a major function of Hash is to quickly find it. For the simplest example, assume that we have a Hash Table is a 2D array a [200] [100], now there is 1000 unordered numbers, we have to find a certain value from this 1000 digits, or Say that when we receive a number, we must judge whether there is already existing, of course, we can traverse these 1000 numbers, but such efficiency is very low. But now we consider a method, that is, divide 1000 numbers in 200, according to the remaining number, put it in a [200] [100] (assuming the maximum number of the same remaining number does not exceed 100), the remainder is the array Sign. In this way, the average is in an array a [i] may have 5 left and right numbers. When we want to discriminate if a number is present, divide this number in 200 (this is the simplest HASH algorithm), according to the remaining number of I. A [i] in the subscript, about 5 times It can be discriminated whether there is already existent, so that the time is opened by opening up memory space a [200] [100] (of course, the Hash algorithm is selected and the size of the Hash Table is a critical issue).
After understanding the basic Hash principle, let's take a look at Oracle's Block management. The database has also opened Hash Table for these blocks. It is assumed to be A, then the number in one dimension is determined by the parameter _db_block_hash_buckets, that is, there is Hash Table A [_DB_BLOCK_HASH_BUCKETS], starting from Oracle8i, _db_block_hash_buckets = db_block_buffers * 2. And one block is put in which buckets is called by Block's file number, block number (x $ bh.dbarfl, x $ bh.dbablk corresponds to the block file belonging to the related number in the tablespace and block in the file Number, x $ BH is all the HEADER information of all Cache Buffers, inquiry can be queried by the form of the form) Do the Hash algorithm decisive which BUCKET is put, and the Bucket is stored in the buffers. When we want to access the data, you can get the segment's extent (can be found by DBA_EXTENTS, the detailed information source is not discussed here), naturally know the file number and block number to be accessed, according to the file and block number The Hash algorithm calculates Hash Bucket, and then goes to the BUFFER corresponding to the Block in Hash Bucket. In addition, in order to maintain access and changes to these Block, Oracle also provides a latch to protect these blocks. Because you want to avoid different processes, it is likely to modify and access these blocks, which is likely to destroy the structure of the Block. Latch is a low-level lock that maintains internal structures provided inside the database. The Latch's survival cycle is extremely short (microsecond below), and the process is quick to quickly perform an access or modify the action and release Latch (About Latch No Excessive elaboration, it may be another article to explain it clearly). This amount of LATCH is defined by parameter_db_block_hash_latches, and a Latch has a lative corresponding to multiple Buckets. Starting from 8i, the Default rule of this parameter is:
When cache buffers less than 2052 BUFFERS
_db_block_hash_latches = power (2, trunc (log (2, db_block_buffers - 4) - 1))
When Cache Buffers is more than 131075 BUFFERS
_db_block_hash_latches = power (2, trunc (log (2, db_block_buffrs - 4) - 6))
When Cache Buffers is located between 2052 and 131075 buffers
_DB_BLOCK_HASH_LATCHES = 1024
Through this rule, we can see that a latch can maintain around 128 BUFFERS. Since Latch makes serialization of Block's operation (improvements in 9i, reading and reading can be parallel, but reading and writing, writing and writing), it is obvious that we can think of a truth, if a large number of processes are the same The Block process is operated, inevitably causes competition on these Latch, that is, the waiting for Latch. This is a system-level in macro. Understand these principles, lay the foundation for our diagnosis in our database.
How to determine hot object objects
If we often pay attention to the StatsPack report, we will find that the wait for Cache Buffer Chains sometimes appears. This cache buffer chains is the general name of the LATCH defined by _db_block_hash_latches, and you can get: 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 5436046 21025 238 98 Cache Buffers Chains 6760354603 1680007 27085 99 Cache Buffer Handles 554532 6 0
In this query result, we can see the status of all CAHCE BUFFER CHAINS for all CAHCE BUFFER CHAINs since the database, and Get represents a total of many requests. Misses means the number of times the request fails (unlock is unsuccessful), and the SLEEPS means requested Number of failures, through Sleeps, we can generally know whether the competition in the database is serious, which is indirectly characterized by the problem of hot blocks. Since V $ Latch is a aggregated information, we can't get which blocks may have frequent access. Then we have to look at another view information, that is, V $ latch_children, v $ latch_children.addr record is the address of this LATCH.
Select "> sys @ o> SELECT Addr, Latch #, Child #, Gets, Misses, Sleeps from v $ latch_children 2 where name = 'cache buffers' and rownum <21;
Addr Latch # child # gets misses sleeps ---------------------------------------------------------------------------------------------------------------- ------ ---------- 91B23B74 98 1024 10365583 3957 3391B23374 98 1023 5458174 964 2591B22B74 98 1022 4855668 868 1591B22374 98 1021 5767706 923 2291B21B74 98 1020 5607116 934 3191B21374 98 1019 9389325 1111 2591B20B74 98 1018 5060207 994 3191B20374 98 1017 18204581 1145 1891B1FB74 98 1016 7157081 920 2391B1F374 98 1015 4660774 922 2291B1EB74 98 1014 6954644 976 3291B1E374 98 1013 4881891 970 1991B1DB74 98 1012 5371135 971 2891B1D374 98 1011 5154497 990 2691B1CB74 98 1010 5013796 936 1891B1C374 98 1009 5667446 939 2591B1BB74 98 1008 4673421 883 1491B1B374 98 1007 4589646 986 1791B1AB74 98 1006 10380781 1020 2091B1A374 98 1005 5142009 1110 1920 rows selected.
At this point we can associate the corresponding x $ bh.hladdr based on V $ latch_child.addr (this is the LATCH address of the current buffer recorded in the buffer header), and can get the block file number and block number by x $ BH.
Select "> SYS @ OCN> Select DBARFIL, DBABLK from x $ BH WHER HLADDR in (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 ........................
From this, we have opened the relationship between cache buffers chains and specific blocks, then continue, knowing block, we need to know which segments exactly. This can be obtained by DBA_EXTENTS.
select distinct a.owner, a.segment_name from dba_extents a, (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)) bwhere a. Relative_fno = b.dbarfiland a.block_id <= b.dbablk and a.block_id a.blocks> b.dbablk;
Owner segment_name segment_type ------------------------------------------------- ------------- ------------------ ALIBABA BIZ_SEARCHER TABLEALIBABA CMNTY_USER_MESSAGE TABLEALIBABA CMNTY_VISITOR_INFO_PK INDEXALIBABA COMPANY_AMID_IND INDEXALIBABA COMPANY_DRAFT TABLEALIBABA FEEDBACK_POST TABLEALIBABA IM_BLACKLIST_PK INDEXALIBABA IM_GROUP TABLEALIBABA IM_GROUP_LID_IND INDEXALIBABA MEMBER TABLEALIBABA MEMBER_PK INDEXALIB Aba mlog $ _Sample Table ........................
We have another way
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_CCOL2RESOURCE_PLAN $ DUALFGA_LOG $ AV_TRANSACTIONCOMPANY_DRAFTMEMBERSAMPLESAMPLE_GROUPVERTICAL_COMPONENTMEMBER_PKSAMPLE_GROUP_PKIM_BLACKLIST_PKIM_CONTACTIM_GROUPCMNTY_USER_MESSAGECMNTY_VISITOR_INFO_PKIM_OFFLINEMSG_TID_INDOFFEROFFER_PKOFFER_EMAIL_INDOFFER_DRAFTCMNTY_USER_MESSAGE_TD_BSM_INDCMNTY_MESSAGE_NUM_PKBIZ_EXPRESS_MEMBER_ID_IND
........................
Here we can basically find hot blocks to the corresponding objects. However, there is still another way to get this information, that is, one way related to X $ BH.TCH. For the 8i Start Oracle, the touch point is provided as a block as a cold-hot flag, and the block is accessed once in the case of a Touch Count to increase the Touch Count, and then move it to the LRU hotter ( About Touch Count does not make a detailed introduction here, it will be a major article). In a short period of time, Touch Count's BLOCK may imply more accessed in a certain cycle. select distinct a.owner, a.segment_name, a.segment_type from dba_extents a, (select dbarfil, dbablk from (select dbarfil, dbablk from x $ bh order by tch desc) where rownum <11) bwhere a.RELATIVE_FNO = b.dbarfiland A.Block_ID <= B.dbablk and a.block_id a.blocks> B.dbablk;
Owner segment_name segment_type ------------------------------------------------- ------------------------------ Alibaba cmnty_user_message Tablealibaba MEMBER_PK INDEXALIBABA OFFER_DRAFT_GMDFY_IND INDEX
There is also this method as above.
SELECT Object_name from dba_objects where data_object_id in (Select Obj from x $ BH ORDER BY TCH DESC) Where rownum <11); object_name ---------------------------------------------------------------------------------------------------------------------------------------- ------------------------------- DUALMEMBER_PKSAMPLE_GROUP_PKCMNTY_USER_MESSAGE_TD_BSM_INDOFFER_DRAFT_MID_GMDFY_INDOFFER_MID_GPOST_INDOFFER_DRAFT_PKMEMBER_GLLOGIN_INDOFFER_MID_STAT_GEXPIRE_INDSAMPLE_MID_STAT_IND
10 rows selected.
Here, we are looking for hot blocks and hotspot objects, but we have not solved the problem.
Hot problem solving
Hot blocks and hotspots we have found, but how should we solve this problem? In general, hot blocks can cause Cache Buffers chains to wait, but not, cache buffer chains must be because of hot blocks, in special cases may be due to the problem of latch, that is, a Latch management There is too much in the buffers and causing the competition. But the quantity of the latch will generally not set it easily, this is the hidden parameters of Oracle.
In fact, the most effective way is from optimizing SQL, poor SQL often brings a lot of unnecessary access, which is the root cause of hot blocks. For example, the inquiry of this use through the full table scanning has taken the indexed RANGE SCAN, which will bring a lot of repetition access to blocks. Thereby forming a hot problem. Or, if you don't have a table connection of Nested loops, you may also cause a lot of repetition access to the non-drive table. So at this time, our goal is to find these SQL and try to optimize. In the StatsPack report, based on the SQL list in the report, if we are determined by DBA_EXTENTS instead of DBA_Objects, it can be converted to a corresponding table by looking for hotspot segment, for the index of the non-partition, index_name is segment_name The corresponding Table_Name is easy to find through DBA_Indexes, which can also find the correspondence between Segment and Table for partition tables and partition indexes and DBA_TAB_PARTITION and DBA_IND_PARTITIONS. Go to the relevant SQL through these Table to the StatsPack report. select sql_text from stats $ sqltext a, (select distinct a.owner, a.segment_name, a.segment_type from dba_extents a, (select dbarfil, dbablk from (select dbarfil, dbablk from x $ bh order by tch desc) where rownum <11 Bwhere a.relative_fno = b.dbarfiland a.block_id <= b.dbablk and a.block_id a.blocks> b.dbablk) bwhere a.sql_text like '%' || B.SEGMENT_NAME || '%' and B .SEGMENT_TYPE = 'Table'Rder by a.hash_value, a.address, A.PIECE
SQL_Text ------------------------------------------------- --------------- SELECT SEQ_SMS_TRANSACTION.nextval FROM DUALSELECT SEQ_BIZ_EXPRESS.nextval FROM DUALSELECT bizgroup.seq_grp_post.NextVal FROM DUALSELECT SEQ_SAMPLE.nextval FROM DUALSELECT bizgroup.seq_grp_user.NextVal FROM DUALSELECT SEQ_BIZ_SEARCHER.nextval FROM DUALSELECT SEQ_OFFER_DRAFT.nextval fROM DUALselect seq_Company_Draft.NextVal from DUALSELECT SEQ_SAMPLE_GROUP.nextval fROM DUALSELECT SEQ_CMNTY_USER_MESSAGE.nextval fROM DUALSELECT SYSDATE fROM DUALselect seq_News_Forum.NextVal from DUALSELECT SEQ_SMS_USER.nextval fROM DUALselect seq_Biz_Member.NextVal from DUALselect seq_Pymt_Managing.NextVal from DUALE = ' 08: 00' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSXSELECT SEQ_COMPANY_DRAFT.nextval fROM DUALSELECT 1 fROM DUALselect seq_offer_draft.NextVal from DUALselect seq_Biz_Express_Category.NextVal from DUAL20 rows selected.
Of course, you are looking for STATS $ SQLText from StatsPack (you can find in the text report of StatsPack), in fact, we can find these SQL directly in the current database V $ SQLAREA or V $ SQLTEXT , Then try to optimize.
select sql_text from v $ sqltext a, (select distinct a.owner, a.segment_name, a.segment_type from dba_extents a, (select dbarfil, dbablk from (select dbarfil, dbablk from x $ bh order by tch desc) where rownum <11 Bwhere a.relative_fno = b.dbarfiland a.block_id <= b.dbablk and a.block_id a.blocks> b.dbablk) bwhere a.sql_text like '%' || B.SEGMENT_NAME || '%' and B .SEGMENT_TYPE = 'Table'Rder by a.hash_value, a.address, a.piece; sql_text ----------------------------- ---------------------------------- Select Null from Dual for Update NowaitslectSelect SEQ_SMS_TRANSACTION.NEXTVAL from DUALSELECT SEQ_BIZ_EXPRESS.NEXTVAL FROM DUALSELECT SEQ_IM_GROUP.nextval FROM DUALSELECT SEQ_SAMPLE.nextval FROM DUAL = 'DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY = '$' NLS_COMP = 'SELECT SEQ_BIZ_SEARCHER.nextval FROM DUALSELECT SEQ_OFFER_DRAFT.nextval FROM DUALSELECT SEQ_SAMPLE_GROUP.nextval FROM DUALDD-MON-RR HH.MI.SSXFF AM TZR 'NLS_DUAL_CURRENCY =' $ 'NLS_COMP =' biselect SEQ_CMNTY_USER_MESSAGE.NEXTVAL from DUALSELECT SYSD ATE FROM DUALSELECT SEQ_SMS_USER.nextval FROM DUALIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRESELECT SEQ_COMPANY_DRAFT.nextval FROM DUALSELECT 1 FROM DUALSELECT USER FROM DUALSELECT DECODE ( 'A', 'A', '1', ' 2 ') from dual18 rows selected.
In addition to optimizing SQL, of course, for hot tables or indexes, if small, we can consider cache in memory, which may reduce physical reading and improve SQL running speed (this does not reduce the number of access to Cache Buffer Chains), For sequences, we can set some cache more than the sequence. If it is an index object in a parallel server environment, and this index is a series of increasing types, we can consider reverse indexes (about reverse indexing here, but also introduce).
Other related symptoms of hot blocks
There may also be some other hot block symptoms in the database. You can see some clues through V $ WaitStat, V $ WaitStat is based on the type of Block in the data buffer (x $ bh.class) Waiting condition. SELECT "> sys @ ocn> select * from v $ waitstat;
Class Count Time -------------------------- ---------- Data Block 1726977 452542Sort Block 0 0save undo block 0 0segment header 40 11save undo header 0 0free list 0 0extent map 0 01st level bmb 611 1122nd level bmb 42 133rd level bmb 0 0bitmap block 0 0bitmap index block 0 0file header block 13 92unused 0 0system undo header 111 28system undo block 7 0undo header 2765 187undo Block 633 156
For example, before the ASSM table space appears, due to the existence of Freelist, if the table is often concurrent, there may be a large amount of Data Block waiting, or the waiting for Free List. Then this time we find such segments, you need to consider adding the amount of freeelist. For example, the table that often occurs for a long time is frequently accessed frequently, which will cause too much access to the block in the segment, which may be more than the Wait for undo block. Then we may need to control DML's time length or find a way to solve the problem from the application. If you are more waiting for the undo header, you may need to consider increasing the number of rollback segments before using undo TableSpace.
to sum up
This paper starts from the principle of hotspots, and the generation and performance characteristics of hot block blocks are introduced in detail by Oracle's internal structural characteristics. Further, the diagnostic hot target and the method of finding the SQL that cause hotspot objects. And provide a solution direction from a solution to hotspot.
