一、背景：
??業(yè)務(wù)核心數(shù)據(jù)庫每月定期做數(shù)據(jù)裁剪（保留一年數(shù)據(jù)），表中的索引存在大量的碎片。最近業(yè)務(wù)低迷，因此想為索引rebuild整理碎片。
??因涉及的表都是業(yè)務(wù)訂單大表（分區(qū)表），涉及分區(qū)索引，每個(gè)分區(qū)索引操作均為20-30分鐘不等。為了不影響生產(chǎn)DML操作，采用rebuild online 方案操作。
??自己寫了一個(gè)腳本，將拼接好的rebuild online SQL文本放在sh 腳本執(zhí)行，使其24小時(shí)執(zhí)行。當(dāng)時(shí)考慮業(yè)務(wù)本身是平時(shí)的1/10，又加了online 操作，因此不會(huì)影響生產(chǎn)。
??沒想到在腳本在正常執(zhí)行一周后，在周六上午8：50左右系統(tǒng)組收到應(yīng)用連接異常增長的報(bào)警，在9：20時(shí)應(yīng)用日志及我的rebuild SQL腳本同時(shí)報(bào)錯(cuò)：
|
ERROR at line 1: ORA-04021: timeout occurred while waiting to lock object |
二、數(shù)據(jù)庫現(xiàn)象
??收集8:50至9：20的ash報(bào)告，顯示存在大量的由業(yè)務(wù)SQL引起的cursor: pin S wait on X 及 library cache lock等待事件
??看到這個(gè)ash報(bào)告后當(dāng)時(shí)想的應(yīng)該是應(yīng)用之間產(chǎn)生的鎖之間的掙用導(dǎo)致了應(yīng)用和我執(zhí)行rebuild腳本同時(shí)出現(xiàn)了ORA-04021報(bào)錯(cuò)。
??后從來dba_hist_active_sess_history視圖里查詢8：00至9：20的等待事件中發(fā)現(xiàn)571與643兩個(gè)會(huì)話有相關(guān)阻塞的現(xiàn)象，并且643第一次產(chǎn)生library cache lock等待事件
– 查詢視圖SQL
select instance_num,sample_time,session_id,h.sql_id,h.sql_exec_start,event,a.SQL_TEXT,h.blocking_session,h.blocking_session_status
from dba_hist_active_sess_history h
left join v$sqlarea a on a.sql_id=h.sql_id
where
sample_time > to_date('****-**-** 08:00', 'yyyy-mm-dd hh24:mi')
and sample_time < to_date('****-**-** 09:20', 'yyyy-mm-dd hh24:mi')
and h.sql_id is not null
order by 2;
下圖為后期測試截圖：當(dāng)時(shí)571執(zhí)行的SQL_TEXT為：dbms_stats.gather_database_stats_job_proc()
三、分析結(jié)論
??回想當(dāng)天數(shù)據(jù)庫操作：
??1、數(shù)據(jù)庫每月的數(shù)據(jù)裁剪剛好是今天凌晨執(zhí)行05點(diǎn)左右執(zhí)行完成。
??2、數(shù)據(jù)庫的自動(dòng)維護(hù)任務(wù)為周六日從晚上10點(diǎn)開始執(zhí)行20小時(shí)。
??3、索引rebuild online 腳本24小時(shí)執(zhí)行。
??因此每月的數(shù)據(jù)裁剪操作為truncate 分區(qū)，觸發(fā)了數(shù)據(jù)庫的自動(dòng)維護(hù)任務(wù)的統(tǒng)計(jì)收集該表的信息，然后表的統(tǒng)計(jì)收集信息可能導(dǎo)致原統(tǒng)計(jì)信息失效 （此為猜測，歡迎大神指正），使用rebuild 語句產(chǎn)生library cache lock事件與收集信息的會(huì)話產(chǎn)生相互阻塞的鎖等待。同時(shí)使用業(yè)務(wù)應(yīng)用設(shè)計(jì)該表的查詢也產(chǎn)生了大量的library cache lock 事件。
??最終結(jié)論：rebuild online 索引需要避開數(shù)據(jù)庫的自動(dòng)維護(hù)任務(wù)或收集統(tǒng)計(jì)信息操作。
Oracle 自動(dòng)收集統(tǒng)計(jì)信息機(jī)制：http://www.sunline.cc/db/403670
Oracle_索引重建—優(yōu)化索引碎片：http://www.sunline.cc/db/399543
??????????????????????歡迎點(diǎn)贊支持&或留言指正錯(cuò)誤
