Learn more about hwm in Oracle)

Speaking of hwm, we should first briefly talk about Oracle's logical storage management. We know that Oracle has four granularities in logical storage: tablespace, segment, partition, and block.

(1) block: it is the smallest storage unit. Currently, the standard block size is 8 K. Each oracle I/O operation is performed by block, that is to say, when Oracle reads data from a data file, it reads the number of blocks instead of the number of rows.

(2) Partition: It is composed of a series of adjacent blocks, which is also the basic unit of Oracle space allocation. For example, when we create a table pm_user, first, Oracle will allocate a partition of space to this table. As data is continuously inserted to pm_user, Oracle will expand in a partition when the original partition cannot accommodate the inserted data, that is to say, how many areas are allocated to pm_user, not how many blocks are allocated.

(3) segment: It is composed of a series of partitions. Generally, when an object (table, index) is created, a segment is allocated to this object. in a sense, segments are specific data. for example, in create table pm_user, this segment is the data segment, while in create index on pm_user (name), Oracle will also allocate a segment to this index, but this is an index segment. the query segment information can be obtained through the data dictionary: Select * From user_segments,

(4) tablespace: The data of the tablespace contains segments, partitions, and blocks. The data of the tablespace is physically stored in the data file where it is located. A database must have at least one tablespace.

 

OK. Now we are back to hwm. So what is the mark of high water level? This is related to Oracle's segment space management.

(1) Oracle uses hwm to define Blocks Used in a segment and unused blocks.

For example, when we create a table: pt_sche_detail, Oracle will allocate a segment to this object. in this segment, even if we do not insert any records, at least one partition is allocated. The first partition of the first partition is called the segment heade ), some information is stored in the field header, and the hwm information in the base is stored here. at this time, because the first block of the first partition is used to store some information about the field header, although it does not store any actual records, it is also used. At this time, hwm is located in 2nd blocks. when we insert data to the pm_user, the first 1st blocks cannot store the data subsequently inserted. In this case, Oracle uses the blocks above the high level to store new data. At the same time, hwm itself also moves up. that is to say, when we continue to insert data, hwm will continue to move up. In this way, the blocks under hwm are used, blocks above hwm are allocated but never used.

(2) When hwm inserts data, it will move up when there is insufficient space for expansion, but will not move down When deleting the data.

This is like the water level of a reservoir. When the water rises, the water level moves up. When the Water exits, the marks of the highest water level are still visible.

Let's take a look at a segment, such as a table, which is filled with blocks, as shown in 1. Some rows are deleted during normal operations, as shown in figure 2. There is a lot of waste of space: (I) there is a lot of space between the last end of the table and the existing blocks, and (ii) inside the block, there are some rows that have not been deleted.

　

　　

Figure "1: block allocated to the table. Use a gray square to represent rows

Oracle does not release space for other objects. There is a simple reason: because space is reserved for newly inserted rows, it must adapt to the growth of existing rows. The maximum space occupied is the maximum use mark (hwm), as shown in 2.

　

　　

Figure "2: The block behind the row has been deleted; hwm remains unchanged

(3) hwm information is stored in the field header.

The hwm information is stored in the segment header. when the segment space is manually managed, Oracle uses freelist (a one-way linked list) to manage the space allocation in the segment. when the segment space is automatically managed (assm), Oracle uses bitmap to manage the space allocation in the segment.

(4) Oracle full table scan reads all the blocks below the high level mark (hwm.

So the problem arises (why Oracle uses this unreasonable method ). when a user initiates a full table scan, Oracle must always scan from the segment to hwm, even if nothing is found. This task extended the full table scan time.

(5) When a row is inserted using a direct path-for example, the data block is directly placed above hwm by directly loading and inserting (prompting insertion with APPEND) or directly path through SQL * loader. The space below it is wasted.

 

Let's analyze these two problems. The latter is just a waste of space, but the former is not only a waste of space, but also a serious performance problem. Let's take a look at the example below:

(A) Let's first build a test environment. The first step is to create a tablespace for manual management:

Create tablespace "rainny"
Logging
Datafile 'd: oracle_homeoradatarainnyrainny.ora 'size 5 m
Autoextend
On next 10 m maxsize unlimited extent management local
Segment space management manual;

(B) Create a table. Note that the second field of this table is intentionally set to Char (100), so that after inserting 10 million records, the space is large enough:

Create Table test_tab (C1 number (10), C2 char (100) tablespace rainny;

Insert record declare
I number (10); begin
For I in 1 .. 10000000 Loop
Insert into test_tab values (I, 'teststring ');
End loop;
Commit; end;

(C) Let's query the number of accessed blocks and the query time after 10 million records are inserted:

SQL> set timing on

SQL> set autotrace traceonly

SQL> select count (*) from test_tab;

 

Elapsed: 00:01:03. 05

 

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 15056 card = 1)

1 0 sort (aggregate)

2 1 Table Access (full) of 'test _ tab' (cost = 15056 card = 10000

000)

 

 

Statistics

----------------------------------------------------------

0 recursive cballs

0 dB block gets

156310 consistent gets

154239 physical reads

0 redo size

379 bytes sent via SQL * Net to client

503 bytes encoded ed via SQL * Net From Client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (Disk)

1 rows processed

 

SQL>

Let's take a look at the above execution plan. The total time for this SQL statement is: 1 minute 3 seconds. the access method uses the full table scan (FTS). The logic reads 156310 blocks and the physical reads 154239 blocks.

Let's analyze this table:

Begin
Dbms_stats.gather_table_stats (ownname => 'test ',
Tabname => 'test _ tab ',
Partname => null); end;

We found that the currently used blocks in this table are: 156532. the unused blocks (empty_blocks) are: 0, and the total number of rows is (num_rows): 1000 0000.

(D) Next we will delete the records in this table using the delete method, and then let's take a look at the time spent in select count (*) from test_tab:

Delete from test_tab;

Commit;

SQL> select count (*) from test_tab;

Elapsed: 00:01:04. 03

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 15056 card = 1)

1 0 sort (aggregate)

2 1 Table Access (full) of 'test _ tab' (cost = 15056 card = 1)

 

 

Statistics

----------------------------------------------------------

0 recursive cballs

0 dB block gets

156310 consistent gets

155565 physical reads

0 redo size

378 bytes sent via SQL * Net to client

503 bytes encoded ed via SQL * Net From Client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (Disk)

1 rows processed

SQL>

We can see that there is no record in the table after the delete table. Why does the select count (*) from test_tab take a little longer than the record, why? In addition, we can see that the logic reads 156310 blocks, which is similar to the previous 10 million lines of records. How can Oracle be so stupid?

We will analyze the table again after the delete table to see what changes have taken place:

At this time, the test_tab table currently uses the block: 156532, the unused block (empty_blocks) is: 0, and the total number of rows (num_rows) has changed to: 0.

Why is the number of blocks in the table still 156532?

The root cause of the problem lies in the hwm of oracle. that is to say, when a new record is added, hwm will slowly move up, But after deleting the record, hwm will not move down, that is, after deleting 10 million records, the hwm of this table is not moved at all and is still in the original position. Therefore, the number of hwm blocks is the same. the full table scan of Oracle reads all the blocks marked by the high-water mark of Oracle. That is to say, Oracle reads all the blocks under the hwm, regardless of whether the blocks actually store data. In this way, we can imagine that, after we delete the table, Oracle reads a large number of empty blocks, which consumes a lot of time.

Let's take a look at the actual usage of the bucket after the delete table:

VaR total_blocks number
VaR total_bytes number
VaR unused_blocks number
VaR unused_bytes number
VaR last_used_extent_file_id number
VaR last_used_extent_block_id number
VaR last_used_block number
Exec dbms_space.unused_space ('test', 'test _ tab', 'table',: total_blocks,: total_bytes,: unused_blocks,: unused_bytes,: bytes,: bytes,: last_used_block );
Print total_blocks
Print total_bytes
Print unused_blocks
Print unused_bytes
Print last_used_extent_file_id
Print last_used_extent_block_id
Print last_used_block

Output result:

PL/SQL procedure successfully completed

Total_blocks

----------------------------------------------------------------------

164352

Total_bytes

----------------------------------------------------------------------

1346371584

Unused_blocks

----------------------------------------------------------------------

7168

Unused_bytes

----------------------------------------------------------------------

58720256

Last_used_extent_file_id

----------------------------------------------------------------------

9

Last_used_extent_block_id

----------------------------------------------------------------------

158856

Last_used_block

----------------------------------------------------------------------

1024

Let's take a look at the data shown in show_space:

SQL> exec show_space ('test _ tab', 'test ');

Total blocks ......

Total Bytes...

Unused blocks ........................... 7168 -- 7168 blocks are useless, that is, the number of blocks on the hwm

Unused bytes...

Last used ext fileid...

Last used ext blockid ................... 158856 --- the block ID indicates the number of the first block of the last extent.

Last used block... 1024 shared 1024 blocks in the last extent.

PL/SQL procedure successfully completed

A total of 164352 parts are used. Except for one segment header, 164351 parts are actually used, and 7168 parts are never used. The last used block indicates the block used in the last extent. The hwm position can be calculated by combining the last used ext block ID:
Last used ext block ID + last used block-1 = block number of the data file where hwm is located

The result is as follows: 158856 + 1024-1 = 159879. This is the number of all blocks in hwm.

Block where hwm is located: Total blocks-unused blocks = 164352-7168 = 157184. That is to say, hwm has 157,184th blocks and Its blockid is 159879.

(E) Let's try again:

Step 1: Execute alter table test_tab deallocate unused;

Let's take a look at the usage of the section space:

SQL> exec show_space ('test _ tab', 'test ');

Total blocks ......

Total Bytes...

Unused blocks ......

Unused bytes...

Last used ext fileid...

Last used ext blockid... 158856

Last used block...

PL/SQL procedure successfully completed

SQL>

In this case, we can use the formula above to calculate the position of hwm: 157184-0 = 157184 the block ID of hwm is 158856 + 1024-1 = 159879, which is unchanged from the previous one, that is to say, after alter table test_tab deallocate unused is executed, the position of the High-level mark of the segment has not changed, but let's see that the unused blocks has changed to 0, and the total number of blocks has been reduced to 157184, which proves that, deallocate unused is used to release unused space on the hwm, but the free space under the hwm is not released or the location of the hwm is not moved.

Step 2: Let's take a look at the usage of the space after alter table test_tab move:

SQL> exec show_space ('test _ tab', 'test ');

Total blocks ......

Total Bytes...

Unused blocks ......

Unused bytes...

Last used ext fileid...

Last used ext blockid... 2632

Last used block...

PL/SQL procedure successfully completed

SQL>

At this time, the total number of blocks used has changed to 8. Then we can use the formula above to calculate the position of hwm: the block ID where 8-5 = 3 hwm is located is 2632 + 3-1 = 2634,

OK. We found that the position of the hwm has changed. Now the position of the hwm is 3rd blocks, and its block ID is 2634, the ID of all data files is 9 (this is not changed, the data file is still the original data file, but the original free space is released), and the number of blocks used is also changed to 3, that is to say, three blocks have been used, and hwm is on the last block used, that is, 3rd blocks. you may wonder why there are five unused blocks after the space is released? That is to say, there are still five allocated but never used blocks on the hwm. the answer is related to the law of hwm movement. when we insert data, Oracle first locates free space in the blocks under the hwm (through the Free List freelist). If there is no free block in the freelist, Oracle begins to expand upwards, and hwm also follows up, every 5 blocks move once. let's take a look at Oracle's description:

The high water mark is:

-Recorded in the segment header Block

-Set to the beginning of the segment on the Creation

-Incremented in five-block increments as rows are inserted

-Reset by the truncate command

-Never reset by the DELETE command

-Space above the high-water-mark can be reclaimed at the table level by using the following command:

Alter table deallocate unused...

Let's take a look at the time taken by select count (*) from test_tab:

SQL> select count (*) from test_tab;

Elapsed: 00:00:00. 00

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose

1 0 sort (aggregate)

2 1 Table Access (full) of 'test _ tab'

Statistics

----------------------------------------------------------

0 recursive cballs

0 dB block gets

3 consistent gets

0 physical reads

0 redo size

378 bytes sent via SQL * Net to client

503 bytes encoded ed via SQL * Net From Client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (Disk)

1 rows processed

SQL>

Soon, less than 1 second.

Finally, we will perform an analysis on the table. Currently, the block used in this table is: 0, the unused block (empty_blocks) is: 0, and the total number of rows is (num_rows): 0.

We can also find that the data displayed in the analysis table and show_space is a bit inconsistent. Which one is accurate? In fact, both are accurate, but the calculation method is a bit different. in fact, after you create an object such as a table, whether or not you insert data, it will occupy some blocks, and Oracle will allocate necessary space for it. similarly, some space is reserved for the table after the free space is released using alter table move.

Finally, run the truncate command to cut off the table and check the usage of the segment space:

Truncate table test_tab;

SQL> exec show_space ('test _ tab', 'test ');

Total blocks ......

Total Bytes...

Unused blocks ......

Unused bytes...

Last used ext fileid...

Last used ext blockid... 2632

Last used block...

PL/SQL procedure successfully completed

SQL>

We found that the truncate and move have no changes.

In order to finally verify my opinion, I will drop the table and create a new table to see if no data is inserted, whether Oracle does allocate necessary space for this object:

Drop table test_tab;

Create Table test_tab (C1 number (10), C2 char (100) tablespace rainny;

SQL> exec show_space ('test _ tab', 'test ');

Total blocks ......

Total Bytes...

Unused blocks ......

Unused bytes...

Last used ext fileid...

Last used ext blockid... 2112

Last used block...

PL/SQL procedure successfully completed

SQL>

As you can see, even if I have not inserted any row of records, Oracle still allocates 8 blocks to it. of course, this is related to the initial parameter and minextents parameter of the table creation statement. For details, refer to the storage parameters of test_tab:

Storage
(
Initial 64 K
Minextents 1
Maxextents Unlimited
);

That is to say, after this object is created, Oracle will allocate at least one partition to it. The initial size is 64 K, and the size of a standard block is 8 K, which is exactly 8 blocks.

Summary:

In 9i:

(1) If minextent can enable alter table tablename deallocate unused to release all unused spaces above hwm
(2) If minextent> hwm, space above minextents will be released. If you want to release the space above hwm, use keep 0.
Alter table tablesname deallocate unused keep 0;
(3) The truncate table drop storage (default) command can completely release the space on the minextent (handed back to the operating system) and reset the hwm.

(4) If you only want to move hwm and do not want to lock the table for a long time, you can use truncate table reuse storage to only reset hwm.
(5) alter table move will move hwm, but the tablespace needs to be doubled during move, and if the table has an index, the index needs to be reconstructed.

(6) The Delete table does not reset hwm or release free space (that is, the space empty by Delete can only be used by the object itself for future insert/update, and cannot be used by other objects)

In Oracle 10g:

(1) You can use the alter table test_tab shrink Space Command to move hwm online,

(2) If you want to compress the table index at the same time, you can release: alter table test_tab shrink space cascade.