Oracle table fragmentation

During the routine use of databases, it is inevitable that the insert, delete, and update operations occur constantly, resulting in fragmentation of tables and indexes. When there are more fragments, the SQL Execution efficiency will naturally be poor, the reason is simple, high water level

During the routine use of databases, it is inevitable that the insert, delete, and update operations occur constantly, resulting in fragmentation of tables and indexes. When there are more fragments, the SQL Execution efficiency will naturally be poor, the reason is simple, high water level

During the routine use of databases, it is inevitable that the insert, delete, and update operations occur constantly, resulting in fragmentation of tables and indexes. When there are more fragments, the SQL Execution efficiency will naturally be poor, the reason for the Hong Kong virtual host is very simple. Many data blocks under the HWL are non-data, but the data blocks of the high-water line need to be scanned during full table scanning, that is to say, oracle has to do a lot of useless work! Therefore, oracle provides the shrink space fragment function. For indexes, rebuild online can be used for fragment. Generally, DBAs that frequently perform DML operations should be regularly maintained, and statistics should be updated in a timely manner!

I. Prepare test data, free of Record Filing space, and a Hong Kong VM. Use HR users to create table T1, insert about 30 W of data, and create common indexes based on object_id, tables occupy about 34 MB of storage space, and indexes occupy about 6 MB of storage space.

Ii. estimate how much space is available in the table under the high water level. The lower the value, the better. The closer the table usage is to the high water level. The less useless the full table scan will be!

The DBMS_STATS package cannot obtain the statistics of EMPTY_BLOCKS. Therefore, you need to use the analyze command to collect statistics again.

Iii. view the execution plan. A full table scan consumes about 1175 of the CPU.

4. delete most of the data and collect statistics. The full table scan still consumes 1168 of the CPU.

5. estimate the amount of space in the table under high water level without data, but useless data is required during full table scan.

Vi. defragmentation of tables and re-collect statistics

Refer:

For details about how to determine which tables need to be split, you can use the script in the attachment to query. For details, refer:

This article is from the "Bo Yue" blog and will not be reproduced!