背景

最近開發(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)生大量碎片。

• 執(zhí)行消耗對(duì)比：

• 等待事件
  

總結(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í)行效果。