原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2020/05/01/hint-hacking/
译文如下：
如何确定需要哪些HINT来将执行计划调整为所需要的效果？
这是一个已经在Oracle开发人员社区（这里 和这里）进行了数周的“案例研究”，最近以我的一个博客笔记的评论告终。文章看起来有些长，但是本篇告诉你如何找到少量的你需要的信息从一个很长的输出中，因此它实际上只是一个包含很长输出的剪短的笔记。
问题如下：查询运行速度不够快，并且在一个批处理中运行了很多次（原始trace / tkprof文件显示执行了842,000次）。但是，每个单独的执行都非常快（据我们所知–我们所看到的单个示例需要百分之几秒的时间）。这是查询的一个执行计划，其中包含了Query Block / Object Alias和Outline Data以及从内存中获取的实际查询统计信息。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 574 (100)| 1 |00:00:00.02 | 3822 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.02 | 3822 | | | |
|* 2 | VIEW | | 1 | 1 | 574 (2)| 0 |00:00:00.02 | 3822 | | | |
|* 3 | COUNT STOPKEY | | 1 | | | 2 |00:00:00.02 | 3822 | | | |
| 4 | VIEW | | 1 | 1 | 574 (2)| 2 |00:00:00.02 | 3822 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 1 | 574 (2)| 2 |00:00:00.02 | 3822 | 2048 | 2048 | 2048 (0)|
|* 6 | FILTER | | 1 | | | 171 |00:00:00.02 | 3822 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 568 (2)| 182 |00:00:00.02 | 3128 | | | |
| 8 | NESTED LOOPS | | 1 | 1 | 568 (2)| 182 |00:00:00.02 | 2946 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 567 (2)| 182 |00:00:00.02 | 2942 | | | |
| 10 | NESTED LOOPS | | 1 | 1 | 566 (2)| 182 |00:00:00.02 | 2938 | | | |
| 11 | NESTED LOOPS ANTI | | 1 | 1 | 565 (2)| 182 |00:00:00.02 | 2752 | | | |
| 12 | NESTED LOOPS ANTI | | 1 | 1 | 562 (2)| 182 |00:00:00.02 | 2388 | | | |
|* 13 | HASH JOIN | | 1 | 5 | 557 (2)| 182 |00:00:00.02 | 2022 | 1599K| 1599K| 1503K (0)|
| 14 | VIEW | index$_join$_008 | 1 | 127 | 2 (0)| 127 |00:00:00.01 | 8 | | | |
|* 15 | HASH JOIN | | 1 | | | 127 |00:00:00.01 | 8 | 1368K| 1368K| 1522K (0)|
| 16 | INDEX FAST FULL SCAN | XXADM_LOVS_CODE_UK | 1 | 127 | 1 (0)| 127 |00:00:00.01 | 4 | | | |
| 17 | INDEX FAST FULL SCAN | XXADM_LOVS_PK | 1 | 127 | 1 (0)| 127 |00:00:00.01 | 4 | | | |
|* 18 | HASH JOIN | | 1 | 478 | 555 (2)| 182 |00:00:00.01 | 2014 | 1245K| 1245K| 1277K (0)|
| 19 | NESTED LOOPS | | 1 | 478 | 243 (2)| 209 |00:00:00.01 | 883 | | | |
| 20 | NESTED LOOPS | | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | | | |
|* 22 | INDEX UNIQUE SCAN | XXADM_COLLEGES_PK | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 1 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | | | |
|* 24 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 1 | | | |
|* 25 | TABLE ACCESS FULL | XXADM_APPLICANT_COURSPREFS_TBL | 1 | 478 | 241 (2)| 209 |00:00:00.01 | 879 | | | |
|* 26 | TABLE ACCESS FULL | XXADM_APPLICANT_DETAILS_TBL | 1 | 6685 | 311 (2)| 10488 |00:00:00.01 | 1131 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_COURSPREFS_TBL | 182 | 8881 | 1 (0)| 0 |00:00:00.01 | 366 | | | |
|* 28 | INDEX UNIQUE SCAN | XXADM_APPLCNT_PREF_ORDER_UK | 182 | 1 | 0 (0)| 182 |00:00:00.01 | 184 | | | |
| 29 | VIEW PUSHED PREDICATE | VW_SQ_1 | 182 | 1 | 3 (0)| 0 |00:00:00.01 | 364 | | | |
| 30 | NESTED LOOPS | | 182 | 1 | 3 (0)| 0 |00:00:00.01 | 364 | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_COURSPREFS_TBL | 182 | 1 | 2 (0)| 0 |00:00:00.01 | 364 | | | |
|* 32 | INDEX UNIQUE SCAN | XXADM_APPLCNT_PREF_ORDER_UK | 182 | 1 | 1 (0)| 182 |00:00:00.01 | 184 | | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | XXADM_CATEGORY_MASTER_TBL | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | XXADM_CATEGORY_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 182 | 1 | 1 (0)| 182 |00:00:00.01 | 186 | | | |
|* 36 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 182 | 1 | 0 (0)| 182 |00:00:00.01 | 4 | | | |
|* 37 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 182 | 1 | 0 (0)| 182 |00:00:00.01 | 4 | | | |
|* 38 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 182 | 1 | 0 (0)| 182 |00:00:00.01 | 4 | | | |
| 39 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 182 | 1 | 1 (0)| 182 |00:00:00.01 | 182 | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID BATCHED | XXADM_APPLICANT_COURSPREFS_TBL | 182 | 1 | 3 (0)| 29 |00:00:00.01 | 507 | | | |
|* 41 | INDEX RANGE SCAN | XXADM_APPLCNT_PREFS_UK | 182 | 5 | 2 (0)| 1450 |00:00:00.01 | 191 | | | |
| 42 | TABLE ACCESS BY INDEX ROWID BATCHED | XXADM_APPLICANT_COURSPREFS_TBL | 171 | 1 | 2 (0)| 0 |00:00:00.01 | 173 | | | |
|* 43 | INDEX RANGE SCAN | XXADM_APPLCNT_APPLICANT_STATUS | 171 | 1 | 1 (0)| 0 |00:00:00.01 | 173 | | | |
|* 44 | VIEW | index$_join$_014 | 6 | 1 | 0 (0)| 0 |00:00:00.01 | 14 | | | |
|* 45 | HASH JOIN | | 6 | | | 0 |00:00:00.01 | 14 | 1519K| 1519K| 666K (0)|
|* 46 | INDEX RANGE SCAN | XXADM_CATEGORY_PK | 6 | 1 | 0 (0)| 6 |00:00:00.01 | 6 | | | |
| 47 | INLIST ITERATOR | | 6 | | | 12 |00:00:00.01 | 8 | | | |
|* 48 | INDEX UNIQUE SCAN | XXADM_CATEGORY_CODE_UK | 12 | 1 | 0 (0)| 12 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$7E0D484F / from$_subquery$_002@SEL$2
5 - SEL$7E0D484F
14 - SEL$082F290F / LMT_GENDER@SEL$3
15 - SEL$082F290F
16 - SEL$082F290F / indexjoin$_alias$_001@SEL$082F290F
17 - SEL$082F290F / indexjoin$_alias$_002@SEL$082F290F
21 - SEL$7E0D484F / CMT@SEL$3
22 - SEL$7E0D484F / CMT@SEL$3
23 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
24 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
25 - SEL$7E0D484F / ACT@SEL$3
26 - SEL$7E0D484F / ADT@SEL$3
27 - SEL$7E0D484F / ACT3@SEL$7
28 - SEL$7E0D484F / ACT3@SEL$7
29 - SEL$A75BE177 / VW_SQ_1@SEL$67DC521B
30 - SEL$A75BE177
31 - SEL$A75BE177 / ACT1@SEL$8
32 - SEL$A75BE177 / ACT1@SEL$8
33 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
34 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
35 - SEL$7E0D484F / LMT_PASS@SEL$3
36 - SEL$7E0D484F / LMT_PASS@SEL$3
37 - SEL$7E0D484F / LMT_APPEARANCE@SEL$3
38 - SEL$7E0D484F / LMT_RELIGION@SEL$3
39 - SEL$7E0D484F / LMT_RELIGION@SEL$3
40 - SEL$5 / ACT1@SEL$5
41 - SEL$5 / ACT1@SEL$5
42 - SEL$6 / ACT2@SEL$6
43 - SEL$6 / ACT2@SEL$6
44 - SEL$F665FE1B / XXADM_CATEGORY_MASTER_TBL@SEL$4
45 - SEL$F665FE1B
46 - SEL$F665FE1B / indexjoin$_alias$_001@SEL$F665FE1B
48 - SEL$F665FE1B / indexjoin$_alias$_002@SEL$F665FE1B
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F665FE1B")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$A75BE177")
PUSH_PRED(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B" 16 15)
OUTLINE_LEAF(@"SEL$082F290F")
OUTLINE_LEAF(@"SEL$7E0D484F")
UNNEST(@"SEL$9D10C90A")
UNNEST(@"SEL$7")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$180402DE")
OUTLINE(@"SEL$7E0D484F")
UNNEST(@"SEL$9D10C90A")
UNNEST(@"SEL$7")
OUTLINE(@"SEL$67DC521B")
OUTLINE(@"SEL$9D10C90A")
UNNEST(@"SEL$9")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$C04829E0")
ELIMINATE_JOIN(@"SEL$3" "CRMT"@"SEL$3")
ELIMINATE_JOIN(@"SEL$3" "MMT"@"SEL$3")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
INDEX_RS_ASC(@"SEL$7E0D484F" "CMT"@"SEL$3" ("XXADM_COLLEGE_MASTER_TBL"."COLLEGE_ID"))
INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
FULL(@"SEL$7E0D484F" "ACT"@"SEL$3")
FULL(@"SEL$7E0D484F" "ADT"@"SEL$3")
INDEX_JOIN(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_CODE") ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
INDEX_RS_ASC(@"SEL$7E0D484F" "ACT3"@"SEL$7" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))
NO_ACCESS(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")
INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
INDEX(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
LEADING(@"SEL$7E0D484F" "CMT"@"SEL$3" "LMT_EDUCATION_TYPE"@"SEL$3" "ACT"@"SEL$3" "ADT"@"SEL$3" "LMT_GENDER"@"SEL$3" "ACT3"@"SEL$7" "VW_SQ_1"@"SEL$67DC521B"
"LMT_PASS"@"SEL$3" "LMT_APPEARANCE"@"SEL$3" "LMT_RELIGION"@"SEL$3")
USE_NL(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3")
USE_NL(@"SEL$7E0D484F" "ACT"@"SEL$3")
USE_HASH(@"SEL$7E0D484F" "ADT"@"SEL$3")
USE_HASH(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")
USE_NL(@"SEL$7E0D484F" "ACT3"@"SEL$7")
USE_NL(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")
USE_NL(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3")
USE_NL(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3")
USE_NL(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")
NLJ_BATCHING(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")
PQ_FILTER(@"SEL$7E0D484F" SERIAL)
INDEX_RS_ASC(@"SEL$A75BE177" "ACT1"@"SEL$8" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))
INDEX_RS_ASC(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID"))
LEADING(@"SEL$A75BE177" "ACT1"@"SEL$8" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")
USE_NL(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")
INDEX_RS_ASC(@"SEL$6" "ACT2"@"SEL$6" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."STATUS_FLAG"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6" "ACT2"@"SEL$6")
INDEX_RS_ASC(@"SEL$5" "ACT1"@"SEL$5" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."COLLEGE_ID"
"XXADM_APPLICANT_COURSPREFS_TBL"."COURSE_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."MEDIUM_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."HOSTEL_REQUIRED"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "ACT1"@"SEL$5")
INDEX_JOIN(@"SEL$4" "XXADM_CATEGORY_MASTER_TBL"@"SEL$4" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID") ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_CODE"))
END_OUTLINE_DATA
*/
这只是少数几个看起来非常相似的执行计划之一，关于查询和执行计划有很多可以说的地方；不过，我只想展示一个想法。提出的建议是在操作25和26步骤中消除全表扫描。下面是执行计划中的相关部分，将其缩小一点范围以使其更窄：
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
--------------------------------------------------------------------------------------------------------
|* 18 | HASH JOIN | | 1 | 478 |
| 19 | NESTED LOOPS | | 1 | 478 |
| 20 | NESTED LOOPS | | 1 | 1 |
| 21 | TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL | 1 | 1 |
|* 22 | INDEX UNIQUE SCAN | XXADM_COLLEGES_PK | 1 | 1 |
| 23 | TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL | 1 | 1 |
|* 24 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 1 | 1 |
|* 25 | TABLE ACCESS FULL | XXADM_APPLICANT_COURSPREFS_TBL | 1 | 478 |
|* 26 | TABLE ACCESS FULL | XXADM_APPLICANT_DETAILS_TBL | 1 | 6685 |
--------------------------------------------------------------------------------------------------------
为了将以上内容隔离为计划的相关且独立的一部分，我检查了26步骤没有子操作，并且我仔细检查了计划以查找26步骤的上级，事实证明这是第18步，这是一个具有嵌套循环（19步骤）作为其第一个子项，与第26步骤的HASH连接。
我们希望将25步和26步从全表扫描更改为索引访问。这时我们需要对25步（嵌套循环连接的第二张表）进行的更改即可，但我们还想将操作18的哈希联接更改为嵌套循环连接。为了使创建正确的HINT变得更准确，我们从查看Query Block / Object Alias开始，以准确确定我们要处理的内容以及在25和26步骤中要处理的“位置”。
25 - SEL$7E0D484F / ACT@SEL$3
26 - SEL$7E0D484F / ADT@SEL$3
现在，我们可以在Outline Data部分中找到HINT，提示将为“ 在查询块sel $ 7E0D484F中对act @ sel $ 3和adt @ sel $ 3进行全表扫描 ”；并且我们需要找到一个提示，告诉我们使用adt4 @ sel $ 3进行HASH连接-这就是我们发现的内容：
FULL(@"SEL$7E0D484F" "ACT"@"SEL$3")
FULL(@"SEL$7E0D484F" "ADT"@"SEL$3")
USE_HASH(@"SEL$7E0D484F" "ADT"@"SEL$3")
我们的use_hash（）HINT很幸运，因为如果我们后面的表也需要交换连接的构造表（swap_join_inputs（）HINT），情况可能会变得更加模糊。
因此，我们现在需要做的就是更改这些HINT（去掉多余的引号，并转换为小写字母，因为我不喜欢在任何地方使用大写字母）为我们提供以下内容：
index( @sel$7e0d484f act@sel$3 {name/definition of index})
index( @sel$7e0d484f adt@sel$3 {name/definition of index})
use_nl(@sel$7e0d484f adt@sel$3)
当然，您必须决定将这些HINT准备就绪的策略。仅将三个HINT插入查询中可能不是一个稳定的解决方案。编辑outline信息以包括这些HINT（替换前三个HINT），然后将整个outline复制到查询中，会有些混乱，您的应用可能不允许这样做。创建一个SQL Patch（使用Oracle的最新版本）或一个SQL Plan Baseline可能是最合适的策略（可能被黑客入侵了一个SQL Profile，但我不喜欢这样做）。不过，这是另一个博客笔记的主题，这里代过。
摘要
如果您有一个需要稍作调整的复杂执行计划，那么从查看执行计划的“ Query Block / Object Alias”部分开始，就很容易找到如何更改当前的Outline数据以到达想要的位置以及要更改的操作，然后在“Outline数据”中搜索需要的查询块，别名和操作。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
