oracle表碎片整理
資料庫在日常使用過程中，不斷的insert，delete，update操作，導致表和索引出現碎片是在所難免的事情，碎片多了，sql的執行效率自然就差了，道理很簡單，高水位線（HWL）下的許多資料塊都是無資料的，但全表掃描的時候要掃描到高水位線的資料塊!
表的碎片和檔案系統的碎片化的是不一樣的,當隨著在一個表上的DML的上操作越來越多時,HWM之前可能有很多空閒空間,而在讀取表時HWM以下的塊都會被讀進來,這樣會產生更多的IO,從而影響效能.只有在DDL操作才會進表的收縮.
對錶進行碎片整理，碎片整理方法有:1,使用alter MOVE 表，然後索引rebuild;2.使用alter table enable row movement;然後alter table shrink space cascade(shrink使用有限制，需注意)；3,透過 create table XXX as select * from abb; 4,使用匯出和匯入表 ;
實驗如下：
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
86956
SQL> insert into t1 select * from t1;
86956 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create index idx_t1_id on t1(object_id);
Index created.
--先查詢表大小及統計資訊：
SQL> set lines 200
SQL> COL TABLE_NAME FOR A15
SQL> COL TABLESPACE_NAME FOR A15
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM
SQL> COL SEGMENT_NAME FOR A15
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- ------------------------------ ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
可以看到dba_segment中已經可以記錄表大小，而dba_tables則沒有。
--使用dbms_stats手機統計資訊
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 173912 2476 0 0 98 2017-10-26 05:35:37
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_table中已有記錄BLOCKS塊大小記錄，但是沒有empty_blocks空塊記錄和AVG_SPACE值。
--需要使用analyze子句收集表t1的空塊資訊。
DBMS_STATS包無法獲取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 173912 2476 83 863 101 2017-10-26 05:38:18
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_tables中的 EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN欄位有值了，且AVG_ROW_LEN的值發生了變化。
--計算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少，如下：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 2.59235382
檢視執行計劃，全表掃描大概需要消耗CPU 675
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 | | 173K| 16M| 675 (1)| 00:00:09 |
| 1 | TABLE ACCESS FULL| T1 | 173K| 16M| 675 (1)| 00:00:09 |
--------------------------------------------------------------------------
8 rows selected.
--刪除大部分資料，收集統計資訊，全表掃描依然耗cpu 673，如下：
SQL> select count(*) from t1;
COUNT(*)
----------
173912
SQL> delete t1 where rownum <170000;
169999 rows deleted.
SQL> select count(*) from t1;
COUNT(*)
----------
3913
--使用dbms_stats分析表
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
--再次查詢dba_segments和dba_tables檢視
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 863 101 2017-10-26 05:50:29
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_tables中的num_rows欄位已經更新了，其他欄位沒有更新；而dba_segments檢視相關欄位也沒有變化。這說明DML操作的刪除行操作，即使進行了統計資訊的更新，但是因為表裡存在碎片，所以表大小沒有變化。
--使用analyze分析表：
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
還是沒有變化，結論如上。。。。。。。。。。。。。。。。。
--檢視執行計劃，cpu cost 673幾乎沒變化
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 | | 3913 | 397K| 673 (1)| 00:00:09 |
| 1 | TABLE ACCESS FULL| T1 | 3913 | 397K| 673 (1)| 00:00:09 |
--------------------------------------------------------------------------
8 rows selected.
--再次估算表在高水位線下還有多少空間是無資料的，但在全表掃描時又需要做無用功的資料塊，如下：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 18.9556503
發現表中碎片增長很多。。。。。。。。。。。。
--對錶進行碎片整理，重新收集統計資訊，如下：
注：碎片整理方法有:1,使用alter MOVE 表，然後索引rebuild;2.使用alter table enable row movement;然後alter table shrink space cascade(shrink使用有限制，需注意)；3,透過 create table XXX as select * from abb; 4,使用匯出和匯入表 ;
SQL> alter table t1 disable row movement;
Table altered.
SQL> alter table t1 move;
Table altered.
SQL> select INDEX_NAME,STATUS from dba_indexes where index_name ='IDX_T1_ID';
INDEX_NAME STATUS
------------------------------ --------
IDX_T1_ID UNUSABLE
SQL> alter index IDX_T1_ID rebuild online;
Index altered.
--先查詢dba_tables/dba_segments：
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
發現經過碎片整理後且在沒有收集統計資訊的情況下dba_segments的塊大小已經自動更新了，而dba_tables各欄位沒有更新
--再次查詢碎片情況：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 18.9556503
沒有變化。
--收集統計資訊，使用dbms_stat包：
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 58 83 7761 101 2017-10-26 06:07:17
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 .076220512
發現經過dbms_stat包收集統計資訊後dba_tables的blocks、AVG_ROW_LEN欄位已經更新，且高水位下的碎片已經回收了，但是EMPTY_BLOCKS、AVG_SPACE欄位沒有更新
--使用analyze子句收集EMPTY_BLOCKS欄位統計資訊，如下；
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 58 5 887 104 2017-10-26 06:10:06
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 .06502533
發現經過analyze子句收集統計資訊後dba_tables的EMPTY_BLOCKS、AVG_SPACE欄位更新了
--再次執行sql，發現CPU cost只有17，如下：
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 | | 3913 | 397K| 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3913 | 397K| 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
實驗結束！！！！！！！
表的碎片和檔案系統的碎片化的是不一樣的,當隨著在一個表上的DML的上操作越來越多時,HWM之前可能有很多空閒空間,而在讀取表時HWM以下的塊都會被讀進來,這樣會產生更多的IO,從而影響效能.只有在DDL操作才會進表的收縮.
對錶進行碎片整理，碎片整理方法有:1,使用alter MOVE 表，然後索引rebuild;2.使用alter table enable row movement;然後alter table shrink space cascade(shrink使用有限制，需注意)；3,透過 create table XXX as select * from abb; 4,使用匯出和匯入表 ;
實驗如下：
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
86956
SQL> insert into t1 select * from t1;
86956 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create index idx_t1_id on t1(object_id);
Index created.
--先查詢表大小及統計資訊：
SQL> set lines 200
SQL> COL TABLE_NAME FOR A15
SQL> COL TABLESPACE_NAME FOR A15
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM
SQL> COL SEGMENT_NAME FOR A15
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- ------------------------------ ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
可以看到dba_segment中已經可以記錄表大小，而dba_tables則沒有。
--使用dbms_stats手機統計資訊
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 173912 2476 0 0 98 2017-10-26 05:35:37
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_table中已有記錄BLOCKS塊大小記錄，但是沒有empty_blocks空塊記錄和AVG_SPACE值。
--需要使用analyze子句收集表t1的空塊資訊。
DBMS_STATS包無法獲取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 173912 2476 83 863 101 2017-10-26 05:38:18
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_tables中的 EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN欄位有值了，且AVG_ROW_LEN的值發生了變化。
--計算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少，如下：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 2.59235382
檢視執行計劃，全表掃描大概需要消耗CPU 675
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 | | 173K| 16M| 675 (1)| 00:00:09 |
| 1 | TABLE ACCESS FULL| T1 | 173K| 16M| 675 (1)| 00:00:09 |
--------------------------------------------------------------------------
8 rows selected.
--刪除大部分資料，收集統計資訊，全表掃描依然耗cpu 673，如下：
SQL> select count(*) from t1;
COUNT(*)
----------
173912
SQL> delete t1 where rownum <170000;
169999 rows deleted.
SQL> select count(*) from t1;
COUNT(*)
----------
3913
--使用dbms_stats分析表
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
--再次查詢dba_segments和dba_tables檢視
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 863 101 2017-10-26 05:50:29
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
發現dba_tables中的num_rows欄位已經更新了，其他欄位沒有更新；而dba_segments檢視相關欄位也沒有變化。這說明DML操作的刪除行操作，即使進行了統計資訊的更新，但是因為表裡存在碎片，所以表大小沒有變化。
--使用analyze分析表：
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 20971520 2560 35
SYS IDX_T1_ID SYSTEM 4194304 512 19
還是沒有變化，結論如上。。。。。。。。。。。。。。。。。
--檢視執行計劃，cpu cost 673幾乎沒變化
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 | | 3913 | 397K| 673 (1)| 00:00:09 |
| 1 | TABLE ACCESS FULL| T1 | 3913 | 397K| 673 (1)| 00:00:09 |
--------------------------------------------------------------------------
8 rows selected.
--再次估算表在高水位線下還有多少空間是無資料的，但在全表掃描時又需要做無用功的資料塊，如下：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 18.9556503
發現表中碎片增長很多。。。。。。。。。。。。
--對錶進行碎片整理，重新收集統計資訊，如下：
注：碎片整理方法有:1,使用alter MOVE 表，然後索引rebuild;2.使用alter table enable row movement;然後alter table shrink space cascade(shrink使用有限制，需注意)；3,透過 create table XXX as select * from abb; 4,使用匯出和匯入表 ;
SQL> alter table t1 disable row movement;
Table altered.
SQL> alter table t1 move;
Table altered.
SQL> select INDEX_NAME,STATUS from dba_indexes where index_name ='IDX_T1_ID';
INDEX_NAME STATUS
------------------------------ --------
IDX_T1_ID UNUSABLE
SQL> alter index IDX_T1_ID rebuild online;
Index altered.
--先查詢dba_tables/dba_segments：
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
發現經過碎片整理後且在沒有收集統計資訊的情況下dba_segments的塊大小已經自動更新了，而dba_tables各欄位沒有更新
--再次查詢碎片情況：
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 18.9556503
沒有變化。
--收集統計資訊，使用dbms_stat包：
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 58 83 7761 101 2017-10-26 06:07:17
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 .076220512
發現經過dbms_stat包收集統計資訊後dba_tables的blocks、AVG_ROW_LEN欄位已經更新，且高水位下的碎片已經回收了，但是EMPTY_BLOCKS、AVG_SPACE欄位沒有更新
--使用analyze子句收集EMPTY_BLOCKS欄位統計資訊，如下；
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
------------------------------ --------------- --------------- ---------- ---------- ------------ ---------- ----------- -------------------
SYS T1 SYSTEM 3913 58 5 887 104 2017-10-26 06:10:06
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1','IDX_T1_ID');
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ --------------- --------------- ---------- ---------- ----------
SYS T1 SYSTEM 524288 64 8
SYS IDX_T1_ID SYSTEM 131072 16 2
SQL> select table_name, (blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name = 'T1';
TABLE_NAME data lower than hwm in mb
--------------- -------------------------
T1 .06502533
發現經過analyze子句收集統計資訊後dba_tables的EMPTY_BLOCKS、AVG_SPACE欄位更新了
--再次執行sql，發現CPU cost只有17，如下：
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 | | 3913 | 397K| 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3913 | 397K| 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
實驗結束！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2146496/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料表碎片整理Oracle
- Oracle資料庫表碎片整理Oracle資料庫
- ORACLE表空間的碎片整理Oracle
- Oracle 資料庫整理表碎片Oracle資料庫
- oracle表碎片以及整理（高水位線）Oracle
- oracle 碎片整理Oracle
- MySQL表碎片整理MySql
- Oracle 整理表碎片、釋放表的空間Oracle
- oracle碎片整理方法Oracle
- Oracle 表碎片Oracle
- ORACLE碎片整理一(轉載)Oracle
- ORACLE碎片整理二(轉載)Oracle
- Oracle 資料庫碎片整理Oracle資料庫
- Oracle資料庫碎片整理Oracle資料庫
- Oracle 資料庫碎片整理(zt)Oracle資料庫
- Oracle資料庫碎片整理（轉）Oracle資料庫
- MySQL的 data_free，表碎片整理MySql
- MysqL碎片整理優化MySql優化
- Sqlserver的表沒有高水位但是有碎片的概念，sqlserver表的碎片的檢視和整理方法SQLServer
- MySQL關於表碎片整理OPTIMIZE TABLE操作的官方建議MySql
- oracle 表碎片太多的處理辦法Oracle
- Oracle表碎片起因及解決辦法Oracle
- mysql之 OPTIMIZE TABLE整理碎片MySql
- windows10系統磁碟碎片整理在哪 win10如何開啟磁碟碎片整理WindowsWin10
- oracle碎片清理Oracle
- 驗證Oracle 10g線上整理碎片索引是否失效過程Oracle 10g索引
- oracle表空間的整理Oracle
- 硬碟加速和磁碟碎片整理軟體硬碟
- MySQL碎片整理小節--例項演示MySql
- MySQL資料碎片的整理和分析MySql
- 回收mysql表的碎片MySql
- ORACLE碎片問題Oracle
- Oracle 20c 新特性：Online SecureFiles Defragmentation 線上的 LOB 碎片整理OracleFragment
- MySQL的表碎片處理MySql
- 表空間碎片檢測
- ORACLE常見檢視和表整理Oracle
- 對oracle分割槽表的理解整理Oracle
- ORACLE_系統字典常用表整理Oracle