Many of the data blocks under the high watermark (HWL) are data-free, but the full table scans to high watermark data blocks, which means that Oracle does a lot of work! Therefore, Oracle provides shrink space defragmentation capabilities. For the index, you can take the rebuild online to defragment, in general, the object that often DML operations DBA to maintain regularly, while paying attention to update statistics in a timely manner!
One: Prepare test data , use HR user, create T1 table, insert about 30W of data, and create normal index according to object_id, table occupies 34M of storage space
Copy Code code as follows:
Sql> Conn/as SYSDBA
is connected.
Sql> Select Default_tablespace from dba_users where username= ' HR ';
Default_tablespace
------------------------------------------------------------
USERS
Sql> Conn Hr/hr
is connected.
sql> INSERT INTO T1 select * from T1;
74812 lines have been created.
sql> INSERT INTO T1 select * from T1;
149624 lines have been created.
Sql> commit;
Submit completed.
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 has completed successfully.
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
Second: The estimate table in the high water level, there is still more space available , this value should be lower the better, table usage is closer to the high watermark, the whole table scan did not work too much less!
The Dbms_stats package cannot get empty_blocks statistics, so you need to collect the statistics again with the Analyze command
Copy Code code 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 is parsed.
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
Three: View the execution plan, the whole table scan probably consumes CPU 1175
Copy Code code as follows:
Sql> explain plan for SELECT * from T1;
has been 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 |
--------------------------------------------------------------------------
Four: Delete Most of the data, collect statistics, full table scan still need to consume CPU 1168
Copy Code code as follows:
sql> Delete from T1 where object_id>100;
298852 rows have been deleted.
Sql> commit;
Submit completed.
Sql> Select COUNT (*) from T1;
COUNT (*)
----------
396
sql> exec dbms_stats.gather_table_stats (' HR ', ' T1 ', cascade=>true);
The PL/SQL process has completed successfully.
sql> Analyze table T1 compute statistics;
The table is parsed.
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;
has been 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 |
--------------------------------------------------------------------------
Five: Estimate the table at the high watermark, there is no data on how much space, but in the full table scan and need to do the hard data
Copy Code code 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
Six: Defragment the table, collect the statistics again
Copy Code code as follows:
Sql> ALTER TABLE T1 enable row movement;
The table has changed.
sql> ALTER TABLE T1 shrink space cascade;
The table has 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 has completed successfully.
At this time, there is only 0.1M of hard work, the implementation plan, full table scanning only need to consume CPU 3
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 there are 50 empty blocks, apparently empty_blocks information 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 is parsed.
Sql> Select Blocks,empty_blocks,num_rows from user_tables where table_name= ' T1 ';
BLOCKS Empty_blocks Num_rows
---------- ------------ ----------
5 3 396
