當然是嚇瘸了
Friday目前就職于一家外企IT公司，總部是在美國，F(xiàn)riday在中國。得益于時差的關(guān)系，東西兩邊的工作能進行24小時相對無縫的切換協(xié)作。Friday每天的日常就是早上到公司，先回復下對面美國同事的相關(guān)郵件，然后開始一天的開發(fā)任務(wù)，下班前將當天的進度和問題發(fā)回總部確認，如果有必要的話，下班后等美國的同事上班發(fā)起一輪電話會議。
今天，本又是個平平無奇的一天，F(xiàn)riday來到公司開始一天的日常。打開郵件，美國同事要求Friday幫忙刪除一張表的某條索引，因為在此時中國的時間是業(yè)務(wù)每天的低峰時刻，幾乎沒有什么流量?！俺Ｒ?guī)操作”，F(xiàn)riday心道。很快他便寫好了刪除的命令，打開sqldeveloper進行了一波操作，“唔...為什么刪了這么久”，索引刪除了，但是Friday感覺有點不對，這本是一張不大的表，理論上秒秒鐘就可以操作完成了，仔細一看后，F(xiàn)riday心涼了半截 -- 他刪錯索引了！再仔細檢查后，他意識到自己麻煩大了-- 這個誤刪的索引的表有近80億條數(shù)據(jù)（難怪刪除花了不少時間，按照一般的方式，重建肯定要花很久的時間。好在此時是業(yè)務(wù)低谷，否則肯定各種瘋狂告警和被投訴了。
Friday決定先死馬當活馬醫(yī)，立刻進行索引重建，但是情況并不樂觀。通常情況下對oracle的表建立索引的時候并不需要考慮效率問題，這個通常情況指的是相應(yīng)的表數(shù)據(jù)在百萬級以下。一旦數(shù)據(jù)量大到千萬級，億級甚至更大的時候，就不能不考慮新建索引的效率問題，因為當表在建立索引的時候，會產(chǎn)生鎖阻塞新數(shù)據(jù)的更新，如果索引不能很快地建立完畢，會對生產(chǎn)環(huán)境造成影響。而Friday現(xiàn)在面臨的問題是，重建的速度太慢以致于從進度上看預計需要幾十個小時才能完成，而再過8個小時就是美國那邊的上班時間，業(yè)務(wù)的流量高峰也會相應(yīng)到來，想到這里Friday感覺自己有點窒息了?！耙欢ㄓ修k法的”，F(xiàn)riday定了定神開始想對策。
“1. 正在執(zhí)行的重建索引繼續(xù)讓它執(zhí)行，如果所有辦法失效，這就是最后的保障?！?br>
“2. 調(diào)查一下索引刪除的影響范圍，應(yīng)該不至于整個系統(tǒng)不可用。” Friday迅速過了一下代碼，果然這張表只在一個統(tǒng)計類的功能里用到了，不屬于核心功能范圍，即使暫時一段時間不可用應(yīng)該也影響不大。(真的嗎? 下文分析)
“3. 馬上找優(yōu)化重建索引效率的辦法，爭取在4小時內(nèi)重建完成”
“4. 要不要報告下領(lǐng)導...算了先瞞著...不行了再說” (呵呵...)
計算機的世界是一套嚴密的邏輯體系，凡事都是有跡可循，因果關(guān)系貫穿始終。一旦掌握原理，就可以扮演上帝進行法則的干預。想要優(yōu)化建索引的效率，首先看創(chuàng)建索引到底做了些什么操作。
- 把索引key的數(shù)據(jù)讀到內(nèi)存
如果數(shù)據(jù)沒在db_cache 中，這時候很容易有大量的db file scatter read wait
- 對索引key的數(shù)據(jù)作排序
sort_area_size 或者pga_aggregate_target 不夠大的情況下，需要做 disk sort, 會有大量的driect path read/write , 另外，消耗大量CPU Time
- 創(chuàng)建新的index segment ,把排過序的索引數(shù)據(jù)寫到所創(chuàng)建的index segment 里面.
如果index 很大，有時會有redo log 相關(guān)等待
那么，只要對這幾個方向進行相應(yīng)的優(yōu)化，就有可能加速整體的效率。經(jīng)過一番研究，F(xiàn)riday得出結(jié)論： 增大pga，增大temp tablepace, 并且用nologging或并行選項。
救火方案有了，看著依舊在龜速重建的進度，F(xiàn)riday決定直接用新的方案進行嘗試，畢竟不會更差了，而且也沒有環(huán)境和時間進行模擬。還好這次幸運女神站在了Friday這邊，優(yōu)化后只用了20分鐘就將索引重建完畢，一切又回到了正軌。
Friday這一天過得驚心動魄，他事后進行復盤覺得自己實在是犯了一個非常低級的錯誤，以致于差點就長跪不起，今后一定要仔細檢查，避免再犯。
然而我們更深入地進行思考，事情其實并沒有這么簡單。
Friday 作為一個開發(fā)人員，為何對數(shù)據(jù)庫有如此高的權(quán)限，以至于DBA都沒有什么存在感
一個“危險”的DDL操作完全沒有被審核的操作，DML那就更不用說了。
-
故障發(fā)生后，F(xiàn)riday并沒有第一時間上報，而是主觀憑自己的經(jīng)驗進行了評估，并企圖自己修復掩蓋失誤，雖然最后的結(jié)果是成功了，但并不足取。原因有幾:
- 由于經(jīng)驗、技術(shù)能力和視野所限，開發(fā)并不一定能正確評估風險。比如：Friday認為這個故障只是影響了部分小眾功能，問題不大，但是實際上，只要有足夠的流量，沒有索引的慢查詢是足以拖死整個系統(tǒng)的，因為慢查詢會占用數(shù)據(jù)庫的連接，不能及時釋放，當連接數(shù)用完后，其他正常的功能就無法連接數(shù)據(jù)庫，導致整體不可用。其二，F(xiàn)riday的視野只是局限在自己的代碼上，也許上報leader后他會發(fā)現(xiàn)，這個表還被其他工程甚至其他團隊共用，而這其中的風險并沒有評估到
- 能獨立解決問題是能力強的表現(xiàn)，但是面對線上故障，群策群力更有助于問題的解決。如果有專業(yè)DBA的幫助，可能會更快，更低風險地解決故障。而如果因為對自己能力的估計過于樂觀進行冒失操作，可能會引起更大的風險和故障。
故障的發(fā)生有時候不是一人或是一事的得失，這是一個系統(tǒng)性的問題。一個良好的系統(tǒng)應(yīng)該是可以規(guī)避風險，甚至自愈的。Friday 可能是個救火能手，但是對于系統(tǒng)上層建筑的機制完善此刻還沒有足夠的意識。
附 oracle大表加速索引建立的實操手冊
1. PGA設(shè)置
hash_area_size： 這個參數(shù)控制每個會話的hash內(nèi)存空間有多大。它也可以在會話級和實例級被修改。默認值是sort area空間大小的兩倍 sort_area_size: 因為排序通常是在PGA中進行的，需要防止因空間或內(nèi)存不足導致需要disk排序。
alter session set workarea_size_policy=manual;
alter session set hash_area_size=100000;
alter session set sort_area_size=2000000000; -- 在系統(tǒng)可用內(nèi)存足夠的情況下，最大可以到2G
question
- 什么是PGA
- 什么是SGA
2. 增加temp表空間
因為大表的數(shù)據(jù)量比較大，導致建索引時需要的temp表空間也比較大，一般來說接近索引的大小，沒把握的情況下可以估算一下索引的大小先：
另外在建索引的過程中也可以隨時監(jiān)控表空間的使用情況，一旦發(fā)現(xiàn)temp表空間不夠，可以隨時加大
set serveroutput on
declare
v_ddl varchar(1024);
v_used_bytes number;
v_alloc_bytes number;
begin
dbms_space.create_index_cost(
ddl =>' create index ids_t on user(userid)',used_bytes=>v_used_bytes,alloc_bytes =>v_alloc_bytes);
dbms_output.put_line('used_bytes='||v_used_bytes||' bytes'||' alloc_bytes='|| v_alloc_bytes || ' bytes');
end;
/
3. 使用并行參數(shù)
關(guān)于利用并行度創(chuàng)建索引，前提是多個CPU，單CPU下用并行度創(chuàng)建索引，可能會造成資源的爭用。理論上來說8個CPU, 可以用parallel 6 ,最多占用6個CPU,另外留下兩個CPU供其他進程使用。 查看CPU核數(shù)的方法有很多, 最簡單地就是用下面這個sql直接查
SELECT * FROM v$osstat where stat_name='NUM_CPUS';
4. 使用nologging
nologging, 絕對應(yīng)該使用，能減少大量redo log，使速度大幅上升。
于是一個比較標準的并行nologging建索引語句就出爐了。對于生產(chǎn)環(huán)境，保險的辦法是再加上online參數(shù)，避免建索引時的鎖對dml產(chǎn)生阻塞。
CREATE INDEX table_idx ON table (col ) NOLOGGING PARALLEL 6;
Note
對于一個比較大的操作，oracle可能會有等待事件發(fā)生 首先可以通過sql developer查看等待時間的信息，得到等待時間的p1，p2，p3。然后通過下面的sql轉(zhuǎn)換p1，p2得到具體等待的object。
select owner,segment_name,segment_typefrom dba_extentswhere file_id = &P1 and &P2 between block_id and block_id + blocks -1;
監(jiān)控oracle 大事務(wù)的執(zhí)行進度
set linesize 400;
set pagesize 400;
set long 4000;
col sql_fulltext format a100;
col opname format a15;
SELECT se.sid,
opname,
TRUNC (sofar / totalwork * 100, 2) pct_work,
elapsed_seconds elapsed,
ROUND (elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
sql_fulltext
FROM v$session_longops sl, v$sqlarea sa, v$session se
WHERE sl.sql_hash_value = sa.hash_value
AND sl.sid = se.sid
AND sofar != totalwork
ORDER BY start_time;
