問題背景:

????????應用運維報障說最近兩天業(yè)務數(shù)據(jù)入庫和表查詢都變得很慢，需要排查一下數(shù)據(jù)庫的性能問題

問題分析：

????????登錄到服務器上，通過TOP命令快速看了一下，服務器整體的CPU使用%usr不算特別高，但%wa IO等待很高，懷疑有可能是數(shù)據(jù)庫存在大量的IO操作語句導致服務器IO負載升高

????????查詢數(shù)據(jù)庫的負載dbtime時間，可以看到數(shù)據(jù)庫的負載明顯變高，平常只有幾百的dbtime值，從2024年1月1日13點左右之后開始飆升，最高達到7000+

????????對比1月1日13點前后的等待事件情況，發(fā)現(xiàn)13點之后的IO等待事件db file sequential read以及direct path read等待事件明顯多了幾個數(shù)量級，等待事件的類型與看到的服務器IO等待負載升高匹配，可以確認數(shù)據(jù)庫肯定存在大量的IO操作

select event,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where  to_char(sample_time,'yyyymmdd hh24:mi:ss')>='20240101 13:00:00'
and to_char(sample_time,'yyyymmdd hh24:mi:ss')<='20240102 16:00:00'
group by event 
order by 2;

????????正常時間段

?文章來源地址http://www.zghlxwxcb.cn/news/detail-807003.html

????????性能緩慢，IO等待高時間段

?

????????進一步查看IO等待事件db file sequential read以及direct path?read每小時的增長趨勢，可以看到在13點之后，IO等待事件出現(xiàn)了顯著的增長

select to_char(sample_time,'yyyymmdd hh24'),event,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where   event in('direct path read','db file sequential read')
group by to_char(sample_time,'yyyymmdd hh24'),event
order by 1;

?

????????分析IO等待事件引發(fā)的sql語句,可以看到TOP主要有4條語句sql:8aq5bt0k6jb4d、3s559a2uc2xk0、3nd18t4tmv0mz、25dy6q436ch3k

select sql_id,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where  to_char(sample_time,'yyyymmdd hh24:mi:ss')>='20240101 13:00:00'
and to_char(sample_time,'yyyymmdd hh24:mi:ss')<='20240102 16:00:00'
and event in ('db file sequential read','direct path read')
having count(*)>50000
group by sql_id 
order by 2;

?

????????分析第一條SQL:8aq5bt0k6jb4d

????????查看語句的執(zhí)行計劃，可以看到語句的執(zhí)行計劃發(fā)生了改變，在1月1日13點生成了一個高消耗的執(zhí)行計劃，執(zhí)行次數(shù)628次，平均消耗時間6583秒,邏輯讀和物理讀次數(shù)都很高平均100W+，而正常的執(zhí)行計劃執(zhí)行時間都在10秒以內(nèi)，,物理讀次數(shù)在1000次以內(nèi)

????????從執(zhí)行計劃的生成時間與數(shù)據(jù)庫負載變高時間一致以及語句的邏輯讀和物理讀消耗，基本可以確認數(shù)據(jù)庫的性能緩慢與這個語句有關

?

????????分析語句的高消耗執(zhí)行計劃，語句里面唯一的大表TAB_MR，大小157628MB，作為了nested loop的驅(qū)動表，并且執(zhí)行的路徑是全表分區(qū)范圍掃描

?

?

????????表TAB_MR根據(jù)時間條件t.syn_time查詢數(shù)據(jù)

?

????????跟正常的執(zhí)行計劃對比，可以看到正常的執(zhí)行計劃是使用到了分區(qū)本地索引IDX_TAB_MR_SYN_TIME，而不是全表分區(qū)范圍掃描

?

????????檢查分區(qū)表以及索引的統(tǒng)計信息，都發(fā)現(xiàn)1月份之后的統(tǒng)計信息與實際表的數(shù)據(jù)差異很大，實際一天的分區(qū)有幾十萬的數(shù)據(jù)

--查看分區(qū)表的統(tǒng)計信息
select table_owner, table_name, partition_name, AVG_ROW_LEN ,num_rows, blocks*8/1024 size_m,last_analyzed
from dba_tab_partitions where table_name='SALES_LIST'
--查看索引的統(tǒng)計信息
select owner,index_name,PARTITION_NAME,SUBPARTITION_NAME,LEAF_BLOCKS,DISTINCT_KEYS,LAST_ANALYZED
from dba_ind_statistics
where table_owner='TEST' and table_name='SALES_LIST';

?

????????查看語句傳入的綁定變量值，發(fā)現(xiàn)傳入的都是查詢最新1月份的數(shù)據(jù)，可以判斷執(zhí)行計劃選錯執(zhí)行路徑的原因為統(tǒng)計信息的不準確導致優(yōu)化器估算出現(xiàn)錯誤，選擇了全表掃描的路徑

?

????????分析第二條SQL:3sruu5v9gtysg

????????語句是一條正常的普通insert語句，執(zhí)行計劃沒有可以分析的，查看語句的執(zhí)行消耗變化情況，可以看到在13點之后，語句的IO_WAIT等待時間變得很高，可以判斷語句應該是受到系統(tǒng)負載IO變慢所影響的

?

????????分析第三條SQL:3sruu5v9gtysg

????????跟第二條語句類似，在執(zhí)行計劃沒有變化的情況下，語句的IO_WAIT等待時間變得很高，語句同樣也是受到系統(tǒng)負載IO變慢所影響的

?

????????分析第四條SQL:3sruu5v9gtysg

????????查看語句的執(zhí)行計劃，可以看到語句的執(zhí)行計劃也發(fā)生了改變，在1月1日13點生成了一個高消耗的執(zhí)行計劃，執(zhí)行次數(shù)7149次，平均消耗時間7.374秒,物理讀和邏輯讀比起正常的執(zhí)行計劃消耗都增長了很多，而正常的執(zhí)行計劃執(zhí)行時間都在0.0005秒以內(nèi)，平均只有10次的邏輯讀

????????查看語句的執(zhí)行計劃，跟第一條語句是同一張表語句TAB_MR，同樣的問題，優(yōu)化器選錯了執(zhí)行的路徑進行全表單分區(qū)范圍掃描

????????分析完這4條TOP SQL可以對問題做個總結(jié)了，數(shù)據(jù)庫從2024年1月1日13點左右負載明顯升高，主要的負載為IO操作，IO操作負載升高的原因為大表TAB_MR的2024年1月份之后的分區(qū)統(tǒng)計信息不準確，導致涉及1月份數(shù)據(jù)的查詢SQL生成了錯誤的高消耗全表分區(qū)掃描執(zhí)行計劃，產(chǎn)生了大量的物理讀以及邏輯讀，最終引發(fā)了整個數(shù)據(jù)庫的性能下降，業(yè)務數(shù)據(jù)入庫和表查詢都變慢

問題解決：

????????對大表TAB_MR1月份的分區(qū)單獨收集統(tǒng)計信息后，語句的執(zhí)行計劃恢復了正常，數(shù)據(jù)庫的IO負載也降下來、

其他問題:

????????最后還有一個問題就是關于統(tǒng)計信息收集的，數(shù)據(jù)庫是有開啟默認的自動統(tǒng)計信息收集的，單個分區(qū)的數(shù)據(jù)變化量也超過了10%，為什么表的統(tǒng)計信息到1月2號還沒有更新

?

?

????????查看統(tǒng)計信息job的執(zhí)行記錄，可以看到2024年1月1日的統(tǒng)計信息收集在晚上22點有正常的開始執(zhí)行，但是最后統(tǒng)計信息收集的job由于4個小時的執(zhí)行窗口時間已到，job被迫暫停了（REASON="Stop job called because associated window was closed"），也就是任務有跑，但沒收集完成

????????注:周一到周五默認統(tǒng)計信息收集窗口4個小時，周六周日默認統(tǒng)計信息收集窗口20個小時

????????通常統(tǒng)計信息沒有在4個小時窗口執(zhí)行完成的可能原因有1 數(shù)據(jù)庫要收集的表數(shù)據(jù)量過大 2 數(shù)據(jù)庫的性能出現(xiàn)問題，導致收集緩慢 3 統(tǒng)計信息收集的并行度不合理，導致收集速度過慢 4 Oracle的bug，結(jié)合統(tǒng)計信息收集的歷史完成時間都在2小時以內(nèi)以及收集時間段存在IO負載高的問題，判斷統(tǒng)計信息收集還是受到數(shù)據(jù)庫的性能下降所影響

?

?

?

?

?

?

?

?

?

到了這里，關于Oracle-數(shù)據(jù)庫性能變慢問題分析的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！