About Oracle Table defragmentation

Database in the daily use of the process, continuous insert,delete,update operation, resulting in the table and index fragmentation is inevitable, fragmentation is more, SQL execution efficiency naturally poor, the reason is very simple, high watermark (HWL) Many data blocks are no data, But the full table scan to scan to the high watermark data block, that is to say Oracle to do a lot of useless! As a result, Oracle provides shrink space defragmentation capabilities. For indexes, you can take rebuild online defragmentation, in general, the objects that are frequently DML operations are regularly maintained by DBAs, while keeping in mind that statistics are updated in a timely manner!

One: Prepare test data, use HR user, create T1 table, insert approximately 30W of data, and create normal index according to object_id, table occupies 34M of storage space, index occupies about 6M storage space

2. SQL> Conn/as sysdba
4. is connected.
6. SQL> select Default_tablespace from dba_users where username=' HR ';
10. Default_tablespace
12. ------------------------------------------------------------
14. USERS
18. SQL> Conn hr/hr
20. is connected.
24. SQL> INSERT INTO T1 select * from T1;
26. 74812 rows have been created.
30. SQL> INSERT INTO T1 select * from T1;
32. 149624 rows have been created.
36. SQL> commit;
38. Submit complete.
42. SQL> CREATE index idx_t1_id on T1 (object_id);
44. The index has been created.
48. SQL> Exec dbms_stats.gather_table_stats (' HR ', ' T1 ',cascade=>true);
50. The PL/SQL process has completed successfully.
54. SQL> select COUNT (1) from T1;
58. COUNT (1)
60. ----------
62. 299248
66. SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name=' T1 ';
68. SUM (BYTES)/1024/1024
70. --------------------
72. 34.0625
76. SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name=' idx_t1_id ';
78. SUM (BYTES)/1024/1024
80. --------------------
82. 6

Second: Estimate how much space is available under the high watermark, the lower the better, the higher the table usage is to the high watermark, the less work done by the full table scan!

Dbms_stats package cannot get empty_blocks statistics, so you need to collect statistics with analyze command again

2. SQL> SELECT blocks, Empty_blocks, num_rows from user_tables WHERE table_name =' T1 ';
6. BLOCKS Empty_blocks Num_rows
8. ---------- ------------ ----------
10. 4302 0 299248
14. SQL> Analyze table T1 compute statistics;
16. Table has been analyzed.
20. SQL> SELECT blocks, Empty_blocks, num_rows from user_tables WHERE table_name =' T1 ';
24. BLOCKS Empty_blocks Num_rows
26. ---------- ------------ ----------
28. 4302 50 299248
32. SQL> Col table_name for A20
34. SQL> SELECT table_name,
36. 2 (BLOCKS * 8192/1024/1024)-
38. 3 (Num_rows * avg_row_len/1024/1024) "Data lower than HWM in MB"
40. 4 from User_tables
42. 5 WHERE table_name = ' T1 ';
46. TABLE_NAME Data lower than HWM in MB
48. -------------------- -------------------------
50. T1 5.07086182

Three: View execution plan, full table scan probably consumes CPU 1175

2. SQL> Explain plan for select * from T1;
4. has been explained.
8. SQL> select * from table (dbms_xplan.display);
12. Plan_table_output
14. --------------------------------------------------------------------------------
16. Plan Hash value:3617692013
18. --------------------------------------------------------------------------
20. | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
22. --------------------------------------------------------------------------
24. | 0 |      SELECT STATEMENT |   |    299k|  28m| 1175 (1) | 00:00:15 |
26. |  1 | TABLE ACCESS full|   T1 |    299k|  28m| 1175 (1) | 00:00:15 |
28. --------------------------------------------------------------------------

Four: Delete Most of the data, collect statistics, full table scan still need to consume CPU 1168

2. SQL> Delete from t1 where object_id>100;
4. 298852 rows have been deleted.
8. SQL> commit;
10. Submit complete.
14. SQL> select COUNT (*) from T1;
18. COUNT (*)
20. ----------
22. 396
26. SQL> Exec dbms_stats.gather_table_stats (' HR ', ' T1 ',cascade=>true);
28. The PL/SQL process has completed successfully.
32. SQL> Analyze table T1 compute statistics;
34. Table has been analyzed.
38. SQL> SELECT blocks, Empty_blocks, num_rows from user_tables WHERE table_name =' T1 ';
42. BLOCKS Empty_blocks Num_rows
44. ---------- ------------ ----------
46. 4302 50 396
52. SQL> Explain plan for select * from T1;
54. has been explained.
58. SQL> select * from table (dbms_xplan.display);
62. Plan_table_output
64. ------------------------------------------------------------------------------
66. Plan Hash value:3617692013
68. --------------------------------------------------------------------------
70. | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
72. --------------------------------------------------------------------------
74. | 0 |      SELECT STATEMENT |   | 396 |  29700 | 1168 (1) | 00:00:15 |
76. |  1 | TABLE ACCESS full|   T1 | 396 |  29700 | 1168 (1) | 00:00:15 |
78. --------------------------------------------------------------------------

Five: Estimate how much space is not available under the high watermark, but it also needs to be hard-working data for full-table scanning

2. SQL> SELECT table_name,
4. 2 (BLOCKS * 8192/1024/1024)-
6. 3 (Num_rows * avg_row_len/1024/1024) "Data lower than HWM in MB"
8. 4 from User_tables
10. 5 WHERE table_name = ' T1 ';
14. TABLE_NAME Data lower than HWM in MB
16. -------------------- -------------------------
18. T1 33.5791626

VI: Defragment the table and collect statistics again

2. SQL> ALTER TABLE T1 enable row movement;
4. The table has changed.
8. SQL> ALTER TABLE T1 shrink space cascade;
10. The table has changed.
14. SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name=' T1 ';
18. SUM (BYTES)/1024/1024
20. --------------------
22. .125
26. SQL> select sum (bytes)/1024/1024 from dba_segments where segment_name= ' idx_t1_id
28. ‘;
32. SUM (BYTES)/1024/1024
34. --------------------
36. .0625
40. SQL> SELECT table_name,
42. 2 (BLOCKS * 8192/1024/1024)-
44. 3 (Num_rows * avg_row_len/1024/1024) "Data lower than HWM in MB"
46. 4 from User_tables
48. 5 WHERE table_name = ' T1 ';
52. TABLE_NAME Data lower than HWM in MB
54. -------------------- -------------------------
56. T1 33.5791626
60. SQL> Exec dbms_stats.gather_table_stats (' HR ', ' T1 ',cascade=>true);
62. The PL/SQL process has completed successfully.
66. At this time, only 0.1M of useless work, the execution plan, full table scan also only need to consume CPU 3
68. SQL> SELECT table_name,
70. 2 (BLOCKS * 8192/1024/1024)-
72. 3 (Num_rows * avg_row_len/1024/1024) "Data lower than HWM in MB"
74. 4 from User_tables
76. 5 WHERE table_name = ' T1 ';
80. TABLE_NAME Data lower than HWM in MB
82. -------------------- -------------------------
84. T1.010738373
90. SQL> select * from table (dbms_xplan.display);
94. Plan_table_output
96. --------------------------------------------------------------------------------
98. Plan Hash value:3617692013
100. --------------------------------------------------------------------------
102. | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
104. --------------------------------------------------------------------------
106. | 0 |      SELECT STATEMENT |   | 396 |     29700 | 3 (0) | 00:00:01 |
108. |  1 | TABLE ACCESS full|   T1 | 396 |     29700 | 3 (0) | 00:00:01 |
110. --------------------------------------------------------------------------
114. A total of only 5 blocks, there are 50 empty blocks, obviously empty_blocks information expires
116. SQL> select blocks,empty_blocks,num_rows from User_tables where table_name=' T1 ';
120. BLOCKS Empty_blocks Num_rows
122. ---------- ------------ ----------
124. 5 50 396
128. SQL> Analyze table T1 compute statistics;
130. Table has been analyzed.
134. SQL> select blocks,empty_blocks,num_rows from User_tables where table_name=' T1 ';
140. BLOCKS Empty_blocks Num_rows
142. ---------- ------------ ----------
144. 5 3 396

Reference: http://surachartopun.com/2011/08/determine-hwm-and-reduce-it-by-shrink.html

For information on how to determine which tables need to be defragmented, you can use the scripts in the attachments to query them, please refer to: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/ Topicid/otnn18/default.aspx

About Oracle Table defragmentation