博客 / 詳情

返回

技術分享 | Oracle SQL執行頻率問題與優化效果預期

本文為墨天輪數據庫管理服務團隊第143期技術分享，內容原創，作者為技術顧問李寧，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

案例一：SQL執行頻率太高 - 業務邏輯

1. 問題 SQL

有三個SQL執行次數太高，8M的表一天能有 110TB 的邏輯讀，猜測應該在一個業務邏輯

涉及表：JUDGE\_TASK （8M）、JUDGE\_TASK\_HIS （歸檔表，52M）

status 字段：0-未處理，1-處理成功，2-處理中，9-處理失敗

SQL 及執行情況：

1）85m3gxkjzaqkt

SELECT INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATE 
FROM JUDGE_TASK  
WHERESTATUS = '0'
ANDROWNUM <= 500
ORDERBY CREATEDATE;
  • 業務邏輯：查詢 JUDGE\_TASK 任務表 中還 未處理 的任務，返回500行按創建時間排序
  • 執行頻率：每30分鐘執行 126,508 次，≈ 每秒 70 次，早晚都是 TOP SQL
  • 平均邏輯讀：1000 塊次，一天產生邏輯讀：≈ 58.7 億塊次 ≈ 43.73 TB

2）dnbf276hzw04k

INSERTINTO JUDGE_TASK_HIS(INST_ID,DTYPE,STATUS,NODEID,ERRMSG,CREATEDATE,EXECUTEDATE) 
SELECT INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATE 
FROM JUDGE_TASK  
WHERESTATUS='1'ORSTATUS='9';
  • 業務邏輯： 將 JUDGE_TASK 任務 處理成功 和 處理失敗 的記錄插入到 JUDGE_TASK_HIS 表（歸檔）
  • 執行頻率：每30分鐘執行 126,599 次，≈ 每秒 70 次，早晚都是 TOP SQL
  • 平均邏輯讀：1000 塊次，一天產生邏輯讀：≈ 58.7 億塊次 ≈ 43.73 TB

3）cyypmad5n2b1m

DELETEFROM JUDGE_TASK  
WHERESTATUS='1'ORSTATUS='9';
  • 業務邏輯：刪除已處理任務（成功+失敗，已歸檔）
  • 執行頻率：每30分鐘執行 125,046 次，≈ 每秒 70 次
  • 平均邏輯讀：1000 塊次，一天產生邏輯讀：≈ 37.18 億塊次 ≈ 27.7 TB

每個月只處理幾千條任務，但是三條 SQL 加起來，單表每天邏輯讀 110TB+，遠超正常業務規模。並且JUDGE_TASK 15天僅有 60條 insert，每天需要執行六百萬次，查詢/歸檔/刪除過於頻繁，屬於 業務邏輯設計不合理。

SQL>selectstatus,count(*)fromPANDA.JUDGE_TASKgroupbystatusorderbycount(*)desc;

norowsselected

SQL>select*fromPANDA.JUDGE_TASK;

norowsselected

SQL>@segPANDA.JUDGE_TASK

SEG_MBOWNERSEGMENT_NAMESEG_PART_NAMESEGMENT_TYPESEG_TABLESPACE_NAMEBLOCKSHDRFILHDRBLK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8PANDAJUDGE_TASKTABLEDATA9991024        5441385609
52PANDAJUDGE_TASK_HISTABLEDATA9996656        5441874569

2rowsselected.

--歷史記錄
SQL>selectcount(*)fromPANDA.JUDGE_TASK_HIS;

COUNT(*)
----------
667816
--估算每月任務量
SELECTTO_CHAR(TRUNC(EXECUTEDATE,'MM'),'YYYY-MM')ASmonth,
COUNT(*)AScnt
FROMPANDA.JUDGE_TASK
GROUPBYTRUNC(EXECUTEDATE,'MM')
ORDERBYmonth;
MONTHCNT
12018-09    541
22018-10    1417
32018-11    7414
42018-12    5875
52019-01    4118
......
742024-10    5059
752024-11    6235
762024-12    8751
772025-01    10422
782025-02    10459
792025-03    1096
802025-04    2199
812025-05    3490
822025-06    4910
832025-07    2480


--根據sql_id估算一天產生的邏輯讀
SELECTAVG(BUFFER_GETS)*24/10,ROUND((AVG(BUFFER_GETS)*24/10*8192)/POWER(1024,4),2)ASLOGICAL_READ_TB_24H
FROMDBMT.GETS_STAT_HISTWHERE
SNAP_TIME<=TO_DATE('20250723','yyyymmdd')
--ANDINSTANCE_NUMBER=1
ANDsql_id='85m3gxkjzaqkt';


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_IDdnbf276hzw04k,childnumber0
-------------------------------------
INSERTINTOJUDGE_TASK_HIS(INST_ID,DTYPE,STATUS,NODEID,E
RRMSG,CREATEDATE,EXECUTEDATE)SELECT
INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATEFROM
JUDGE_TASKWHERESTATUS='1'ORSTATUS='9'

Plan hash value:3244728187

-----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                          |       |       |   223 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | JUDGE_TASK_HIS |||||
|*2|TABLEACCESSFULL|JUDGE_TASK|822|183K|223(1)|00:00:01|
-----------------------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

2-filter(("STATUS"='1'OR"STATUS"='9'))


22rowsselected.


--索引信息
TABLETABLEIndexCOLUMNCol
OWNERNAMENameUCPTDVSNAMEPosDESC
----------------------------------------------------------------------------------------------
PANDAJUDGE_TASKIDX_INST_ID_STATUSNNNNNVYINST_ID1ASC
NNNNNVYSTATUS2ASC
JUDGE_TASK_HISIDX_INST_ID_STATUS_HISNNNNNVYINST_ID1ASC
NNNNNVYSTATUS2ASC

PLANGETDISKWRITEROWSROWSUSER_IO(MS)ELA(MS)CPU(MS)CLUSTER(MS)PLSQL
END_TIINAMEHASHVALUEEXECPREEXECPREEXECPEREXECROW_PPREEXECPREFETCHPEREXECPREEXECPREEXECPEREXECPEREXEC
--------------------------------------------------------------------------------------------------------------------------------------------------------------
26111PANDA324472818711.W1,0090000002,2301,41000
26111PANDA324472818711.W1,0090000002,2371,41300
26121PANDA324472818712.W1,0090000002,1641,32800
26121PANDA324472818712.W1,0090000002,1551,33500
26131PANDA324472818712.W1,0090000001,9611,11800
26131PANDA324472818712.W1,0090000002,1361,31700
26141PANDA324472818712.W1,0090000002,0551,22600
26141PANDA324472818712.W1,0090000002,0951,25200
26151PANDA324472818712.W1,0090000002,0721,22800
26151PANDA324472818711.W1,0090000002,2201,39700
......

2. 優化實施

（1）業務邏輯優化（首選方案）

  • 降低執行頻率：建議 30s~1min 執行一次任務處理或者重構一下將相關SQL模塊封裝出去，而不是毫秒級頻繁執行。
  • 合理批量處理：判斷任務優先級，如果不要求實時，可以批量歸檔/刪除，而非頻繁小批量。

猜測業務邏輯如下：

檢查status='0'是否有任務未處理-> 處理任務 -> 處理成功和失敗的（STATUS='1' OR STATUS='9'）任務歸檔在JUDGE_TASK_HIS->刪除JUDGE_TASK任務記錄（STATUS='1' OR STATUS='9'）

（2）數據庫層面優化（預備方案）

  • 在 STATUS 字段建立索引（或組合索引）：
CREATEINDEX PANDA.JUDGE_TASK_status 
ON PANDA.JUDGE_TASK(status);

訪問路徑優化，將目前執行計劃 TABLE ACCESS FULL，建索引後預期轉為 INDEX RANGE SCAN，邏輯讀可由 ~658 降至幾十。

3. 優化效果（預期）

很簡單的一個業務每天能有110TB的邏輯讀，全庫每天才產生1500TB左右。但生產上還未優化，該業務不屬於核心，在一個沒見過的用户下，業務還在定位，預期效果邏輯讀是從 110TB+/天 → <1TB/天。

案例二：SQL執行頻率太高 - 4M 表進AWR TOP SQL

1. 問題 SQL

表 PANDA.ZONE\_LIST 僅 13 行數據，核心表，頻繁訪問。每分鐘執行9122次，每次執行0.17ms,邏輯讀70塊次，一天概產生讀13.18TB數據。

SELECTT.ZONE
FROMZONE_LISTT
WHERET.PARTITION=:1;

--測試
SQL>SELECTT.ZONEFROMZONE_LISTTWHERET.PARTITION='G';

Elapsed:00:00:00.00

ExecutionPlan
----------------------------------------------------------
Plan hash value:3167746917

---------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |     6 |    28   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ZONE_LIST |1|6|28(0)|00:00:01|
---------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

1-filter("T"."PARTITION"='G')


Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
70consistentgets
0physicalreads
0redosize
549bytessentviaSQL*Nettoclient
421bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed

2. 優化實施

（1）索引優化--(數據庫層優化建議)

為查詢條件建立唯一索引 (PARTITION, ZONE)，優化前執行計劃走TABLE ACCESS FULL，Consistent gets = 70，優化後執行計劃走INDEX RANGE SCAN UN_ZONE_LIST_PARTGConsistent gets = 1 邏輯讀從 70 → 1，降幅 98.5%。

---創建索引
createUNIQUE index PANDA.UN_ZONE_LIST_PARTG on PANDA.ZONE_LIST(PARTITION,ZONE);


SQL>SELECT T.ZONE FROM ZONE_LIST T  WHERE T.PARTITION = 'G';

Elapsed: 00:00:00.01

ExecutionPlan
----------------------------------------------------------
Planhash value: 586154005

-----------------------------------------------------------------------------------------
|Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|0 | SELECT STATEMENT |                      |     1 |     6 |     1   (0)| 00:00:01 |
|*1 |  INDEX RANGE SCAN| UN_ZONE_LIST_PARTG |     1 |     6 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):
---------------------------------------------------

1- access("T"."PARTITION"='G')


Statistics
----------------------------------------------------------
0recursive calls
0db block gets
1consistent gets
0physical reads
0redo size
549bytes sent via SQL*Net to client
421bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed

（2）應用層面優化建議

現在主要矛盾是應用層頻繁請求數據庫，應用層-數據庫層面有一個東西可以解決這一問題-中間件緩存，對於熱點數據，可以放入緩存中間件 比如 Redis，避免頻繁訪問 DB。案例二是一個只有select的表，比較好修改，案例一也可以引用這種方法，但會涉及到緩存一致性問題，一致性越強代碼越不好改動。

但其實除了應用層，Oracle也提供了兩種方法，一個是KEEP Buffer Pool， 專為“熱點小表”準備的一塊獨立內存區，啓用後這些表的數據塊不再被 LRU 算法換出，從而避免反覆磁盤 I/O，提高訪問速度。上面方式可以降低物理讀，對於熱點表邏輯讀可以通過Oracle11g的一種新特性Result Cache，可以用來緩存SQL Query Result Cache：存儲 SQL 查詢的結果集和PL/SQL Function Result Cache：用於存儲 PL/SQL 函數的結果集。

配合result_cache_max_result`result_cache_mode參數/+ result_cache /`hint可直接訪問結果集不需要再查詢這個sql，比如案例二可將邏輯讀降為0。

3. 優化效果（預期）

單靠建索引即可將 每天 I/O 量由 13.18TB → <1TB；若再結合緩存/隊列，能進一步大幅降低數據庫壓力。

指標 優化前 優化後 降幅
平均邏輯讀 70 blocks 1 block -98.5%
每天邏輯讀量 ≈ 13.18 TB ≈ 0.986 TB -92.5%
平均響應耗時 0.17 ms 0.01 ms 提升17倍
執行頻率影響 高頻仍然存在 可通過緩存/隊列進一步優化 -

SQL優化效果預期

上面説一個4M的表的SQL進AWR TOP SQL，一天概產生讀13.18TB數據，一個8M的表和一個簡單任務的處理邏輯3個SQL一天產生110TB 的邏輯讀，還有前面幾篇講到的

【SQL優化案例】Oracle統計信息缺失 - 墨天輪
https://www.modb.pro/db/1957824741260144640
【SQL優化案例】索引問題 - 墨天輪
https://www.modb.pro/db/1958198758420787200
【SQL優化案例】SQL改寫 - 用 UNION ALL 替代 OR - 墨天輪
https://www.modb.pro/db/1958204225146597376
【SQL優化案例】表結構與數據分佈問題 - 墨天輪
https://www.modb.pro/db/1960011992815644672

這些案例都是很常見的優化案例，相較於慢SQL，TOP SQL優化起來並不困難，但是在實施之後怎麼觀察預期效果呢？

除了單個SQL實施之後的執行時間、邏輯讀大小，常見的指標有CPU使用率、操作系統負載，AAS，DB CPU、DB TIME等。

在實施40+SQL左右，可以觀察到實例每天產生的邏輯讀降了30%-50%。

圖片

CPU和操作系統負載下降幅度也很明顯

實例一

圖片

實例二

圖片

圖片

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service

oracle , 數據庫 , SQL
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.