Small table hotspot disks with abnormal methods for conversion of large data volumes, abnormal hot spots

Source: Internet
Author: User

Small table hotspot disks with abnormal methods for conversion of large data volumes, abnormal hot spots


I encountered a strange problem.

There are two tables, which are about 0.3 billion of the Data. Table A has the A1 field, table B has the B1 field, and are updated through other tables. A1 uses the C table and B1 uses the D table. The difference is that the C table is very small and has only nearly 10 thousand rows. The D table is very large and has 0.3 billion rows. (Table C is followed by a small table, and table D is used as a large table)


Because table B has 0.3 billion million data records, we need to obtain updates from Table D's 0.3 billion data records. At the beginning, I was very worried about this. I plan to do this for table D, however, after testing, we found that table B is updated faster than table. Think twice about it. Ask the DBA and find that there is a hot disk problem after analysis.


During the update process, the index field modulo is used to update the table. Each table is divided into about 50 modulo values. Since the small table is read during the update process, the same disk is read every time, so there is a hotspot. When reading a large table, the probability of reading the same disk is very low, table B is faster than table.


Although it is a bit twisted, this is the reason. But how can this problem be solved?


The first way I think of it is to put the data keep of the small table into the cache, that is, to access the small table, use the database cache as soon as possible (see the

According to the instructions in this article, I have added small tables to the memory. Although the efficiency has been improved a little, the effect is not obvious. I still have no idea why.


The second way, according to the DBA's guidance, I change the small table pctfree (can see the to large, to 70, this can make the table Storage more dispersed, maybe because my table is too small, it basically does not work.


In the end, I thought that since it was a problem caused by modulo splitting, why not directly split the table according to the modulo logic? In this way, the access will be more dispersed. I added a modulo field to the small table and copied 50 copies directly. The original index was added and the modulo ID was created as a composite index. Split the table again and put the table keep in the cache.

-- Table

Create table prod_attr_spec_on_mod pctfree 70 as select * from prod_attr_spec where 1 = 2;

Alter table prod_attr_spec_on_mod add TABLE_NAME VARCHAR2 (50 );

Alter table prod_attr_spec_on_mod add FUN_NAME VARCHAR2 (100 );

Alter table prod_attr_spec_on_mod add PROCESS_MOD_NUM number;



I number;


For I in 0 .. 50 loop

Insert into prod_attr_spec_on_mod

Select t .*,

'Serv _ attr', 'Get _ PRODUCT_ATTR_ID_BY_OLD_ONMOD ', I

From prod_attr_spec t;


End loop;


-- Index

Create index IDX_PRIDATTRIDPRODATTRonmod on prod_attr_spec_on_mod (attr_id, PROCESS_MOD_NUM );

-- Keep into memory

Alter table prod_attr_spec_on_mod storage (buffer_pool keep );


Alter table prod_attr_spec_on_mod cache;


-- Table Analysis



(Ownname => 'gj _ user ',

Tabname => 'prod _ attr_spec_on_mod ',

Estimate_percent = & gt; 100,

Method_opt => 'for all columns ',

Cascade => true,

Degree => 16




After doing so, the efficiency is more than doubled, and the method is indeed abnormal, but it is quite practical.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.