Concept of high water level (Water Mark) and solution of high water level problem

Speaking of HWM, we first need to talk briefly about Oracle's logical storage management. We know that Oracle is divided into 4 granularity on logical storage: tablespace, segment, region, and block.
(1) Block: Is the smallest granularity of storage units, now the standard block size is 8k,oracle every I/O operation is also done by block, that is, when Oracle reads data from the data file, is read how many blocks, not how many rows.
(2) Area: 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 allocates a space for the table, with constant insert data to Pm_user, When the original area cannot accommodate the inserted data, Oracle expands in a district, that is to say, how many extents to allocate to pm_user, not how many blocks.
(3) Paragraph: is made up of a series of zones, generally, when an object is created (a table, an index), and a segment is assigned to the object. So in a sense, a segment is a certain kind of data. such as Create TABLE Pm_user, this segment is the data segment, and create Index on Pm_user (NAME), Oracle also assigns a segment to the index, but this is an index segment. The information for the query segment can be obtained from the data dictionary: SELECT * from User_segments.
(4) Tablespace: Contains segments, areas, and blocks. The data for the tablespace is physically stored in the data file in which it resides. A database must have at least one tablespace.     OK, we're back in HWM now, so what's the high water mark? This is related to Oracle's segment space management. A Oracle uses HWM to define blocks and unused blocks used 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, and at least one of the areas is allocated, the first block of the first zone is called the segment header (SEGMENT Heade), Some information is stored in the header of the paragraph, the HWM information in the base is stored here. At this point, because the first block of the first zone is used to store some of the information for the segment header, although no actual records are stored, the HWM is in the 2nd block. When we keep inserting data into Pm_user, The 1th block has not put back the newly inserted data. At this point, Oracle uses blocks above the high water level to store the new data, and the HWM itself moves up. That is, when we keep inserting data, the HWM will move up, so that, under HWM, it means the used block, Above the HWM is a block that has been allocated but has never been used.
Two HWM when inserting data, it moves up when there is not enough space available, but does not move down when the data is deleted.
This is like the water level of the reservoir, when the swollen, the water level upward, when the water exit, the highest level of traces or clear to the visible.
Consider letting us look at a segment, such as a table, filled with blocks, as shown in Figure 1. During normal operation, some rows were deleted, as shown in Figure 2. There is a lot of wasted space: (I) between the last end of the table and the existing blocks, and (II) inside the block, and some of the rows that have not been deleted.
Figure 1: The block assigned to the table. To represent a line with a gray square
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 maximum space occupied is called the maximum use Mark (HWM), as shown in Figure 2.
Figure 2: The block at the back of the line has been deleted; HWM remains unchanged (iii) the HWM information is stored in the section header.

The information of the HWM itself is stored in the segment head. When the segment space is a manual management method, Oracle manages the space allocation within the paragraph through freelist (a one-way list). When the segment space is automated (ASSM), Oracle manages the space allocation within the segment through bitmap.
Four Oracle's full table scan reads all blocks below the high water mark (HWM).
So the problem arises. When a user makes a full table scan, ORACLE always has to scan from paragraph to HWM, even if it finds nothing. This task extends the time of the full table scan.
(v) when inserting rows with direct paths-for example, by directly loading inserts (inserted with APPEND prompts) or by Sql*loader direct paths-blocks of data directly above HWM. The space underneath it is wasted.
Let's analyze these two questions, 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 take a look at the following example: (A) We first set up the test environment, the first step is to create a space for the manual management of the table space:
CREATE tablespace "Rainny"
LOGGING
DataFile ' D:oracle_homeoradatarainnyrainny. ORA ' SIZE 5M
Autoextend
On NEXT 10M MAXSIZE Unlimited EXTENT MANAGEMENT Local
SEGMENT space MANAGEMENT MANUAL; (B) Create a table, note that the second field of this table was deliberately set to CHAR (100) so that the table has a large enough space after inserting 10 million records: Create TABLE Test_tab (C1 number (), 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 look at the number of blocks accessed after inserting 10 million records and the time spent in the query:
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) ' Test_tab ' (cost=15056 card=10000 000 ) STATISTICS----------------------------------------------------------0 Recursive CALLS 0 DB block GETS 1563 Consistent GETS 154239 physical reads 0 REDO SIZE 379 BYTES SENT VIA sql*net to CLIENT 503 BYTES RECEIVED VI A sql*net from CLIENT 2 sql*net roundtrips to/from CLIENT 0 sorts (MEMORY) 0 sorts (DISK) 1 ROWS proce ssed
Let's take a look at the execution plan above, which is a total of 1 minutes and 3 seconds. The approach is to use the full table scanning method (FTS), logic read 156,310 blocks, 154,239 blocks of physics read.
Let's analyze This table:
BEGIN
Dbms_stats. Gather_table_stats (ownname=> ' TEST ',
Tabname=> ' Test_tab ',
Partname=> NULL); End; /
Found that this table currently uses the block has: 156532, unused block (empty_blocks) is: 0, the total number of rows (num_rows): 1000 0000
(D) Next we delete the record of the table by deleting it and then 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) ' 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)
1 ROWS processed You see, after the delete table, there is no record in the table, why select COUNT (*) from Test_tab spent 1 minutes 4 seconds, instead of a record slightly longer, this is why? And everyone looks at the logic Read 156,310 blocks, similar to the previous 10 million line records, how can Oracle be so stupid?

We analyze the table again after the delete table and see what happens:
At this point, the Test_tab table currently uses the block is: 156532, unused block (empty_blocks) is: 0, the total number of rows (num_rows) has become: 0
Why is the table currently making the block number 156532?
The root of the problem is Oracle's HWM. That is, when new records are added, HWM will move up slowly, but after deleting the records, HWM will not move down, that is, the HWM of the table has not moved since the deletion of the 10 million records, which is still in the original position, so The number of blocks below the HWM is also the same. Oracle's full table scan is all block under the Oracle high watermark, that is, the Oracle will one by one read, regardless of whether the HWM block now actually holds the data, so you can imagine, After we delete the table, Oracle reads a lot of empty chunks and consumes a lot of time.
Let's take a look at the actual use of the space after the delete table: sql> EXEC show_space (' Test_tab ', ' TEST ');
Total BLOCKS ................... 164352--a total of 164352 pieces
Total BYTES ... ................... 1346371584
Unused BLOCKS ......... .......... ... 7168--There are 7168 pieces that have not been used, that is, the number of blocks above the HWM
Unused BYTES .......... ......... .... 58720256
Last USED EXT Fileid ............ 9
Last USED EXT blockid ............ The 158856--block ID is numbered for the data file, indicating the number of the first blocks of the last extent used
Last USED blocks ................ ... 1024-a total of 1024 blocks in a extent last used
Pl/sql PROCEDURE successfully COMPLETED
A total of 164352 pieces, in addition to a segment HEADER, the actual total used 164,351 blocks, 7168 pieces have never been used. The last USED block represents the block that is used in the final extent, combined with the USED EXT block ID, to compute the HWM location:
Last USED EXT blocks ID + last USED BLOCK-1 = HWM The block number of the data file in which it was found: 158856+1024-1=159879, this is HWM all the number
HWM in block: Total blocks-unused blocks=164352-7168=157184, that is, HWM in the 157,184th block, its blockid is 159879
(E) Let's do a few more experiments:
The first step: Execute alter TABLE test_tab deallocate unused;
Let's take a look at the usage of the segment 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 blocks ................ ... 1024 at this time we are substituting the formula above to calculate the position of HWM: 157184-0=157184 HWM is in the block ID is 158856+1024-1=159879, with just no change, that is, execute alter TABLE Test_tab After the deallocate unused, the position of the high water mark of the segment has not changed, but we look at the unused blocks into 0, the total number of blocks reduced to 157184, which proves that deallocate unused for the release of HWM above unused space, But it does not release the free space below the HWM, nor does it move the HWM position.
Step two: Let's take a look at the usage of the space after the execute 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 blocks ... .............. ... 3
At this point, the total number of blocks used has become 8, we are substituting the formula above to calculate the position of HWM: 8-5=3 HWM is located in the Block ID is 2632+3-1=2634, OK, we found that at this time HWM position has changed, now HWM position is in the 3rd block, Its block ID is 2634, all data file ID is 9 (this does not change, the data file or the original data file, just released the original free space), the last use of the block number also becomes 3, that is, already used 3, HWM is in the last block used, On the 3rd block. You may find it strange why there are 5 unused blocks after the release of space. That is, there are 5 blocks that have been allocated but never used on top of the HWM. The answer is related to the law of HWM movement. When we insert data, Oracle First locates free space (via the free list freelist) in the blocks underneath the HWM, and if Freelist does not have free chunks, Oracle begins to expand, and HWM moves up, moving every 5 blocks. Let's 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 is reclaimed at the table level by using the following command:
ALTER TABLE deallocate Unused ...
Let's take another look: SELECT COUNT (*) time spent 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) ' 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 1 seconds. We end with an analysis of the table, at which point the block currently used is: 0, unused block (empty_blocks) is: 0, the total number of rows (num_rows): 0
From this we can also find that the analysis table and the show_space display of the data is a little inconsistent. So which one is accurate? In fact, these two are accurate, but the method of calculation is a little different. In fact, when you create an object like a table, it takes up chunks, whether you insert it or not, Oracle also assigns it the necessary space. Also, after releasing free space with ALTER TABLE move, some space is reserved for the table.
Finally, we'll execute the TRUNCATE command, truncate the table, and look at the usage of the segment space:
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 blocks ................ ... 3
Pl/sql PROCEDURE successfully COMPLETED
Sql>
We found no change after truncate and move.
To finally verify my view, I'll drop the table and create a new one to see if Oracle does have the necessary space allocated to this object before inserting any data:
DROP TABLE Test_tab;
CREATE TABLE Test_tab (C1 number (), C2 CHAR ()) 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 blocks ................ ... 3 See, even if I didn't insert any row, Oracle allocated it 8 blocks. Of course, this is related to the initial parameters and the minextents parameters of the table statement: see Test_tab storage parameters:
S Torage
(
INITIAL 64K
Minextents 1
Maxextents Unlimited
);
That is, after the object is created, Oracle assigns at least one zone, the initial size is 64K, and the size of a standard block is 8K, just 8 blocks.
Summary:
In the 9I:
(1) If minextent can make alter TABLE tablename DEALLOCATE unused will release all unused space above HWM
(2) If the Minextent >HWM release the space above minextents. If you want to release more space than HWM, use keep 0.
ALTER TABLE tablesname deallocate unused KEEP 0;
(3) The TRUNCATE TABLE DROP STORAGE (default) command can completely free up space on the minextent (return to the operating system) and reset HWM.
(4) If you only want to move the HWM, but do not want to keep the table locked for a long time, you can use the TRUNCATE table reuse STORAGE, only the HWM reset.
(5) ALTER table Move moves the HWM, but doubles the table space when moving, and needs to refactor the index if there is an index on the table
(6) The delete table does not reset the HWM, nor does it free space (that is, the delete space is only available to the object itself for future insert/update and cannot be used to other objects).
In Oracle 10G:
You can move HWM online using the alter TABLE test_tab SHRINK Space Command.
If you want to compress the index of the table at the same time, you can publish: ALTER table Test_tab SHRINK space CASCADE
Note: When you use this command, you need to first make the row portable (see example) to migrate rows movement.
Unlike using alter TABLE move, you do not need to refactor the index after executing this command.
Oracle Official Description
Shrinking Database Segments Online
Your use online segment shrink to reclaim fragmented free spaces below the high water mark into Oracle Database segment. The benefits of segment shrink are these:
* Compaction of data leads to better cache utilization, which on turn leads to better online transaction processing (OLTP) Performance.
* The compacted data requires fewer blocks to is scanned in full table scans, which in turns leads to better decision ORT system (DSS) performance.
Segment Shrink is a online, in-place operation. DML operations and queries can be issued during the data movement phase of segment. Concurrent DML operation are blocked for the "a short" at the "end of" shrink operation, when the "is" is deallocated. Indexes are maintained during the shrink operation and remain after the usable is operation. Segment shrink does not require extra disk spaces to be allocated.
Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused spaces only above mark of the high water. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the Reclaim Ed space.
Segment shrink requires that rows is moved to new locations. Therefore, you must the "I" Row movement in the object for you want to shrink and disable any rowid-based triggers D on the object.
Shrink operations can is performed only to segments in locally managed tablespaces with automatic segment spaces management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink:
* IOT Mapping tables
* Tables with ROWID based materialized views
* Tables with function-based indexes the process of the 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 400,000-record table below to see how the table is distributed in space.
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 blocks ................ ... 256
A total of 9,216 pieces of data, HWM in 9216-768=8448 this block.
You can also get hwm=8*16+128*63+256=8192+256=8448 by looking at extents
Pl/sql procedure successfully completed.
elapsed:00:00:00.01
Sql> Delete from demo where rownum<220000;
219999 rows deleted.
elapsed:00:00:40.99
Sql> 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 blocks .....