Lower Cost Ignored
Filed under: CBO,Oracle — Jonathan Lewis @ 3:49 pm BST Jun 17,2020
这是对我约10年前写的一篇文章的更新，文章描述了优化器如何选择忽略成本较低的索引访问路径，并且使用了更高成本的索引，如果该低成本是基于猜测的话。最初的文章使用（select {constant} from dual）作为在谓词中提供“不可绑定变量”（意为不可以使用绑定变量代替该条件值）的方法，但该机制在11gR2中停止了工作，因此进行了更新
该更新还对event 38036进行了更详细的介绍，通过定义一个“截止”百分比，Oracle将切换回使用较低成本的路径的行为。
我们从生成数据的代码开始——在本例中，包括一个表，我可以查询该表以向优化器提供“隐藏常量”：
rem rem Script: index_range_2.sql rem Author: Jonathan Lewis rem Dated: June 2020 rem rem Last tested rem 19.3.0.0 rem 12.2.0.1 rem create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= 10000 -- > comment to avoid wordpress format issue ) select rownum id, 9 + mod(rownum-1,3) scattered, trunc(rownum / 500) clustered, lpad(mod(rownum,2),10) ind_pad, lpad(rownum,10,'0') small_vc, rpad('x',100) padding from generator v1, generator v2 where rownum <= 40000 -- > comment to avoid wordpress format issue ; create index t1_equi on t1(scattered, ind_pad, clustered); create index t1_range on t1(clustered, scattered); create table driver(name varchar2(20), value number(4,0)); create index drv_i1 on driver(name); insert into driver values('Forty',40); insert into driver values('FortyOne',41); commit; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); dbms_stats.gather_table_stats( ownname => user, tabname =>'DRIVER', method_opt => 'for all columns size 1' ); end; /复制
DRIVER表模拟了一种情形：我经常看到一个表，其中包含一组“常量”，这些常量会并不经常的更改。（另一种策略是使用一个由常量组成的PL/SQL包和一个在提供名称时返回值的函数。）
T1表中的主要列是：
scattered: 3个唯一值平均分散在表中。无该列上的单独索引。
clustered: 每500个连续行一个值，共80个唯一值。聚集性非常不错。
T1表上有两个索引：
t1_range (clustered scattered) — 大约 (80 * 3 =) 240 个唯一键值。
t1_equi (scattered, ind_pad, clustered) — 大约 (3 * 2 * 80) = 480 个唯一键值。
如果我执行查询：
select * from t1 where clustered between 40 and 41 and scattered = 10
Oracle会使用索引t1_range，预估返回496行，成本为36.这个预估差得不多，在我的环境中查询返回了334行，访问了26个逻辑读（当我的SQLPLUS中的arraysize的值为500时）。
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 496 | 8432 | 36 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 496 | 8432 | 36 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_RANGE | 496 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLUSTERED">=40 AND "SCATTERED"=10 AND "CLUSTERED".le.41) filter("SCATTERED"=10)复制
但是，当我们决定把CLUSTERED列的条件值保存在DRIVER表中时，会发生什么:
set autotrace traceonly explain select /* index(t1 t1_range) */ small_vc from t1 where scattered = 10 and clustered between (select value from driver where name = 'Forty') and (select value from driver where name = 'FortyOne') ; set autotrace off复制
你会注意到 “并不是提示的提示” 。我测试了查询的两个版本，一个没有提示，另一个在其中插入了必需的“+”以获得提示而不是注释。以下是执行计划（谓词信息中“<=”编辑为了“.le.”）：
======= Default ======= ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 561 | 67 (3)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 33 | 561 | 63 (4)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_EQUI | 60 | | 56 (4)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SCATTERED"=10 AND "CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) filter("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) 4 - access("NAME"='Forty') 6 - access("NAME"='FortyOne') ============= Hint t1_range ============= -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 561 | 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 33 | 561 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_RANGE | 60 | | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "SCATTERED"=10 AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) filter("SCATTERED"=10) 4 - access("NAME"='Forty') 6 - access("NAME"='FortyOne')复制
最大的区别发生在ID为2的操作。你应该注意到两件事 —— 首先，行的估计值错误了近10倍，但这不是我要讨论的重点。其次，在默认情况下，优化器选择的执行计划使用了更昂贵的索引。这是一个经过精心编码的选择，如果您有权访问MOS，那么您应该检查文档ID 4112254.8：“CBO可能不会为使用unpeeked bind/function的单表访问选择最佳索引”。
发出以下命令后重新运行测试（示提示的）：
alter session set events '38036 trace name context forever, level 10'复制
您会发现优化器自动选择使用了较低成本的索引路径。对于值为10或更高，Oracle将选择较低成本的索引，对于值为9或更低，优化器将坚持使用较高成本的索引。
当然，问题是值10有什么特别之处。根据MOS文件中的线索，以及上述计划的成本，并考虑到对算法的一些改动，我们可以注意到以下几点：
- 使用成本昂贵的索引进行表访问的成本是63（从10053跟踪来看，实际上是62.525996）
- 使用成本更低的索引进行表访问的成本是6（同样，来自10053跟踪，6.037011）
- 将6.037011/62.525996表示为百分比，得到9.655，四舍五入到10。
可能不是巧合！（尽管可能需要更多的测试才能确定它对结果是round()还是ceil()的）
因此，如果优化器选择了错误的索引，并且它应该使用的索引成本的计算是由猜测决定的，那么计算表访问的两个开销的比率，用百分比表示，并向上取整（如果需要，还可以添加一些），然后设置该EVENT。
我不太确定使用该EVENT，你能或应该有多大的用处。可能这是您临时使用的一种方法，允许您生成SQL基线（与您可能使用的其他此类调整不同，事件设置不会被捕获为“opt_param（）”的提示）。在批处理作业中可能有一些特殊情况，比如优化器在选择哪个索引和事件是否相关方面有点不一致，可以通过“alter session”命令进行设置。知道有这个方法很不错-但可能很难在实践中使用。
脚注
如果您想知道为什么从索引中评估有60行，而从表中评估有33行的基数估计值。这是优化器使用“不可绑定变量的猜测”的标准效果。对于索引上的有界（介于）范围，选择率为0.0045，对于表，选择率为0.0025；在这种情况下，我们还必须在计算时包含1/3的因子，以遵从“scattered=10”谓词：
40000 * 0.0045 * 1/3=60
40000 * 0.0025 * 1/3=33.33…。
原文链接：https://jonathanlewis.wordpress.com/2020/06/17/lower-cost-ignored/
原文内容如下：
Lower Cost Ignored
Filed under: CBO,Oracle — Jonathan Lewis @ 3:49 pm BST Jun 17,2020
This is an update on a post I wrote nearly 10 years ago describing how the optimizer could choose to ignore a lower cost indexed access path and use a higher cost index if the lower cost were based on guesswork. The original article article used (select {constant} from dual) as a way of supplying an “unpeekable bind” in a predicate, but that mechanism stopped working 11gR2, hence the update.
The upate also goes into a little more detail about event 38036 which can be used to modify this behaviour by defining a “cut-off” percentage where Oracle will switch back to using the lower cost path.
We start with the code to generate the data – including, in this case – a table that I can query to supply “hidden constants” to the optimizer:
rem rem Script: index_range_2.sql rem Author: Jonathan Lewis rem Dated: June 2020 rem rem Last tested rem 19.3.0.0 rem 12.2.0.1 rem create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= 10000 -- > comment to avoid wordpress format issue ) select rownum id, 9 + mod(rownum-1,3) scattered, trunc(rownum / 500) clustered, lpad(mod(rownum,2),10) ind_pad, lpad(rownum,10,'0') small_vc, rpad('x',100) padding from generator v1, generator v2 where rownum <= 40000 -- > comment to avoid wordpress format issue ; create index t1_equi on t1(scattered, ind_pad, clustered); create index t1_range on t1(clustered, scattered); create table driver(name varchar2(20), value number(4,0)); create index drv_i1 on driver(name); insert into driver values('Forty',40); insert into driver values('FortyOne',41); commit; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); dbms_stats.gather_table_stats( ownname => user, tabname =>'DRIVER', method_opt => 'for all columns size 1' ); end; /复制
The driver table models a pattern that I see fairly frequently of a table holding a set of “constants” that are subject to infrequent changes. (An alternative strategy is to have a PL/SQL package of constants and a function to return a value when supplied with a name.)
The key columns in the table are
scattered: 3 distinct values evenly but randomly scattered across the table – not a column you would index separately
clustered: 500 consecutive rows each for 80 distinct values, so very well clustered data
There are two indexes on the main data table
t1_range (clustered scattered) — roughly (80 * 3 =) 240 distinct keys
t1_equi (scattered, ind_pad, clustered) — roughly (3 * 2 * 80) = 480 distinct keys
If I execute the query: “select * from t1 where clustered between 40 and 41 and scattered = 10” Oracle uses the t1_range index to return an estimated 491 rows at a cost of 36. The estimates aren’t too far out since in my case the query returned 334 rows in 26 buffer visits (when my SQL*Plus arraysize was 500).
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 496 | 8432 | 36 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 496 | 8432 | 36 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_RANGE | 496 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLUSTERED">=40 AND "SCATTERED"=10 AND "CLUSTERED".le.41) filter("SCATTERED"=10)复制
But what happens if we decide to keep the start and end values for clustered in the driver table:
set autotrace traceonly explain select /* index(t1 t1_range) */ small_vc from t1 where scattered = 10 and clustered between (select value from driver where name = 'Forty') and (select value from driver where name = 'FortyOne') ; set autotrace off复制
You’ll notice the “hint that isn’t a hint” I’ve tested two versions of the query, one without a hint and one where I inserted the necessary “+” to have a hint instead of a comment. Here are the execution plans (with “<=” edited to “.le.” in the Predicate Information):
======= Default ======= ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 561 | 67 (3)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 33 | 561 | 63 (4)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_EQUI | 60 | | 56 (4)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SCATTERED"=10 AND "CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) filter("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) 4 - access("NAME"='Forty') 6 - access("NAME"='FortyOne') ============= Hint t1_range ============= -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 561 | 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 33 | 561 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_RANGE | 60 | | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| DRIVER | 1 | 11 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | DRV_I1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLUSTERED">= (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='Forty') AND "SCATTERED"=10 AND "CLUSTERED".le. (SELECT "VALUE" FROM "DRIVER" "DRIVER" WHERE "NAME"='FortyOne')) filter("SCATTERED"=10) 4 - access("NAME"='Forty') 6 - access("NAME"='FortyOne')复制
The big difference occurs at operation 2. You should notice two things – first, the estimate of rows is wrong by a factor of 10, but that’s not the point I want to chase. Secondly by default the optimizer has selected the plan using the (far) more expensive index. This is a deliberately coded choice and if you have access to MOS then you should check Document ID 4112254.8: “CBO may not choose best index for single table access with unpeeked bind/function”.
Rerun the test (unhinted) after issuing.
alter session set events '38036 trace name context forever, level 10'复制
You will find that the optimizer automatically takes the path using the lower cost index. For values of 10 or higher Oracle will pick the lower cost index, for values of 9 or less the optimizer will stick with the higher cost index.
The question, of course, is what’s special about the value 10. Given the clue in the MOS document, and the costs from the plans above, and allowing for some fiddling with the arithmetic we can note the following:
Cost of using the expensive index for the table access is 63 (From the 10053 trace it’s actually 62.525996)
Cost of using the cheaper index for the table access if 6 (Again, from the 1003 trace, 6.037011)
Express 6.037011 / 62.525996 as a percentage and you get 9.655 which rounds to 10.
Probably not a coincidence! (Though it might take a few more test to decide whether it’s round() or ceiling())
So if the optimizer is picking the wrong index, and the arithmetic for the one it should be using is dictated by guessing, then calculate the ratio of the two costs for the table access, express as a percentage and round up (and add some if you want to) then set the event.
I’m not really sure how much use you could, or should, make of this event. Possibly it’s one of those things you use temporarily to allow you to generate an SQL Baseline (the event setting doesn’t get captured as an “opt_param()” hint unlike other fiddles of this type that you might use). Maybe there are a few special cases in batch jobs were the optimizer is a little inconsistent about which index to pick and the event is relevant and could be set through an ‘alter session’ command. It’s nice to know it’s there – but may be hard to use in practice.
Footnote
If you were wondering about the cardinality estimates of 60 rowids from the index and 33 rows from the table. This is the effect of the standard “guesses for unpeeked binds” the optimizer uses. For bounded (between) range on an index the selectivity is 0.0045, for a table it’s 0.0025; in this case we then have to include a factor of 1/3 in the arithmetic to allow for the “scattered=10” predicate:
40,000 * 0.0045 * 1/3 = 60
40,000 * 0.0025 * 1/3 = 33.33….
