问题描述
嗨，康纳，克里斯，
我有一个FOR UPDATE SQL用于锁定ORDERS表中的某些行，但它似乎有点慢 (大约需要1分钟)。
我试着从dbms_xplan.display_awr获取计划。您能否从您过去的经验中给我一些线索，我可以寻找任何SQL调优的东西。
我有一个FOR UPDATE SQL用于锁定ORDERS表中的某些行，但它似乎有点慢 (大约需要1分钟)。
我试着从dbms_xplan.display_awr获取计划。您能否从您过去的经验中给我一些线索，我可以寻找任何SQL调优的东西。
SELECT PT.ORDER_ID
FROM STAGING_001 PN
JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID,
XMLTABLE (
'hsbcEnvelope/hsbcMessageBody/pymtTran'
PASSING PN.XML_MSG
COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001',
REF_002 VARCHAR2 (50) PATH 'REF_001',
REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT,
ORDERS PT
WHERE 1 = 1
AND ( ( PMT.REF_002 IS NOT NULL
AND PMT.REF_001 IS NOT NULL
AND PMT.REF_002 = PT.REF_002
AND PT.REF_001 = PMT.REF_001
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
OR ( PMT.REF_002 IS NOT NULL
AND PMT.REF_002 = PT.REF_002
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
OR ( PMT.REF_001 IS NOT NULL
AND PT.REF_001 = PMT.REF_001
AND NVL (PMT.REF_003, :B1) = PT.REF_003)
)
FOR UPDATE OF PT.ORDER_ID NOWAIT;
----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27043 (100)| | | | | 1 | FOR UPDATE | | | | | | | | | 2 | BUFFER SORT | | | | | | | | | 3 | CONCATENATION | | | | | | | | | 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | | | 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | | | 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 10 | XPATH EVALUATION | | | | | | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | | | 14 | NESTED LOOPS | | 3480 | 4652K| 249 (1)| 00:00:01 | | | | 15 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 16 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 17 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 18 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 19 | XPATH EVALUATION | | | | | | | | | 20 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 21 | INDEX RANGE SCAN | IDX_PT_REF_002 | 1 | | 3 (0)| 00:00:01 | | | | 22 | NESTED LOOPS | | 1 | 1976 | 899 (1)| 00:00:01 | | | | 23 | NESTED LOOPS | | 204 | 272K| 287 (1)| 00:00:01 | | | | 24 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 25 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 26 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 27 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 28 | XPATH EVALUATION | | | | | | | | | 29 | TABLE ACCESS BY GLOBAL INDEX ROWID | ORDERS | 1 | 607 | 3 (0)| 00:00:01 | ROWID | ROWID | | 30 | INDEX UNIQUE SCAN | ORDERS_UK1 | 1 | | 2 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2)
专家解答
首先，为了真正帮助您，我们需要查看语句正在做的工作-它处理了多少行，与估计值的比较，一致完成等。
要查看此信息，您需要:
-在运行查询之前，使用gather_plan_statistics提示运行查询或在会话中设置statistics_level = all
-获取格式为ALLSTATS LAST的计划
完成此操作后，请发布更新的执行计划以供我们检查。
也就是说，我怀疑where子句是否在执行作者的意图。
记住:
NULL = <任何事情> => 未知
NULL和 <任何东西> => 未知
查询永远不会返回这些行!
这意味着IS NOT NULL检查是不相关的。如果列具有null值，则它们的比较无论如何都将返回null。
虽然这并不重要，但删除不必要的子句会使查询更容易阅读。
这个谓词的情况更有趣:
当它为null时，它将pmt.ref_003映射到bind变量，因此表达式减少到:
因此，所有这实际上是返回pmt.ref_003不为null的行。我怀疑它应该首先在nvl中绑定:
其次，如果条件:
为真，那么后两个ORs子句也是真。所以要么:
-您可以删除第一个子句检查 (pmt.ref_002 = pt.ref_002和pt.ref_001 = pmt.ref_001)
-您可以删除后两个或子句
-条件需要重写，因此它们是互斥的 (例如pmt.ref_002 = pt.ref_002和pt.ref_001 <> pmt.ref_001等)
您需要执行的确切操作取决于此过程的业务逻辑。
要查看此信息，您需要:
-在运行查询之前，使用gather_plan_statistics提示运行查询或在会话中设置statistics_level = all
-获取格式为ALLSTATS LAST的计划
完成此操作后，请发布更新的执行计划以供我们检查。
也就是说，我怀疑where子句是否在执行作者的意图。
记住:
NULL = <任何事情> => 未知
NULL和 <任何东西> => 未知
查询永远不会返回这些行!
这意味着IS NOT NULL检查是不相关的。如果列具有null值，则它们的比较无论如何都将返回null。
虽然这并不重要，但删除不必要的子句会使查询更容易阅读。
这个谓词的情况更有趣:
nvl ( pmt.ref_003, :b1 ) = pt.ref_003
当它为null时，它将pmt.ref_003映射到bind变量，因此表达式减少到:
:b1 = pmt.ref_003 => :b1 = null => UNKNOWN
因此，所有这实际上是返回pmt.ref_003不为null的行。我怀疑它应该首先在nvl中绑定:
NVL (:B1, PMT.REF_003) = PT.REF_003
其次，如果条件:
pmt.ref_002 = pt.ref_002 and pt.ref_001 = pmt.ref_001
为真，那么后两个ORs子句也是真。所以要么:
-您可以删除第一个子句检查 (pmt.ref_002 = pt.ref_002和pt.ref_001 = pmt.ref_001)
-您可以删除后两个或子句
-条件需要重写，因此它们是互斥的 (例如pmt.ref_002 = pt.ref_002和pt.ref_001 <> pmt.ref_001等)
您需要执行的确切操作取决于此过程的业务逻辑。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
