關於Oracle表碎片整理

来源:http://www.cnblogs.com/youzhangcai1/archive/2016/06/17/5593310.html
-Advertisement-
Play Games

資料庫在日常使用過程中，不斷的insert，delete，update操作，導致表和索引出現碎片是在所難免的事情，碎片多了，sql的執行效率自然就差了，道理很簡單，高水位線（HWL）下的許多數據塊都是無數據的，但全表掃描的時候要掃描到高水位線的數據塊，也就是說oracle要做許多的無用功！因此ora ...


資料庫在日常使用過程中，不斷的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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 曲演雜壇--為什麼SELECT語句會被其他SELECT阻塞？
    很多剛入門的DBA在捕獲阻塞得時候，會問這麼一個問題“為什麼這個SELECT語句被那個SELECT語句阻塞了，難道不是共用鎖麽？” 讓我們來做個小測試，首先準備一些測試數據： 註意上面創建的表中RID是唯一聚集索引，因此如果我們按照RID來進行更新或查詢，會加行鎖。 首先開啟一個事務來修改數據： 然 ...
  • mysql查詢區分大小寫
    在查找的條件值加上的binary() 來源：http://my.oschina.net/xiangtao/blog/33983 ...
  • hadoop初次接觸----------虛擬機、SSH互信及hadoop配置
    部署環境： OS：Fedora 23 JDK：jdk-7u80-linux-x64 Hadoop：hadoop-2.7.2 VMWare：VMware Workstation 12 Pro 安裝JDK rpm -ivh jdk-7u80-linux-x64.rpm 配置環境變數 用vim編輯器打開/ ...
  • SQL SERVER 的模糊查詢 LIKE
    今天寫個動態腳本，需要把資料庫裡面包含“USER_"的表刪除掉，突然想不起來如何搜索通配字元了，趕緊查查MSDN，整理了下模糊查詢的知識點，留著以後查閱用。 LIKE模糊查詢的通配符 通配符 說明 示例 % 包含零個或多個字元的任意字元串。 WHERE title LIKE '%computer%' ...
  • 資料庫設計（3/9）：創建表
    對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO ...
  • MySQL 複製介紹及搭建
    MySQL複製介紹 MySQL複製就是一臺MySQL伺服器（slave）從另一臺MySQL伺服器（master）進行日誌的複製然後再解析日誌並應用到自身，類似Oracle中的Data Guard。 MySQL複製有那些好處： 第一是解決宕機帶來的數據不一致，因為MySQL複製可以實時備份數據； 第二 ...
  • sqlserver 時間格式函數詳細
    一、時間函數 在使用存儲過程，sql函數的時候，會遇到一些對時間的處理。比如時間的獲取與加減。這裡就用到了sql自帶的時間函數。下麵我列出這些函數，方便日後記憶，使用。 二、時間格式轉換 三、時間格式相關表格 ...
  • Oracle迴圈中的EXIT、RETURN、CONTINUE解密
    有時候編寫Oracle中用游標等信息去迴圈處理邏輯的時候，對EXIT、RETURN、CONTINUE很容易搞混淆，網上搜了資料也不是很清楚，所以本人自己寫了一小段代碼測試了這三種用法。案例代碼如下： 案例測試得到結果如下（分析的結果中註意本次迴圈和本迴圈的區別。本次迴圈是本迴圈執行的這次迴圈）： 第 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類