Oracle table fragmentation causes and solutions

Basic knowledge related to table fragments:
What is waterline? (High water mark )?
----------------------------
All the Oracle segments (segments here, for ease of understanding, we recommend that you use segment as a synonym for the table) have an upper limit to accommodate data within the segments, we call this upper limit "high water mark" or hwm. This hwm is a tag used to indicate how many unused data blocks have been allocated to this segment. Hwm usually increases by five data blocks at a time. In principle, hwm only increases and does not shrink. Even if all the data in the table is deleted, hwm is the original value, this makes hwm very similar to the historical highest water level of a reservoir, which is the original meaning of hwm. Of course, it cannot be said that a reservoir has no water, but the historical highest water level of the reservoir is 0. However, if we use the truncate command on the table, the hwm of the table will be reset to 0.

Hwm database operations have the following effects:
A) Full table scan usually reads all the database blocks in the table until the hwm mark, even if the table does not have any data.
B) even if there are idle database blocks below hwm AND THE append keyword is used for data insertion, the data blocks above hwm are used for data insertion, And the hwm will automatically increase.

How do I know the hwm of a table?
A) analyze the table first:
Analyze table <tablename> estimate/compute statistics;
B) select blocks, empty_blocks, num_rows
From user_tables
Where table_name = <tablename>;

The blocks column indicates the number of database blocks used in the table, that is, the waterline.
Empty_blocks indicates the database block that is allocated to the table but above the waterline, that is, the data block that has never been used.

Let's take a big_emp1 table with 28672 rows as an example:
1) SQL> select segment_name, segment_type, blocks
From dba_segments
Where segment_name = 'Big _ emp1 ';
Segment_name segment_type blocks extents
---------------------------------------------------------------
Big_emp1 table 1024 2
1 row selected.

2) SQL> analyze table big_emp1 estimate statistics;
Statement processed.

3) SQL> select table_name, num_rows, blocks, empty_blocks
From user_tables
Where table_name = 'Big _ emp1 ';
Table_name num_rows blocks empty_blocks
--------------------------------------------------------------
Big_emp1 28672 700 323
1 row selected.

Note:
Blocks + empty_blocks (700 + 323 = 1023) has fewer database blocks than dba_segments.blocks because a database block is reserved as the segment header. Dba_segments.blocks indicates the number of all database blocks allocated to this table. User_tables.blocks indicates the number of used database blocks.

4) SQL> select count (distinct
Dbms_rowid.rowid_block_number (rowid) |
Dbms_rowid.rowid_relative_fno (rowid) "used"
From big_emp1;
Used
----------
700
1 row selected.

5) SQL> Delete from big_emp1;
28672 rows processed.

6) SQL> commit;
Statement processed.

7) SQL> analyze table big_emp1 estimate statistics;
Statement processed.

8) SQL> select table_name, num_rows, blocks, empty_blocks
From user_tables
Where table_name = 'Big _ emp1 ';
Table_name num_rows blocks empty_blocks
--------------------------------------------------------------
Big_emp1 0 700 323
1 row selected.

9) SQL> select count (distinct
Dbms_rowid.rowid_block_number (rowid) |
Dbms_rowid.rowid_relative_fno (rowid) "used"
From big_emp1;
Used
----------
0 -- the table name does not contain any database block to accommodate data, that is, the table does not have data.
1 row selected.

10) SQL> truncate table big_emp1;
Statement processed.

11) SQL> analyze table big_emp1 estimate statistics;
Statement processed.

12) SQL> select table_name, num_rows, blocks, empty_blocks
2> from user_tables
3> where table_name = 'Big _ emp1 ';
Table_name num_rows blocks empty_blocks
--------------------------------------------------------------
Big_emp1 0 0 511
1 row selected.

13) SQL> select segment_name, segment_type, blocks
From dba_segments
Where segment_name = 'Big _ emp1 ';
Segment_name segment_type blocks extents
---------------------------------------------------------------
Big_emp1 table 512 1
1 row selected.

Note:
The truncate command recycles the free space generated by the DELETE command. Note that the space allocated to the table is reduced from the original 1024 blocks to 512 blocks.
To retain the free space generated by the DELETE command, you can use
Truncate table big_emp1 reuse Storage
After using this command, the table will still be the first 1024 blocks.Row chaining and row migration)
When a row of data is too long to be inserted into a single data block, two possible events may occur: Row chaining or row migration ).

Line Link
When the row is inserted for the first time, because the row is too long to be contained in a data block, a row link occurs. In this case, Oracle uses one or more data blocks linked to the block to accommodate the data of the row. Row connections often occur when large rows are inserted, such as long, long row, and LOB data. In these cases, downstream links are inevitable.

Row migration
When the modified row is not a row link, when the modified row length is greater than the length of the row before the modification, and the free space in the data block is small, it cannot fully accommodate the data of the row, row migration will occur. In this case, Oracle will migrate the entire row of data to a new data block, and put only one pointer to the original space of the row, pointing to the new location of the row, in addition, the remaining space of the row is no longer used by the database. We call the remaining space empty. This is the main reason for table fragmentation. Table fragmentation is also inevitable, but we can reduce it to an acceptable level. Note: even if a row migration occurs, the rowid of the row to be migrated remains unchanged. This is also the reason why the row migration will cause database I/O performance degradation. In fact, row migration is a special form of row link, but its cause and behavior are very different from that of the row link. Therefore, it is generally independent from the row link and processed separately.

Reasons for database performance degradation caused by row link and row migration:
The main cause of performance degradation is the excess I/O. When accessing existing row migration through an index, the database must scan more than one data block to retrieve the row-changing data. There are two forms:
1) cause row migration or row chaining insert or update statements to have poor performance, because they need to execute additional processing.
2) The performance of select statements that use indexes to query connected or migrated rows is poor because they need to execute additional I/O

How can I detect row migration and row link:
The migrated or linked rows in the table can be identified by the analyze statement with the list chained rows option. This command collects the information of each row to be migrated or linked and places the information in the specified output table. To create this output table, run the script utlchain. SQL.

SQL> analyze table Scott. EMP list chained rows;
SQL> select * From chained_rows;

Of course, you can also check the migrated or linked rows by checking the 'table fetch continued row' in the V $ sysstat view.

SQL> select name, value from V $ sysstat where name = 'table fetch continued row ';
NAME value
-------------------------------------------------------------------------
Table fetch continued row 308

Although row migration and row link are two different things, they are considered the same thing in Oracle. Therefore, when detecting row migration and row link, you should carefully analyze whether row migration or row link is currently being processed.

Solution
O in most cases, row links cannot be overcome, especially when a table contains columns such as longs and lobs. When there are a large number of linked rows in different tables and the row length of which tables is not very long, you can use a larger block size to reconstruct the database.

For example, the data block size of your database is 4 K, but the average length of your row is 6 K, then you can use 8 K data blocks to reconstruct the database to solve the row link problem.

Row O migration is mainly caused by the small pctfree parameter, which does not leave sufficient free space for the update operation. To avoid row migration, appropriate pctfree values should be set for all modified tables to reserve sufficient space for data modification within each data block. We can avoid row migration by adding the pctfree value, but this solution is at the cost of sacrificing more space, which is what we usually call space-for-efficiency. In addition, the increase of the pctfree value can only alleviate the phenomenon of row migration, but cannot completely solve the row migration problem. Therefore, a better solution is to set the appropriate pctfree value, if row migration is serious, reorganize the table data.
The following is the procedure for reorganizing row migration data (this method has also become CTAs ):
-- Get the name of the table with migrated rows:
Accept table_name prompt 'Enter the name of the table with migrated rows :'

-- Clean up from last execution
Set echo off
Drop table migrated_rows;
Drop table chained_rows;

-- Create the chained_rows table
@.../Rdbms/admin/utlchain. SQL
Set echo on
Spool fix_mig
-- List the chained and migrated rows
Analyze table & table_name list chained rows;

-- Copy the chained/migrated rows to another table
Create Table migrated_rows
Select orig .*
From & table_name orig, chained_rows cr
Where orig. rowid = Cr. head_rowid
And Cr. table_name = upper ('& table_name ');

-- Delete the chained/migrated rows from the original table
Delete from & table_name where rowid in (select head_rowid from chained_rows );

-- Copy the chained/migrated rows back into the original table
Insert into & table_name select * From migrated_rows;

Spool off

When a full table scan is performed on a table, we actually ignore the pointers pointing to other rows in the row migration, because we know that the full table scan will traverse the whole table, the row data of the row to be migrated will be read, and the row data will be processed at this time. Therefore, row migration does not cause additional work during full table scan.
When reading data from a table through an index, the migrated rows cause additional I/O operations. This is because we will read the rowid of the Data row from the reference, which tells the database to find the required data on the specified slot of the specified data block of the specified file, but because of row migration, only one pointer pointing to the data is stored here, rather than the real data. Therefore, the database needs to follow this pointer (similar to rowid) go to the specified slot of the specified data block of the specified file to find the real data. Repeat the above process to find the real data. We can see that this will introduce additional I/O operations.Steps for discovering a table with severe table fragments:
There are two reasons for table arrangement:
A) Too many migration rows
B) There are a large number of empty blocks after the table is deleted. During full table scan, you still need to read these empty blocks.

To find a table that requires reorganization, you must start with the actual space used by the table and the hwm of the table.

First analysis table:
Alter table emp compute statistics.

Then we can query the number of data blocks with data:
For oracle 7:
Select count (distinct substr (rowid, 15, 4) |
Substr (rowid, 1, 8) "used"
From schema. Table;

For Oracle 8 +:
Select count (distinct
Dbms_rowid.rowid_block_number (rowid) |
Dbms_rowid.rowid_relative_fno (rowid) "used"
From schema. Table;
Or
Select count (distinct substr (rowid, 1, 15) "used"
From schema. Table;

Query the number of data blocks below hwm (data blocks may not contain data due to delete ):
This will update the table statistics. After generating the statistics, to determine the high water mark:
Select blocks, empty_blocks, num_rows
From user_tables
Where table_name = <tablename>;

The following is a comprehensive SQL statement, It can query tables with a waste of space (waste of more than 25%), and calculate other information (modify the blocks in the WHERE clause as needed, owner restrictions ):
Select owner, segment_name table_name, segment_type,
Greatest (round (100 * (nvl (hwm-avg_used_blocks, 0)/greatest (nvl (hwm, 1), 1), 2), 0) waste_per,
Round (Bytes/1024, 2) table_kb, num_rows,
Blocks, empty_blocks, hwm highwater_mark, avg_used_blocks,
Chain_per, extents, max_extents, allo_extent_per,
Decode (greatest (max_free_space-next_extent, 0), 0, 'n', 'y') can_extend_space,
Next_extent, max_free_space,
O_tablespace_name tablespace_name
From
(Select a. Owner owner, A. segment_name, A. segment_type, A. bytes,
B. num_rows, A. Blocks blocks, B. empty_blocks,
A. blocks-B. empty_blocks-1 hwm,
Decode (round (B. avg_row_len * num_rows * (1 + (pct_free/100)/C. blocksize, 0 ),
0, 1,
Round (B. avg_row_len * num_rows * (1 + (pct_free/100)/C. blocksize, 0)
) + 2 avg_used_blocks,
Round (100 * (nvl (B. chain_cnt, 0)/greatest (nvl (B. num_rows, 1), 1), 2) chain_per,
Round (100 * (A. extents/a. max_extents), 2) allo_extent_per, A. extents,
A. max_extents, B. next_extent, B. tablespace_name o_tablespace_name
From SYS. dba_segments,
SYS. dba_tables B,
SYS. Ts $ C
Where a. Owner = B. Owner and
Segment_name = table_name and
Segment_type = 'table' and
B. tablespace_name = C. Name
Union all
Select a. Owner owner, segment_name | '.' | B. partition_name, segment_type, bytes,
B. num_rows, A. Blocks blocks, B. empty_blocks,
A. blocks-B. empty_blocks-1 hwm,
Decode (round (B. avg_row_len * B. num_rows * (1 + (B. pct_free/100)/C. blocksize, 0 ),
0, 1,
Round (B. avg_row_len * B. num_rows * (1 + (B. pct_free/100)/C. blocksize, 0)
) + 2 avg_used_blocks,
Round (100 * (nvl (B. chain_cnt, 0)/greatest (nvl (B. num_rows, 1), 1), 2) chain_per,
Round (100 * (A. extents/a. max_extents), 2) allo_extent_per, A. extents,
A. max_extents, B. next_extent,
B. tablespace_name o_tablespace_name
From SYS. dba_segments,
SYS. dba_tab_partitions B,
SYS. Ts $ C,
SYS. dba_tables d
Where a. Owner = B. table_owner and
Segment_name = B. table_name and
Segment_type = 'table partition' and
B. tablespace_name = C. Name and
D. Owner = B. table_owner and
D. table_name = B. table_name and
A. partition_name = B. partition_name ),
(Select tablespace_name f_tablespace_name, max (bytes)
Max_free_space
From SYS. dba_free_space
Group by tablespace_name)
Where f_tablespace_name = o_tablespace_name and
Greatest (round (100 * (nvl (hwm-avg_used_blocks, 0)/greatest (nvl (hwm, 1), 1), 2), 0)> 25
And owner = '?? 'And blocks> 128
Order by 10 DESC, 1 ASC, 2 ASC;

Description of each column:
Waste_per: Percentage of idle space (that is, waste space) below the waterline in the allocated space.
Table_kb: The size of all space allocated to the table, in K.
Num_rows: number of rows in the table
Blocks: number of data blocks allocated to the table, including the parts above the waterline
Empty_blocks: idle space above the water line in the allocated space
Highwater_mark: Current waterline
Avg_used_blocks: the number of data blocks that the table data should occupy in an ideal case (no row migration is performed ).
Chain_per: Ratio of rows to the total row in case of row migration
Extents: Number of extent instances allocated to the table
Max_extents: Maximum number of extents that can be allocated to the table
Allo_extent_per: percentage of the number of currently allocated extent instances to the largest extent available for allocation
Can_extend_space: Specifies whether the next extent can be allocated.
Next_extent: the size of the next extent
Max_free_space: Maximum free space in the allocated space of the table