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資料庫
- MySQL表碎片整理MySql
- 索引碎片整理索引
- oracle表空間的整理Oracle
- Sqlserver的表沒有高水位但是有碎片的概念，sqlserver表的碎片的檢視和整理方法SQLServer
- mysql之 OPTIMIZE TABLE整理碎片MySql
- 驗證Oracle 10g線上整理碎片索引是否失效過程Oracle 10g索引
- Oracle 20c 新特性：Online SecureFiles Defragmentation 線上的 LOB 碎片整理OracleFragment
- windows10系統磁碟碎片整理在哪 win10如何開啟磁碟碎片整理WindowsWin10
- 對oracle分割槽表的理解整理Oracle
- ORACLE常見檢視和表整理Oracle
- 硬碟加速和磁碟碎片整理軟體硬碟
- MySQL碎片整理小節--例項演示MySql
- MySQL的表碎片處理MySql
- win10系統無法整理磁碟碎片提示已由其它程式建立了計劃磁碟碎片整理的解決方法Win10
- java/android 做題中整理的碎片小貼士(4)JavaAndroid
- oracle資料庫檢視鎖表的sql語句整理Oracle資料庫SQL
- Oracle Database Scheduler整理OracleDatabase
- win10怎麼禁用磁碟整理_win10關閉磁碟碎片整理的方法Win10
- 表碎片起因及解決辦法(zt)
- win10系統如何關閉磁碟碎片整理計劃Win10
- Oracle 常用運維命令整理Oracle運維
- 利用姑姑印表機，說說碎片化知識整理的那些事兒
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- 字串碎片字串
- jQuery碎片jQuery
- Oracle建表Oracle
- Oracle 外部表Oracle
- oracle的表Oracle
- Win10系統下不能使用碎片整理沒反應如何解決Win10
- [待整理]oracle10g刪除(釋放)資料檔案/表空間流程Oracle
- Oracle 選擇題知識點整理Oracle
- defrag" 命令的幫助資訊，該命令用於對磁碟進行碎片整理操作
- 碎片化的學習需要整理、沉澱，記錄到部落格是最好的方式！
- ORACLE 備份表Oracle
- Oracle表空間Oracle
- oracle 表空間Oracle