Oracle Space Management Experiment (v) Effect of ASSM on high water level under block management--delete and query

High water level concept:

All Oracle segments (segments, here, in order to understand the convenience, it is recommended that segment as a synonym for the table) has a cap to accommodate the data in the paragraph, we call this cap "high water mark" or HWM. This HWM is a tag that shows how much of the unused block of data is allocated to this segment. HWM in principle HWM will only increase, will not shrink, even if all the data in the table deleted, HWM or for the original value, because of this feature, so that HWM very much like a reservoir of the history of the highest water level, which is the original meaning of HWM.

This concept Baidu next a lot, you can refer to:

Http://www.blogjava.net/decode360/archive/2009/07/14/287767.html

Http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html

High water level and low high water level: the state of the data blocks existing between the low and high water levels may be unformatted or formatted. Below the low and high water level is formatted and can be used.

HWM has the following effects on the operation of the database:

1. A full table scan is usually read out until all the HWM tags belong to the table database block, even if there is no data in the table.

3. Even if there are free database blocks below the HWM, type to use the APPEND keyword when inserting data, then use the block above HWM when inserting, HWM automatically increase.

2. When inserting, the default can only be inserted into the data block below the high water level, which can cause hot blocks resulting in performance problems when high concurrency occurs.

Here is an experiment to verify:

A full table scan usually reads out all of the table database blocks that are HWM marked at a low high water level, even if there is no data--delete in the table to test. Truncate will be released.

The solution is:Expdp/impdp,shrink: Need to be in ASSM and table to open row move. Move does not support online, or Crteate table A as, and then delete the original table, the new table was renamed.

#################################################

1. Delete Does not change the high water level, but after the deletion of the table do shrink operation can reclaim space, reduce the high water level

bys@ bys3>create Table test12 as SELECT * from Dba_objects;

Table created.

bys@ Bys3>insert into the test12 select * from Dba_objects;

17558 rows created.

bys@ bys3>commit;

Commit complete.

bys@ Bys3>insert into the test12 select * from test12;

35116 rows created.

bys@ bys3>commit;

Commit complete.

bys@ Bys3>insert into the test12 select * from test12;

70232 rows created.

bys@ bys3>commit;

Commit complete.

bys@ Bys3>insert into the test12 select * from test12;

140464 rows created.

bys@ bys3>commit;

Commit complete.

bys@ Bys3>alter System checkpoint; ---have to do a checkpoint.

System altered.

bys@ Bys3>select header_block,header_file from dba_segments where segment_name= ' TEST12 ' and owner= ' BYS ';

Header_block Header_file

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

922 4

bys@ bys3>select sum (blocks) from dba_extents where segment_name= ' TEST12 ' and owner= ' BYS ';

SUM (BLOCKS)

-----------

3840

bys@ bys3>select count (extent_id) from dba_extents where segment_name= ' TEST12 ' and owner= ' BYS ';

COUNT (extent_id)

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

45