關於Oracle表碎片整理,
數據庫在日常使用過程中,不斷的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