COST值相同？是真是假？

你的名字 2022-12-28 12:34 103阅读 0赞

这两天碰到一个问题，一条SQL的两个执行计划COST成本值相同，Oracle是怎么选择的？

首先，创建测试表，以及两个索引，

create table t1 as select * from dba_objects;
    create index idx_t1_01 on t1(owner, object_name, created);
    create index idx_t1_02 on t1(owner, created, object_name);

我们要测试的，就是这条SQL，其中owner是等值条件，object\_name和created是不等值条件，从以上的索引定义可以看到，无论采用哪种索引都无需回表，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

**1. Oracle 11g下的测试**

执行如下SQL，记为SQL1，返回记录数0，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    no rows selected

其执行计划，如下所示，用到了idx\_t\_01的索引，COST是2，

![format_png][]

执行如下SQL，记为SQL2，强制使用idx\_t\_02，返回记录数0，

SQL> select /*+ index(T1, IDX_T1_02) */  owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    no rows selected

其执行计划，如下所示，COST同样是2，

![format_png 1][]

两个执行计划，COST相同，为什么Oracle选择的是idx\_t1\_01？

我们看下这两条语句的10053，SQL1的trace，如下所示，

![format_png 2][]

SQL2的trace，如下所示，

![format_png 3][]

的确，从COST来看，都是2.00，有什么其他条件，能帮助Oracle做出选择？

经albert指点，dbsnake有篇文章《CBO对于Cost值相同的索引的选择》，介绍的场景，和这个很像，

> 这意味着对于Oracle 10gR2及其以上的版本，CBO对于Cost值相同的索引的选择实际上会这样：  
> 1、如果Cost值相同的索引的叶子块数量不同，则Oracle会选择叶子块数量较少的那个索引；  
> 2、如果Cost值相同的索引的叶子块数量相同，则Oracle会选择索引名的字母顺序在前面的那个索引。

P.S. 原文链接，

http://www.dbsnake.net/handle-equally-costed-indexes.html)

从trace看，idx\_t1\_01和idx\_t2\_02的索引叶子块数量都是589，按照如上，Oracle会选择索引名的字母顺序在前面的索引，这里两个索引的前缀都是“idx\_t1\_0”，唯独结尾不同，1和2，难道数字顺序，对选择会有影响？

![format_png 4][]

我们删除索引，重新创建这两个索引，但是这次idx\_t1\_01和idx\_t1\_02索引字段调换下顺序，

drop index idx_t1_01;
    drop index idx_t1_02;
    create index idx_t1_01 on t1(owner, created, object_name);
    create index idx_t1_02 on t1(owner, object_name, created);

然而这次Oracle仍选择的是(owner, object\_name, created)，只是这次索引名称是idx\_t1\_02，trace信息不展示了，两个索引的成本值和上面的相同，说明并不是在COST值相同，而且索引叶子快数量相同的情况下，选择索引名字母顺序在前面的索引，这就很奇怪了，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    
    
    -------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
    -------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |      1 |        |     2 (100)|          |      0 |00:00:00.01 |       6 |      5 |
    |*  1 |  INDEX RANGE SCAN| IDX_T1_02 |      1 |      1 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |      5 |
    -------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10 AND "CREATED" IS NOT NULL)
           filter(("OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10))

**2. Oracle 19c下的测试**

我们转战19c，相同表、数据、索引，以及测试的SQL1和SQL2，

create table t1 as select * from dba_objects;
    create index idx_t1_01 on t1(owner, object_name, created);
    create index idx_t1_02 on t1(owner, created, object_name);
    select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    select /*+ index(T1,IDX_T1_02) */ owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

Oracle还是选择的idx\_t1\_01，

SQL> select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    
    
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |      1 |        |     3 (100)|          |      0 |00:00:00.01 |      10 |
    |*  1 |  INDEX RANGE SCAN| IDX_T1_01 |      1 |      1 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |      10 |
    ----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10 AND "CREATED" IS NOT NULL)
           filter(("OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED">SYSDATE@!-10))

而且强制用idx\_t1\_02的成本值，和idx\_t1\_01相同，

SQL> select /*+ index(T1, IDX_T1_02) */  owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;
    
    
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation        | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |      1 |        |     3 (100)|          |      0 |00:00:00.01 |       4 |
    |*  1 |  INDEX RANGE SCAN| IDX_T1_02 |      1 |      1 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
    ----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - access("OWNER"='SYS' AND "CREATED">SYSDATE@!-10 AND "OBJECT_NAME" LIKE 'DBA_HIST%' AND "CREATED" IS NOT NULL)
           filter("OBJECT_NAME" LIKE 'DBA_HIST%')

从trace看两个索引叶子块数量是相同的，

![format_png 5][]

但是SQL1的成本，我们看到是3.000578，

![format_png 6][]

SQL2的成本是3.000691，

![format_png 7][]

和11g的trace比较一下，11g的trace中SQL的成本是保留两位小数，都是2.00，而在19c，SQL的成本是保留6位小数，因此SQL2的成本是比SQL1高的，所以通过COST比较就可以得出选择的执行计划。没牵扯到索引叶子块数量和索引名称的字母顺序。

有理由猜测，11g中两个执行计划对应的COST很可能在小数两位以内是相同的，例如一个是2.001，一个是2.002，因为trace只显示小数点右侧两位，所以看到的是2.00，都是相同的，其实不同，

Index: IDX_T1_01
             Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0
             
    Index: IDX_T1_02
             Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

19c中，直接展示6位小数，因此就可以看出两者的不同，

Index: IDX_T1_02
             Cost: 3.000578  Degree: 1  Resp: 3.000578  Card: 0.000364  Bytes: 0.000000
    Index: IDX_T1_02
             Cost: 3.000691  Degree: 1  Resp: 3.000691  Card: 0.000364  Bytes: 0.000000

另外，上述SQL有些特殊，返回记录数是0，多少有些左右成本的计算了，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>sysdate-10;

如果用这些条件，返回记录数>0，默认还是会选择idx\_t1\_01的索引，但此时SQL1和SQL2的COST就会大相径庭，在整数位就可以看出不同了，

select owner,object_name,created from t1 where object_name like 'DBA_HIST%' and owner='SYS' and created>to_date('2019-04-17 01:03:50','yyyy-mm-dd hh24:mi:ss');

虽然这案例某些地方有些极端了，但是从这个过程当中，能得出以下几点，

1. 从trace中COST值的显示位数的增加，我们知道Oracle也是不断演进中。

2. 尤其在CBO下，一条SQL执行计划的成本，Oracle的计算和选择还是很精妙的，10053就像我们应用软件的日志，为我们呈现出Oracle选择的过程和依据，这是非常值得借鉴的。

**近期更新的文章：**

《[Oracle 11g的examples静默安装][Oracle 11g_examples]》

《[同名的同义词和视图解惑][Link 1]》

《[v$和v\_$的一些玄机][v_v]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[format_png]: /images/20221120/b17aaff7e3df4226bc58c29412034b66.png
[format_png 1]: /images/20221120/62a82583b27f4b8d90bd23add527abff.png
[format_png 2]: /images/20221120/413c42efe3804ce4b73eca248c38aac9.png
[format_png 3]: /images/20221120/a8ecf7c7f3394fa8afe8021347992849.png
[format_png 4]: /images/20221120/e503f9a794554befb74136d2bd4789bd.png
[format_png 5]: /images/20221120/01c040779b444eb4ae9454f2e1fa655d.png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW40WjJRcHpscEFGRFNIRE95bktRWURsSTNlU1hJemJ3ZUtMS0ZPWHh6WWliZFMzSjVpYzlBdUR5MnVuY1JpY0tTMlU5V2tCTm9OVWx6R1EvNjQw?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW40WjJRcHpscEFGRFNIRE95bktRWUR6cnZCZmlhSnBZZUFkdjFrdnIxRGphTEg2TFpHQmhVRlBWZ05NZ1Y5WWQ0aWJrbHpHdzhiWU53dy82NDA?x-oss-process=image/format,png
[Oracle 11g_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5ccddf52cda2812771d563eea1db4c3b66f9891869920171bbe5650a45a2865bde0100e&idx=1&mid=2247491647&scene=21&sn=eca45fd1d7dffbf91ab199198adc1591#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c9ddf52cdfe10f04528a52dc3522a0bce8e452ad8f28aa72546ac95c83399c23fbf563&idx=1&mid=2247491642&scene=21&sn=9f4541a6750ff754c17d6d9ddd1da09c#wechat_redirect
[v_v]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c2ddf52cd4ed4a1a6de5d89f7a6638d8d9378c66739e63ebfd7f670002d76212eb88a2&idx=1&mid=2247491633&scene=21&sn=56f7465a50792828e5c679c69c2f6182#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect