Oracle表碎片對效能有多大影響?

語言: CN / TW / HK

theme: awesome-green

持續創作,加速成長!這是我參與「掘金日新計劃 · 6 月更文挑戰」的第27天,點選檢視活動詳情

📢📢📢📣📣📣\ 哈嘍!大家好,我是【IT邦德】,江湖人稱jeames007,10年DBA工作經驗\ 中國DBA聯盟(ACDU)成員,目前從事DBA及程式程式設計😜😜😜\ 擅長Oracle、MySQL、PG 運維開發,備份恢復,安裝遷移,效能優化、故障應急處理。\ ❤️❤️❤️感謝各位大可愛小可愛!❤️❤️❤️

摘要:本文通過對Oracle 表碎片整理,對比了前後對資料庫效能的影響。

⛳️ 1.建立測試表

🐴1.1 建立表空間

SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

在這裡插入圖片描述

🐴1.2 建立ASSM表空間

js CREATE TABLESPACE "JEAMES" DATAFILE '/u01/app/oracle/oradata/EDB/jeames01' SIZE 50M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

在這裡插入圖片描述

🐴1.3 建立表及索引

js ##建立測試表t1,id列建立索引in_t1_id\ create table t1 tablespace JEAMES as select level as id from dual connect by level<=300000; create index in_t1_id on t1(id); analyze table t1 compute statistics; select count(*) from t1;

在這裡插入圖片描述

⛳️ 2.查看錶統計資訊

select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';

在這裡插入圖片描述 select sum(bytes)/1024/1024 from dba_segments where segment_name='IN_T1_ID';

在這裡插入圖片描述 SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

在這裡插入圖片描述

總結: 查看錶T1,段4M, 佔用473個數據塊,39個空塊,索引IN_T1_ID段6M;

⛳️ 3.空塊佔用空間

檢視沒有資料的塊佔用的空間\ DBMS_STATS 包無法獲取 EMPTY_BLOCKS 統計資訊,\ 所以需要用 analyze 命令再收集一次統計資訊,\ 估算表在高水位線下還有多少空間可用 ,這個值應當越低越好,\ 表使用率越接近高水位線,全表掃描所做的無用功也就越少! !

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';

在這裡插入圖片描述

⛳️ 4.檢視執行計劃

檢視全表掃描cost為131,基於成本 explain plan for select * from t1; select * from table(dbms_xplan.display);

在這裡插入圖片描述

⛳️ 5.刪除大量資料

刪除大部分資料,並收集統計資訊,檢視T1佔用資料塊和空塊都沒有減少\ delete from t1 where id>10;

在這裡插入圖片描述

analyze table t1 compute statistics;\ SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

在這裡插入圖片描述

⛳️ 6.再次檢視執行計劃

檢視全表掃描cost為125,基於成本, 使用率幾乎沒有下降\ explain plan for select * from t1;\ select * from table(dbms_xplan.display);

在這裡插入圖片描述

⛳️ 7.再次空塊佔用空間

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';

在這裡插入圖片描述

⛳️ 8.整理表碎片

開啟行遷移\ alter table t1 enable row movement;\ 降低水位線\ alter table t1 shrink space;\ 關閉行遷移\ alter table t1 disable row movement; SYS@EDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'

在這裡插入圖片描述

js 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';

在這裡插入圖片描述

收集統計資訊 analyze table t1 compute statistics;

⛳️ 9.效果確認

js 佔用資料塊及空閒資料塊下降,並且cost使用也下降 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';

select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

在這裡插入圖片描述

explain plan for select * from t1; \ select * from table(dbms_xplan.display);

在這裡插入圖片描述

⛳️ 10.技能拓展

1.再用alter table table_name move 時,表相關的索引會失效, 所以之後還要執行 alter index index_name rebuild online; 最後重新編譯資料庫所有失效的物件 2. 在用 alter table table_name shrink space cascade 時, 3. 他相當於 alter table table_name move 和 alter index index_name rebuild online. 所以只要編譯資料庫失效的物件就可以; 4. Move 會移動高水位,但不會釋放申請的空間,是在高水位以下(below HWM)的操作。 5. shrink space 同樣會移動高水位, 6. 但也會釋放申請的空間,是在高水位上下(below and above HWM)都有的操作。 原理不一樣,move 是以 block 為單位重組資料, 行的 rowid 都會跟著變化,而 shrink 是以”行“為單位重組 資料,他是根據複雜的演算法從邏輯+物理重組資料 move 速度快於 shrink. Move 相當於 從 segment 底部 move 到 頭。 Shrink 相當於先 delete,然後再 insert 這樣產生很多 undo,redo 通常首選 MOVE 語法: alter table <table_name> shrink space [ <null> | compact | cascade ]; alter table <table_name> shrink space compcat; k segment shrink 分為兩個階段: 1、資料重組(compact):通過一系列 insert、delete 操作, 將資料儘量排列在段的前面。在這個過程中需 要在表上加 RX 鎖,即只在需要移動的行上加鎖。由於涉及到 rowid 的改變, 需要 enable row movement.同時要 disable 基於 rowid 的 trigger.這一過程對業務影響比較小。 2、HWM 調整:第二階段是調整 HWM 位置,釋放空閒資料塊。 此過程需要在表上加 X 鎖,會造成表上的所有 DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。 Shrink Space語句兩個階段都執行。Shrink Space compact 只執行第一個階段。 如果系統業務比較繁忙,可以先執行 Shrink Space compact 重組資料,然後在業務不忙的時候再執行 Shrink Space 降低 HWM 釋放空閒資料塊。shrink 必須開啟行遷移功能。

cd0cc1d6fe704cdf90f6c3ddcf4aacde.gif