Why does the CBO not chose a lower cost skip scan without stats?

Posted on

Question :

In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan for select * from foo where baz=1). This choice is made despite the skip scan being the lower cost choice.

Once the table is analyzed, the skip scan is preferred (despite the relative cost actually rising). It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? If not, why is the skip scan not chosen before the stats are gathered?

schema:

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.

pre-analyze:

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:

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

post-analyze:

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 |
--------------------------------------------------------------------------------
*/

something similar on SQLFiddle here and here

Answer :

It seems the CBO does not consider a skip scan at all with dynamic
sampling, is this true?

Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the “_optimizer_skip_scan_guess” parameter. The default value for this parameter is FALSE, meaning the optimizer will not consider skip scan when all it has is “guessed” selectivity, which is the case with dynamic sampling.

If you set “_optimizer_skip_scan_guess” to TRUE, skip scan will be considered, this can be also confirmed with the 10053 trace again.

PS: your db_file_multiblock_read_count parameter seems to be lower than the default value. On my 11.2.0.4 sandbox, with the default value of 128, after collecting statistics on the table, index FFS had about one-third of the cost of index SS.

Edit: added output

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.

Original:

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)

Enable skip scan for guessed selectivity:

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.

Just for completeness:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *