背景
最近開發(fā)反饋有個(gè)任務(wù)偶爾性的執(zhí)行變慢,平時(shí)都是10分鐘左右完成,偶爾1小時(shí)完成。如果超過2小時(shí)的話可能就會(huì)影響業(yè)務(wù)后續(xù)進(jìn)度,因此讓我排查一下或優(yōu)化一下SQL。
- 查看任務(wù)執(zhí)行日志如下:
select start_time 創(chuàng)建日期,end_time 結(jié)束日期,
to_char(TRUNC(sysdate) + (end_time - start_time), 'HH24:MI:SS') 執(zhí)行耗時(shí)
from summary_log l
order by 1 desc;
- 確實(shí)快的時(shí)候2-3分鐘完成,慢的時(shí)候1小時(shí)+還沒有跑完

- SQL 脫敏如下:
INSERT INTO T1
(......)
SELECT
......
FROM T1 A
LEFT JOIN (SELECT
......
FROM T2
WHERE CREATE_DATE >= to_date('2025-07-27','yyyy-mm-dd')
AND CREATE_DATE < to_date('2025-07-28','yyyy-mm-dd')
GROUP BY ACCT_ID) SUBQUERY
ON SUBQUERY.ACCT_ID = A.ACCT_ID
LEFT JOIN T2 B ON B.ACCT_ID = A.ACCT_ID
WHERE A.CREATE_DATE >= to_date('2025-07-26','yyyy-mm-dd')
AND A.CREATE_DATE < to_date('2025-07-27','yyyy-mm-dd')
AND B.STATUS= 0;
排查分析
根據(jù)sql_id 生成執(zhí)行計(jì)劃報(bào)告
- 手動(dòng)執(zhí)行腳本
@?/rdbms/admin/awrsqrpt.sql
- 默認(rèn)生成html格式

- 選擇天數(shù)

- 選擇Snap ID

- 填寫SQL_id:4af6vusvps6cu

查看20250726(00:10:43)、20250727(00:54:09)、20250728(00:12:42)、20250731(01:01:10)的AWR_SQL報(bào)告
- awr_sql 性能瓶頸發(fā)現(xiàn)在IO

- TABLE ACCESS FULL 與 NDEX RANGE SCAN的執(zhí)行結(jié)果都有快與慢的時(shí)候,因此分析與執(zhí)行計(jì)劃無關(guān)。

生成ash報(bào)告
- 手動(dòng)執(zhí)行腳本
@?/rdbms/admin/ashrpt.sql
- 發(fā)現(xiàn)在執(zhí)行慢的ash報(bào)告中,SQL引發(fā)的db file sequential read等待事件占比30-40%左右

- db file sequential read
Oracle數(shù)據(jù)庫中一種常見的I/O相關(guān)等待事件,表示數(shù)據(jù)庫進(jìn)程正在從數(shù)據(jù)文件中順序讀取數(shù)據(jù)塊到SGA(系統(tǒng)全局區(qū))中。遇到的案例:Insert 引起的 db file sequential read有細(xì)節(jié)介紹。 - 查看引發(fā)此事件的對(duì)象
select o.OBJECT_NAME,h.event,h.sql_opname,h.sql_plan_operation,h.machine,h.SAMPLE_TIME
from dba_hist_active_sess_history h
join dba_objects o on o.OBJECT_ID=h.current_obj#
where h.SAMPLE_TIME >= to_date('2025-08-07 10:30:00', 'yyyy-mm-dd hh24:mi:ss')
and h.SAMPLE_TIME <= to_date('2025-08-07 11:30:00', 'yyyy-mm-dd hh24:mi:ss')
AND h.sql_id='4af6vusvps6cu';

Insert操作引發(fā)db file sequential read
雖然insert操作本身主要是寫入操作,但在以下情況下會(huì)引發(fā)db file sequential read等待事件:
1、索引維護(hù)開銷
當(dāng)表上有索引時(shí),insert操作需要同時(shí)維護(hù)所有相關(guān)索引,這會(huì)導(dǎo)致:
- 數(shù)據(jù)庫需要查詢索引塊以確定新記錄的插入位置;
- 每次索引塊查詢都會(huì)產(chǎn)生單塊讀取,表現(xiàn)為db file sequential read;
- 索引分裂(當(dāng)索引塊空間不足時(shí))會(huì)產(chǎn)生額外的單塊讀取;
2、高并發(fā)insert場(chǎng)景
在高并發(fā)insert環(huán)境下:
- 多個(gè)會(huì)話同時(shí)維護(hù)相同索引會(huì)導(dǎo)致索引塊訪問沖突;
- 索引塊可能頻繁從磁盤讀取到SGA,增加db file sequential read等待;
3、索引碎片問題
當(dāng)索引存在嚴(yán)重碎片時(shí):
- 索引維護(hù)需要訪問更多分散的索引塊;
- 增加單塊讀取的次數(shù)和等待時(shí)間;
- 行遷移和行鏈接也會(huì)導(dǎo)致額外的單塊讀取;
模擬生產(chǎn)環(huán)境
- 在測(cè)試環(huán)境對(duì)T1表進(jìn)行歷史數(shù)據(jù)恢復(fù),然后保留1年內(nèi)的基礎(chǔ)數(shù)據(jù),其它歷史數(shù)據(jù)進(jìn)行truncate partition update indexes 操作,使索引產(chǎn)生大量碎片。
| 表名 | 索引 | 實(shí)際大小GB | 產(chǎn)生碎片后大小GB | 碎片率 |
|---|---|---|---|---|
| T1 | IDX_T1_ID | 12.01 | 40.01 | 70% |
| T1 | IDX_T1_CUST | 7.6 | 7.3 | - |
| T1 | SYS_C00722650 | 15.38 | 48.14 | 68% |
- 執(zhí)行消耗對(duì)比:
| 性能指標(biāo) | 無碎片 | 有碎片 |
|---|---|---|
| Elapsed | 00:01:01.73 | 00:15:38.00 |
| recursive calls | 187 | 33 |
| db block gets | 6,103,959 | 3,407,341 |
| consistent gets | 1,311,473 | 818,618 |
| physical reads | 977,810 | 1,384,066 |
| redo size | 1,696,362,588 | 587,160,312 |
| rows processed | 644,921 | 644,921 |
- 等待事件

總結(jié)
根據(jù)以上模擬實(shí)驗(yàn),基本可以肯定是因?yàn)樗槠瑢?dǎo)致insert 產(chǎn)生的db file sequential read(單塊讀)從而引起SQL的偶發(fā)性變慢。
- 碎片分析
將生產(chǎn)表通過expdp/impdp操作導(dǎo)入到測(cè)試庫中查詢其索引大小,然后與生產(chǎn)對(duì)比大小
SELECT a.owner, b.table_name,b.INDEX_NAME, round(a.GB,2) GB
FROM (SELECT OWNER,
SEGMENT_NAME,
TABLESPACE_NAME,
(SUM(BYTES) / (1024 * 1024 * 1024)) AS GB
FROM DBA_SEGMENTS
GROUP BY OWNER, SEGMENT_NAME, TABLESPACE_NAME
) a
left join ALL_INDEXES b on a.SEGMENT_NAME = b.INDEX_NAME
where b.table_name IN ('T1')
order by 2,3,4

- 最終對(duì)涉及到的表進(jìn)行索引重建完成碎片整理,后續(xù)繼續(xù)觀察執(zhí)行效果。
歡迎贊賞支持或留言指正

「喜歡這篇文章,您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容,轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容,歡迎發(fā)送郵件至:contact@modb.pro進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),墨天輪將立刻刪除相關(guān)內(nèi)容。




