Shulou(Shulou.com)06/01 Report--

The method of lowering the high water mark by Oracle

High water level (HIGH WARTER MARK,HWM) is like the level of water stored in a reservoir and is used to describe how the middle section of the database is expanded. The high water level has an important influence on the scanning mode of the full meter. When table records are deleted using DELETE, the high water level does not fall, resulting in no reduction in the actual cost of a full table scan.

For example, first create an empty table with a size of 64K, and then insert data until the size of the table becomes 50G, then use DELETE to delete all the data and submit it. At this time, the size of the query table is still 50G. This is because the high water level of the table is not released. In this case, if you use "SELECT * FROM TABLE_NAME" "statement to query the data, then the query process will be slow, because Oracle will perform a full table scan, all blocks from high water levels have to be scanned until all 50G blocks are scanned. I once encountered a colleague who used DELETE to delete a large partition table, and then executed a SELECT query without results for a long time, thinking that the database HANG lived, but the problem was actually due to the high water level. Therefore, the table performs a TRUNCATE operation, and the result can be returned quickly when it is SELECT again.

There are usually several ways to release the high water level of the meter:

(1) MOVE the table: ALTER TABLE TABLE_NAME MOVE;. If there is an index on the table, remember to rebuild the index.

(2) SHRINK SPACE the table: ALTER TABLE TABLE_NAME SHRINK SPACE;, note that row movement: ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT; must be enabled before executing this instruction. The advantage of this method is that after defragmentation, the relevant indexes on the table are still valid, but the disadvantage is that a large number of UNDO and REDO are generated.

(3) copy the data to be retained to the original table of temporary table T _ drop, and then the temporary table T of RENAME is the original table.

(4) exp/imp or expdp/impdp reconstruction table.

(5) if there is no data in the table, TRUNCATE is directly used to release the high water level.

How to find out which meters in the system have high water level? Here are two methods: ① compares the relationship between the number of rows of a table and the size of the table. If the number of rows is 0 and the current occupancy size of the table minus the size at initialization (INITIAL_EXTENT) is still large, the table has a high water level. The ratio of ② rows to blocks, that is, to see how many rows of data a block can store. If the number of rows stored in a block is less than 5 or less, then the water level is high. Note that neither of these methods is very accurate and the query results need to be filtered. It is important to note that when querying the high water level of the table, you first need to analyze the table to get the most accurate statistical information.

Here are several SQL statements for querying high water levels:

Segment Space and the High Water Mark

To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used.

MSSM uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads allblocks below the HWM.

ASSM does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.

Every data block in an ASSM segment is in one of the following states:

Above the HWM

These blocks are unformatted and have never been used.

Below the HWM

These blocks are in one of the following states:

Allocated, but currently unformatted and unused

Formatted and contain data

Formatted and empty because the data was deleted

Figure 12-23 depicts an ASSM segment as a horizontal series of blocks. At table creation, the HWM is at the beginning of the segment on the left. Because no data has been inserted yet, all blocks in the segment are unformatted and never used.

Figure 12-23 HWM at Table Creation

Description of "Figure 12-23 HWM at Table Creation"

Segment space and high water mark

The oracle database manages space by tracking the state of blocks in segments. The high water mark (HWM) is a point in a segment beyond which blocks of data are unformatted and unused.

MSSM uses free lists to manage segment space. The blocks in the segment are not formatted when the table is created. When a session first inserts rows into a table, the database searches the free list to find available blocks. If the database does not find available blocks, it pre-formats a set of blocks, places them in the free list, and starts inserting data into the block. In MSSM, a full table scan reads all blocks under HWM.

ASSM does not use free lists, so space must be managed differently. When a session first inserts data into a table, the database formats only a single-bit block, rather than preformatting a set of blocks as in MSSM. The bitmap replaces the free list and is used to track the status of blocks in segments. The database uses bitmaps to find available blocks and then formats them before writing data to them. ASSM divides the insert operation into multiple blocks to avoid concurrency problems.

Each block in an ASSM segment is in one of the following states:

L above HWM

These blocks are unformatted and have never been used.

L under HWM

These blocks are in one of the following states:

U is assigned, but not currently formatted and not used

U is formatted and contains data

U is formatted and empty because the data has been deleted

Figure 12-23 describes an ASSM segment as a series of horizontal blocks. When you create the table, the HWM is at the beginning of the left section. Because the data has not been inserted, all blocks in the segment are still unformatted and have never been used.

Figure shows the HWM of 12-23 when creating the table

Suppose that a transaction inserts rows into the segment. The database must allocate a group of blocks to hold the rows. The allocated blocks fall below the HWM. The database formats a bitmap block in this group to hold the metadata, but does not preformat the remaining blocks in the group.

In Figure 12-24, the blocks below the HWM are allocated, whereas blocks above the HWM are neither allocated or formatted. As inserts occur, the database can write to any block with available space. The low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.

Figure 12-24 HWM and Low HWM

Description of "Figure 12-24 HWM and Low HWM"

In Figure 12-25, the database chooses a block between the HWM and low HWM and writes to it. The database could have just as easily chosen any other block between the HWM and low HWM, or any block below the low HWM that had available space. In Figure 12-25, the blocks to either side of the newly filled block are unformatted.

Figure 12-25 HWM and Low HWM

Description of "Figure 12-25 HWM and Low HWM"

The low HWM is important in a full table scan. Because blocks below the HWM are formatted only when used, some blocks could be unformatted, as in Figure 12-25. For this reason, the database reads the bitmap block to obtain the location of the low HWM. The database reads all blocks up to the low HWM because they are known to be formatted, and then carefully reads only the formatted blocks between the low HWM and the HWM.

Assume that a new transaction inserts rows into the table, but the bitmap indicates that insufficient free space exists under the HWM. In Figure 12-26, the database advances the HWM to the right, allocating a new group of unformatted blocks.

Figure 12-26 Advancing HWM and Low HWM

Description of "Figure 12-26 Advancing HWM and Low HWM"

When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. As the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. Unless you manually rebuild, truncate, or shrink the object, the HWM never retreats.

See Also:

Oracle Database Administrator's Guide to learn how to shrink segments online

Oracle Database SQL Language Reference for TRUNCATE TABLE syntax and semantics

Move can not only reset the water level line (HWM) to solve the IO waste caused by loose tables, but also solve the problem of row migration in the table.

The move table requires double space, otherwise it will not succeed. The move table can shrink the data file by rearranging the space of the data file.

When you move a table, an exclusive lock lock is generated, and you can only select the table at this time.

After the move table, if there is an index on the table, remember to rebuild.

2) the shrink table is only valid for tables managed by ASSM, and the related commands are:

-alter table TABLE_NAME shrink space; defragment and reclaim space

-alter table TABLE_NAME shrink space compact; only defragments and does not recycle space

-alter table TABLE_NAME shrink space cascate; defragmentation reclaims space and collates it together with cascading objects of the table (such as indexes)

It can be performed online without affecting the DML operations on the table. Of course, concurrent DML operations will have a brief block at the end of the shrink.

Another advantage of shrink is that the relevant index on the table is still enable after defragmentation.

3) the operation speed of move is much faster than that of shrink, not as fast as usual, not an order of magnitude, and shrink will generate a large number of undo and redo operations.

4) truncate is a DDL operation, which is equivalent to rebuilding after deleting the table.

5) there are other methods, such as export and then re-import.

To prepare for the work, create a table:

CREATE TABLE TEST2

(

ID NUMBER (10)

NAME VARCHAR2 (32)

);

1. Move the table:

SQL > begin

2 for i in 1..10000 loop

3 insert into test2 values (iMagnesia bbb')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 244 12

SQL > delete test2

100000 rows deleted.

SQL > alter table test2 move

Table altered.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 0 8

2. Contraction table:

SQL > begin

2 for i in 1..100000 loop

3 insert into test2 values (iMagnesia kkk')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 244 12

SQL > delete test2

100000 rows deleted.

SQL > alter table test2 shrink space

Alter table test2 shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

SQL > alter table test2 enable row movement

Table altered.

SQL > alter table test2 shrink space

Table altered.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 1 7

3. Truncate table

SQL > begin

2 for i in 1..100000 loop

3 insert into test2 values (iMagnesia kkk')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 244 12

SQL > truncate table test2

Table truncated.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 0 8

4. Create a new temporary table and then rename

SQL > begin

2 for i in 1..100000 loop

3 insert into test2 values (iMagnesia kkk')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > delete test2

100000 rows deleted.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 244 12

SQL > create table test3 as select * from test2

Table created.

SQL > drop table test2

Table dropped.

SQL > alter table test3 rename to test2

Table altered.

SQL > analyze table test2 compute statistics

Table analyzed.

SQL > select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2'

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

-

TEST2 LXM 0 0

=

There is no need to rebuild the index for empty table movement:

SQL > begin

2 for i in 1..10000 loop

3 insert into test2 values (iMagnesia bbb')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS

IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

SQL > delete test2 where id=1

1 row deleted.

SQL > alter table test2 move

Table altered.

SQL > select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS

IND_TEST2 NORMAL TEST2 UNUSABLE 65536 2147483645

SQL > alter index ind_test2 rebuild

Index altered.

SQL > select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS

IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

SQL > delete test2

9999 rows deleted.

SQL > alter table test2 move

Table altered.

SQL > select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS

IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

The high water level is the upper limit of the data in the recording section, and the high water level is stored in the section.

Scan the whole table and read the paragraph head block first, then find the HWM in the paragraph head block.

The following is an experiment to understand Oracle's HWM from the inside out.

[sql] there is a data entry in the view plain copy print?--t table hr@ORCL > select * from t _ sitID NAME--1 AAAAA-- to find the segment header block of t segment hr@ORCL > select header_file,header_block from dba_segments where segment_name='T' and owner='HR' HEADER_FILE HEADER_BLOCK--4387 HEADER_FILE HEADER_BLOCK--another session,dump segment header block sys@ORCL > alter session set tracefile_identifier='sys_dump_t_01';Session altered.sys@ORCL > alter system dump datafile 4 block 387 words system altered.

Part of the trc content of dump is extracted into

[sql] view plain copy print?Extent Control Header-Extent Header:: spare1: 0 spare2: 0 # extents: 1 # blocks: 8last map 0x00000000 # maps: 0 offset: 2716 Highwaterlane: 0x01000189 ext#: 0 blk#: 8 ext size: 8-- Highwater is the high water level 0x01000189 this is the address of HWM # blocks in seg. Hdr's freelists: 0#blocks below: 5mapblk 0x00000000 offset: 0Unlocked---Low HighWater Mark: Highwater:: 0x01000189 ext#: 0 blk#: 8 ext size: 8#blocks in seg. Hdr's freelists: 0#blocks below: 5mapblk 0x00000000 offset: 0Level 1 BMB for High HWM block: 0x01000181Level 1 BMB for Low HWM block: 0x01000181---Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0L2 Array start offset: 0x00001434First Level 3 BMB: 0x00000000L2 Hint for inserts: 0x01000182Last Level 1 BMB: 0x01000181Last Level II BMB: 0x01000182Last Level III BMB: 0x00000000Map Header:: next 0x00000000 # extents: 1 obj#: 52713 flag: 0x10000000Inc # 0Extent Map-0x01000181 length: 8Auxillary Map- -- Extent 0: L1 dba: 0x01000181 Data dba: 0x01000184---Second Level Bitmap block DBAs- -DBA 1: 0x01000182d dump data blocks tsn: 4 file#: 4 minblk 387 maxblk 387

[sql] view plain copy print?-- does a full table scan of the t table hr@ORCL > set autot traceonlyhr@ORCL > select * from t Execution Plan---Plan hash value: 1601196873 Murray- | | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | 1 | | | 20 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | T | 1 | 20 | 3 (0) | 00:00:01 |-Note- dynamic sampling used | For this statementStatistics---0 recursive calls0 db block gets7 consistent gets-6 blocks 0 physical reads0 redo size469 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 1 rows processed were scanned in the full table

How are these six pieces calculated?

[sql] view plain copy print?hr@ORCL > select file_id,block_id,blocks from dba_extents where segment_name='T';FILE_ID BLOCK_ID BLOCKS- 4 3858

A total of 8 blocks are used in this t paragraph, which are 385 386 387 388 389 390 391 392 393

Highwater:: 0x01000189: block 393of document No. 4.

This can be calculated from the following dbms_utility package

[sql] view plain copy print?sys@ORCL > select to_number ('01000189') from dual;TO_NUMBER ('01000189') 16777609sys@ORCL > select dbms_utility.data_block_address_file (16777609) from dual DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (16777609)-4sys@ORCL > select dbms_utility.data_block_address_block (16777609) from dual DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (16777609)-393

Read the paragraph head block once: document No. 4 block 387

Read five blocks such as 388 389 390 391 392 under the high water level

In this way, I read a total of 6 pieces.

Note:

385 is FIRST LEVEL BITMAP BLOCK.

386 is SECOND LEVEL BITMAP BLOCK.

Then analyze the content of our dump:

[sql] view plain copy print?Low HighWater Mark: Highwater:: 0x01000189 ext#: 0 blk#: 8 ext size: 8

Let's talk about highwater mark and low highwater mark.

There may be a formated block or a unformatted block between low high water mark and high water mark

Let's first sort out the meaning of the field blocks in dba_tables.

Dba_tables.blocks records the total number of formatted block obtained by analysis.

There may be both formatted block and unfomatted block between low hwm and high hwm.

So blocks doesn't exactly represent low hwm or high hwm.

If there is no formatted block between low hwm and high hwm, the blocks under dba_tables.blocks and low hwm is the same.

So what are unformatted blocks in Oracle?

Unformatted, which means that this block already belongs to this paragraph, but still retains its original appearance.

Formatting is to erase the data in the block and change the block to this object.

The segment in the MSSM table space has only one high water level, and the blocks at the high water level are all formatted.

But the segment in the ASSM table space has two high water levels: low high water level and high high water level

In the above trc: Highwater:: 0x01000189 and Low HighWater Mark Highwater:: 0x01000189

The blocks under low and high water levels are all formatted.

But the block between the low and high water levels may or may not be formatted.

Now the high and high water level of t is file 4jinblock 393; its low and high water level is file 4je block 393.

Now let's take a look at the current data_object_id of t:

[sql] view plain copy print?hr@ORCL > select object_id,data_object_id from dba_objects where object_name='T';OBJECT_ID DATA_OBJECT_ID--52713 52714

It is obvious that the data_object_id of t is greater than object_id.

That is to say, move or truncate operations have occurred on t

Note that for truncate operations, the data_object_id after truncate does not necessarily add 1 to the original data_object_id.

[sql] view plain copy print?sys@ORCL > select to_char ('52714') from dual;TO_CHAR ('- CDEA

In other words, the block in t between its low high water level and its high high water level, as long as the data_object_id recorded on this block is not equal to CDEA

We can use the seg/obj in the Block header dump part of the dump to determine whether its data_object_id is equal to the segment number.

So this block is an unformatted block.

That is, you can use data_object_id to determine where the block is located in HWM and LHWM.

So why did Oracle add the low and high water level setting? For what purpose? When scanning the whole table, is it to the low and high water level, or to the high and high water level?

There is a principle in Oracle design that a large operation is dispersed into many small operations to ensure that a large operation does not take too long.

Whether it is delayed submission or whatever, it embodies this idea, which is similar to the idea of Linux.

The combination of low and high water mark and high water mark is the embodiment of this idea.

Instead of formatting all the newly allocated blocks at once, you can leave some of them for the next operation.

When scanning a full table, it is usually read to the low and high water mark, and then read the formatted blocks between the low and high according to the bitmap to avoid the unformatted blocks

How do I view HWM? How do I know how much free space there is under HWM?

The most practical method is dump segment_header, which is fast and has no effect on the application.

Also, the "# blocks in seg. Hdr's freelists:" in trc can tell us how many free blocks there are under HWM.

It should be noted here that if dba_segments.freelist_groups > 1, then freelist is no longer in segment header

For example, if freelist_group = 3, you need to see the number of freelist in each dump header by dividing the three blocks behind the group.

So how to reduce HWM?

① expdp/impdp

② 10G and later versions can use shrink, it should be noted that the table space must be automatic segment space management

Alter table tab_name enable row movement

Alter table tab_name shrink space

③ uses move, but during Move, it will affect DML statements, such as UPDATE, and space needs to be considered.

Anyway, move locks the table and TM also affects index, so remember rebuild index later

Alter table move tab_name; move in the current tablespace

Alter table move tab_name tablespace tbs_name; move it to another tablespace

④ CTAS, then rename,rebuild index.

⑤ online redefinition

Wait a minute.

The concept of high water level (High Water Mark) and the solution to the problem of high water level when it comes to HWM, we should first briefly talk about the logical storage management of ORACLE. We know that ORACLE has four granularities in logical storage: tablespaces, segments, extents, and blocks.

(1) Block: the storage unit with the smallest granularity. Now the standard block size is 8K Oracle. Every Ibank O operation of Oracle also operates by block, that is to say, when ORACLE reads data from a data file, it reads how many blocks, not how many rows.

(2) Zone: consists 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, ORACLE first allocates the space of one zone to the table. As the continuous INSERT data is sent to PM_USER, when the original zone has no room for inserted data, ORACLE is expanded on an area-by-zone basis, that is, how many extents are allocated to PM_USER. Not how many pieces.

(3) paragraph: consists of a series of extents. Generally speaking, when an object is created (table, index), a segment is assigned to that object. So in a sense, a segment is some kind of specific data. For example, CREATE TABLE PM_USER, this segment is the data segment, and CREATE INDEX ON PM_USER (NAME), ORACLE will also assign a segment to the index, but this is an index segment. The information of the query segment can be obtained through the data dictionary: SELECT * FROM USER_SEGMENTS

(4) Table space: contains segments, blocks and blocks. The data of the tablespace is physically stored in the data file in which it is located. A database must have at least one table space. OK, let's go back to HWM, so what is the high water mark? This is related to the segment space management of ORACLE. (1) ORACLE uses HWM to define blocks used and unused blocks in a segment.

For example, when we create a table: PT_SCHE_DETAIL, ORACLE assigns a segment to the object. In this section, even if we do not insert any records, at least one section is allocated. The first block of the first region is called the SEGMENT HEADE. Some information is stored in the segment header, and the information of the base HWM is stored here. At this point, because the first block of the first area is used to store some information about the segment header, although it does not store any actual records, it is used, and the HWM is located in the second block. When we continue to insert data into PM_USER, the first block can no longer hold the newly inserted data. At this time, ORACLE uses the block above the high water level to store the new data, and the HWM itself moves up. In other words, as we continue to insert data, the HWM will continue to move up, so that those under HWM represent used blocks, and those above HWM represent allocated blocks that have never been used.

(2) when HWM inserts data, it will move up when the existing space is insufficient and expand the space, but it will not move down when deleting data.

This is like the water level of a reservoir. When the water rises, the water level moves up, and when the water withdraws, the traces of the highest water level are still visible.

Consider let's look at a paragraph, such as a table, filled with blocks, as shown in figure 1. During normal operation, some rows are deleted, as shown in figure 2. There is a lot of wasted space: (I) between the last end of the table and the existing block, and (II) inside the block, with some rows that have not been deleted.

Figure 1: blocks assigned to the table. Use gray squares to represent rows

ORACLE does not free up space for use by other objects for a simple reason: because space is reserved for newly inserted rows and to accommodate the growth of existing rows. The highest space occupied is called the maximum usage mark (HWM), as shown in figure 2.

Figure 2: the block after the line has been deleted; HWM remains unchanged. (3) the information of HWM is stored in the section header.

The information of HWM itself is stored in the head of the paragraph. When the segment space is managed manually, ORACLE manages the space allocation within the segment through FREELIST (an one-way linked list). When the segment space is automatically managed (ASSM), ORACLE manages the space allocation within the segment through BITMAP.

(4) the full table scan of ORACLE reads all blocks below the high water mark (HWM).

So the problem arises. When a user issues a full table scan, ORACLE must always scan from segment to HWM, even if it finds nothing. This task extends the time of the full table scan.

(v) when inserting rows with a direct path-for example, by direct load insertion (with the APPEND prompt) or through the SQL*LOADER direct path-the data block is placed directly above the HWM. The space under it is wasted.

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

(a) first, let's build a test environment. The first step is to create a manually managed table space:

CREATE TABLESPACE "RAINNY"

LOGGING

DATAFILE'DRAACLEX HOMEORADATARAINNYRAINNY.ORA'SIZE 5M

AUTOEXTEND

ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT MANUAL; (B) creates a table. Note that the second field of this table is deliberately set to CHAR, so that the table has enough space after inserting 10 million records: CREATE TABLE TEST_TAB (C1 NUMBER (10), C2 CHAR (10)) TABLESPACE RAINNY; inserts record DECLARE

I NUMBER (10); BEGIN

FOR I IN 1..10000000 LOOP

INSERT INTO TEST_TAB VALUES (I recorder TESTRING')

END LOOP

COMMIT; END; /

(C) Let's look at the number of blocks accessed after inserting 10 million records and the query time:

SQL > SET TIMING ON SQL > SET AUTOTRACE TRACEONLY SQL > SELECT COUNT (*) FROM TEST_TAB ELAPSED: 00 SELECT STATEMENT OPTIMIZER=CHOOSE 01 COST=15056 CARD=1 03.05 EXECUTION PLAN-0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1) 10 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=10000 000) STATISTICS- -0 RECURSIVE CALLS 0 DB BLOCK GETS 156310 CONSISTENT GETS 154239 PHYSICAL READS 0 REDO SIZE 379BYTES SENT VIA SQL*NET TO CLIENT BYTES RECEIVED VIA SQL*NET FROM CLIENT 2 SQL*NET ROUNDTRIPS TO/FROM CLIENT 0 SORTS (MEMORY) 0 SORTS (DISK) 1 ROWS PROCESSED

Let's take a look at the execution plan above. The total time spent on SQL is: 1 minute 3 seconds. The access mode is full table scan (FTS). 156310 BLOCK are read logically and 154239 BLOCK are physically read.

Let's analyze this table:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (OWNNAME= > 'TEST'

TABNAME= > 'TEST_TAB'

PARTNAME= > NULL); END; /

It is found that the BLOCK currently used in this table is 156532, the unused BLOCK (EMPTY_BLOCKS) is 0, and the total number of rows is (NUM_ROWS): 1000 0000.

(d) next, let's delete the records of this table in DELETE mode, and then take a look at the time taken by 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 CALLS

0 DB BLOCK GETS

156310 CONSISTENT GETS

155565 PHYSICAL READS

0 REDO SIZE

378 BYTES SENT VIA SQL*NET TO CLIENT

503 BYTES RECEIVED VIA SQL*NET FROM CLIENT

2 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

From the point of view of 1 ROWS PROCESSED, there is no record in the time table after the DELETE table. Why does the SELECT COUNT (*) FROM TEST_TAB take 1 minute and 4 seconds, but slightly longer than the record? why? And you see, its logic has read 156310 BLOCK, which is about the same as when there were 10 million lines of records before. how could ORACLE be so stupid?

Let's analyze the table again after the DELETE table to see what has changed:

At this time, the BLOCK currently used by the TEST_TAB table is 156532, the unused BLOCK (EMPTY_BLOCKS) is 0, and the total number of rows (NUM_ROWS) has become: 0

Why does the table currently make the number of BLOCK still 156532?

The root of the question lies in ORACLE's HWM. In other words, when adding new records, HWM will slowly move up, but after deleting records, HWM will not move down, that is to say, after 10 million records of DELETE, the HWM of this table has not moved at all and is still in the same position, so the number of blocks below HWM is the same. Oracle's full table scan reads all the BLOCK under the ORACLE high water mark, that is to say, No matter whether the BLOCK under HWM actually stores the data or not, ORACLE will read it one by one, so you can imagine that after we delete the table, ORACLE reads a lot of empty blocks and consumes a lot of time.

Let's take a look at the actual usage of the space at the back of the DELETE table:

SQL > EXEC SHOW_SPACE ('TEST_TAB','TEST')

TOTAL BLOCKS..164352-164352 yuan in total

TOTAL BYTES..1346371584

UNUSED BLOCKS..7168-there are 7168 unused, that is, the number of blocks on the HWM

UNUSED BYTES..58720256

LAST USED EXT FILEID.9

LAST USED EXT BLOCKID.158856-- BLOCKID is numbered for data files, indicating the number of the first BLOCK of the last EXTENT used

LAST USED BLOCK.1024-A total of 1024 yuan was spent in the last EXTENT used

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED

A total of 164352 yuan was used, except for one SEGMENT HEADER, a total of 164351 blocks were actually used, of which 7168 yuan have never been used. LAST USED BLOCK represents the BLOCK used in the last EXTENT used, and the HWM location can be calculated in combination with LAST USED EXT BLOCK ID:

LAST USED EXT BLOCK ID + LAST USED BLOCK-1 = the BLOCK number of the data file where HWM is located: 158856 "1024-1" 159879, which is all the BLOCK numbers of HWM.

The block in which HWM is located: TOTAL BLOCKS- UNUSED BLOCKS=164352-7168 blocks 157184, that is to say, HWM is 157184 blocks, and its BLOCKID is 159879

(e) after the conclusion, we will do a few more experiments:

Step 1: execute ALTER TABLE TEST_TAB DEALLOCATE UNUSED

Let's take a look at the use of space:

SQL > EXEC SHOW_SPACE ('TEST_TAB','TEST'); TOTAL BLOCKS..157184

TOTAL BYTES..1287651328

UNUSED BLOCKS..0

UNUSED BYTES..0

LAST USED EXT FILEID.9

LAST USED EXT BLOCKID.158856

LAST USED BLOCK.1024 at this time, we substitute the above formula to calculate the location of the HWM: the BLOCK ID where the HWM is located is 158856 "1024-1" 159879, which is the same as the previous one, that is to say, after the implementation of ALTER TABLE TEST_TAB DEALLOCATE UNUSED, the position of the high water mark of the section has not changed, but you can see that the UNUSED BLOCKS has changed to 0, and the total number of blocks has been reduced to 157184, which proves DEALLOCATE UNUSED frees up unused space above the HWM, but does not free up the free space under the HWM, nor does it move the HWM.

Step 2: let's take a look at the usage of the space at the end of the execution of ALTER TABLE TEST_TAB MOVE:

SQL > EXEC SHOW_SPACE ('TEST_TAB','TEST') TOTAL BLOCKS..8 TOTAL BYTES..65536 UNUSED BLOCKS..5 UNUSED BYTES. ... 40960 LAST USED EXT FILEID.9 LAST USED EXT BLOCKID.2632 LAST USED BLOCK.3

At this point, the total number of blocks used has become 8. We substitute the above formula to calculate the location of HWM: the BLOCK ID where 8-5'3 HWM is located is 2632'3-1 '2634, OK. We find that the location of HWM has changed. Now the location of HWM is in the third BLOCK, its BLOCK ID is 2634, and the ID of all data files is 9 (this has not changed, the data file is still the original data file). Just free the original free space), the final number of blocks used is also changed to 3, that is, 3 blocks have been used, and HWM is on the last used block, that is, the third block. You may wonder why there are still 5 unused blocks after releasing the space. In other words, there are still 5 allocated but never used blocks on top of HWM. The answer is related to the law of HWM movement. When we insert data, ORACLE first locates the free space in the block under HWM (through the free list FREELIST). If there is no free block in FREELIST, ORACLE begins to expand up, and HWM moves up every 5 blocks. Let's take a look at ORACLE's instructions:

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 CALLS

0 DB BLOCK GETS 3 CONSISTENT GETS

0 PHYSICAL READS

0 REDO SIZE

378 BYTES SENT VIA SQL*NET TO CLIENT

503 BYTES RECEIVED VIA SQL*NET FROM CLIENT

2 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1 ROWS PROCESSED

Soon, less than a second. Finally, let's analyze the table again. The BLOCK currently used for 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 analysis table is a little inconsistent with the data displayed by SHOW_SPACE. So which one is accurate? In fact, both of them are accurate, but the method of calculation is a little different. In fact, when you create an object such as a table, it will take up some blocks, whether you insert data or not, and ORACLE will allocate the necessary space to it. Similarly, after freeing free space with ALTER TABLE MOVE, some space is reserved for the table.

Finally, let's execute the TRUNCATE command to truncate the table to see how the segment space is being used:

TRUNCATE TABLE TEST_TAB

SQL > EXEC SHOW_SPACE ('TEST_TAB','TEST')

TOTAL BLOCKS..8

TOTAL BYTES..65536

UNUSED BLOCKS..5

UNUSED BYTES..40960

LAST USED EXT FILEID.9

LAST USED EXT BLOCKID.2632

LAST USED BLOCK.3

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED

SQL >

We found that there was no change between TRUNCATE and MOVE.

In order to finally verify my point above, I DROP the table again, and then create a new table to see if ORACLE does allocate the necessary space to this object before inserting any data:

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..8

TOTAL BYTES..65536

UNUSED BLOCKS..5

UNUSED BYTES..40960

LAST USED EXT FILEID.9

LAST USED EXT BLOCKID.2112

LAST USED BLOCK.3 look, even though I didn't insert any row of records, ORACLE allocated eight blocks to it. Of course, this is related to the INITIAL parameter and MINEXTENTS parameter of the table statement: please see the storage parameters of TEST_TAB:

S TORAGE

(

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

);

That is, after the object is created, ORACLE assigns it at least one extent, the initial size is 64K, and the size of a standard block is 8K, which is exactly 8 BLOCK.

Summary:

In 9I:

(1) if MINEXTENT can enable ALTER TABLE TABLENAME DEALLOCATE UNUSED to release all unused space above HWM

(2) if MINEXTENT > HWM, free up the space above MINEXTENTS. Use KEEP 0 if you want to free up space above HWM.

ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0

(3) the TRUNCATE TABLE DROP STORAGE (default) command completely frees up the space above the MINEXTENT (returns it to the operating system) and resets the HWM.

(4) if you only want to move the HWM and do not want the table to be locked for a long time, you can use TRUNCATE TABLE REUSE STORAGE and just reset the HWM.

(5) ALTER TABLE MOVE will move the HWM, but double the table space when MOVE, and if there is an index on the table, the index needs to be reconstructed.

(6) the DELETE table will not reset HWM, nor will it release free space (that is, the space vacated by DELETE can only be used by the object itself in the future INSERT/UPDATE, not for other objects)

In ORACLE 10G:

You can use the ALTER TABLE TEST_TAB SHRINK SPACE command to move HWM online

If you want to compress the index of a table at the same time, you can publish: ALTER TABLE TEST_TAB SHRINK SPACE CASCADE

Note: when using this command, you need to first make the line portable row movement (see the example for details).

Unlike using ALTER TABLE MOVE, there is no need to reconstruct the index after executing this command.

Oracle official statement

Shrinking Database Segments Online

You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:

* Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.

* The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:

* IOT mapping tables

* Tables with rowid based materialized views

* the process of Tables with function-based indexes operation:

SQL > create table demo as select * from dba_source

Table created.

Elapsed: 00:00:05.83

SQL > select count (*) from demo

COUNT (*)

-

210992

Elapsed: 00:00:01.06

SQL > insert into demo select * from demo

210992 rows created.

Elapsed: 00:00:59.83

SQL > commit

Commit complete.

/ / get a table of 400000 records, let's look at the distribution of this tablespace.

SQL > exec show_space ('demo','auto')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

SQL > set serveroutput on

SQL > exec show_space ('demo','auto')

Total Blocks..9216

Total Bytes..75497472

Unused Blocks..768

Unused Bytes..6291456

Last Used Ext FileId.4

Last Used Ext BlockId.8328

Last Used Block.256

There are 9216 data blocks, with HWM in 9216-768 blocks 8448.

You can also get HWM=8*16+128*63+256=8192+256=8448 by viewing extents

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL > delete from demo where rownum commit

Commit complete.

Elapsed: 00:00:00.01

SQL > exec show_space ('demo','auto')

Total Blocks..9216

Total Bytes..75497472

Unused Blocks..768

Unused Bytes..6291456

Last Used Ext FileId.4

Last Used Ext BlockId.8328

Last Used Block.256

PL/SQL procedure successfully completed.

/ / the HWM of the table does not change after the delete operation, still at block 8448.

Elapsed: 00:00:00.00

SQL > alter table demo shrink space

Alter table demo shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

/ / need enable row movement before you can shrink

Elapsed: 00:00:00.09

SQL > alter table demo enable row movement

Table altered.

Elapsed: 00:00:00.10

SQL > alter table demo shrink space

Table altered.

Elapsed: 00:01:35.51

SQL > exec show_space ('demo','auto')

Total Blocks..3656

Total Bytes..29949952

Unused Blocks..0

Unused Bytes..0

Last Used Ext FileId.4

Last Used Ext BlockId.3720

Last Used Block.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

/ / you can see that the HWM has dropped to 3656!

- -recently studied Oracle for a while. It feels that Oracle is really broad and profound, including Oralce memory structure, performance adjustment, data backup, etc., which are also very important for development. Here are some experiments of Oracle high water mark to facilitate future Review:

High water mark experiment:

-- create a test3 table

SQL > create table test3 as

2 select * from dba_objects where 1 = 2

Table created

-- View the block and area size allocated in the table

SQL > SELECT segment_name, segment_type, blocks-number of allocated blocks, extents-number of allocated blocks

2 FROM dba_segments

3 WHERE segment_name = 'TEST3'

4

SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS

-

TEST3 TABLE 8 1

TEST3 TABLE 8 1

-- Analysis Table TEST3 Table

SQL > ANALYZE TABLE TEST3 ESTIMATE STATISTICS

Table analyzed

-- query the high water mark of TEST3 table

SQL > SELECT blocks-- High water mark (number of blocks in TEST3 table), empty_blocks-- number of free blocks in TEST3 table, num_rows

2 FROM user_tables

3 WHERE table_name = 'TEST3'

BLOCKS EMPTY_BLOCKS NUM_ROWS

0 7 0

Because no data has been inserted into the TEST3 table, the high water mark of this table is 0. Now insert the data into the TEST3 table and observe again.

SQL > insert into test3

2 select * from dba_objects

50361 rows inserted

SQL > commit

Commit complete

-- reanalyze the table

SQL > ANALYZE TABLE TEST3 ESTIMATE STATISTICS

Table analyzed

-- check the allocated block and area size in the table again

SQL > SELECT segment_name, segment_type, blocks, extents

2 FROM dba_segments

3 WHERE segment_name = 'TEST3'

4

SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS

-

TEST3 TABLE 8 1

TEST3 TABLE 768 21

At this point, we see that the number of BLOCKS has increased to 768, that is, Oracle allocates 768 blocks and 21 extents to the TEST3 table.

-- check the TEST3 table high water mark again

SQL > SELECT blocks, empty_blocks, num_rows

2 FROM user_tables

3 WHERE table_name = 'TEST3'

BLOCKS EMPTY_BLOCKS NUM_ROWS

689 78 50361

It has grown to 689 blocks, and there are 78 free blocks, 689 + 78 = 767, one less than the allocated data block because this one is used as segment header

-- now delete the TEST3 table to view the high water mark.

SQL > delete from test3

50361 rows deleted

SQL > commit

Commit complete

SQL > ANALYZE TABLE TEST3 ESTIMATE STATISTICS

Table analyzed

SQL >

SQL > SELECT blocks, empty_blocks, num_rows

2 FROM user_tables

3 WHERE table_name = 'TEST3'

BLOCKS EMPTY_BLOCKS NUM_ROWS

689 78 0

It is found that the high water mark of the table has not decreased, which proves that delete only deletes the records of the data blocks in the table, but does not lower the high water mark in the table. During the full table scan, Oracle will scan all the data blocks below the high water mark in the table.

So although the data has been deleted, the query may still be slow. Therefore, you should use the truncate statement when deleting large tables, as shown in the following experiment:

SQL > truncate table test3

Table truncated

SQL > ANALYZE TABLE TEST3 ESTIMATE STATISTICS

Table analyzed

SQL >

SQL > SELECT blocks, empty_blocks, num_rows

2 FROM user_tables

3 WHERE table_name = 'TEST3'

BLOCKS EMPTY_BLOCKS NUM_ROWS

0 7 0

Now the high water level in the table has dropped to 0, a little experience, record it.

-

4. Correct the high water mark of ORACLE meter

In ORACLE, performing a delete operation on a table does not lower the high water mark of the table. A full table scan will always read all blocks in a segment (extent) that are below the high watermark mark. If the high watermark mark is not lowered after the delete operation, it will result in poor performance of the query statement.

All of the following methods can lower the high watermark mark.

1. Execute the table reconstruction instruction alter table table_name move

Transfer tablespace ALTER TABLE online. MOVE TABLESPACE..

When you create an object such as a table, it will take up some blocks, whether you insert data or not, and ORACLE will allocate the necessary space to it. Similarly, after freeing free space with ALTER TABLE MOVE, some space is reserved for the table.

ALTER TABLE... MOVE can not be followed by parameters, do not follow the parameter table or in the original table space, remember to rebuild the index after M ove. If you want to continue to add data to this table in the future, there is no need for move, just the free space, which can only be used by this table, and cannot be used by other tables or segment.

two。 Execute alter table table_name shrink space

Note that this command is a new feature for Oracle 10g, and the line must be allowed to move alter table table_name enable row movement before executing this instruction

3. Copy the data to be retained to the temporary table t, drop the original table, and then the rename temporary table t is the original table

4. Import and export with logic: e mp/ I mp

5. A lter table table_name deallocate unused

Note: this proves that DEALLOCATE UNUSED frees up the unused space above the HWM, but does not release the free space under the HWM, nor will it move the location of the HWM.

6. Try to use truncate.

Note:

In 9I:

1. If it is INEXTENT, you can make ALTER TABLE TABLENAME DEALLOCATE UNUSED release all unused space above HWM.

two。 If MINEXTENT > HWM, free up space above MINEXTENTS. Use KEEP 0 if you want to free up space above HWM.

ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0

3. The TRUNCATE TABLE DROP STORAGE (default) command completely frees up the space above the MINEXTENT (returns it to the operating system) and resets the HWM.

4. If you just want to move the HWM and don't want the table to be locked for a long time, you can use TRUNCATE TABLE REUSE STORAGE and just reset the HWM.

5. ALTER TABLE MOVE moves the HWM, but requires double table space when MOVE, and needs to restructure the index if there is an index on the table

6. The DELETE table will not reset HWM, nor will it release free space (that is, the space vacated by DELETE can only be used by the object itself in the future INSERT/UPDATE, not for other objects)

In ORACLE 10G:

1. You can use the ALTER TABLE TEST_TAB SHRINK SPACE command to move HWM online

two。 If you want to compress the index of a table at the same time, you can publish: ALTER TABLE TEST_TAB SHRINK SPACE CASCADE

5. Characteristics of HWM:

1. ORACLE uses HWM to define blocks used and unused blocks in a segment.

For example, when we create a table, ORACLE assigns a segment to the object. In this section, even if we do not insert any records, at least one section is allocated. The first block of the first region is called the SEGMENT HEADE. Some information is stored in the segment header, and the information of the base HWM is stored here. At this point, because the first block of the first area is used to store some information about the segment header, although it does not store any actual records, it is used, and the HWM is located in the second block. When we keep inserting data into the table, the first block can no longer hold the newly inserted data. At this time, ORACLE uses the block above the high water level to store the new data, and the HWM itself moves up. In other words, as we continue to insert data, the HWM will continue to move up, so that those under HWM represent used blocks, and those above HWM represent allocated blocks that have never been used.

2. When HWM inserts data, it will move up when the existing space is insufficient and expand the space, but it will not move down when deleting data.

This is like the water level of a reservoir. When the water rises, the water level moves up, and when the water withdraws, the traces of the highest water level are still visible.

ORACLE does not free up space for use by other objects for a simple reason: because space is reserved for newly inserted rows and to accommodate the growth of existing rows. The highest space occupied is called the maximum usage mark (HWM).

3. The information of HWM is stored in the header of the paragraph.

The information of HWM itself is stored in the head of the paragraph. When the segment space is managed manually, ORACLE manages the space allocation within the segment through FREELIST (an one-way linked list). When the segment space is automatically managed (ASSM), ORACLE manages the space allocation within the segment through BITMAP.

4. The full table scan of ORACLE reads all blocks below the high water mark (HWM).

So the problem arises. When a user issues a full table scan, ORACLE must always scan from segment to HWM, even if it finds nothing. This task extends the time of the full table scan.

5. When inserting rows using a direct path, even if there are free database blocks below HWM, and typing uses the append keyword when inserting data, data blocks above HWM are used when inserting, and the HWM will automatically grow.

What is a High Water Mark?

All oracle segments (segments, here, for ease of understanding, it is recommended to use segment as a synonym for tables) have an upper limit to hold data within the segment, which we call "high water mark" or HWM. The HWM is a tag that indicates how many unused blocks have been allocated to the segment. HWM usually increases by 5 data blocks at a time, in principle HWM will only increase, not shrink, even if all the data in the table are deleted, HWM is still the original value. Because of this feature, HWM is very much like the historical highest water level of a reservoir, which is the original meaning of HWM. Of course, you can't say that a reservoir is out of water, that is to say, the historical highest water level of the reservoir is 0. But if we use the truncate command on the table, the table's HWM will be reset to 0.

2. The operation of HWM database has the following effects:

A) A full table scan usually reads out everything marked by HWM that belongs to the table database block, even if there is no data in the table.

B) even if there are free database blocks below HWM, and typing uses the append keyword when inserting data, data blocks above HWM are used when inserting, and HWM will automatically grow.

3. How to know the HWM of a watch?

A) first analyze the table:

ANALYZE TABLE ESTIMATE/COMPUTE STATISTICS

B) SELECT blocks, empty_blocks, num_rows

FROM user_tables

WHERE table_name =

Description:

The BLOCKS column represents the number of database blocks used in the table, that is, the waterline.

EMPTY_BLOCKS represents the database block assigned to the table but above the waterline, that is, the data block that has never been used.

Let's take a BIG_EMP1 table with 28672 rows as an example:

1) SQL > SELECT segment_name, segment_type, blocks

FROM dba_segments

WHERE segment_name='BIG_EMP1'

SEGMENT_NAME SEGMENT_TYPE BLOCKS

BIG_EMP1 TABLE 1024

1 row selected.

2) SQL > ANALYZE TABLE big_emp1 ESTIMATE STATISTICS

Statement processed.

3) SQL > SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name='BIG_EMP1'

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--

BIG_EMP1 28672 700 323

1 row selected.

Note:

BLOCKS + EMPTY_BLOCKS (700 / 323 / 1023) has 1 less database block than DBA_SEGMENTS.BLOCKS because one database block is reserved for segment header. DBA_SEGMENTS.BLOCKS represents the number of all database blocks assigned to this table. USER_TABLES.BLOCKS indicates the number of database blocks that have been used.

4) SQL > SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) | |

DBMS_ROWID.ROWID_RELATIVE_FNO (rowid)) "Used"

FROM big_emp1

Used

-

seven hundred

1 row selected.

5) SQL > delete from big_emp1

28672 rows processed.

6) SQL > commit

Statement processed.

7) SQL > ANALYZE TABLE big_emp1 ESTIMATE STATISTICS

Statement processed.

8) SQL > SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name='BIG_EMP1'

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--

BIG_EMP1 0 700 323

1 row selected.

9) SQL > SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) | |

DBMS_ROWID.ROWID_RELATIVE_FNO (rowid)) "Used"

FROM big_emp1

Used

-

0-this table name does not contain any database blocks, that is, there is no data in the table

1 row selected.

10) SQL > TRUNCATE TABLE big_emp1

Statement processed.

11) SQL > ANALYZE TABLE big_emp1 ESTIMATE STATISTICS

Statement processed.

12) SQL > SELECT table_name,num_rows,blocks,empty_blocks

2 > FROM user_tables

3 > WHERE table_name='BIG_EMP1'

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--

BIG_EMP1 0 0 511

1 row selected.

13) SQL > SELECT segment_name,segment_type,blocks

FROM dba_segments

WHERE segment_name='BIG_EMP1'

SEGMENT_NAME SEGMENT_TYPE BLOCKS

BIG_EMP1 TABLE 512

1 row selected.

Note:

The TRUNCATE command reclaims the free space generated by the delete command, and notice that the space allocated by the table is reduced from 1024 to 512.

To preserve the free space generated by the delete command, you can use TRUNCATE TABLE big_emp1 REUSE STORAGE. Exe.

After using this command, the table will still be the original 1024 yuan.

4. HWM of high water mark in Oracle section

In the storage of Oracle data, we can think of the storage space as a reservoir and the data as the water in the reservoir. The position of the water in the reservoir has a line called the water level line, in Oracle, this line is called the high water level line (High-warter mark, HWM). When the database table is first created, because there is no data, the watermark is empty, that is, HWM is the lowest value. When the data is inserted, the high water mark rises, but there is also a feature here, that is, if you use the delete statement to delete the data, the data is deleted, but the high water mark is not lowered. It is still as high as it was before you deleted the data. In other words, this high water mark will only rise, not fall, in the daily operation of adding and deleting.

Let's talk about the features of the Select statement in Oracle. The Select statement scans the data in the table once, but how many data blocks are scanned? this is not about how many data there are in the database. Oracle scans such large data blocks, but Oracle scans data blocks below the high water mark. Now imagine that if you just created an empty table and you did a Select operation, then because the high watermark HWM is at the lowest position 0, there are no data blocks to be scanned, and the scanning time will be very short. If you insert 10 million pieces of data first, and then delete the 10 million pieces of data with the delete statement. Due to the insertion of 10 million pieces of data, the high water mark at this time is here in the 10 million pieces of data. When you delete these 10 million pieces of data later, because the delete statement does not affect the high water mark, the high water mark is still here at 10 million pieces of data. At this time, use select statement to scan again, although there is no data in the table at this time, but because the scan is based on the high water mark, you need to scan the storage space of 10 million pieces of data. in other words, this scan takes as much time as it takes to scan 10 million pieces of data. So sometimes people often say, how come I don't have a lot of data in my table, but it's still so slow. At this time, the secret is the high water mark here.

Is there any way to lower the high water mark? in fact, there is a relatively simple way to delete data by using the truncate statement. When you delete the data of a table with the truncate statement, it is similar to re-establishing the table, not only deleting all the data, but also emptying the HWM and restoring it to 0. So if you need to empty the table, use the truncate statement to delete the table when it is possible to use the truncate statement to delete the data, especially the temporary storage table with a large amount of data.

In manual segment space management (Manual Segment Space Management), there is only one HWM in the segment, but in automatic segment space management (Automatic Segment Space Management), which is just added by Oracle9iRelease1, there is a concept of low HWM. Why is there a low HWM with HWM? this is due to the feature of automatic segment space management. In the means segment space management, when the data is inserted, if it is inserted into a new data block, the data block will be automatically formatted to wait for data access. In automatic segment space management, after the data is inserted into a new block, the block is not formatted, but the block is formatted the first time it is accessed. So we need another watermark to mark the blocks that have been formatted. This water mark is called low HWM. Generally speaking, low HWM must be lower than or equal to HWM.

5. Modify the high water mark of ORACLE meter

In ORACLE, performing a delete operation on a table does not lower the high water mark of the table. A full table scan will always read all blocks in a segment (extent) that are below the high watermark mark. If the high watermark mark is not lowered after the delete operation, it will result in poor performance of the query statement. All of the following methods can lower the high watermark mark.

1. Execute the table reconstruction instruction alter table table_name move

(transfer tablespace ALTER TABLE online. MOVE TABLESPACE . ALTER TABLE . MOVE can not be followed by parameters, do not follow the parameter table or in the original tablespace, remember to rebuild the index after move. If you want to continue to add data to this table in the future, there is no need for move, just the free space, which can only be used by this table, and cannot be used by other tables or segment)

two。 Execute alter table table_name shrink space; Note: this command is a new feature for Oracle 10g. Before executing this instruction, you must allow lines to move the alter table table_name enable row movement.

3. Copy the data that you want to keep to the temporary table tfocus drop the original table, and then the rename temporary table t is the original table

4.emp/imp

5.alter table table_name deallocate unused

6. Try to truncate.

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/abstract/1/

● article blog park address: http://www.cnblogs.com/lhrbest

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (646634621), indicate the reason for adding

● completed in Mordor from 09:00 on 2017-05-09 to 22:00 on 2017-05-30.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

Pick up your phone and use Wechat client to scan the picture on the left below to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, scan the QR code on the right to join the QQ group of wheat seedlings, and learn the most practical database technology.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.