溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
在這個簡短系列的第1部分中，我提供了兩個場景的非正式描述，在這些場景中，我們可以從表中進行大規模刪除。沒有一個具體的例子,很難想象刪除數據的性質和可用的訪問路徑會產生大量刪除操作對系統的性能影響,所以我要把大部分的時間花在本文討論的兩個測試生成的數據集。這篇文章似乎有點長但相當多的空間會被表格占用。
簡單的數據集
隨著硬件的能力和規模的不斷增長，我們越來越難以就“大表”或“大規模刪除”的含義達成一致——對于一個人來說，100萬行似乎很大，而對于另一個人來說，1億行似乎相當普通。
我將使用一個折中方案，用1000萬行表示一個投資系統，該系統10年來以每年100萬行的速度增長，并且已經達到了1.6GB的段大小。
當然，這個表只是組成整個系統的幾個表中的一個，在某個時候我們會對所需要的數據擔心，但是，目前，我們只考慮這個表，只考慮表本身和表上的4個索引。
下面是生成數據集的代碼:
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個月(10年3652天)開始，用于增加值的算法意味著最近的條目在當前日期。
date_closed：是添加到date_open(該表是記錄定期投資的簡單模型)的1到5年(包括5年)之間的整數。
deal_type：是隨機生成的單個大寫字符——生成26個不同的值，這些值具有相同的數據量;
client_ref：是隨機生成的一個固定長度的字符串，由4個大寫字母組成，每個組合提供大約50萬個組合和20行。
note：作為補充說明-已經生成的數據集沒有使用rownum在任何地方的高容量選擇;這將使我能夠使用并行執行更快地生成數據(“level”和“rownum”偽列都限制了Oracle使用并行執行的能力)。但是在本例中，因為我希望id列對按到達順序存儲的按順序生成的值進行建模，所以我是按順序運行代碼的。
我的筆記本電腦上，在Linux 5 VM上運行了database 12.1.0.2，我得到了創建數據、收集統計數據和創建索引所花費的時間如下:
表創建:7:06.40 數據收集:0:10.54 PK主鍵:0:10.94 創建索引:0:10.79 (date_open) 創建索引:0:12.17 (date_closed) 創建索引:0:13.65 (client_ref)
當然，這就要我們開始提一個很現實問題，即不同的系統可能會有不同的時間消耗結果。
虛擬機分配4 gb的內存(1.6 gb是留出memory_target)和一個四核CPU 2.8 ghz 的CPU,但可能最重要的是機器1 tb的固態盤,所以不會失去太多時間在物理I / O。
數據庫配置了3個重做日志組，每個重做日志組的大小為200MB(為了日志文件檢查點和日志文件切換等待出現一些延遲)，日志是重復的，但是實例沒有在archivelog模式下運行。
在stats收集之后，大多數塊中的表塊計數大約為204,000個塊，每個塊有49行，PK索引和client_ref索引大約有22,000個葉塊，兩個日期索引大約有26,500個葉塊。
Quality
當使用這樣的模型來質疑它們與現實生產中有多接近時是非常重要的。到目前來看，在我所的的準備工作中，你能發現其中存在哪些問題呢？
首先，表中的Id列太完美了，id列在表中的順序從小到大排列的非常有序，然而在現實當中，并發性的插入會有一點都抖動，一定范圍內連續性的值可能分布在少量的塊上，這可能不是很重要，重要的是我是在創建表之后插入數據才創建的索引，這意味著索引在物理上來看是沒有什么問題。（每個塊中有10%的自由空間），我應該先創建一張空的表，然后在表上建立索引，在這之后再運行幾個并發性的腳本使用序列進行單行插入來生成id，但是我上次這樣創建的時候，所需要的時間增加了40倍。同樣的，這可能也不是很重要，我記得在生產系統中索引的葉塊中平均可用空間在任何時候都接近30%。 隨著塊與塊之間明顯的變化差異，我想時不時的通過基于葉塊狀態的檢查，尤其是date_open這個索引。
Scenarios（場景）
盡管任何時間消耗都取決于機器的配置和資源的分配，并且這個模型過于簡單化，但是我們任然可以從一些基本的測試當中獲取一些有意思的信息。讓我們從幾個與業務相關的的場景開始：
a、刪除所有5年前完成的交易 b、刪除client_ref以“A”-“E”開頭的所有交易 c、刪除所有5年以上的交易
A 項可能在刪除前已經做了一次最基本要求的歸檔，也可能已經cpye 到另一張表中了。
B 項可能告訴我們，client_ref已經(ab)用于在第一個字母中為引用編碼一些重要的分類，我們將數據分成兩個處理集
C 項可能是按照date_open 對數據進行分區的過程的一部分。（雖然我不確定在這種情況下分區是不是一個好方法），在做任何對于數據庫來說影響比較大的操作之前，最好看看時刻能夠可視化的知道oracle將要做什么？執行的步驟是什么，以及工作負載會出現在哪里？這些場景都是相同的嗎？如果不是，他們有什么不同？如果你不知道你的數據以及你刪除數據的影響，你可以從數據庫中尋求答案-舉個例子：
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 ;
您將注意到，在這個查詢中，我有六個注釋謂詞(在三個互補對中)。這個查詢的基本目的是讓我總結一下有多少塊可以容納多少行。但是每對謂詞都讓我對每種場景的效果有了一些想法-每一對中的一個告訴我關于將要刪除的數據量和模式的一些信息。下面是sql*plus中執行如上查詢的輸出：
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年以上打開的數據行，留下來的數據將會是什么樣子?(也就是說，使用謂詞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
這相當不錯--粗略的來說我們已經將表一半的塊清空了，另一半沒有動。如果我們現在嘗試‘收縮空間’，那么我們只需要將表的下半部分復制到表的上半部分。我們會生成大量的undo數據和redo日志。但是任何索引的任何聚簇因子可能沒有一點改變。另一種選擇是，如果我們決定讓空白空間保持原樣，那么任何新數據都會非常有效地開始填充空白空間（幾乎就想是重新分配區一樣），同樣的我們也會看到任何聚簇的因子也沒有什么改變。將此結果與刪除所有5年前關閉的行所帶來的結果進行比較，（也就是說，如果我們使用謂詞date_closed >= add_months(sysdate， -60)，會看到什么?）這個結果集.會大很多。
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依然每個塊持有原來的49行，但是表中的其他塊幾乎沒有被刪除，而是被完全清空。（如果您將第一個輸出中的總塊數與第一個報告中的總塊數進行比較，您會注意到現在肯定有幾個塊(24個塊)是完全空的）現在有多少塊可用來插入?這里有一個快速的計算，我們的大部分塊有49行，占了90%（default pctree = 10），因此，一個塊將下降到75%的標記(即當ASSM將其標記為有空閑空間時)，當它少于41行時(49 * 75 / 90)，在204,000個塊中，大約75,000個符合這個標準(檢查“運行的塊總數”列)
索引空間
上一節展示了一些簡單的SQL，讓您了解了表中將如何顯示空間(或數據將如何保留)-我們可以對索引做類似的事情嗎？答案必然是肯定的。但是，回答“在刪除匹配謂詞X的數據之后，索引會是什么樣子”這個問題的代碼運行起來要比運行表的代碼開銷更大。首先，這里有一段簡單的代碼來檢查索引的當前內容:
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()’的調用將一個表rowid作為它的如數之一，并返回一些類似于塊的第一行的rowid的內容，而該塊的地址是索引葉塊的地址，索引你塊持有表rowid所提供的索引條目。另外兩個參數是索引object_id(如果索是分區的，則是分區或者是子分區)和一個表示函數的特定用法的標志。在這個例子中是“L”。hint在目標索引上使用快速索引掃描是必要的-任何其他路徑都可能返回錯誤的出結果-‘client_ref’不為空是必要的。以確保查詢可以有效的使用index_ffs路徑。
對于我的初始化數據集，索引在每個塊中都有448個索引條目，除了一個（大概是最后一個，192行）。即使這是簡單的查詢也要為了每個索引的要求而精心設計-因為索引快速掃描需要得到正確的結果，這就是我們不得不做一些不同尋常的刪除操作，看看我們大量刪除會怎么影響索引。下面是一個例子，展示我們如何找出試圖刪除5年多前打開的行對client_ref索引產生什么影響。
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 ;
正如您所看到的，我們從一個內聯視（按時不可合并）圖開始將索引塊id附加每個表的rowid上，然后將這組行id連接回表-通過rowid連接并強制進行散列連接。我已經暗示了散列連接，因為它(可能)是最有效的策略，但是盡管我引入了一個leading()提示，但我沒有包含關于交換(或不)連接輸入的提示-我將讓優化器決定這兩個數據集中哪個更小，由此來更適合的構建哈希表。
在這種特殊的情況下優化器能夠使用一個僅索引的訪問路徑來查找date_open 比五年前跟早行的所有rowid。盡管如此(部分原因是我的pga_aggregate_target相對較小，散列連接溢出到(固態)磁盤)，查詢耗時3分15秒，而上一個查詢在緩存整個索引時恰好運行了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個葉塊——這是索引中的每一個葉塊;我們從一個葉塊中刪除的行數從1到266不等。我一次從83行輸出中選擇了幾行，但是您可能仍然可以看到該模式似乎遵循正態分布，以222(50%)為中心。
如果這樣刪除我們應該很清楚，我們將花費大量的精力來更新這個索引；即使這樣，“每個葉塊刪除多少行”這個簡單的數字也不能告訴我們要做的工作的全部內容。我們不知道我們是否會(例如)在同一時間刪除所有266個索引條目從最后一塊上面顯示刪除完成，我們將非常隨機地在索引周圍跳躍式來回，并發現自己不斷地重新訪問該塊，以便一次刪除一個索引條目。因此在下一期中，我們將研究需要考慮工作負載的哪些方面，以及不同的刪除策略如何對工作負載產生重大影響。
譯者: 湯建 原作者: Jonathan Lewis 原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
