溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
在這個(gè)簡短系列的第1部分中，我提供了兩個(gè)場景的非正式描述，在這些場景中，我們可以從表中進(jìn)行大規(guī)模刪除。沒有一個(gè)具體的例子,很難想象刪除數(shù)據(jù)的性質(zhì)和可用的訪問路徑會產(chǎn)生大量刪除操作對系統(tǒng)的性能影響,所以我要把大部分的時(shí)間花在本文討論的兩個(gè)測試生成的數(shù)據(jù)集。這篇文章似乎有點(diǎn)長但相當(dāng)多的空間會被表格占用。
簡單的數(shù)據(jù)集
隨著硬件的能力和規(guī)模的不斷增長，我們越來越難以就“大表”或“大規(guī)模刪除”的含義達(dá)成一致——對于一個(gè)人來說，100萬行似乎很大，而對于另一個(gè)人來說，1億行似乎相當(dāng)普通。
我將使用一個(gè)折中方案，用1000萬行表示一個(gè)投資系統(tǒng)，該系統(tǒng)10年來以每年100萬行的速度增長，并且已經(jīng)達(dá)到了1.6GB的段大小。
當(dāng)然，這個(gè)表只是組成整個(gè)系統(tǒng)的幾個(gè)表中的一個(gè)，在某個(gè)時(shí)候我們會對所需要的數(shù)據(jù)擔(dān)心，但是，目前，我們只考慮這個(gè)表，只考慮表本身和表上的4個(gè)索引。
下面是生成數(shù)據(jù)集的代碼:
execute dbms_random.seed(0)
create table t1 (
idnot null,
date_open, date_closed,
deal_type,client_ref,
small_vc,padding
)
nologging
as
with generator as (
select/*+ materialize cardinality(1e4) */
rownumid
fromdual
connect by
rownum <= 1e4
)
select
1e4 * (g1.id - 1) + g2.idid,
trunc(
add_months(sysdate, - 120) +
(1e4 * (g1.id - 1) + g2.id)* 3652 / 1e7
)date_open,
trunc(
add_months(
add_months(sysdate, - 120) +
(1e4 * (g1.id - 1) + g2.id) * 3652 / 1e7,
12 * trunc(dbms_random.value(1,6))
)
)date_closed,
cast(dbms_random.string('U',1) as varchar2(1))deal_type,
cast(dbms_random.string('U',4) as varchar2(4))client_ref,
lpad(1e4 * (g1.id - 1) + g2.id,10)small_vc,
rpad('x',100,'x')padding
from
generatorg1,
generatorg2
where
g1.id <= 1e3
and g2.id <= 1e4
;
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');
alter table t1 add constraint t1_pk primary key(id) using index nologging;
create index t1_dt_open on t1(date_open) nologging;
create index t1_dt_closed on t1(date_closed) nologging;
create index t1_client on t1(client_ref) nologging;
上面看起來不是很明顯，但是代碼生成了10000萬行；
date_open：從過去的120個(gè)月(10年3652天)開始，用于增加值的算法意味著最近的條目在當(dāng)前日期。
date_closed：是添加到date_open(該表是記錄定期投資的簡單模型)的1到5年(包括5年)之間的整數(shù)。
deal_type：是隨機(jī)生成的單個(gè)大寫字符——生成26個(gè)不同的值，這些值具有相同的數(shù)據(jù)量;
client_ref：是隨機(jī)生成的一個(gè)固定長度的字符串，由4個(gè)大寫字母組成，每個(gè)組合提供大約50萬個(gè)組合和20行。
note：作為補(bǔ)充說明-已經(jīng)生成的數(shù)據(jù)集沒有使用rownum在任何地方的高容量選擇;這將使我能夠使用并行執(zhí)行更快地生成數(shù)據(jù)(“l(fā)evel”和“rownum”偽列都限制了Oracle使用并行執(zhí)行的能力)。但是在本例中，因?yàn)槲蚁Ｍ鹖d列對按到達(dá)順序存儲的按順序生成的值進(jìn)行建模，所以我是按順序運(yùn)行代碼的。
我的筆記本電腦上，在Linux 5 VM上運(yùn)行了database 12.1.0.2，我得到了創(chuàng)建數(shù)據(jù)、收集統(tǒng)計(jì)數(shù)據(jù)和創(chuàng)建索引所花費(fèi)的時(shí)間如下:
表創(chuàng)建:7:06.40 數(shù)據(jù)收集:0:10.54 PK主鍵:0:10.94 創(chuàng)建索引:0:10.79 (date_open) 創(chuàng)建索引:0:12.17 (date_closed) 創(chuàng)建索引:0:13.65 (client_ref)
當(dāng)然，這就要我們開始提一個(gè)很現(xiàn)實(shí)問題，即不同的系統(tǒng)可能會有不同的時(shí)間消耗結(jié)果。
虛擬機(jī)分配4 gb的內(nèi)存(1.6 gb是留出memory_target)和一個(gè)四核CPU 2.8 ghz 的CPU,但可能最重要的是機(jī)器1 tb的固態(tài)盤,所以不會失去太多時(shí)間在物理I / O。
數(shù)據(jù)庫配置了3個(gè)重做日志組，每個(gè)重做日志組的大小為200MB(為了日志文件檢查點(diǎn)和日志文件切換等待出現(xiàn)一些延遲)，日志是重復(fù)的，但是實(shí)例沒有在archivelog模式下運(yùn)行。
在stats收集之后，大多數(shù)塊中的表塊計(jì)數(shù)大約為204,000個(gè)塊，每個(gè)塊有49行，PK索引和client_ref索引大約有22,000個(gè)葉塊，兩個(gè)日期索引大約有26,500個(gè)葉塊。
Quality
當(dāng)使用這樣的模型來質(zhì)疑它們與現(xiàn)實(shí)生產(chǎn)中有多接近時(shí)是非常重要的。到目前來看，在我所的的準(zhǔn)備工作中，你能發(fā)現(xiàn)其中存在哪些問題呢？
首先，表中的Id列太完美了，id列在表中的順序從小到大排列的非常有序，然而在現(xiàn)實(shí)當(dāng)中，并發(fā)性的插入會有一點(diǎn)都抖動，一定范圍內(nèi)連續(xù)性的值可能分布在少量的塊上，這可能不是很重要，重要的是我是在創(chuàng)建表之后插入數(shù)據(jù)才創(chuàng)建的索引，這意味著索引在物理上來看是沒有什么問題。（每個(gè)塊中有10%的自由空間），我應(yīng)該先創(chuàng)建一張空的表，然后在表上建立索引，在這之后再運(yùn)行幾個(gè)并發(fā)性的腳本使用序列進(jìn)行單行插入來生成id，但是我上次這樣創(chuàng)建的時(shí)候，所需要的時(shí)間增加了40倍。同樣的，這可能也不是很重要，我記得在生產(chǎn)系統(tǒng)中索引的葉塊中平均可用空間在任何時(shí)候都接近30%。 隨著塊與塊之間明顯的變化差異，我想時(shí)不時(shí)的通過基于葉塊狀態(tài)的檢查，尤其是date_open這個(gè)索引。
Scenarios（場景）
盡管任何時(shí)間消耗都取決于機(jī)器的配置和資源的分配，并且這個(gè)模型過于簡單化，但是我們?nèi)稳豢梢詮囊恍┗镜臏y試當(dāng)中獲取一些有意思的信息。讓我們從幾個(gè)與業(yè)務(wù)相關(guān)的的場景開始：
a、刪除所有5年前完成的交易 b、刪除client_ref以“A”-“E”開頭的所有交易 c、刪除所有5年以上的交易
A 項(xiàng)可能在刪除前已經(jīng)做了一次最基本要求的歸檔，也可能已經(jīng)cpye 到另一張表中了。
B 項(xiàng)可能告訴我們，client_ref已經(jīng)(ab)用于在第一個(gè)字母中為引用編碼一些重要的分類，我們將數(shù)據(jù)分成兩個(gè)處理集
C 項(xiàng)可能是按照date_open 對數(shù)據(jù)進(jìn)行分區(qū)的過程的一部分。（雖然我不確定在這種情況下分區(qū)是不是一個(gè)好方法），在做任何對于數(shù)據(jù)庫來說影響比較大的操作之前，最好看看時(shí)刻能夠可視化的知道oracle將要做什么？執(zhí)行的步驟是什么，以及工作負(fù)載會出現(xiàn)在哪里？這些場景都是相同的嗎？如果不是，他們有什么不同？如果你不知道你的數(shù)據(jù)以及你刪除數(shù)據(jù)的影響，你可以從數(shù)據(jù)庫中尋求答案-舉個(gè)例子：
select rows_in_block, count(*) blocks, rows_in_block * count(*) row_count, sum(count(*)) over (order by rows_in_block) running_blocks, sum(rows_in_block * count(*)) over (order by rows_in_block) running_rows from ( select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid), count(*) rows_in_block from t1 -- -- where date_open >= add_months(sysdate, -60) -- where date_open < add_months(sysdate, -60) -- -- where date_closed >= add_months(sysdate, -60) -- where date_closed < add_months(sysdate, -60) -- -- where substr(client_ref,2,1) >= 'F' -- where substr(client_ref,2,1) < 'F' -- group by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) ) group by rows_in_block order by rows_in_block ;
您將注意到，在這個(gè)查詢中，我有六個(gè)注釋謂詞(在三個(gè)互補(bǔ)對中)。這個(gè)查詢的基本目的是讓我總結(jié)一下有多少塊可以容納多少行。但是每對謂詞都讓我對每種場景的效果有了一些想法-每一對中的一個(gè)告訴我關(guān)于將要刪除的數(shù)據(jù)量和模式的一些信息。下面是sql*plus中執(zhí)行如上查詢的輸出：
Blocks Rows Rows per block Blocks Rows Running total Running total -------------- -------- ------------ ------------- ------------- 27 1 27 1 27 49 203,877 9,989,973 203,878 9,990,000 50 200 10,000 204,078 10,000,000 -------- sum 204,078
下面的輸出顯示了如果刪除了5年以上打開的數(shù)據(jù)行，留下來的數(shù)據(jù)將會是什么樣子?(也就是說，使用謂詞date_open >= add_months(sysdate， -60))
Blocks Rows Rows per block Blocks Rows Running total Running total -------------- -------- -------------- ------------- -------------- 27 1 27 1 27 42 1 42 2 69 49 102,014 4,998,686 102,016 4,998,755 -------- sum 102,016
這相當(dāng)不錯--粗略的來說我們已經(jīng)將表一半的塊清空了，另一半沒有動。如果我們現(xiàn)在嘗試‘收縮空間’，那么我們只需要將表的下半部分復(fù)制到表的上半部分。我們會生成大量的undo數(shù)據(jù)和redo日志。但是任何索引的任何聚簇因子可能沒有一點(diǎn)改變。另一種選擇是，如果我們決定讓空白空間保持原樣，那么任何新數(shù)據(jù)都會非常有效地開始填充空白空間（幾乎就想是重新分配區(qū)一樣），同樣的我們也會看到任何聚簇的因子也沒有什么改變。將此結(jié)果與刪除所有5年前關(guān)閉的行所帶來的結(jié)果進(jìn)行比較，（也就是說，如果我們使用謂詞date_closed >= add_months(sysdate， -60)，會看到什么?）這個(gè)結(jié)果集.會大很多。
Blocks Rows Rows per block Blocks Rows Running total Running total -------------- -------- -------------- ------------- -------------- 1 5 5 5 5 2 22 44 27 49 3 113 339 140 388 4 281 1,124 421 1,512 5 680 3,400 1,101 4,912 6 1,256 7,536 2,357 12,448 7 1,856 12,992 4,213 25,440 8 2,508 20,064 6,721 45,504 9 2,875 25,875 9,596 71,379 10 2,961 29,610 12,557 100,989 11 2,621 28,831 15,178 129,820 12 2,222 26,664 17,400 156,484 13 1,812 23,556 19,212 180,040 14 1,550 21,700 20,762 201,740 15 1,543 23,145 22,305 224,885 16 1,611 25,776 23,916 250,661 17 1,976 33,592 25,892 284,253 18 2,168 39,024 28,060 323,277 19 2,416 45,904 30,476 369,181 20 2,317 46,340 32,793 415,521 21 2,310 48,510 35,103 464,031 22 2,080 45,760 37,183 509,791 23 1,833 42,159 39,016 551,950 24 1,696 40,704 40,712 592,654 25 1,769 44,225 42,481 636,879 26 1,799 46,774 44,280 683,653 27 2,138 57,726 46,418 741,379 28 2,251 63,028 48,669 804,407 29 2,448 70,992 51,117 875,399 30 2,339 70,170 53,456 945,569 31 2,286 70,866 55,742 1,016,435 32 1,864 59,648 57,606 1,076,083 33 1,704 56,232 59,310 1,132,315 34 1,566 53,244 60,876 1,185,559 35 1,556 54,460 62,432 1,240,019 36 1,850 66,600 64,282 1,306,619 37 2,131 78,847 66,413 1,385,466 38 2,583 98,154 68,996 1,483,620 39 2,966 115,674 71,962 1,599,294 40 2,891 115,640 74,853 1,714,934 41 2,441 100,081 77,294 1,815,015 42 1,932 81,144 79,226 1,896,159 43 1,300 55,900 80,526 1,952,059 44 683 30,052 81,209 1,982,111 45 291 13,095 81,500 1,995,206 46 107 4,922 81,607 2,000,128 47 32 1,504 81,639 2,001,632 48 3 144 81,642 2,001,776 49 122,412 5,998,188 204,054 7,999,964 -------- sum 204,054
在這種情況下，大約有60%的blocks依然每個(gè)塊持有原來的49行，但是表中的其他塊幾乎沒有被刪除，而是被完全清空。（如果您將第一個(gè)輸出中的總塊數(shù)與第一個(gè)報(bào)告中的總塊數(shù)進(jìn)行比較，您會注意到現(xiàn)在肯定有幾個(gè)塊(24個(gè)塊)是完全空的）現(xiàn)在有多少塊可用來插入?這里有一個(gè)快速的計(jì)算，我們的大部分塊有49行，占了90%（default pctree = 10），因此，一個(gè)塊將下降到75%的標(biāo)記(即當(dāng)ASSM將其標(biāo)記為有空閑空間時(shí))，當(dāng)它少于41行時(shí)(49 * 75 / 90)，在204,000個(gè)塊中，大約75,000個(gè)符合這個(gè)標(biāo)準(zhǔn)(檢查“運(yùn)行的塊總數(shù)”列)
索引空間
上一節(jié)展示了一些簡單的SQL，讓您了解了表中將如何顯示空間(或數(shù)據(jù)將如何保留)-我們可以對索引做類似的事情嗎？答案必然是肯定的。但是，回答“在刪除匹配謂詞X的數(shù)據(jù)之后，索引會是什么樣子”這個(gè)問題的代碼運(yùn)行起來要比運(yùn)行表的代碼開銷更大。首先，這里有一段簡單的代碼來檢查索引的當(dāng)前內(nèi)容:
select rows_per_leaf, count(*) leaf_blocks from ( select /*+ index_ffs(t1(client_ref)) */ sys_op_lbid(94255, 'L', t1.rowid) leaf_block, count(*) rows_per_leaf from t1 where client_ref is not null group by sys_op_lbid(94255, 'L', t1.rowid) ) group by rows_per_leaf order by rows_per_leaf ;
對于‘SYS_OP_LBID()’的調(diào)用將一個(gè)表rowid作為它的如數(shù)之一，并返回一些類似于塊的第一行的rowid的內(nèi)容，而該塊的地址是索引葉塊的地址，索引你塊持有表rowid所提供的索引條目。另外兩個(gè)參數(shù)是索引object_id(如果索是分區(qū)的，則是分區(qū)或者是子分區(qū))和一個(gè)表示函數(shù)的特定用法的標(biāo)志。在這個(gè)例子中是“L”。hint在目標(biāo)索引上使用快速索引掃描是必要的-任何其他路徑都可能返回錯誤的出結(jié)果-‘client_ref’不為空是必要的。以確保查詢可以有效的使用index_ffs路徑。
對于我的初始化數(shù)據(jù)集，索引在每個(gè)塊中都有448個(gè)索引條目，除了一個(gè)（大概是最后一個(gè)，192行）。即使這是簡單的查詢也要為了每個(gè)索引的要求而精心設(shè)計(jì)-因?yàn)樗饕焖賿呙栊枰玫秸_的結(jié)果，這就是我們不得不做一些不同尋常的刪除操作，看看我們大量刪除會怎么影響索引。下面是一個(gè)例子，展示我們?nèi)绾握页鲈噲D刪除5年多前打開的行對client_ref索引產(chǎn)生什么影響。
select rows_per_leaf, count(*) blocks, rows_per_leaf * count(*) row_count, sum(count(*)) over (order by rows_per_leaf) running_blocks, sum(rows_per_leaf * count(*)) over (order by rows_per_leaf) running_rows from ( select /*+ leading(v1 t1) use_hash(t1) */ leaf_block, count(*) rows_per_leaf from ( select /*+ no_merge index_ffs(t1(client_ref)) */ sys_op_lbid(94255, 'L', t1.rowid) leaf_block, t1.rowid rid from t1 where client_ref is not null ) v1, t1 where t1.rowid = v1.rid and date_open < add_months(sysdate, -60) group by leaf_block ) group by rows_per_leaf order by rows_per_leaf ;
正如您所看到的，我們從一個(gè)內(nèi)聯(lián)視（按時(shí)不可合并）圖開始將索引塊id附加每個(gè)表的rowid上，然后將這組行id連接回表-通過rowid連接并強(qiáng)制進(jìn)行散列連接。我已經(jīng)暗示了散列連接，因?yàn)樗?可能)是最有效的策略，但是盡管我引入了一個(gè)leading()提示，但我沒有包含關(guān)于交換(或不)連接輸入的提示-我將讓優(yōu)化器決定這兩個(gè)數(shù)據(jù)集中哪個(gè)更小，由此來更適合的構(gòu)建哈希表。
在這種特殊的情況下優(yōu)化器能夠使用一個(gè)僅索引的訪問路徑來查找date_open 比五年前跟早行的所有rowid。盡管如此(部分原因是我的pga_aggregate_target相對較小，散列連接溢出到(固態(tài))磁盤)，查詢耗時(shí)3分15秒，而上一個(gè)查詢在緩存整個(gè)索引時(shí)恰好運(yùn)行了1.5秒。以下是輸出的摘錄:
Blocks Rows Rows_per_leaf Blocks Rows Running total Running total ------------- -------- -------------- ------------- -------------- 181 2 362 3 458 186 2 372 5 830 187 2 374 7 1,204 188 1 188 8 1,392 ... 210 346 72,660 2,312 474,882 211 401 84,611 2,713 559,493 ... 221 808 178,568 8,989 1,921,410 222 851 188,922 9,840 2,110,332 223 832 185,536 10,672 2,295,868 ... 242 216 52,272 21,320 4,756,575 243 173 42,039 21,493 4,798,614 244 156 38,064 21,649 4,836,678 ... 265 1 265 22,321 5,003,718 266 1 266 22,322 5,003,984
我們要修改22322個(gè)葉塊——這是索引中的每一個(gè)葉塊;我們從一個(gè)葉塊中刪除的行數(shù)從1到266不等。我一次從83行輸出中選擇了幾行，但是您可能仍然可以看到該模式似乎遵循正態(tài)分布，以222(50%)為中心。
如果這樣刪除我們應(yīng)該很清楚，我們將花費(fèi)大量的精力來更新這個(gè)索引；即使這樣，“每個(gè)葉塊刪除多少行”這個(gè)簡單的數(shù)字也不能告訴我們要做的工作的全部內(nèi)容。我們不知道我們是否會(例如)在同一時(shí)間刪除所有266個(gè)索引條目從最后一塊上面顯示刪除完成，我們將非常隨機(jī)地在索引周圍跳躍式來回，并發(fā)現(xiàn)自己不斷地重新訪問該塊，以便一次刪除一個(gè)索引條目。因此在下一期中，我們將研究需要考慮工作負(fù)載的哪些方面，以及不同的刪除策略如何對工作負(fù)載產(chǎn)生重大影響。
譯者: 湯建 原作者: Jonathan Lewis 原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼