高水位線（HWL）下的許多資料區塊都是無資料的，但全表掃描的時候要掃描到高水位線的資料區塊，也就是說oracle要做許多的無用功！因此oracle提供了shrink space磁碟重組功能。對於索引，可以採取rebuild online的方式進行磁碟重組，一般來說，經常進行DML操作的對象DBA要定期進行維護，同時注意要及時更新統計資料！
一：準備測試資料，使用HR使用者，建立T1表，插入約30W的資料，並根據object_id建立普通索引，表占儲存空間34M
複製代碼 代碼如下:
SQL> conn /as sysdba
已串連。
SQL> select default_tablespace from dba_users where username='HR';
DEFAULT_TABLESPACE
------------------------------------------------------------
USERS
SQL> conn hr/hr
已串連。
SQL> insert into t1 select * from t1;
已建立 74812 行。
SQL> insert into t1 select * from t1;
已建立 149624 行。
SQL> commit;
提交完成。
SQL> create index idx_t1_id on t1(object_id);
索引已建立。
SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
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
二：估算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少!
DBMS_STATS包無法擷取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊
複製代碼 代碼如下:
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;
表已分析。
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
三: 查看執行計畫，全表掃描大概需要消耗CPU 1175
複製代碼 代碼如下:
SQL> explain plan for select * from t1;
已解釋。
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 |
--------------------------------------------------------------------------
四：刪除大部分資料，收集統計資訊，全表掃描依然需要消耗CPU 1168
複製代碼 代碼如下:
SQL> delete from t1 where object_id>100;
已刪除298852行。
SQL> commit;
提交完成。
SQL> select count(*) from t1;
COUNT(*)
----------
396
SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> analyze table t1 compute statistics;
表已分析。
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;
已解釋。
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 |
--------------------------------------------------------------------------
五：估算表在高水位線下還有多少空間是無資料的，但在全表掃描時又需要做無用功的資料
複製代碼 代碼如下:
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> alter table t1 enable row movement;
表已更改。
SQL> alter table t1 shrink space cascade;
表已更改。
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);
PL/SQL 過程已成功完成。
這個時候，只剩下0.1M的無用功了，執行計畫中，全表掃描也只需要消耗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 |
--------------------------------------------------------------------------
總共只有5個塊，空塊卻有50個，明顯empty_blocks資訊到期
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;
表已分析。
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
5 3 396
