Filed under: Execution plans,Hints,Oracle — Jonathan Lewis @ 6:49 pm BST Sep 3,2021
我已经不记得Oracle是何时废弃 /*+ ordered / 提示了，但从那时起到现在已经过去很久了。最初，你使用 /+ leading(…) */提示（也许是在9i时被引入的），它只允许你指定优化器评估连接顺序的第一张表，但是很快，该提示就允许你指定的一个完整的连接次序了。
我写过一些关于在生产SQL中去掉任何/+ ordered/提示的注意事项，因为它可以生成您不期望的连接顺序。我刚刚发现在19.11.0.0和21.3.0.0上，一个运行测试的极端情况。
我不想为构造查询的数据而费神，这只是一个简单的父子查询，但它展示了一个另人惊讶的示例，查询如下：
I’m not going to bother with the data setup for the query, but it’s a simple parent/child query that exhibits a surprising pattern. Here’s the query:
select /*+ no_adaptive_plan ordered use_nl(ch) */ par.n1, par.small_vc, sum(ch.n1) from parent par, child ch where par.n1 <= 20 and ch.id_par = par.id group by par.n1, par.small_vc ;复制
下面是它的执行计划，它是使用dbms_xplan.display_cursor()从内存中获取的使用ordered提示的执行计划。包括了outline information, hint report 和 query block registry的信息：
:
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 32 (100)| | | 1 | HASH GROUP BY | | 20 | 780 | 32 (7)| 00:00:01 | |* 2 | HASH JOIN | | 20 | 780 | 31 (4)| 00:00:01 | | 3 | JOIN FILTER CREATE | :BF0000 | 20 | 440 | 8 (0)| 00:00:01 | | 4 | VIEW | VW_GBF_6 | 20 | 440 | 8 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | PARENT | 20 | 380 | 8 (0)| 00:00:01 | | 6 | VIEW | VW_GBC_5 | 1000 | 17000 | 23 (5)| 00:00:01 | | 7 | HASH GROUP BY | | 1000 | 8000 | 23 (5)| 00:00:01 | | 8 | JOIN FILTER USE | :BF0000 | 4000 | 32000 | 22 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL| CHILD | 4000 | 32000 | 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$D2EA58F1") ELIM_GROUPBY(@"SEL$FFB6458A") OUTLINE_LEAF(@"SEL$FE9D3122") OUTLINE_LEAF(@"SEL$E2E47E3A") PLACE_GROUP_BY(@"SEL$1" ( "PAR"@"SEL$1" ) ( "CH"@"SEL$1" ) 5) OUTLINE(@"SEL$FFB6458A") ELIM_GROUPBY(@"SEL$1D9E464A") OUTLINE(@"SEL$E26B953F") OUTLINE(@"SEL$1") OUTLINE(@"SEL$1D9E464A") OUTLINE(@"SEL$E132E821") NO_ACCESS(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821") NO_ACCESS(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") LEADING(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821" "VW_GBC_5"@"SEL$E26B953F") USE_HASH(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") PX_JOIN_FILTER(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") USE_HASH_AGGREGATION(@"SEL$E2E47E3A" GROUP_BY) FULL(@"SEL$D2EA58F1" "PAR"@"SEL$1") FULL(@"SEL$FE9D3122" "CH"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$FE9D3122" GROUP_BY) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ITEM_1"="ITEM_1") 5 - filter("PAR"."N1"<=20) 9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CH"."ID_PAR")) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (1)) --------------------------------------------------------------------------- 0 - STATEMENT - no_adaptive_plan 1 - SEL$E2E47E3A - ordered 9 - SEL$FE9D3122 / "CH"@"SEL$1" U - use_nl(ch) Query Block Registry: --------------------- SEL$1 (PARSER) SEL$E26B953F (QUERY BLOCK TABLES CHANGED SEL$1) SEL$E132E821 (QUERY BLOCK TABLES CHANGED SEL$E26B953F) SEL$1D9E464A (SPLIT/MERGE QUERY BLOCKS SEL$E132E821) SEL$FFB6458A (ELIMINATION OF GROUP BY SEL$1D9E464A) SEL$D2EA58F1 (ELIMINATION OF GROUP BY SEL$FFB6458A) [FINAL] SEL$FE9D3122 (SPLIT/MERGE QUERY BLOCKS SEL$E26B953F) [FINAL] SEL$E2E47E3A (PLACE GROUP BY SEL$1) [FINAL]复制
优化器似乎痴迷于其所拥有的聪明程度了。下面是一个使用/*+ leading(par ch) /代替/+ ordered */后的结果–我不想为所有的额外内容操心，因为这是一个非常简单的计划：
:
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 109 (100)| | | 1 | HASH GROUP BY | | 80 | 2160 | 109 (1)| 00:00:01 | | 2 | NESTED LOOPS | | 80 | 2160 | 108 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 80 | 2160 | 108 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | PARENT | 20 | 380 | 8 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | CHI_PK | 4 | | 1 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| CHILD | 4 | 32 | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("PAR"."N1"<=20) 5 - access("CH"."ID_PAR"="PAR"."ID")复制
总而言之：
你不应在较新的Oracle版本（译者注：这里应该是指19.11及之后的版本）中使用/*+ ordered */提示。
原文链接：https://jonathanlewis.wordpress.com/2021/09/03/ordered-hint/
Filed under: Execution plans,Hints,Oracle — Jonathan Lewis @ 6:49 pm BST Sep 3,2021
It’s been such a long time since Oracle deprecated the /*+ ordered / hint that I can’t remember when it happened. The hint you should be using is the /+ leading(…) */ hint which initially – maybe some time in 9i – would only allow you to specify the first table that the optimizer should use when examining join orders, but which soon changed to allow you to specify a complete join order.
I’ve written a few notes about the need to get rid of any /*+ ordered */ hints in production SQL, because it can produce a join order you’re not expecting. I’ve just found an extreme case of this running a quick test on 19.11.0.0 then 21.3.0.0
I’m not going to bother with the data setup for the query, but it’s a simple parent/child query that exhibits a surprising pattern. Here’s the query:
select /*+ no_adaptive_plan ordered use_nl(ch) */ par.n1, par.small_vc, sum(ch.n1) from parent par, child ch where par.n1 <= 20 and ch.id_par = par.id group by par.n1, par.small_vc ;复制
And here’s the plan, pulled from memory with a call to dbms_xplan.display_cursor() with ordered hint in place. I’ve included the outline information, hint report and (since this is from 21c) the query block registry:
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 32 (100)| | | 1 | HASH GROUP BY | | 20 | 780 | 32 (7)| 00:00:01 | |* 2 | HASH JOIN | | 20 | 780 | 31 (4)| 00:00:01 | | 3 | JOIN FILTER CREATE | :BF0000 | 20 | 440 | 8 (0)| 00:00:01 | | 4 | VIEW | VW_GBF_6 | 20 | 440 | 8 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | PARENT | 20 | 380 | 8 (0)| 00:00:01 | | 6 | VIEW | VW_GBC_5 | 1000 | 17000 | 23 (5)| 00:00:01 | | 7 | HASH GROUP BY | | 1000 | 8000 | 23 (5)| 00:00:01 | | 8 | JOIN FILTER USE | :BF0000 | 4000 | 32000 | 22 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL| CHILD | 4000 | 32000 | 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$D2EA58F1") ELIM_GROUPBY(@"SEL$FFB6458A") OUTLINE_LEAF(@"SEL$FE9D3122") OUTLINE_LEAF(@"SEL$E2E47E3A") PLACE_GROUP_BY(@"SEL$1" ( "PAR"@"SEL$1" ) ( "CH"@"SEL$1" ) 5) OUTLINE(@"SEL$FFB6458A") ELIM_GROUPBY(@"SEL$1D9E464A") OUTLINE(@"SEL$E26B953F") OUTLINE(@"SEL$1") OUTLINE(@"SEL$1D9E464A") OUTLINE(@"SEL$E132E821") NO_ACCESS(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821") NO_ACCESS(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") LEADING(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821" "VW_GBC_5"@"SEL$E26B953F") USE_HASH(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") PX_JOIN_FILTER(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F") USE_HASH_AGGREGATION(@"SEL$E2E47E3A" GROUP_BY) FULL(@"SEL$D2EA58F1" "PAR"@"SEL$1") FULL(@"SEL$FE9D3122" "CH"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$FE9D3122" GROUP_BY) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ITEM_1"="ITEM_1") 5 - filter("PAR"."N1"<=20) 9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CH"."ID_PAR")) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (1)) --------------------------------------------------------------------------- 0 - STATEMENT - no_adaptive_plan 1 - SEL$E2E47E3A - ordered 9 - SEL$FE9D3122 / "CH"@"SEL$1" U - use_nl(ch) Query Block Registry: --------------------- SEL$1 (PARSER) SEL$E26B953F (QUERY BLOCK TABLES CHANGED SEL$1) SEL$E132E821 (QUERY BLOCK TABLES CHANGED SEL$E26B953F) SEL$1D9E464A (SPLIT/MERGE QUERY BLOCKS SEL$E132E821) SEL$FFB6458A (ELIMINATION OF GROUP BY SEL$1D9E464A) SEL$D2EA58F1 (ELIMINATION OF GROUP BY SEL$FFB6458A) [FINAL] SEL$FE9D3122 (SPLIT/MERGE QUERY BLOCKS SEL$E26B953F) [FINAL] SEL$E2E47E3A (PLACE GROUP BY SEL$1) [FINAL]复制
The optimizer seems to have got rather carried away with how clever it cn be; so here’s the result of switching from /*+ ordered / to using /+ leading(par ch) */ – I won’t bother with all the extras since it’s a very simple plan:
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 109 (100)| | | 1 | HASH GROUP BY | | 80 | 2160 | 109 (1)| 00:00:01 | | 2 | NESTED LOOPS | | 80 | 2160 | 108 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 80 | 2160 | 108 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | PARENT | 20 | 380 | 8 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | CHI_PK | 4 | | 1 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| CHILD | 4 | 32 | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("PAR"."N1"<=20) 5 - access("CH"."ID_PAR"="PAR"."ID")复制
tl;dr
You should not be using the /*+ ordered */ hint in any recent version of Oracle.
