为什么CBO不选择低成本的跳过扫描而不使用统计数据？

Question

在下面的脚本中，CBO选择了一个INDEX FAST FULL SCAN而不是一个INDEX SKIP SCAN (这可以通过使用UNION的查询来说明，但是您可以证明这在explain plan for select * from foo where baz=1中是正确的)。尽管跳过扫描是成本较低的选择，但还是做出了此选择。

一旦表被分析，跳过扫描是首选的(尽管相对成本实际上上升)。似乎CBO根本不考虑动态抽样的跳过扫描，这是真的吗？如果不是，为什么在收集统计数据之前不选择跳过扫描？

模式：

create table foo( bar integer, 
                  baz integer, 
                  qux char(99), 
                  constraint pk_foo primary key (bar, baz) )
             organization index compress;
--table FOO created.
insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;
--999,999 rows inserted.
commit;
--committed.

预分析：

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   132 | 16764 |  8480  (53)| 00:01:34 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 | <-- why full?
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    44 |  5588 |   411   (0)| 00:00:05 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
*/

分析：

analyze table foo compute statistics;
--table FOO analyzed.

事后分析：

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  6038  (84)| 00:01:07 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 | <-- makes sense
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |  4033   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 |
--------------------------------------------------------------------------------
*/

类似于SQLFiddle 这里和这里的东西

Answer 1

似乎CBO根本不考虑动态抽样的跳过扫描，这是真的吗？

实际上，这很容易验证，您可以通过启用10053跟踪来做到这一点。您将看到优化器甚至根本不考虑跳过扫描。原因是"_optimizer_skip_scan_guess“参数。此参数的默认值为FALSE，这意味着优化器将不考虑跳过扫描，因为它所具有的全部是“猜测”的选择性，这是动态抽样的情况。

如果您将"_optimizer_skip_scan_guess“设置为真，将考虑跳过扫描，这也可以与10053跟踪再次确认。

PS:您的db_file_multiblock_read_count参数似乎低于默认值。在我的11.2.0.4沙箱中，默认值为128，在收集表上的统计数据之后，索引FFS的成本约为索引SS的三分之一。

编辑:添加输出

SQL> create table foo( bar integer, baz integer, qux char(99), constraint pk_foo primary key (bar, baz) ) organization index compress;

Table created.

SQL> insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;

999999 rows created.

SQL> commit;

Commit complete.

原件：

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |  1302   (1)| 00:00:16 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

启用跳过扫描以获得猜测的选择性：

SQL> alter session set "_optimizer_skip_scan_guess"=true;

Session altered.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3033162421

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |   772   (1)| 00:00:10 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   350   (1)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BAZ"=1)
       filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)

24 rows selected.

只是为了完整：

SQL> analyze table foo compute statistics;

Table analyzed.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  1717   (2)| 00:00:21 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1002   (0)| 00:00:13 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

19 rows selected.