Speaking of HWM, we'll start with a brief discussion of Oracle's logical storage management. We know that Oracle has 4 granularity on logical storage: Table space, segments, extents, and blocks. (1) Block: is the smallest storage unit, now the standard block size is 8k,oracle every I/O operation is also a block operation, that is, when Oracle reads data from the data file, how many blocks, not how many rows. (2) District: 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 the constant insert data to Pm_user, The original block cannot tolerate the inserted data, Oracle is extended in extents, that is, how many extents are allocated to pm_user, not how many blocks. (3) Paragraph: is composed of a series of zones, in general, when creating an object (table, index), it will be assigned a segment to this object. So in a sense, a paragraph is a particular kind of data. If Create TABLE Pm_user, this segment is the data segment, and the Create Index on Pm_user (NAME), Oracle also assigns a segment to the index, but this is an index segment. The information for a query segment can be obtained from the data dictionary: SELECT * from User_segments, (4) Tablespace: Contains segments, Areas and blocks. The data in 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 to HWM now, so what's a high water mark? This is related to Oracle's space management. A Oracle uses HWM to define the blocks and unused blocks used in a segment.
For example, when we create a table: Pt_sche_detail, Oracle assigns a segment to this object. In this paragraph, even if we do not insert any records, and at least one area is allocated, the first block of the first area is called the segment header (SEGMENT Heade), Some information is stored in the header, and the information in the base is HWM. At this point, because the first block of the first section is used to store some information about the segment header, although no actual records are stored, it is also used, at which point the HWM is located in the 2nd block. When we constantly insert data into Pm_user, The 1th block has not been placed behind the newly inserted data, at which point Oracle uses blocks above the high water level to store the new data, while the HWM itself moves up. That is, when we are constantly inserting data, HWM will move upward so that, under HWM, the used blocks are represented, HWM represents a block that has been allocated but never used. Two HWM when inserting data, it moves up when there is insufficient space for the space to expand, but does not move down when data is deleted. This is like the water level of the reservoir, when the swollen, the water level to move up, when the water exits, the highest water mark is clear.
Consider letting us see a segment, such as a table, filled with blocks, shown in 1. During normal operation, some rows were deleted, as shown in 2. There is a lot of wasted space: (I) between the previous end of the table and the existing block, and (II) inside the block, with some rows that are not deleted. Figure 1: The block assigned to the table. Using a gray square to represent rows ORACLE does not free up space for other objects to use, for a simple reason: Because space is reserved for newly inserted rows and adapts to the growth of existing rows. The highest occupied space is called the highest use Mark (HWM), shown in 2.
Figure 2: The block after the line has been deleted; HWM remains unchanged (iii) HWM information is stored in the segment header.
The HWM itself is stored in the segment header. When the segment space is managed manually, Oracle manages the spatial allocations within the segment through Freelist, a one-way list. When the segment space is automatically managed (ASSM), Oracle manages the space allocations within the segment through bitmap. Four Oracle's full table scan is read 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 the segment to HWM, even if it finds nothing. This task prolongs the time of a full table scan. (v) when inserting a row with a direct path-for example, by directly loading the insert (inserted with APPEND hint) or by Sql*loader Direct path-data block directly above HWM. The space beneath it is wasted. We analyze these two problems, the latter is only a waste of space, but the former is not only a waste of space, but also bring serious performance problems. Let's take a look at the following example: (A) Let's start with a test environment, first creating a space for manually managed Tablespaces: 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 and note that the second field of this table is intentionally set to CHAR (100) so that the table has 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 us look at the number of blocks accessed and the time spent in the query after inserting 10 million records:
Sql> set TIMING onsql> set AUTOTRACE traceonlysql> 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=150 card=10000000) STATISTICS----------------------------------------------------------0RECURSIVE CALLS0DB BLOCK GETS156310 Consistent GETS154239 Physical READS0REDO SIZE379BYTES SENT VIA sql*net to CLIENT503BYTES RECEIVED VIA sql*net from CLIENT2sql*net roundtrips to/from CLIENT0sorts (MEMORY)0sorts (DISK)1ROWS processed
Let's take a look at the execution plan above, which is time-consuming: 1 minutes and 3 seconds. The access method is to use the full table scan mode (FTS), logically read 156,310 blocks, and physically read 154,239 blocks.
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, unused block (empty_blocks) is: 0, total number of rows is (num_rows): 0000 (D) Then we delete the record of this table by deleting it, 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)
1 rows processed As you can see, after the delete table, there is no record in the table, why does the Select COUNT (*) from Test_tab take 1 minutes and 4 seconds, instead of a record a little longer, which is why? and you see, the logic reads 156,310 blocks, How can Oracle be so stupid when it's about 10 million lines of records?
We'll parse the table again after the delete table to see what's changed:
At this point, the Test_tab table currently uses the block is: 156532, the unused block (empty_blocks) is: 0, the total number of rows (num_rows) has become: 0 Why does the table currently make the block number still 156532?
The root of the problem is Oracle's HWM. That is, in the new record, HWM will slowly move up, but after deleting the record, HWM will not move down, that is, after the delete 10 million records, the HWM of this table does not move at all, still in the original position, so, The number of blocks below the HWM is also the same. Oracle's full table scan reads all of the block under the Oracle high-water mark, meaning that, regardless of whether or not the block under HWM is actually storing data, Oracle reads it one by one, so you can imagine After we delete the table, Oracle reads a lot of empty chunks and consumes a lot of time.
Let's look at the status of the delete table after the actual use of space: sql> EXEC show_space (' Test_tab ', ' TEST '); Total BLOCKS ......... ............ 164352
--164352 grand total
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 ......... ..... 158856
--The block ID is numbered against the data file, indicating the number of the first block of the last used extent
Last used BLOCK ......... .......... 1024x768
--a total of 1024 in the last used extent.
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. Last used block represents the block used in the final used extent, and the HWM location can be computed in conjunction with the previous used EXT BLOCK ID:
Last used EXT block ID + last Used BLOCK-1 = The block number of the data file where the HWM resides: 158856+1024-1=159879, this is HWM all blocks with block number HWM: Tota L blocks-unused blocks=164352-7168=157184, that is, HWM in the 157,184th block, whose blockid is 159879 (E), we'll do a few more experiments: Step one: Execute ALTER TABLE test _tab deallocate UNUSED; We 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 then substituting the formula above, to calculate the position of HWM: 157184-0=157184 HWM is located in the Block ID is 158856+1024-1=159879, with just no change, that is to do alter TABLE Test_tab Deallocate unused, the position of the high water mark of the segment has not changed, but we look at unused blocks into 0, the total number of blocks to 157184, which proves that deallocate unused to release HWM above the unused space, But it does not release the free space underneath the HWM, nor does it move the HWM position. Step two: Let's take a look at the usage status of the space after the alter TABLE test_tab move: sql> EXEC show_space (' Test_tab ', ' TEST '); Total BLOCKS ......... ............ 8TOTAL BYTES ......... ............. 65536UNUSED BLOCKS ......... ........... 5UNUSED BYTES ......... ............ 40960LAST used EXT FILEID ......... ..... 9LAST used EXT blockid ............ 2632LAST used BLOCK ......... .......... 3
At this point, the total number of blocks used has changed to 8, we will substitute the formula above to calculate the location 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 the data file ID is 9 (this does not change, the data file or the original data file, just free the original free space), the last use of the number of blocks also changed to 3, that is, the use of 3 blocks, HWM is in the last use of the block, That is, the 3rd block. You may wonder why there are 5 unused blocks after releasing space. That is, there are 5 blocks that have been allocated but never used on top of HWM. The answer is related to the law of HWM movement. When we insert data, Oracle first locates free space in blocks below HWM (via free list freelist), and if there is no free block in the Freelist, Oracle begins to scale up, and HWM moves up, once every 5 blocks. Let's take a look at Oracle's note: 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 is 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: SELECT COUNT (*) from Test_tab time: 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
----------------------------------------------------------
0RECURSIVE CALLS
0 DB BLOCK GETS 3 consistent gets
0 physical reads
0 378 BYTES SENT VIA Sql*net to client ,
503 bytes RECEIVED VIA sql*net from CLIENT&NBSP
2 sql*net ROUNDTRIPS TO/FROM CLIENT
0 sorts (MEMORY)
0 sorts (DISK)
1 rows processed
Soon, less than 1 seconds. We're going to make a final analysis of the table, where the block currently used is: 0, unused block (empty_blocks) is: 0, total number of rows is (Num_rows): 0
From this we can also find that the analysis table and Show_space show the data is a bit inconsistent. So which one is right? In fact, these two are accurate, but the method of calculation is a little different. In fact, when you create an object such as a table, whether you have inserted data or not, it will occupy some blocks Oracle also allocates the necessary space for it. Again, 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 see how the space is 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 no change in truncate and move. In order to finally verify my view above, I DROP the table and create a new table to see if Oracle actually allocates the necessary space for this object before inserting any data: DROP table Test_tab; CREATE TABLE Test_tab (C1 number (ten), 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 BLOCK ......... .......... 3 See, even if I didn't insert any line of records, Oracle allocated 8 blocks to it. Of course, this is related to the initial parameter and the minextents parameter of the built-in statement: see Test_tab storage parameters: S torage
(
INITIAL 64K
Minextents 1
MAXEXTENTS UNLIMITED
); That is, after this 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 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 free more space than HWM, use keep 0.
ALTER TABLE tablesname deallocate UNUSED KEEP 0;
(3) The TRUNCATE TABLE DROP STORAGE (default) command allows the space above the minextent to be fully released (returned to the operating system) and resets HWM.
(4) If you want to move the HWM only, instead of keeping the table locked for a long time, you can use TRUNCATE TABLE reuse STORAGE to reset the HWM only.
(5) ALTER table Move moves the HWM, but requires double table space on move, and if there is an index on the table, you need to refactor the index
(6) The delete table does not reset the HWM, nor frees free space (that is, the delete empty space can only be used for future insert/update of the object itself, not for other objects) in Oracle 10G: ALTER TABLE can be used Test_ TAB SHRINK Space Command to move HWM online,
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 make the row migrate to row movement (see example) first.
Unlike using Alter TABLE MOVE, you do not need to refactor the index after you execute this command.
Oracle Official NotesShrinking 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 is 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 being scanned in full table scans, which in turns leads to better decision Supp ORT 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 was blocked for a short time at the end of the shrink operation, when the space was deallocated. Indexes was maintained during the shrink operation and remain usable after the operation was 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 is above the high water mark. 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, must first enable row movement in the object want to shrink and disable any rowid-based triggers define D 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 is eligible for online segment shrink except these:
* IOT Mapping tables
* Tables with ROWID based materialized views
* Tables with function-based indexes operation process: 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 400,000 records, below to see the table space distribution. 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 ......... .......... 2,561 a total of 9,216 data blocks, HWM in 9216-768=8448 this block.
You can also get hwm=8*16+128*63+256=8192+256=8448 PL/SQL procedure successfully completed by looking at extents.
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 Block ......... .......... Successfully completed, procedure PL/SQL. The HWM of the table does not change after the delete operation, or it is in the No. 8448 block of this position.
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//first to enable ROW movement to 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 ......... .......... Procedure PL/SQL successfully completed.
elapsed:00:00:00.02//can see HWM dropped to 3656 on this block! Resources:
http://tolywang.itpub.net/post/48/307529
http://www.itpub.net/viewthread.php?tid=205560
Oracle High Watermark (HWM) and performance optimization
