Detailed steps for Oracle table fragmentation

Many data blocks in the HWL are non-data, but the data blocks in 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 dataUse the HR user to create table T1, insert about 30 W of data, and create common indexes based on object_id. The table occupies 34 MB of storage space.

Copy codeThe Code is as follows:
SQL> conn/as sysdba
Connected.
SQL> select default_tablespace from dba_users where username = 'hr ';

DEFAULT_TABLESPACE
------------------------------------------------------------
USERS

SQL> conn hr/hr
Connected.

SQL> insert into t1 select * from t1;
You have created 74812 rows.

SQL> insert into t1 select * from t1;
You have created 149624 rows.

SQL> commit;
Submitted.

SQL> create index idx_t1_id on t1 (object_id );
The index has been created.

SQL> exec dbms_stats.gather_table_stats ('hr', 't1', CASCADE => TRUE );
The PL/SQL process is successfully completed.

SQL> select count (1) from t1;

COUNT (1)
----------
299248

SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name = 't1 ';
SUM (BYTES)/1024/1024
--------------------
34.0625

SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name = 'idx _ T1_ID ';
SUM (BYTES)/1024/1024
--------------------
6

Ii. estimate how much space is available under a high-water level tableThe lower the value, the better. The closer the table usage is to the high waterpoint, 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.

Copy codeThe Code is as follows:
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 't1 ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
4302 0 299248

SQL> analyze table t1 compute statistics;
The table has been analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 't1 ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
4302 50 299248

SQL> col table_name for a20
SQL> SELECT TABLE_NAME,
2 (BLOCKS * 8192/1024/1024 )-
3 (NUM_ROWS * AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB"
4 FROM USER_TABLES
5 WHERE table_name = 't1 ';

TABLE_NAME Data lower than HWM in MB
---------------------------------------------
T1. 5.07086182

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

Copy codeThe Code is as follows:
SQL> explain plan for select * from t1;
Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 299K | 28M | 1175 (1) | 00:00:15 |
| 1 | table access full | T1 | 299K | 28M | 1175 (1) | 00:00:15 |
--------------------------------------------------------------------------

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

Copy codeThe Code is as follows:
SQL> delete from t1 where object_id> 100;
298852 rows have been deleted.

SQL> commit;
Submitted.

SQL> select count (*) from t1;

COUNT (*)
----------
396

SQL> exec dbms_stats.gather_table_stats ('hr', 't1', CASCADE => TRUE );
The PL/SQL process is successfully completed.

SQL> analyze table t1 compute statistics;
The table has been analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 't1 ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
4302 50 396

 
SQL> explain plan for select * from t1;
Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 396 | 29700 | 1168 (1) | 00:00:15 |
| 1 | table access full | T1 | 396 | 29700 | 1168 (1) | 00:00:15 |
--------------------------------------------------------------------------

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

Copy codeThe Code is as follows:
SQL> SELECT TABLE_NAME,
2 (BLOCKS * 8192/1024/1024 )-
3 (NUM_ROWS * AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB"
4 FROM USER_TABLES
5 WHERE table_name = 't1 ';

TABLE_NAME Data lower than HWM in MB
---------------------------------------------
T1. 33.5791626

Vi. defragmentation of tables and re-collect statistics

Copy codeThe Code is as follows:
SQL> alter table t1 enable row movement;
The table has been changed.

SQL> alter table t1 shrink space cascade;
The table has been changed.

SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name = 't1 ';

SUM (BYTES)/1024/1024
--------------------
. 125

SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name = 'idx _ T1_ID
';

SUM (BYTES)/1024/1024
--------------------
. 0625

SQL> SELECT TABLE_NAME,
2 (BLOCKS * 8192/1024/1024 )-
3 (NUM_ROWS * AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB"
4 FROM USER_TABLES
5 WHERE table_name = 't1 ';

TABLE_NAME Data lower than HWM in MB
---------------------------------------------
T1. 33.5791626

SQL> exec dbms_stats.gather_table_stats ('hr', 't1', CASCADE => TRUE );
The PL/SQL process is successfully completed.

At this time, only MB of useless work is left. In the execution plan, the full table scan only consumes three CPUs.
SQL> SELECT TABLE_NAME,
2 (BLOCKS * 8192/1024/1024 )-
3 (NUM_ROWS * AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB"
4 FROM USER_TABLES
5 WHERE table_name = 't1 ';

TABLE_NAME Data lower than HWM in MB
---------------------------------------------
T1. 010738373

 
SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 396 | 29700 | 3 (0) | 00:00:01 |
| 1 | table access full | T1 | 396 | 29700 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

There are only 5 blocks in total, but 50 are empty blocks. The empty_blocks information has expired.
SQL> select blocks, empty_blocks, num_rows from user_tables where table_name = 't1 ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
5 50 396

SQL> analyze table t1 compute statistics;
The table has been analyzed.

SQL> select blocks, empty_blocks, num_rows from user_tables where table_name = 't1 ';

 
BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
5 3 396