How to deal with high water mark in Oracle Database

Time:2021-5-11

1、 What is a high water mark?

When Oracle database creates a table, it will allocate a segment for the table. For the convenience of understanding, the upper limit of the segment space to hold data is called high water mark (HWM). HWM is a mark to indicate how many unused blocks are allocated to the segment.
There are two conclusions
1. The block above the water level indicates that it has been allocated but not used, and the block below the water level indicates that it has been allocated and used (including the block in use and the empty block used and deleted data)
2. Theoretically, the water level of a table will only increase but not decrease (unless reset by a special method). Even if all the data in the table are deleted, HWM is still the original value.

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

a) A full table scan usually reads all the database blocks that belong to the table until the HWM tag, even if there is no data in the table.

b) Even if there are idle database blocks below HWM and the append keyword is used when inserting data, HWM will continue to increase, occupying system resources, and the actual space occupied by tables will continue to increase, resulting in system problems

3、 Causes of high water level and solutions:

The causes are as follows
1. A large amount of data was deleted when using the operation table.
2. Used in insertion/append nologging/Statement, the append keyword will be inserted from a random position in the allocation segment for the table, and the waterline will continue to increase.
3. In SQL load, truncate is used by default, with its own reuse storage parameter, so that the water level will not decrease after truncate.

resolvent:
1. Direct truncate table drop storage
2. Create a maintenance table, move regularly and rebuild the index or shrink space.
3. When the table data is dropped into the table, the backup table is established according to the date, and the data of a certain number of days is retained
4. Rename the table name, rebuild the table, rebuild the index, import the data into the rebuilt table, drop the original table, and then rename the rebuilt table to the original table
5. Use the alter table name shrink space (oracle10 new function)
6. Online table redefinition (powerful, complex operation, generally not used, can change the structure of the table)
Comparison of two methods of table reconstruction: move and shrink
Move is a command in Oracle8. When it is used, another table space with the same size as the original table space will be created, and then the data will be copied. After that, the original table will be replaced by the latter table to solve the HWM problem.
Disadvantages: lock the table during operation, and the index will be invalid.
Shrink is a new function of oracle10. It will not open up a new table space when it is used. The operation is divided into two steps. The first step is to sort out the data, the second step is to lower the water level, and the first step is to operate online. You can do the second step when the business is not busy.
Disadvantages: it’s slower than move.
The detailed operation steps of shrink are as follows:
Detailed shrinkage steps

  1. Total surface contraction
    Whether a partitioned table or a non partitioned table, shrinking can be performed at the table level
    ALTER TABLE owner.table_ name ENABLE ROW MOVEMENT;( Open row movement will make cursor invalid, be careful)
    ALTER TABLE owner.table_ Name shrink space compact cascade (the first step is to sort out the data without lowering the high water mark, which can be operated online);
    ALTER TABLE owner.table_ Name shrink space cascade (the second step is to reset the high water mark, which will temporarily lock the table and need to be operated when the business volume is small);

  2. Single partition shrink
    The partition table can also be contracted at the partition level. The specific statements are as follows:
    ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;
    ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
    ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;
    ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
    ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;

4、 Steps of online table redefinition:

1. Confirm whether the table can be redefined online:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => ‘OWNER’,
tname => ‘ORIGTABLENAME’,
options_flag => dbms_redefinition.cons_use_pk);
END;
/
–If there is a primary key, it is options_ flag => DBMS_ REDEFINITION.cons_ use_ PK, if there is no DBMS_ REDEFINITION.cons_ use_ rowid

2. Create a new middle table tablename_ TMP is ready to redefine (it can add and delete fields, modify table storage parameters, modify to partition table, etc.)
Note that DBMS cannot be used because the properties of columns are required to be the same during online redefinition_ Redefinition completes the adjustment of column type

–Common table
CREATE TABLE OWNER.TABLENAME_TMP ( ) TABLESPACE XXX;
Partition table
CREATE TABLE OWNER.TABLENAME_TMP ( )
PARTITION BY RANGE (PARTITIONNAME)
(
PARTITION P1 VALUES LESS THAN (‘xxx’),

PARTITION PMAX VALUES LESS THAN (MAXVALUE)
)
TABLESPACE XXX;

3. Turn on parallel to speed up online redefinition:
ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

4. Start online redefinition:
BEGIN
dbms_redefinition.start_redef_table(
uname => ‘OWNER’,
orig_table => ‘ORIGTABLENAME’,
int_table => ‘TABLENAME_TMP’,
options_flag => dbms_redefinition.cons_use_pk);
END;
/
–If there is a primary key, it is options_ flag => DBMS_ REDEFINITION.cons_ use_ PK, if there is no DBMS_ REDEFINITION.cons_ use_ rowid

5. Using copy_ TABLE_ The permissions, constraints and indexes of the original table are created on the intermediate table by dependencies
DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
uname => ‘OWNER’,
orig_table => ‘ORIGTABLENAME’,
int_table => ‘TABLENAME_TMP’,
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => true,
copy_constraints => true,
copy_privileges => true,
ignore_errors => false,
num_errors => num_errors,
copy_statistics => true);
END;
/

6. If online redefinition takes a long time, during this period, the application inserts data into the source table, and the intermediate table will not have this data. Use sync_ interim_ The table package synchronizes all DML data of the source table during online redefinition
BEGIN
dbms_redefinition.sync_interim_table(
uname => ‘OWNER’,
orig_table => ‘ORIGTABLENAME’,
int_table => ‘TABLENAME_TMP’);
END;
/

7. Complete online redefinition:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => ‘OWNER’,
orig_table => ‘ORIGTABLENAME’,
int_table => ‘TABLENAME_TMP’);
END;
/

9. When an error is reported in the intermediate redefinition, the following command should be executed to terminate the redefinition:
*BEGIN
*DBMS_REDEFINITION.ABORT_REDEF_TABLE(
*uname => ‘OWNER’,
*orig_table => ‘ORIGTABLENAME’,
*int_table => ‘TABLENAME_TMP’);
*END;
*/

10. Delete the intermediate table after the data index is synchronized successfully
drop table OWNER.TABLENAME_TMP;

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]