標籤：
資料庫在日常使用過程中，不斷的insert，delete，update操作，導致表和索引出現片段是在所難免的事情，片段多了，sql的執行效率自然就差了，道理很簡單，高水位線（HWL）下的許多資料區塊都是無資料的，但全表掃描的時候要掃描到高水位線的資料區塊，也就是說oracle要做許多的無用功！因此oracle提供了shrink space磁碟重組功能。對於索引，可以採取rebuild online的方式進行磁碟重組，一般來說，經常進行DML操作的對象DBA要定期進行維護，同時注意要及時更新統計資料！
一：準備測試資料，使用HR使用者，建立T1表，插入約30W的資料，並根據object_id建立普通索引，表占儲存空間34M左右，索引佔6M左右的儲存空間
- 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
參考：http://surachartopun.com/2011/08/determine-hwm-and-reduce-it-by-shrink.html
關於如何確定哪些表需要進行磁碟重組，可以使用附件中的指令碼去查詢，具體請參考：http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTNN18/Default.aspx
關於Oracle表磁碟重組
