sql - 使用提示优化 Oracle 查询
问题描述
我有这个复杂的 Oracle 查询。
SELECT
r3.object_id
FROM
nc_references r
INNER JOIN nc_objects o ON r.object_id = o.object_id
AND o.object_class_id = 90000330 /* Work Item */
INNER JOIN nc_params p ON p.object_id = o.object_id
AND p.attr_id = 90100070 /* State */ AND p.list_value_id IN ( 90100071 /* Ready */, 90100072 /* Active */ )
INNER JOIN nc_po_actions poa ON poa.manual_task_id = o.object_id
INNER JOIN nc_po_tasks pot ON pot.task_id = poa.task_id
INNER JOIN nc_references r1 ON r1.object_id = pot.container_id
AND r1.attr_id = 9145923960313063683 /* Wave ID */
INNER JOIN nc_references r2 ON r2.object_id = pot.container_id
AND r2.attr_id = 9145685312013687931 /* Product OrderID */
INNER JOIN nc_references r3 ON r1.reference = r3.reference
AND r3.attr_id = 9145065302013613216 /* Project Wave */ AND r2.reference = r3.object_id
INNER JOIN nc_objects o2 ON r3.object_id = o2.object_id
WHERE
r.reference = 9155224548713314821
AND r.attr_id = 90100080 /* Assigned To */
AND o.object_type_id = 9146598858613093106
我尝试了一些优化：
SELECT
r3.object_id
FROM
nc_references src
INNER JOIN nc_objects o ON o.object_type_id = 9146598858613093106
AND o.object_class_id = 90000330 /* Work Item */
AND src.object_id = o.object_id
AND src.reference = 9155224548713314821
AND src.attr_id = 90100080 /* Assigned To */
INNER JOIN nc_params p ON ( p.list_value_id = 90100071 /* Ready */ OR p.list_value_id = 90100072 /* Active */ )
AND p.object_id = o.object_id
AND p.attr_id = 90100070 /* State */
INNER JOIN nc_po_actions poa ON poa.manual_task_id = o.object_id
INNER JOIN nc_po_tasks pot ON pot.task_id = poa.task_id
INNER JOIN nc_references r1 ON r1.object_id = pot.container_id
AND r1.attr_id = 9145923960313063683 /* Wave ID */
INNER JOIN nc_references r2 ON r2.object_id = pot.container_id
AND r2.attr_id = 9145685312013687931 /* Product OrderID */
INNER JOIN nc_references r3 ON r2.reference = r3.object_id
AND r3.attr_id = 9145065302013613216 /* Project Wave */
AND r1.reference = r3.reference;
但我得到了超过 40 秒的执行时间。看起来是因为列表tasks非常庞大。
您知道如何在此查询中添加提示以提高性能吗？
编辑：解释计划
SELECT /*+ gather_plan_statistics MIDR03*/ r3.object_id
FROM
nc_references src
INNER JOIN nc_objects o ON o.object_type_id = 9146598858613093106
AND o.object_class_id = 90000330 /* Work Item */ AND src.object_id = o.object_id
AND src.reference = 9155224548713314821
AND src.attr_id = 90100080 /* Assigned To */ INNER JOIN nc_params p ON ( p.list_value_id = 90100071 /* Ready */ OR p.list_value_id =
90100072
/* Active */ )
AND p.object_id = o.object_id
AND p.attr_id = 90100070 /*
State */ INNER JOIN nc_po_actions poa ON poa.manual_task_id = o.object_id
INNER JOIN nc_po_tasks pot ON pot.task_id = poa.task_id
INNER JOIN nc_references r1 ON r1.object_id = pot.container_id
AND r1.attr_id = 9145923960313063683 /* Wave [d:1|] */ INNER JOIN nc_references r
Plan hash value: 1629775924
----------------------------------------------------------------------------------------------------------------------------------------------
| [d:1|] |Lvl| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 0|.SELECT STATEMENT | | 1 | | 0 |00:00:43.23 | 441K| 48190 |
| 1 | 1|..NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 2 | 2|...NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 3 | 3|....NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 4 | 4|.....NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 5 | 5|......NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 6 | 6|.......NESTED LOOPS | | 1 | 1 | 0 |00:00:43.23 | 441K| 48190 |
| 7 | 7|........NESTED LOOPS | | 1 | 1 | 23315 |00:00:26.82 | 355K| 31538 |
| 8 | 8|.........TABLE ACCESS BY INDEX ROWID BATCHED| NC_REFERENCES | 1 | 1 | 80719 |00:00:18.92 | 40952 | 17331 |
|* 9 | 9|..........INDEX RANGE SCAN | XIF01NC_REFERENCES | 1 | 1 | 80719 |00:00:00.18 | 337 | 0 |
|* 10 | 8|.........TABLE ACCESS BY INDEX ROWID | NC_OBJECTS | 80719 | 1 | 23315 |00:00:11.79 | 314K| 14207 |
|* 11 | 9|..........INDEX UNIQUE SCAN | XPKNC_OBJECTS | 80719 | 1 | 80719 |00:00:01.55 | 233K| 813 |
|* 12 | 7|........TABLE ACCESS BY INDEX ROWID BATCHED | NC_PARAMS | 23315 | 1 | 0 |00:00:12.94 | 85919 | 16652 |
|* 13 | 8|.........INDEX RANGE SCAN | XIF12NC_PARAMS | 23315 | 1 | 23315 |00:00:03.40 | 70073 | 4638 |
| 14 | 6|.......TABLE ACCESS BY INDEX ROWID BATCHED | NC_PO_ACTIONS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 15 | 7|........INDEX RANGE SCAN | NC_PO_ACTIONS_IX_MANUAL_TASK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
| 16 | 5|......TABLE ACCESS BY INDEX ROWID | NC_PO_TASKS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 17 | 6|.......INDEX UNIQUE SCAN | XPKNC_PO_TASKS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 18 | 4|.....INDEX RANGE SCAN | XIF02NC_REFERENCES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 19 | 3|....INDEX RANGE SCAN | XIF02NC_REFERENCES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 20 | 2|...INDEX RANGE SCAN | XIF02NC_REFERENCES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation [d:1|]):
---------------------------------------------------
9 - access("SRC"."REFERENCE"=9155224548713314821 AND "SRC"."ATTR_ID"=90100080)
10 - filter(("O"."OBJECT_TYPE_ID"=9146598858613093106 AND "O"."OBJECT_CLASS_ID"=90000330))
11 - access("SRC"."OBJECT_ID"="O"."OBJECT_ID")
12 - filter(("P"."LIST_VALUE_ID"=90100071 OR "P"."LIST_VALUE_ID"=90100072))
13 - access("P"."OBJECT_ID"="O"."OBJECT_ID" AND "P"."ATTR_ID"=90100070)
15 - access("POA"."MANUAL_TASK_ID"="O"."OBJECT_ID")
17 - access("POT"."TASK_ID"="POA"."TASK_ID")
18 - access("R1"."OBJECT_ID"="POT"."CONTAINER_ID" AND "R1"."ATTR_ID"=9145923960313063683)
19 - access("R2"."OBJECT_ID"="POT"."CONTAINER_ID" AND "R2"."ATTR_ID"=9145685312013687931)
20 - access("R2"."REFERENCE"="R3"."OBJECT_ID" AND "R3"."ATTR_ID"=9145065302013613216 AND "R1"."REFERENCE"="R3"."REFERENCE")
解决方案
首先，确保统计数据是最新的，这样优化器就有最好的机会生成一个好的执行计划。对于 XIF01NC_REFERENCES 的扫描，它估计为 1 行，但它得到 80719，这表明它认为该表是空的或非常小，而实际上它不是。嵌套循环计划通常不能很好地适应大容量，因此由于统计数据低估而使用这些计划，当哈希连接会更好地工作时，这是一个常见问题。
80719 占整个表的比例是多少 - 是大多数行，还是只有少数行？当嵌套循环可以选择表的一小部分时，它通常效果最好，而如果您需要大部分行，则完全扫描通常会更有效。
我也会整齐地布置查询。意见各不相同，因此由您决定，但我更喜欢只将连接谓词放在join子句中（这对内部连接的优化器没有影响）并将过滤谓词移动到where子句中。inner和关键字也outer很混乱，所以我不想使用它们，大写代码属于 1970 年代的 COBOL，所以我明白了：
select r3.object_id
from nc_references r
join nc_objects o on o.object_id = r.object_id
join nc_params p on p.object_id = o.object_id
join nc_po_actions poa on poa.manual_task_id = o.object_id
join nc_po_tasks pot on pot.task_id = poa.task_id
join nc_references r1 on r1.object_id = pot.container_id
join nc_references r3 on r3.reference = r1.reference
join nc_objects o2 on o2.object_id = r3.object_id
join nc_references r2 on r2.object_id = pot.container_id
and r2.reference = r3.object_id
where r.reference = 9155224548713314821
and r.attr_id = 90100080 --Assigned To
and o.object_type_id = 9146598858613093106
and o.object_class_id = 90000330 --Work Item
and r1.attr_id = 9145923960313063683 --Wave ID
and r2.attr_id = 9145685312013687931 --Product OrderID
and r3.attr_id = 9145065302013613216 --Project Wave
and p.attr_id = 90100070 --State
and p.list_value_id in (90100071, 90100072) --Ready,Active
现在提示（假设更新统计信息并没有修复执行计划）。有很多可能的加入订单，我不知道哪个是最好的，所以我没有指定一个，但你可以通过leading提示来做到这一点，例如
/*+ leading(r o p) */
会告诉优化器只考虑连接顺序开始的计划rthen o，p并让它自己决定其余的（尽管请参阅下面关于哈希连接的注释）。
要强制进行全面扫描，请使用full提示，例如：
/*+ full(o) */
要强制一个或多个哈希连接，请使用use_hash提示，它可以应用于多个源，所以
/*+ use_hash(o p) */
是相同的
/*+ use_hash(o) use_hash(p) */
哈希连接由“构建”和“探测”操作组成，优化器可以自由选择每个表使用哪个表，即使您使用 指定了连接顺序leading，因此您可能仍需要考虑no_swap_join_inputs/swap_join_inputs提示想要一个特定的顺序。我读过的关于提示哈希连接的最佳描述是 Jonathan Lewis：Hash Joins。
现在对于您的查询，如果您想在任何地方尝试完全扫描和散列连接而不是索引和嵌套循环，而不指定任何顺序，您可以尝试：
/*+
use_hash(r o p poa pot r1 r3 o2 r2)
full(r) full(o) full(p) full(poa) full(pot) full(r1) full(r3) full(o2) full(r2)
*/
然而，
如果统计信息错误，那么优化器可能仍会选择低效的连接顺序，因此您可能需要查看
leading并可能no_swap_join_inputs提示。也许完全扫描和散列连接的某种组合效果最好。9张桌子有很多可能性。
提示非常适合实验，当您怀疑优化器遗漏了某些东西时，有时在生产代码中由于某种原因它没有得到一个好的计划，但理想情况下，您应该用统计数据解决问题，以便优化器可以在没有的情况下完成它的工作提示。
推荐阅读
- javascript - JSON在反应中没有正确处理并且没有保存在状态中
- css - 关键帧动画意外令牌错误的问题
- python - 当通过 APIView 类请求 GET 方法时，如何从 API 响应中删除 \n 或以适当的 JSON 格式获取响应
- arrays - 如何在结构中打印无符号字符数组的内容？
- java - 如何在 Android 上水平居中 ListPopupWindow/Other 视图？
- typescript - 对象可能为空。打字稿错误
- reactjs - Create-react-app 从终端启动，但不是 vs-code
- go - 无法安装 bleve，在任何地方都找不到包“github.com/blevesearch/zap/v11”
- php - 从 PHP 应用程序写入 Azure SQL 数据库
- vscode-remote - 如何使用 `code` 命令连接到远程 SSH 主机？