oracle - 具有最佳执行计划的高 IO%
问题描述
我有一个包PKG_P2K_IMP_RESGATE_AUT，它运行一个在执行计划中成本较低的查询，但在 AWR 上的 IO% 时间很大。
-------------------------------------
SELECT COUNT (1) FROM MAG_V_CXA_LANCTO L, MAG_V_MOV_PEDIDO P WHERE
L.CODFIL = P.CODFIL AND L.NUMPED = P.NUMPEDVEN AND L.TIPOPED =
P.TIPOPED AND P.NUMPEDPRINC = :B2 AND P.CODFIL = :B1 AND L.FORMA IN
(1,2,3)
Plan hash value: 1651369101
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
| 1 | SORT AGGREGATE | | 1 | 178 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 178 | 23 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 147 | 19 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 143 | 19 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 137 | 17 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 129 | 16 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 115 | 14 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 107 | 13 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 103 | 13 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 85 | 10 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 72 | 8 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| CXA_LANCTO | 1 | 48 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | CXA_LANCTO_IDX_PDV | 1 | | 4 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| MOV_PEDIDO | 1 | 24 | 3 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | MOV_PEDIDO_PK_R | 1 | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | MOV_PEDIDO_FINANC_COMPL_PK | 2 | 26 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | MOV_PEDIDO | 1 | 18 | 3 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | MOV_PEDIDO_PK_R | 1 | | 2 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | CAD_TPNOTA_PK | 1 | 4 | 0 (0)| |
|* 20 | INDEX UNIQUE SCAN | CAD_CONDPG_PK | 1 | 8 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | MOV_PEDIDO_COMPL_PK | 1 | 14 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | CAD_CONDPG_PK | 1 | 8 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | CAD_CLIENTE_PK | 1 | 6 | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | CXA_EVENTO_PK | 1 | 4 | 0 (0)| |
|* 25 | TABLE ACCESS BY INDEX ROWID | CXA_LANCTO_COMPL | 1 | 31 | 4 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | CXA_LANCTO_COMPL_IDX02 | 6 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A3800E45
12 - SEL$A3800E45 / LAN@SEL$2
13 - SEL$A3800E45 / LAN@SEL$2
14 - SEL$A3800E45 / B@SEL$9
15 - SEL$A3800E45 / B@SEL$9
16 - SEL$A3800E45 / F@SEL$9
17 - SEL$A3800E45 / PEDIDO@SEL$67
18 - SEL$A3800E45 / PEDIDO@SEL$67
19 - SEL$A3800E45 / TPNOTA@SEL$67
20 - SEL$A3800E45 / D@SEL$9
21 - SEL$A3800E45 / C@SEL$9
22 - SEL$A3800E45 / CONDPG@SEL$2
23 - SEL$A3800E45 / CLI@SEL$2
24 - SEL$A3800E45 / EVE@SEL$2
25 - SEL$A3800E45 / LANCPL@SEL$2
26 - SEL$A3800E45 / LANCPL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
12 - filter(("LAN"."CONDPGTO" IS NOT NULL AND INTERNAL_FUNCTION("LAN"."FORMA")))
13 - access("LAN"."CODFIL"=:B1)
14 - filter(("B"."NUMPEDPRINC"=:B2 AND "B"."CONDPGTO" IS NOT NULL))
15 - access("B"."CODFIL"=:B1 AND "LAN"."TIPOPED"="B"."TIPOPED" AND "LAN"."NUMPED"="B"."NUMPEDVEN")
16 - access("F"."CODFIL"=:B1 AND "B"."NUMPEDVEN"="F"."NUMPEDVEN" AND "B"."TIPOPED"="F"."TIPOPED")
18 - access("PEDIDO"."CODFIL"=:B1 AND "PEDIDO"."TIPOPED"="B"."TIPOPED" AND
"PEDIDO"."NUMPEDVEN"="B"."NUMPEDVEN")
19 - access("PEDIDO"."TPNOTA"="TPNOTA"."TPNOTA")
20 - access("D"."CODFIL"=:B1 AND "D"."CONDPGTO"="B"."CONDPGTO")
21 - access("C"."CODFIL"=:B1 AND "C"."TIPOPED"="B"."TIPOPED" AND "C"."NUMPEDVEN"="B"."NUMPEDVEN")
22 - access("CONDPG"."CODFIL"=:B1 AND "LAN"."CONDPGTO"="CONDPG"."CONDPGTO")
23 - access("CLI"."CODCLI"="LAN"."CODCLI")
24 - access("LAN"."CODEVE"="EVE"."CODEVE")
25 - filter(("LANCPL"."CODFIL"=:B1 AND "LAN"."TIPOPED"="LANCPL"."TIPOPED" AND
"LAN"."TPNOTA"="LANCPL"."TPNOTA" AND "LAN"."NUMPRC"="LANCPL"."NUMPRC" AND "LAN"."NUMLAN"="LANCPL"."NUMLAN"
AND "LAN"."DATREF"="LANCPL"."DATREF"))
26 - access("LAN"."NUMPED"="LANCPL"."NUMPED")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."NUMLAN"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22]
4 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."NUMLAN"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22]
5 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."NUMLAN"[NUMBER,22], "LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22],
"LAN"."TPNOTA"[NUMBER,22]
6 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22]
7 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22], "B"."TIPOPED"[NUMBER,22],
"B"."NUMPEDVEN"[NUMBER,22]
8 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22], "B"."TIPOPED"[NUMBER,22],
"B"."NUMPEDVEN"[NUMBER,22], "B"."CONDPGTO"[VARCHAR2,3]
9 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22], "B"."TIPOPED"[NUMBER,22],
"B"."NUMPEDVEN"[NUMBER,22], "B"."CONDPGTO"[VARCHAR2,3], "PEDIDO"."TPNOTA"[NUMBER,22]
10 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22], "B"."TIPOPED"[NUMBER,22],
"B"."NUMPEDVEN"[NUMBER,22], "B"."CONDPGTO"[VARCHAR2,3]
11 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22], "B"."TIPOPED"[NUMBER,22],
"B"."NUMPEDVEN"[NUMBER,22], "B"."CONDPGTO"[VARCHAR2,3]
12 - "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22], "LAN"."DATREF"[DATE,7],
"LAN"."CODEVE"[NUMBER,22], "LAN"."CONDPGTO"[VARCHAR2,3], "LAN"."NUMLAN"[NUMBER,22],
"LAN"."CODCLI"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."TPNOTA"[NUMBER,22]
13 - "LAN".ROWID[ROWID,10], "LAN"."NUMPED"[NUMBER,22], "LAN"."TIPOPED"[NUMBER,22],
"LAN"."TPNOTA"[NUMBER,22], "LAN"."NUMPRC"[NUMBER,22], "LAN"."NUMLAN"[NUMBER,22], "LAN"."DATREF"[DATE,7]
14 - "B"."TIPOPED"[NUMBER,22], "B"."NUMPEDVEN"[NUMBER,22], "B"."CONDPGTO"[VARCHAR2,3]
15 - "B".ROWID[ROWID,10], "B"."TIPOPED"[NUMBER,22], "B"."NUMPEDVEN"[NUMBER,22]
17 - "PEDIDO"."TPNOTA"[NUMBER,22]
18 - "PEDIDO".ROWID[ROWID,10]
26 - "LANCPL".ROWID[ROWID,10]
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
20,669.64 108 191.39 42.12 3.42 93.41 5ufgnmqnyzswp JDBC Thin Client call PKG_P2K_IMP_RESGATE_AUT.R...
14,422.03 65 221.88 29.39 3.28 93.95 4j3bt7f88grgp JDBC Thin Client SELECT COUNT (1) FROM MAG_V_CX...
是:B1分公司 ID，:B2是订单号。
我有一个应用程序服务，它最多可以打开 100 个线程，每个线程都查询不同的订单号，突然之间，所有线程都会在这个 SQL_ID 中停留 20 分钟，直到超时。
今天问题开始发生，只有不到 10 个线程被卡住。只是为了比较一下，在黑色星期五，我们让所有 100 个线程同时执行包，并且它没有卡住。
当我在 DBeaver 上执行相同的查询时，它会在不到 20 毫秒的时间内运行，并使用卡在其中一个线程上的相同订单号。
查询的预期结果将是几行的简单计数（最多 0、1 或 20）。
我已经尝试清除执行计划以强制 Oracle 构建一个新的，并且他得到了相同的执行计划。
问题只是突然发生，几个小时后它就消失了，包在不到一秒的时间内执行。它以 20 天的间隔发生，但昨天我们得到了它，今天它又发生了。
我不知道该怎么做，有人可以帮忙吗？
解决方案
推荐阅读
- jenkins - 如何在詹金斯的多个从节点中运行作业？
- c# - 如何在异步任务列表中创建 DataGrid ItemSource
- regex - 正则表达式中的 False [] 范围
- odoo - 视图未返回
- python - 如何在 div 中级联图像 src
- visual-studio-code - VS Code：排除具有相同名称但扩展名不同的文件
- ios - 如何通过 segue 到下一个 ViewController 使“查看全部”按钮显示数据？
- ruby-on-rails - DeviseLdapAuthenticatable：无法使用用户名或电子邮件登录（没有此类列 users.login 错误）
- html - 将数据数组作为帖子发送到 django
- post - 将正文“表单数据”添加到 Power Query 中的发布请求 - Power BI