程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 關於Oracle表碎片整理,

關於Oracle表碎片整理,

編輯:Oracle教程

關於Oracle表碎片整理,


數據庫在日常使用過程中,不斷的insert,delete,update操作,導致表和索引出現碎片是在所難免的事情,碎片多了,sql的執行效率自然就差了,道理很簡單,高水位線(HWL)下的許多數據塊都是無數據的,但全表掃描的時候要掃描到高水位線的數據塊,也就是說oracle要做許多的無用功!因此oracle提供了shrink space碎片整理功能。對於索引,可以采取rebuild online的方式進行碎片整理,一般來說,經常進行DML操作的對象DBA要定期進行維護,同時注意要及時更新統計信息!

一:准備測試數據,使用HR用戶,創建T1表,插入約30W的數據,並根據object_id創建普通索引,表占存儲空間34M左右,索引占6M左右的存儲空間 

  1. SQL> conn /as sysdba 
  2. 已連接。 
  3. SQL> select default_tablespace from dba_users where username='HR'; 
  4.  
  5. DEFAULT_TABLESPACE 
  6. ------------------------------------------------------------ 
  7. USERS 
  8.  
  9. SQL> conn hr/hr 
  10. 已連接。 
  11.  
  12. SQL> insert into t1 select * from t1; 
  13. 已創建 74812 行。 
  14.  
  15. SQL> insert into t1 select * from t1; 
  16. 已創建 149624 行。 
  17.  
  18. SQL> commit; 
  19. 提交完成。 
  20.  
  21. SQL> create index idx_t1_id on t1(object_id); 
  22. 索引已創建。 
  23.  
  24. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
  25. PL/SQL 過程已成功完成。 
  26.  
  27. SQL> select count(1) from t1; 
  28.  
  29.   COUNT(1) 
  30. ---------- 
  31.     299248 
  32.  
  33. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
  34. SUM(BYTES)/1024/1024 
  35. -------------------- 
  36.              34.0625 
  37.  
  38. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID'; 
  39. SUM(BYTES)/1024/1024 
  40. -------------------- 
  41.                    6 

 

二:估算表在高水位線下還有多少空間可用,這個值應當越低越好,表使用率越接近高水位線,全表掃描所做的無用功也就越少!

DBMS_STATS包無法獲取EMPTY_BLOCKS統計信息,所以需要用analyze命令再收集一次統計信息

  1. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
  2.  
  3.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
  4. ---------- ------------ ---------- 
  5.       4302            0     299248 
  6.  
  7. SQL> analyze table t1 compute statistics; 
  8. 表已分析。 
  9.  
  10. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
  11.  
  12.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
  13. ---------- ------------ ---------- 
  14.       4302           50     299248 
  15.  
  16. SQL> col table_name for a20 
  17. SQL> SELECT TABLE_NAME, 
  18.   2         (BLOCKS * 8192 / 1024 / 1024) - 
  19.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
  20.   4    FROM USER_TABLES 
  21.   5   WHERE table_name = 'T1'; 
  22.  
  23. TABLE_NAME           Data lower than HWM in MB 
  24. -------------------- ------------------------- 
  25. T1                                  5.07086182 

三: 查看執行計劃,全表掃描大概需要消耗CPU 1175

  1. SQL> explain plan for select * from t1; 
  2. 已解釋。 
  3.  
  4. SQL> select * from table(dbms_xplan.display); 
  5.  
  6. PLAN_TABLE_OUTPUT 
  7. -------------------------------------------------------------------------------- 
  8. Plan hash value: 3617692013 
  9. -------------------------------------------------------------------------- 
  10. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  11. -------------------------------------------------------------------------- 
  12. |   0 | SELECT STATEMENT  |      |   299K|    28M|  1175   (1)| 00:00:15 | 
  13. |   1 |  TABLE ACCESS FULL| T1   |   299K|    28M|  1175   (1)| 00:00:15 | 
  14. -------------------------------------------------------------------------- 

四:刪除大部分數據,收集統計信息,全表掃描依然需要消耗CPU 1168

  1. SQL> delete from t1 where object_id>100; 
  2. 已刪除298852行。 
  3.  
  4. SQL> commit; 
  5. 提交完成。 
  6.  
  7. SQL> select count(*) from t1; 
  8.  
  9.   COUNT(*) 
  10. ---------- 
  11.        396 
  12.  
  13. SQL>  exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
  14. PL/SQL 過程已成功完成。 
  15.  
  16. SQL> analyze table t1 compute statistics; 
  17. 表已分析。 
  18.  
  19. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
  20.  
  21.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
  22. ---------- ------------ ---------- 
  23.       4302           50        396 
  24.  
  25.  
  26. SQL> explain plan for select * from t1; 
  27. 已解釋。 
  28.  
  29. SQL> select * from table(dbms_xplan.display); 
  30.  
  31. PLAN_TABLE_OUTPUT 
  32. ------------------------------------------------------------------------------ 
  33. Plan hash value: 3617692013 
  34. -------------------------------------------------------------------------- 
  35. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  36. -------------------------------------------------------------------------- 
  37. |   0 | SELECT STATEMENT  |      |   396 | 29700 |  1168   (1)| 00:00:15 | 
  38. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 | 
  39. -------------------------------------------------------------------------- 

五:估算表在高水位線下還有多少空間是無數據的,但在全表掃描時又需要做無用功的數據

  1. SQL> SELECT TABLE_NAME, 
  2.   2         (BLOCKS * 8192 / 1024 / 1024) - 
  3.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
  4.   4    FROM USER_TABLES 
  5.   5   WHERE table_name = 'T1'; 
  6.  
  7. TABLE_NAME           Data lower than HWM in MB 
  8. -------------------- ------------------------- 
  9. T1                                  33.5791626 

六:對表進行碎片整理,重新收集統計信息

  1. SQL> alter table t1 enable row movement; 
  2. 表已更改。 
  3.  
  4. SQL> alter table t1 shrink space cascade; 
  5. 表已更改。 
  6.  
  7. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
  8.  
  9. SUM(BYTES)/1024/1024 
  10. -------------------- 
  11.                 .125 
  12.  
  13. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID 
  14. '; 
  15.  
  16. SUM(BYTES)/1024/1024 
  17. -------------------- 
  18.                .0625 
  19.  
  20. SQL> SELECT TABLE_NAME, 
  21.   2         (BLOCKS * 8192 / 1024 / 1024) - 
  22.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
  23.   4    FROM USER_TABLES 
  24.   5   WHERE table_name = 'T1'; 
  25.  
  26. TABLE_NAME           Data lower than HWM in MB 
  27. -------------------- ------------------------- 
  28. T1                                  33.5791626 
  29.  
  30. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
  31. PL/SQL 過程已成功完成。 
  32.  
  33. 這個時候,只剩下0.1M的無用功了,執行計劃中,全表掃描也只需要消耗CPU 3 
  34. SQL> SELECT TABLE_NAME, 
  35.   2         (BLOCKS * 8192 / 1024 / 1024) - 
  36.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
  37.   4    FROM USER_TABLES 
  38.   5   WHERE table_name = 'T1'; 
  39.  
  40. TABLE_NAME           Data lower than HWM in MB 
  41. -------------------- ------------------------- 
  42. T1                                  .010738373 
  43.  
  44.  
  45. SQL> select * from table(dbms_xplan.display); 
  46.  
  47. PLAN_TABLE_OUTPUT 
  48. -------------------------------------------------------------------------------- 
  49. Plan hash value: 3617692013 
  50. -------------------------------------------------------------------------- 
  51. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  52. -------------------------------------------------------------------------- 
  53. |   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 | 
  54. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 | 
  55. -------------------------------------------------------------------------- 
  56.  
  57. 總共只有5個塊,空塊卻有50個,明顯empty_blocks信息過期 
  58. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
  59.  
  60.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
  61. ---------- ------------ ---------- 
  62.          5           50        396 
  63.  
  64. SQL> analyze table t1 compute statistics; 
  65. 表已分析。 
  66.  
  67. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
  68.  
  69.  
  70.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
  71. ---------- ------------ ---------- 
  72.          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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved