案例一：SQL執(zhí)行頻率太高 - 業(yè)務邏輯
1. 問題 SQL
有三個sql執(zhí)行次數(shù)太高，8M的表一天能有 110TB 的邏輯讀，猜測應該在一個業(yè)務邏輯
涉及表：JUDGE_TASK （8M）、JUDGE_TASK_HIS （歸檔表，52M）
status 字段：0-未處理，1-處理成功，2-處理中，9-處理失敗
SQL 及執(zhí)行情況：
1）85m3gxkjzaqkt
SELECT INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATE
FROM JUDGE_TASK
WHERE STATUS = '0'
AND ROWNUM <= 500
ORDER BY CREATEDATE;- 業(yè)務邏輯：查詢 JUDGE_TASK 任務表 中還 未處理 的任務，返回500行按創(chuàng)建時間排序
- 執(zhí)行頻率：每30分鐘執(zhí)行 126,508 次，≈ 每秒 70 次，早晚都是 TOP SQL
- 平均邏輯讀：1000 塊次，一天產(chǎn)生邏輯讀：≈ 58.7 億塊次 ≈ 43.73 TB
2）dnbf276hzw04k
INSERT INTO JUDGE_TASK_HIS(INST_ID,DTYPE,STATUS,NODEID,ERRMSG,CREATEDATE,EXECUTEDATE)
SELECT INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATE
FROM JUDGE_TASK
WHERE STATUS='1' OR STATUS='9';- 業(yè)務邏輯： 將
JUDGE_TASK任務 處理成功 和 處理失敗 的記錄插入到JUDGE_TASK_HIS表（歸檔） - 執(zhí)行頻率：每30分鐘執(zhí)行 126,599 次，≈ 每秒 70 次，早晚都是 TOP SQL
- 平均邏輯讀：1000 塊次，一天產(chǎn)生邏輯讀：≈ 58.7 億塊次 ≈ 43.73 TB
3）cyypmad5n2b1m
DELETE FROM JUDGE_TASK
WHERE STATUS='1' OR STATUS='9';- 業(yè)務邏輯：刪除已處理任務（成功+失敗，已歸檔）
- 執(zhí)行頻率：每30分鐘執(zhí)行 125,046 次，≈ 每秒 70 次
- 平均邏輯讀：1000 塊次，一天產(chǎn)生邏輯讀：≈ 37.18 億塊次 ≈ 27.7 TB
每個月只處理幾千條任務，但是三條 SQL 加起來，單表每天邏輯讀 110TB+，遠超正常業(yè)務規(guī)模。并且JUDGE_TASK 15天僅有 60條 insert，每天需要執(zhí)行六百萬次，查詢/歸檔/刪除過于頻繁，屬于 業(yè)務邏輯設計不合理。
SQL> select status,count(*) from PANDA.JUDGE_TASK group by status order by count(*) desc;
no rows selected
SQL> select * from PANDA.JUDGE_TASK;
no rows selected
SQL> @seg PANDA.JUDGE_TASK
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
8 PANDA JUDGE_TASK TABLE DATA999 1024 544 1385609
52 PANDA JUDGE_TASK_HIS TABLE DATA999 6656 544 1874569
2 rows selected.
-- 歷史記錄
SQL> select count(*) from PANDA.JUDGE_TASK_HIS;
COUNT(*)
----------
667816
-- 估算每月任務量
SELECT TO_CHAR(TRUNC(EXECUTEDATE, 'MM'), 'YYYY-MM') AS month,
COUNT(*) AS cnt
FROM PANDA.JUDGE_TASK
GROUP BY TRUNC(EXECUTEDATE, 'MM')
ORDER BY month;
MONTH CNT
1 2018-09 541
2 2018-10 1417
3 2018-11 7414
4 2018-12 5875
5 2019-01 4118
......
74 2024-10 5059
75 2024-11 6235
76 2024-12 8751
77 2025-01 10422
78 2025-02 10459
79 2025-03 1096
80 2025-04 2199
81 2025-05 3490
82 2025-06 4910
83 2025-07 2480
-- 根據(jù) sql_id 估算一天產(chǎn)生的邏輯讀
SELECT AVG(BUFFER_GETS) * 24 / 10 ,ROUND((AVG(BUFFER_GETS) * 24 / 10 * 8192)/ POWER(1024, 4), 2 ) AS LOGICAL_READ_TB_24H
FROM DBMT.GETS_STAT_HIST WHERE
SNAP_TIME <= TO_DATE('20250723', 'yyyymmdd')
--AND INSTANCE_NUMBER = 1
AND sql_id = '85m3gxkjzaqkt';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dnbf276hzw04k, child number 0
-------------------------------------
INSERT INTO JUDGE_TASK_HIS(INST_ID,DTYPE,STATUS,NODEID,E
RRMSG,CREATEDATE,EXECUTEDATE) SELECT
INST_ID,DTYPE,STATUS,NODEID,ERROR_MSG,CREATEDATE,EXECUTEDATE FROM
JUDGE_TASK WHERE STATUS='1' OR STATUS='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 | TABLE ACCESS FULL | JUDGE_TASK | 822 | 183K| 223 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("STATUS"='1' OR "STATUS"='9'))
22 rows selected.
-- 索引信息
TABLE TABLE Index COLUMN Col
OWNER NAME Name UCPTDVS NAME Pos DESC
--------------- ------------- -------------------------- ------- ------------------------- ---- ----
PANDA JUDGE_TASK IDX_INST_ID_STATUS NNNNNVY INST_ID 1 ASC
NNNNNVY STATUS 2 ASC
JUDGE_TASK_HIS IDX_INST_ID_STATUS_HIS NNNNNVY INST_ID 1 ASC
NNNNNVY STATUS 2 ASC
PLAN GET DISK WRITE ROWS ROWS USER_IO(MS) ELA(MS) CPU(MS) CLUSTER(MS) PLSQL
END_TI I NAME HASH VALUE EXEC PRE EXEC PRE EXEC PER EXEC ROW_P PRE EXEC PRE FETCH PER EXEC PRE EXEC PRE EXEC PER EXEC PER EXEC
------ - --------------- ------------- ---------- ------------ -------- -------- ----- ----------- --------- ----------- --------------- --------------- ----------- --------
26 11 1 PANDA 3244728187 11.W 1,009 0 0 0 0 0 0 2,230 1,410 0 0
26 11 1 PANDA 3244728187 11.W 1,009 0 0 0 0 0 0 2,237 1,413 0 0
26 12 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,164 1,328 0 0
26 12 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,155 1,335 0 0
26 13 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 1,961 1,118 0 0
26 13 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,136 1,317 0 0
26 14 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,055 1,226 0 0
26 14 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,095 1,252 0 0
26 15 1 PANDA 3244728187 12.W 1,009 0 0 0 0 0 0 2,072 1,228 0 0
26 15 1 PANDA 3244728187 11.W 1,009 0 0 0 0 0 0 2,220 1,397 0 0
......2. 優(yōu)化實施
（1）業(yè)務邏輯優(yōu)化（首選方案）
- 降低執(zhí)行頻率：建議 30s~1min 執(zhí)行一次任務處理或者重構(gòu)一下將相關(guān)SQL模塊封裝出去，而不是毫秒級頻繁執(zhí)行。
- 合理批量處理：判斷任務優(yōu)先級，如果不要求實時，可以批量歸檔/刪除，而非頻繁小批量。
猜測業(yè)務邏輯如下：
檢查status='0'是否有任務未處理-> 處理任務 -> 處理成功和失敗的（STATUS='1' OR STATUS='9'）任務歸檔在JUDGE_TASK_HIS->刪除JUDGE_TASK任務記錄（STATUS='1' OR STATUS='9'）
（2）數(shù)據(jù)庫層面優(yōu)化（預備方案）
- 在
STATUS字段建立索引（或組合索引）： CREATE INDEX PANDA.JUDGE_TASK_status ON PANDA.JUDGE_TASK(status);
訪問路徑優(yōu)化，將目前執(zhí)行計劃 TABLE ACCESS FULL，建索引后預期轉(zhuǎn)為 INDEX RANGE SCAN，邏輯讀可由 ~658 降至幾十。
3. 優(yōu)化效果（預期）
很簡單的一個業(yè)務每天能有110TB的邏輯讀，全庫每天才產(chǎn)生1500TB左右。但生產(chǎn)上還未優(yōu)化，該業(yè)務不屬于核心，在一個沒見過的用戶下，業(yè)務還在定位，預期效果邏輯讀是從 110TB+/天 → <1TB/天。
案例二：SQL執(zhí)行頻率太高 - 4M 表進AWR TOP SQL
1. 問題 SQL
表 PANDA.ZONE_LIST 僅 13 行數(shù)據(jù)，核心表，頻繁訪問。每分鐘執(zhí)行9122次，每次執(zhí)行0.17ms,邏輯讀70塊次，一天概產(chǎn)生讀13.18TB數(shù)據(jù)。
SELECT T.ZONE
FROM ZONE_LIST T
WHERE T.PARTITION = :1;
-- 測試
SQL> SELECT T.ZONE FROM ZONE_LIST T WHERE T.PARTITION = 'G';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
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 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."PARTITION"='G')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed 2. 優(yōu)化實施
（1）索引優(yōu)化--(數(shù)據(jù)庫層優(yōu)化建議)
為查詢條件建立唯一索引 (PARTITION, ZONE)，優(yōu)化前執(zhí)行計劃走TABLE ACCESS FULL，Consistent gets = 70，優(yōu)化后執(zhí)行計劃走INDEX RANGE SCAN UN_ZONE_LIST_PARTGConsistent gets = 1 邏輯讀從 70 → 1，降幅 98.5%。
--- 創(chuàng)建索引
create UNIQUE 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
Execution Plan
----------------------------------------------------------
Plan hash 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 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PARTITION"='G')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed（2）應用層面優(yōu)化建議
現(xiàn)在主要矛盾是應用層頻繁請求數(shù)據(jù)庫，應用層-數(shù)據(jù)庫層面有一個東西可以解決這一問題-中間件緩存，對于熱點數(shù)據(jù)，可以放入緩存中間件 比如 Redis，避免頻繁訪問 DB。案例二是一個只有select的表，比較好修改，案例一也可以引用這種方法，但會涉及到緩存一致性問題，一致性越強代碼越不好改動。
但其實除了應用層，Oracle也提供了兩種方法，一個是KEEP Buffer Pool， 專為“熱點小表”準備的一塊獨立內(nèi)存區(qū)，啟用后這些表的數(shù)據(jù)塊不再被 LRU 算法換出，從而避免反復磁盤 I/O，提高訪問速度。上面方式可以降低物理讀，對于熱點表邏輯讀可以通過Oracle11g的一種新特性Result Cache，可以用來緩存SQL Query Result Cache：存儲 SQL 查詢的結(jié)果集和PL/SQL Function Result Cache：用于存儲 PL/SQL 函數(shù)的結(jié)果集。
配合result_cache_max_resultresult_cache_mode參數(shù)/*+ result_cache */hint可直接訪問結(jié)果集不需要再查詢這個sql，比如案例二可將邏輯讀降為0。
3. 優(yōu)化效果（預期）
單靠建索引即可將 每天 I/O 量由 13.18TB → <1TB；若再結(jié)合緩存/隊列，能進一步大幅降低數(shù)據(jù)庫壓力。
指標 | 優(yōu)化前 | 優(yōu)化后 | 降幅 |
平均邏輯讀 | 70 blocks | 1 block | -98.5% |
每天邏輯讀量 | ≈ 13.18 TB | ≈ 0.986 TB | -92.5% |
平均響應耗時 | 0.17 ms | 0.01 ms | 提升17倍 |
執(zhí)行頻率影響 | 高頻仍然存在 | 可通過緩存/隊列進一步優(yōu)化 | - |
SQL優(yōu)化效果預期
上面說一個4M的表的SQL進AWR TOP SQL，一天概產(chǎn)生讀13.18TB數(shù)據(jù)，一個8M的表和一個簡單任務的處理邏輯3個SQL一天產(chǎn)生110TB 的邏輯讀，還有前面幾篇講到的
【SQL優(yōu)化案例】Oracle統(tǒng)計信息缺失 - 墨天輪
【SQL優(yōu)化案例】SQL改寫 - 用 UNION ALL 替代 OR - 墨天輪
【SQL優(yōu)化案例】表結(jié)構(gòu)與數(shù)據(jù)分布問題 - 墨天輪
這些案例都是很常見的優(yōu)化案例，相較于慢SQL，TOP SQL優(yōu)化起來并不困難，但是在實施之后怎么觀察預期效果呢？
除了單個SQL實施之后的執(zhí)行時間、邏輯讀大小，常見的指標有CPU使用率、操作系統(tǒng)負載，AAS，DB CPU、DB TIME等。
在實施40+SQL左右，可以觀察到實例每天產(chǎn)生的邏輯讀降了30%-50%。
CPU和操作系統(tǒng)負載下降幅度也很明顯
實例一
實例二
