2016-08-11 Chen Long-mo College
Explore Oracle High water level impact on database performance1
Hello everyone! I am a chen long from the cloud and Emme, and I am currently primarily responsible for Oracle technical support work. When I began to learn Oracle, listen to Eygle teacher said, to learn technology, must do more experiments, do more study records, theory and practice to combine, to really understand the absorption of those knowledge, so today I would like to share the Oracle high watermark and SQL access performance of the research experience. Not very deep research, just want to share with you my Oracle learning process, I hope to communicate with you progress. The reason I share this learning is because in some of the optimization projects I've been through, I've had problems with the slow performance of SQL access due to high water levels, which has prompted me to go deeper into understanding: what is a high watermark? What are the effects of high water levels? How can we better solve the problem caused by high water level? Before we begin our deep analysis, let's take a look at the high watermark HWM.
1
Fundamentals of HWM (concept)In Oracle, the high watermark (High-warter mark, HWM) is used to describe the location of data blocks, which means that those blocks are already in use and those that are not. When you create a new table, Oracle assigns a segment to the object. In this section, the first block of the first section is created and is allocated to store some header information. The management mechanism of high water level is different in MSSM and ASSM, the following respectively describes the two management mechanisms: Manual segment Space Management (MSSM) in the previous manual segment space Management (MSSM), high water mark Hwm, a segment divided into three parts, header block,used block ( Row data), unused block, where the dividing line between used block and unused block is the high water mark Hwm, when the full table scan, will scan all the data blocks under HWM, even if many of the used block data is deleted, The full-table scan is still subject to HWM. When inserting inserts, there is no free block in the freelist, the HWM moves upward, but only moves upward, does not shrink automatically, even if the delete is large, resulting in a lot of free blocks under HWM. Such as:
The first block in the first area is called the segment header (SEGMENT Heade), and some information is stored in the segment header, where the HWM information is stored. At this point, because the first block of the first section is used to store some information about the segment header, although no actual record is stored, but it is also used, at this time HWM is located in the 2nd block when we constantly insert data into Pm_user, the 1th block has not been placed behind the newly inserted data, at this time, Oracle uses blocks above the high water level to store new data.
After inserting the data into the table, the HWM itself moves up. That is, when we insert data constantly, HWM will move upward, so that, under HWM, the used blocks are represented, and the blocks above the HWM represent those that have been allocated but never used.
When the table is exported, after the import, or move and other operations, the HWM will be lowered to the true level. Automatic segment Management (ASSM) in automatic segment Management (ASSM), using bitmaps instead of idle lists, when a session inserts data into a table, the database formats only a single bitmap block, rather than a set of blocks that are preformatted as in MSSM.
In the ASSM table space, there is a low HWM in addition to a HWM. In MSSM, when HWM advances, all blocks are formatted and take effect immediately, so that Oracle can read the blocks safely. But for ASSM, when HWM advances, Oracle does not immediately format all of the blocks, but only when they are first used to format them. That is, when you first use the insert operation, the data is inserted into any watermark in the block, between the low watermark (LHMW) and the high watermark (HHMW). So many blocks in this area will not be formatted. Each block of data in a ASSM segment may be of these states: On HWM These blocks are not formatted and are not used under HWM these blocks will be in one of these states: • Already allocated, but not formatted and unused • Formatted and contains data • Formatted, But the data has been deleted, the block is empty below to understand the entire process:
, all data blocks in the segment are not formatted and are not used until the data is inserted. Let's say that when a session inserts data into it, the database writes the data to any available block of space. As shown in the following:
The database may also select any block of data between HWM and low HWM, or any available space below the low HWM. Note the low HWM position, because the blocks under HWM are only formatted when they are used, so Oracle reads low HWM locations when there is a data scan, especially a full table scan. As shown in the following:
If a session inserts data into a table, but there is not enough free space under the current HWM, then Oracle will push HWM and reassign a new set of unformatted blocks. When the position between the HWM and the low HWM is filled, the HWM will continue to move forward, while the low HWM will advance to the old HWM position accordingly. And so on, when the database is constantly inserting data, the HWM will continue to move forward, and the low HWM will be followed, unless the object is rebuilt, or shrunk, or HWM never goes back.
2
HWM Demo process (top)The principle is finished, so let's test it and verify the part we described earlier. Let's start with the MSSM management approach:
Create a new table, and then store the more allocated objects.
Insert more data,
Now to query the time spent on the full table scan,
From the execution plan, the query goes through a full table scan, generating 6,278 physical reads and 30,028 logical reads. Now analyze the table, view the statistics,
From the table's statistics, the table has 15,384 blocks, of which there are 0, and a total of 1003089 rows. Now to test the effect of delete on HWM,
From the statistics, the table uses a block or 15,384 blocks, but the spare block is still 0, according to the truth, after the delete, there should be a spare block. Here, you can see that the number of rows has changed to 0. From here, it can be verified that HWM, when inserting data, will move up when there is insufficient space to expand, but will not move down when data is deleted. This causes the Racle full-table scan to read all blocks under the Oracle high-water mark, meaning that Oracle reads a large number of empty chunks after the delete table, regardless of whether or not the block under HWM is actually storing the data, so that Oracle reads a significant amount of space. It took a lot of time to test the environment without show_space the process, now declare it:
So this is HWM all the block numbers.
Explore Oracle high water level impact on database performance
