前言

本文通過對Oracle 表碎片整理，對比了前后對數(shù)據(jù)庫性能的影響。

?? 1.創(chuàng)建測試表

??1.1 建立表空間

SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

??1.2 創(chuàng)建ASSM表空間

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 創(chuàng)建表及索引

##創(chuàng)建測試表t1,id列創(chuàng)建索引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.查看表統(tǒng)計(jì)信息

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

總結(jié)：

查看表T1,段4M, 占用473個數(shù)據(jù)塊,39個空塊,索引IN_T1_ID段6M；

?? 3.空塊占用空間

查看沒有數(shù)據(jù)的塊占用的空間

DBMS_STATS 包無法獲取 EMPTY_BLOCKS 統(tǒng)計(jì)信息，所以需要用 analyze 命令再收集一次統(tǒng)計(jì)信息，估算表在高水位線下還有多少空間可用 ，這個值應(yīng)當(dāng)越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少! !

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.查看執(zhí)行計(jì)劃

查看全表掃描cost為131，基于成本

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

?? 5.刪除大量數(shù)據(jù)

刪除大部分?jǐn)?shù)據(jù),并收集統(tǒng)計(jì)信息,查看T1占用數(shù)據(jù)塊和空塊都沒有減少

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.再次查看執(zhí)行計(jì)劃

查看全表掃描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;
關(guān)閉行遷移
alter table t1 disable row movement;
SYS@EDB> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1'

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

收集統(tǒng)計(jì)信息

analyze table t1 compute statistics;

?? 9.效果確認(rèn)

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

總結(jié)

到此這篇關(guān)于Oracle表碎片對性能有多大影響的文章就介紹到這了,更多相關(guān)Oracle表碎片對性能影響內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論