Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the knowledge of high water level in database table space". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the knowledge of high water level in database tablespace"?

For manual segment space management (MSSM) tablespaces

High water mark (HWM) refers to the dividing line between used and unused block in this segment of space, and the space above the HWM cannot be used until it is formatted. That is to say, the data blocks above HWM are unformatted blocks, and these unformatted blocks cannot be used by insert data before formatting.

In a database transaction, when a new free block is requested and the blocks in the existing free list do not meet the requirements, the HWM moves up, then formats a set of data blocks and joins the Free List for use.

The data blocks under the HWM may also be idle. When the data is deleted, the data blocks are released back to the FreeList and can be used by other data changes. HWM usually can only grow upward and will not shrink automatically.

HWM will affect the reading behavior of Oracle when performing a full table scan. For a full table scan operation, Oracle must read all data blocks under HWM. If a data table deletes most of the records due to DELETE operation, but HWM will not decrease, so when performing a full table scan again, Oracle still needs to read all data blocks in the object segment (that is, all data blocks below HWM).

For normal objects, we don't need to pay much attention to the impact of their HWM, but if table deletions are very frequent and part of the Block in the table is already empty, then we may need to pay attention to its spatial performance.

Second, simply estimate the data of empty blocks in the table

Find out the number of blocks occupied by the table through the dba_tables view

SELECT blocks FROM dba_tables WHERE table_name=' table name'

Calculate the number of blocks occupied by the data in the actual table through rowid

The current rowid format of Oracle (later than 8i) is OOOOOO.FFF.BBBBBB.RRR, which has a total of 18 bits and occupies 10 bytes, representing 80 bits.

Where O represents the object number, F represents the file number, B represents the block number, and R represents the line number. The way of these 80 digits is:

32bit obj# + 10bit rfile# + 22bit block# + 16bit row#

So through such a SQL, we can roughly calculate the number of block occupied by the table (take the top 15 places of rowid).

SELECT COUNT (DISTINCT (SUBSTR (ROWID,1,15) FROM table name

The data block occupancy ratio and the empty block occupancy ratio can be obtained by dividing the results of the two steps.

For automatic segment space management (ASSM) tablespaces

For the section managed by ASSM, there are two kinds of high water level marks at the head of the section, namely, LHWM and HHWM, namely, low high water level and high high water level.

Since Free List does not exist in ASSM management, the concepts of LHWM and HHWM are introduced to implement segment space management. In ASSM management mode, when a session inserts data into a table, the database first formats a bitmap block (rather than a set of blocks as before), which instead of Free List is used to track the state changes of data blocks in the segment. The database uses bitmap blocks to find free blocks and format them before using them.

The LHWM and HHWM features of Segment under ASSM management are as follows:

All blocks above HHWM must be unformatted blocks

All data blocks below LHWM must be formatted blocks

Blocks between LHWM and HHWM may or may not be formatted

When Oracle scans a Segment for a full table, it scans all the way to HHWM.

For Segment managed by ASSM, the space usage can be calculated directly through the DBMS_SPACE provided by Oracle

Create or replace procedure show_space_assm (p_segname in varchar2,p_owner in varchar2 default user,p_type in varchar2 default 'TABLE') as l_fs1_bytes number;l_fs2_bytes number;l_fs3_bytes number;l_fs4_bytes number;l_fs1_blocks number;l_fs2_blocks number;l_fs3_blocks number;l_fs4_blocks number;l_full_bytes number;l_full_blocks number;l_unformatted_bytes number L_unformatted_blocks number; procedure p (p_label in varchar2, p_num in number) isbegindbms_output.put_line (rpad (paired labelJournal 40 dint.') | | p_num); end Begindbms_space.space_usage (segment_owner = > paired ownergy segmentalization name = > paired segnamephery segmentalization type = > paired typemark FS1 blocks bytes = > lumped fs1 blocks bytesdepartment FS1 blocks = > lumped fs1 blocks Fs2 blocks Fs2 blocks = > lumped fs2 blocks fs3 blocks bytes = > lumped fs3 blocks Fs4_bytes = > lumped unformatted bytes = > l_unformatted_bytes) P ('free space 0-25% Blocks:',l_fs1_blocks); p (' free space 25-50% Blocks:',l_fs2_blocks); p ('free space 50-75% Blocks:',l_fs3_blocks); p (' free space 75-100% Blocks:',l_fs4_blocks); p ('Full Blocks:',l_full_blocks); p (' Unformatted blocks:',l_unformatted_blocks); end;/

We know that under ASSM, the space usage of block is divided into free space: 0-25%, 25-50% 50-75%, 70-100% full. Show_space_assm will summarize the number of block of these 5 types for the table to be counted.

Let's take a look at table HWM1's space usage:

SQL > exec show_space_assm ('HWM1','DLINGER')

Free space 0-25% Blocks:.0

Free space 25-50% Blocks:.1

Free space 50-75% Blocks:.0

Free space 75-100% Blocks:.8

Full Blocks:..417

Unformatted blocks:.0

This result shows that there are 417 block in table HWM1,full, 8 free space in 75-100% Block, and 1 Block in free space 25-50%. When the state of blocks under table HWM is mostly the higher value of free space, we consider merging the blocks under HWM to release the idle block and reduce the HWM of table.

IV. Methods of lowering the high water level

Export / Import combined with TRUNCATE

The truncate command can lower the high water level, but there may be very few scenarios for this method

You can export data through EXP or create an incense table using CTAS, then Truncate table, import or insert data back, but it is not suitable for data tables with uninterrupted service

Combination of RENAME and INSERT

For continuously used data tables, if the data is log data based on writing, the data table can be renamed through RENAME, and then the data table can be rebuilt according to the original structure, and the insert operation can be restored at this time. This process is very fast, with little impact on the database, and then the data in the RENAME table can be inserted back. This completes the data collation, and HWM can be reduced. This method is suitable for write-based business types. It is not suitable for objects with frequent additions, deletions, changes and searches.

Online redefinition (DBMS_REDEFINITION)

The online redefinition feature has been introduced from Oracle 9i. Through the DBMS_REDEFINITION package, you can redefine the table online, such as changing the table field name, adding fields, etc., of course, you can also borrow this package to organize the space.

In the process of online redefinition, Oracle records the intermediate change data through the intermediate temporary table. After completing the redefinition, the data can be integrated into the redefined table, and the normal operation of the database can continue.

Shrink characteristics

Oracle 10g has been introduced to support online space reorganization, which is the online segment space recovery function (Shrink Database Segments Online).

Online segment contraction is effective for tables in the ASSM tablespace. The essence of Shrink is to perform a series of DML operations on the table, delete the sparse rows at the end of the table, and reinsert at the top of the table. Through such a series of operations, you can fill the "loophole" space in the table segment, gradually leaving all the remaining space at the end of the table, and then Oracle can reset the HWM of the table to free up space. Because Shrink deals with data rows, row-level exclusive locks will be obtained on the table, so it will not affect the DML operation of the whole table, which is the meaning of Online, but Shrink may generate a large number of Redo, affecting the amount of archives, which needs to be considered in the operation.

Because reclaiming segment space requires moving row data, the rowid of the data will change and the index will be maintained at the same time, so you need to set the ENABLE ROW MOVEMENT property of the table before executing Shrink.

It should be noted, however, that since segment space restructuring is done through DML operations, additional redo will be generated, and if the data table is very large, the resulting Redo may be acceptable to the production.

Alter table table name enable row movement;alter table table name shrink space

Tables that do not support Shrink:

IOT mapping tables

Tables with rowid based materialized views

Tables with function-based indexes

SECUREFILE LOBs

Compressed tables

Move object

Moving objects through Move can reduce HWM, but after Move, the index needs to be rebuilt, and the online application will be affected in the process of Move, so the use of this method is limited.

Other methods

The reconstruction of space is always a difficult problem, and all kinds of methods are at the expense of certain performance, which makes many methods unavailable in the actual production process, so a better way should be to start with application and planning. Some possible problems can be avoided from the very beginning.

Among the most commonly used methods, partitioning is a commonly used means. Many data tables involving a large number of data changes can be dealt with through partitions. Because partitioned tables can carry out operations such as DROP, TRUNCATE and so on, partitions can be easily maintained and a series of space problems can be solved.

Of course, zoning is not omnipotent, and its applicable environment is limited, so the real solution to the problem is ourselves. Only after we understand and understand the various technologies of Oracle, can we work out the means of space maintenance that suits our needs.

At this point, I believe you have a deeper understanding of "what is the knowledge of high water level in database tablespace?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.