问题描述
嗨，大师们，
我有一个查询，它运行真的很长。添加提示use_hash后，它运行不到一分钟。
表中的数据: det-239968 bal --239968 inst-244713 PT_INST-168745
查询如下:
以下3个计划为1，使用哈希提示，查询运行不到1分钟
2.没有提示，第三分钟计划看起来像，3，没有提示，第七分钟计划看起来像，
如您所见，在没有提示的情况下，A行分别是4,500万和6,800万。表中的最大记录计数大约为244K。
我想知道在oracle中nest循环外部联接会发生什么，为什么A行数量很大？
有人可以解释如何在没有提示的情况下执行查询吗？
以下是使用哈希提示的计划
没有提示4分钟计划
没有提示7分钟计划
我有一个查询，它运行真的很长。添加提示use_hash后，它运行不到一分钟。
表中的数据: det-239968 bal --239968 inst-244713 PT_INST-168745
查询如下:
以下3个计划为1，使用哈希提示，查询运行不到1分钟
2.没有提示，第三分钟计划看起来像，3，没有提示，第七分钟计划看起来像，
如您所见，在没有提示的情况下，A行分别是4,500万和6,800万。表中的最大记录计数大约为244K。
我想知道在oracle中nest循环外部联接会发生什么，为什么A行数量很大？
有人可以解释如何在没有提示的情况下执行查询吗？
SELECT *
FROM PT_INST A,
M_ID M,
(SELECT *
FROM INSTB,
DET DT,
BAL BL
WHERE B.SRC_SYS = 'EST'
AND DT.SRC_SYS_CD = 'EST'
AND DT.RUN_DT = DATE '2018-02-02'
AND DT.INST_KEY = B.INST_KEY
AND BL.SRC_SYS_CD = 'EST'
AND BL.RUN_DT = DATE '2018-02-02'
AND DT.DET_KEY = BL.DET_KEY
AND BL.AMT_TYPE = 'ABAL') B
WHERE A.SRC_SYS_CD = 'DELA'
AND A.RUN_DT = DATE '2018-02-02'
AND A.SRC_SYS = B.SRC_SYS(+)
AND CASE
WHEN A.SRC_SYS IN ('TSS')
THEN
TRIM (A.INST_ACCT_NMBR_CHAR)
ELSE
LTRIM (TRIM (A.INST_ACCT_NMBR_CHAR), 0)
END = B.INST_ACCT_NMBR(+)
AND A.SRC_SYS = 'EST'
AND A.SRC_SYS = M.SRC_SYS(+)
GROUP BY A.SRC_SYS_CD以下是使用哈希提示的计划
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 83188 (100)| | | | 1 |00:00:08.80 | 23835 | 10665 | | | |
| 1 | HASH GROUP BY | | 1 | 4 | 392 | 83188 (13)| 00:01:51 | | | 1 |00:00:08.80 | 23835 | 10665 | 669K| 669K| 342K (0)|
|* 2 | HASH JOIN OUTER | | 1 | 1342 | 128K| 83187 (13)| 00:01:51 | | | 239K|00:00:08.44 | 23835 | 10665 | 13M| 1902K| 17M (0)|
|* 3 | HASH JOIN OUTER | | 1 | 28 | 1120 | 79126 (13)| 00:01:46 | | | 168K|00:00:06.09 | 10632 | 10612 | 10M| 2054K| 17M (0)|
| 4 | PARTITION RANGE SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 74 | 74 | 168K|00:00:05.81 | 10616 | 10598 | | | |
| 5 | PARTITION LIST SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 1 | 1 | 168K|00:00:05.81 | 10616 | 10598 | | | |
|* 6 | TABLE ACCESS FULL | PT_INST | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 221 | 221 | 168K|00:00:05.81 | 10616 | 10598 | | | |
|* 7 | TABLE ACCESS FULL | M_ID | 1 | 1 | 11 | 6 (0)| 00:00:01 | | | 1 |00:00:00.02 | 16 | 14 | | | |
| 8 | VIEW | | 1 | 4871 | 275K| 4061 (4)| 00:00:06 | | | 239K|00:00:01.56 | 13203 | 53 | | | |
|* 9 | HASH JOIN | | 1 | 4871 | 537K| 4061 (4)| 00:00:06 | | | 239K|00:00:01.56 | 13203 | 53 | 8868K| 2757K| 12M (0)|
|* 10 | HASH JOIN | | 1 | 4827 | 296K| 3504 (2)| 00:00:05 | | | 239K|00:00:00.67 | 11590 | 17 | 8645K| 2824K| 12M (0)|
| 11 | PARTITION RANGE SINGLE| | 1 | 4827 | 141K| 1832 (2)| 00:00:03 | 74 | 74 | 239K|00:00:00.25 | 6082 | 17 | | | |
| 12 | PARTITION LIST SINGLE| | 1 | 4827 | 141K| 1832 (2)| 00:00:03 | KEY | KEY | 239K|00:00:00.25 | 6082 | 17 | | | |
|* 13 | TABLE ACCESS FULL | BAL | 1 | 4827 | 141K| 1832 (2)| 00:00:03 | 5996 | 5996 | 239K|00:00:00.01 | 6082 | 17 | | | |
| 14 | PARTITION RANGE SINGLE| | 1 | 238K| 7675K| 1660 (2)| 00:00:03 | 74 | 74 | 239K|00:00:00.24 | 5508 | 0 | | | |
| 15 | PARTITION LIST SINGLE| | 1 | 238K| 7675K| 1660 (2)| 00:00:03 | KEY | KEY | 239K|00:00:00.01 | 5508 | 0 | | | |
|* 16 | TABLE ACCESS FULL | DET | 1 | 238K| 7675K| 1660 (2)| 00:00:03 | 6119 | 6119 | 239K|00:00:00.01 | 5508 | 0 | | | |
| 17 | PARTITION LIST SINGLE | | 1 | 246K| 11M| 545 (11)| 00:00:01 | KEY | KEY | 244K|00:00:00.26 | 1613 | 36 | | | |
| 18 | TABLE ACCESS FULL | INST | 1 | 246K| 11M| 545 (11)| 00:00:01 | 123 | 123 | 244K|00:00:00.02 | 1613 | 36 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - access("A"."SRC_SYS"="B"."SRC_SYS" AND "B"."INST_ACCT_NMBR"=CASE "A"."SRC_SYS" WHEN 'TSS' THEN
TRIM("A"."INST_ACCT_NMBR_CHAR") ELSE LTRIM(TRIM("A"."INST_ACCT_NMBR_CHAR"),'0') END )
3 - access("A"."SRC_SYS"="M"."SRC_SYS")
6 - filter(("A"."SRC_SYS"='EST' AND "A"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
7 - filter("M"."SRC_SYS"='EST')
9 - access("DT"."INST_KEY"="B"."INST_KEY")
10 - access("DT"."DET_KEY"="BL"."DET_KEY")
13 - filter(("BL"."AMT_TYPE"='ABAL' AND "BL"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
16 - filter("DT"."RUN_DT"=TO_DATE(' 2018-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))没有提示4分钟计划
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 79127 (100)| | | | 0 |00:00:00.01 | 0 | 0 | | | | | 1 | HASH GROUP BY | | 1 | 4 | 388 | 79127 (13)| 00:01:46 | | | 0 |00:00:00.01 | 0 | 0 | 700K| 700K| | | 2 | NESTED LOOPS OUTER | | 1 | 1342 | 127K| 79126 (13)| 00:01:46 | | | 195 |00:04:37.12 | 2452K| 683 | | | | |* 3 | HASH JOIN OUTER | | 1 | 28 | 1120 | 79126 (13)| 00:01:46 | | | 186 |00:00:01.26 | 10623 | 683 | 10M| 2054K| 17M (0)| | 4 | PARTITION RANGE SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 74 | 74 | 168K|00:00:01.01 | 10616 | 681 | | | | | 5 | PARTITION LIST SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 1 | 1 | 168K|00:00:01.01 | 10616 | 681 | | | | |* 6 | TABLE ACCESS FULL | PT_INST | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 221 | 221 | 168K|00:00:01.01 | 10616 | 681 | | | | |* 7 | TABLE ACCESS FULL | M_ID | 1 | 1 | 11 | 6 (0)| 00:00:01 | | | 1 |00:00:00.01 | 7 | 2 | | | | |* 8 | VIEW PUSHED PREDICATE | | 186 | 49 | 2793 | 0 (0)| | | | 195 |00:04:35.99 | 2442K| 0 | | | | |* 9 | HASH JOIN | | 186 | 4871 | 537K| 4061 (4)| 00:00:06 | | | 44M|00:03:51.18 | 2454K| 0 | 8868K| 2757K| 13M (0)| |* 10 | HASH JOIN | | 186 | 4827 | 296K| 3504 (2)| 00:00:05 | | | 44M|00:02:40.31 | 2155K| 0 | 8645K| 2824K| 13M (0)| | 11 | PARTITION RANGE SINGLE| | 186 | 4827 | 141K| 1832 (2)| 00:00:03 | 74 | 74 | 44M|00:00:44.67 | 1131K| 0 | | | | | 12 | PARTITION LIST SINGLE| | 186 | 4827 | 141K| 1832 (2)| 00:00:03 | KEY | KEY | 44M|00:00:00.04 | 1131K| 0 | | | | |* 13 | TABLE ACCESS FULL | BAL | 186 | 4827 | 141K| 1832 (2)| 00:00:03 | 5996 | 5996 | 44M|00:00:00.02 | 1131K| 0 | | | | | 14 | PARTITION RANGE SINGLE| | 186 | 238K| 7675K| 1660 (2)| 00:00:03 | 74 | 74 | 44M|00:00:44.63 | 1024K| 0 | | | | | 15 | PARTITION LIST SINGLE| | 186 | 238K| 7675K| 1660 (2)| 00:00:03 | KEY | KEY | 44M|00:00:44.63 | 1024K| 0 | | | | |* 16 | TABLE ACCESS FULL | DET | 186 | 238K| 7675K| 1660 (2)| 00:00:03 | 6119 | 6119 | 44M|00:00:00.01 | 1024K| 0 | | | | | 17 | PARTITION LIST SINGLE | | 186 | 246K| 11M| 545 (11)| 00:00:01 | KEY | KEY | 45M|00:00:00.02 | 298K| 0 | | | | |* 18 | TABLE ACCESS FULL | INST | 186 | 246K| 11M| 545 (11)| 00:00:01 | 123 | 123 | 45M|00:00:00.02 | 298K| 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
没有提示7分钟计划
--------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 79127 (100)| | 0 |00:00:00.01 | | 1 | HASH GROUP BY | | 1 | 4 | 388 | 79127 (13)| 00:01:46 | 0 |00:00:00.01 | | 2 | NESTED LOOPS OUTER | | 1 | 1342 | 127K| 79126 (13)| 00:01:46 | 299 |00:07:02.21 | |* 3 | HASH JOIN OUTER | | 1 | 28 | 1120 | 79126 (13)| 00:01:46 | 286 |00:00:00.41 | | 4 | PARTITION RANGE SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 168K|00:00:00.16 | | 5 | PARTITION LIST SINGLE | | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 168K|00:00:00.16 | |* 6 | TABLE ACCESS FULL | PT_INST | 1 | 28 | 812 | 79119 (13)| 00:01:46 | 168K|00:00:00.16 | |* 7 | TABLE ACCESS FULL | M_ID | 1 | 1 | 11 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | |* 8 | VIEW PUSHED PREDICATE | | 286 | 49 | 2793 | 0 (0)| | 299 |00:07:02.03 | |* 9 | HASH JOIN | | 286 | 4871 | 537K| 4061 (4)| 00:00:06 | 68M|00:05:54.58 | |* 10 | HASH JOIN | | 286 | 4827 | 296K| 3504 (2)| 00:00:05 | 68M|00:04:05.82 | | 11 | PARTITION RANGE SINGLE| | 286 | 4827 | 141K| 1832 (2)| 00:00:03 | 68M|00:01:08.65 | | 12 | PARTITION LIST SINGLE| | 286 | 4827 | 141K| 1832 (2)| 00:00:03 | 68M|00:00:00.02 | |* 13 | TABLE ACCESS FULL | BAL | 286 | 4827 | 141K| 1832 (2)| 00:00:03 | 68M|00:00:00.02 | | 14 | PARTITION RANGE SINGLE| | 286 | 238K| 7675K| 1660 (2)| 00:00:03 | 68M|00:01:08.65 | | 15 | PARTITION LIST SINGLE| | 286 | 238K| 7675K| 1660 (2)| 00:00:03 | 68M|00:01:08.65 | |* 16 | TABLE ACCESS FULL | DET | 286 | 238K| 7675K| 1660 (2)| 00:00:03 | 68M|00:00:00.02 | | 17 | PARTITION LIST SINGLE | | 286 | 246K| 11M| 545 (11)| 00:00:01 | 69M|00:00:00.01 | |* 18 | TABLE ACCESS FULL | INST | 286 | 246K| 11M| 545 (11)| 00:00:01 | 69M|00:00:00.01 | ----------------------------------------------------------------------------------------------------------------------------------
专家解答
在嵌套循环中，对于第一个表返回的每一行，数据库查询第二个表。
在你的第三个计划中，步骤三的 “散列联接外部” 返回286行 (从A行列)。它将值从此传递到步骤8的 “视图推送谓词”。286倍。
从您的第一个计划中，您可以看到在步骤9以下的哈希联接返回239K行。
239,000*286 = 63,354,000
所以有你的6800万行!
那么您如何修复它？
一种方法是使用SQL计划管理。您可以使用SQL配置文件来帮助修复优化器估计值。或基线，以将查询锁定到特定计划。
在以下位置阅读有关此的更多信息:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
另一件事是查看表格的统计数据。优化器估计pt_inst在步骤6的全扫描将只返回28行。但它实际上变得168,000!
当估计行和实际行之间存在较大差异时，优化器很可能会选择 “错误” 的计划。
在你的第三个计划中，步骤三的 “散列联接外部” 返回286行 (从A行列)。它将值从此传递到步骤8的 “视图推送谓词”。286倍。
从您的第一个计划中，您可以看到在步骤9以下的哈希联接返回239K行。
239,000*286 = 63,354,000
所以有你的6800万行!
那么您如何修复它？
一种方法是使用SQL计划管理。您可以使用SQL配置文件来帮助修复优化器估计值。或基线，以将查询锁定到特定计划。
在以下位置阅读有关此的更多信息:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
另一件事是查看表格的统计数据。优化器估计pt_inst在步骤6的全扫描将只返回28行。但它实际上变得168,000!
当估计行和实际行之间存在较大差异时，优化器很可能会选择 “错误” 的计划。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
