In my previous articles, I have discussed the danger of adding default columns to a data table, especially in online production environments. A default column is added to a large data table, which has the following serious hazards: The system runs at high load and consumes a large amount of resources. Adding columns is a one-time DDL operation that generates a large number of RedoLog records.
In my previous articles, I have discussed the danger of adding default columns to a data table, especially in online production environments. A default column is added to a large data table, which has the following serious hazards: The system runs at high load and consumes a large amount of resources. Adding columns is a one-time DDL operation that generates a large number of Redo logs.
In my previous articles, I have discussed that adding default columns to a data table is a very dangerous thing, especially in online production environments. To add a default column to a large data table, the most direct cause is:
The system runs at high load, consuming a large amount of resources. Adding columns is a one-time DDL operation that generates a large number of Redo Log records;
Data Tables are locked for a long time, which hinders the production system. When a data column is added, an exclusive lock is added to the data table, which hinders other DML operations;
Destroys the original storage structure, resulting in a large amount of Row Migration data. When you try to add each data row to the default value for expansion, the rowid feature can cause serious row connections and damage the storage structure of the original data table;
This article mainly talks about the Row Migration phenomenon caused by adding the default value.
1. Start with the Row Migration phenomenon
Row Migration is essentially a phenomenon that occurs due to the Oracle storage and Row location features. In Oracle, all data rows are retained on the data block unit. A data block can hold several pieces of data (normally ). Some data columns, such as varchar2, usually allocate space based on the length of the input data.
If the data column and column are filled with larger data, that is, the space is expanded. What changes will happen on block storage? Each data block reserves a part of free space as a reserved location for changing data rows. If the length continues to expand, what will happen?
Oracle will try to copy the data row and find a new data block for storage. In this way, you can put down the data block. Then, a new problem occurs, that is, the Rowid problem.
In Oracle, Rowid is the physical address used to locate a record. Rowid includes the relative number, object number, data block number, and Slot Number of the data file. Rowid is generally used as a flag for data rows and is stored on the related index leaf nodes. However, when a data row is transferred to another data block, the physical storage location has essentially changed. The Rowid contained in indexes and other objects is invalid.
Oracle solves this problem through the "virtual Portal" method. Although the location of the Data row has reached another place, the corresponding Rowid has not changed. When we retrieve the data and the Server Process locates the original location, it will find a conversion jump address, which records the real Rowid address. This is where Row Migration occurs.
Row Migration poses many potential problems to system performance. For example, you only need to find one data block record for a row of data. Now you need to find multiple data blocks. This is the performance problem.
When we add the default data Row, the Row Migration outbreak will occur.
2. Add Row Migration and default value Columns
Next we will use experiments to prove the emergence of Row Migration. We chose the 11gR2 environment for the experiment.
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
SQL> create table t as select object_id from dba_objects where 1 = 0;
Table created
-- Add several records;
SQL> insert into t select object_id from dba_objects where rownum
99 rows inserted
SQL> commit;
Commit complete
Data Table T, in terms of storage structure and space allocation:
SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );
PL/SQL procedure successfully completed
SQL> select bytes, blocks, extents from user_segments where segment_name = 'T ';
BYTES BLOCKS EXTENTS
------------------------------
65536 8 1
SQL> select blocks from user_tables where table_name = 'T ';
BLOCKS
----------
1
User_segment records the total space allocated to the data segment, but this does not represent all the HWM locations. Blocks in User_tables indicates the number of data blocks in HWM. From the above results, there is only one data block in HWM. From the rowid analysis, the actual situation is also true.
SQL> select dbms_rowid.rowid_block_number (rowid) blockno, count (*) from t group by dbms_rowid.rowid_block_number (rowid );
Blockno count (*)
--------------------
85857 99
Next we will add data columns.
SQL> alter table t add vc varchar2 (1000) default lpad ('T', 500, 'T ');
Table altered
Executed in 0.078 seconds
The usage of the corresponding space is as follows:
SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );
PL/SQL procedure successfully completed
Executed in 0.141 seconds
SQL> select blocks from user_tables where table_name = 'T ';
BLOCKS
----------
12
Executed in 0.016 seconds
SQL> select bytes, blocks, extents from user_segments where segment_name = 'T ';
BYTES BLOCKS EXTENTS
------------------------------
131072 16 2
SQL> select dbms_rowid.rowid_block_number (rowid) blockno, count (*) from t group by dbms_rowid.rowid_block_number (rowid );
Blockno count (*)
--------------------
85857 99
Executed in 0.016 seconds
The above situation shows that the Oracle Data Table T has pushed up the watermark line HWM to 12 blocks, and the new extent is also allocated for space allocation.
However, the rowid of all data rows remains unchanged. The "house number" of all data rows has not changed, but what about storage? It's a strange increase. Under normal capacity, the data block condition should be as follows:
SQL> create table t_bak as select * from t;
Table created
SQL> exec dbms_stats.gather_table_stats (user, 't_bak ', cascade => true );
PL/SQL procedure successfully completed
SQL> select bytes, blocks, extents from user_segments where segment_name = 't_ BAK ';
BYTES BLOCKS EXTENTS
------------------------------
131072 16 2
SQL> select blocks from user_tables where table_name = 't_ BAK ';
BLOCKS
----------
8
SQL> select dbms_rowid.rowid_block_number (rowid) blockno, count (*) from t_bak group by dbms_rowid.rowid_block_number (rowid );
Blockno count (*)
--------------------
86589 14
86588 14
86585 14
86586 14
86591 14
86590 14
86587 14
86592 1
8 rows selected
Next, let's prove that a line link has occurred.
3. data table row link test
Analyze statements were once a popular method for collecting data table statistics. However, with the mature popularization of the dbms_stats package, analyze's functions in statistic collection have been gradually weakened. However, Oracle retains the two basic functions of this statement: Row Migration and index health check for data tables.
The following uses the analyze statement to test the data table T. First, we need to create a data table containing the analysis results.
-- Call the script in Oracle_HOME;
SQL> @? /Rdbms/admin/utlchain. SQL
Table created.
SQL> desc chained_rows;
Name Null? Type
-----------------------------------------------------------------------------
OWNER_NAME VARCHAR2 (30)
TABLE_NAME VARCHAR2 (30)
CLUSTER_NAME VARCHAR2 (30)
PARTITION_NAME VARCHAR2 (30)
SUBPARTITION_NAME VARCHAR2 (30)
HEAD_ROWID ROWID
ANALYZE_TIMESTAMP DATE
SQL> create public synonym chained_rows for chained_rows;
Synonym created.
SQL> grant all on chained_rows to public;
Grant succeeded.
Analyze the data table as follows:
-- Check Row Migration of data rows;
SQL> analyze table t list chained rows into chained_rows;
Table analyzed
Executed in 0.125 seconds
-- Number of Row Migration events;
SQL> select count (*) from chained_rows;
COUNT (*)
----------
86
Executed in 0.016 seconds
SQL> select head_rowid from chained_rows where rownum
HEAD_ROWID
------------------
AAASUCAABAAAU9hAAN
AAASUCAABAAAU9hAAO
AAASUCAABAAAU9hAAP
AAASUCAABAAAU9hAAQ
Executed in 0.016 seconds
SQL> select * from t where rowid = 'aaasucaabaaau9haaq ';
OBJECT_ID VC
------------------------------------------------------------------------------------------
38 bytes
Executed in 0.016 seconds
In 99 rows of records, 86 Row-based links were generated.
4. Conclusion
The solution to Oracle Row Migration is to reconstruct the data table and reorganize the storage structure and Rowid. In the production environment, adding data columns with default values may cause a series of problems.
