Infrastructure at your Service

During tests in Oracle 19c I recently experienced this:

[email protected]@orcl> select * from demo4 where m=103;
[email protected]@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       | 26439 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO4 |     1 |    10 | 26439  (14)| 00:00:02 |
---------------------------------------------------------------------------

–> The costs of the full table scan are 26439.

Setting back the optimizer_features_enable to 18.1.0 showed different full table scan costs:

[email protected]@orcl> alter session set optimizer_features_enable='18.1.0';
[email protected]@orcl> select * from demo4 where m=103;
[email protected]@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   109K(100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO4 |     1 |    10 |   109K  (4)| 00:00:05 |
---------------------------------------------------------------------------

–> The costs are 109K versus around 26K in 19c.

Why do we have such a difference for the costs of a full table scan between 18c and 19c?
With the CPU-cost model full table scans are computed as follows:

FTS Cost = ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM + “CPU-costs”
REMARK: This is not 100% correct, but the difference is not important here.

In my case:

[email protected]@orcl> select blocks from tabs where table_name='DEMO4';
 
    BLOCKS
----------
     84888
 
[email protected]@orcl> select * from sys.aux_stats$;
 
SNAME                          PNAME                          PVAL1      PVAL2
------------------------------ ------------------------------ ---------- ----------
...
SYSSTATS_MAIN                  SREADTIM                       1
SYSSTATS_MAIN                  MREADTIM                       10
SYSSTATS_MAIN                  CPUSPEED                       2852
SYSSTATS_MAIN                  MBRC                           8
...

I.e.
FTS Cost = ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM + CPU = ((84888/8) x 10)/ 1 + CPU = 106110 + CPU
Considering the additional CPU-cost we are at the costs we see in 18c: 109K
Why do we see only costs of 26439 in 19c (around 25% of 18c)?
The reason is that the optimizer considers “wrong” system statistics here. I.e. let’s check the system statistics again:

SNAME                          PNAME                          PVAL1      PVAL2
------------------------------ ------------------------------ ---------- ---------
SYSSTATS_MAIN                  SREADTIM                       1
SYSSTATS_MAIN                  MREADTIM                       10
SYSSTATS_MAIN                  MBRC                           8

In theory it’s not possible that MREADTIM > SREADTIM * MBRC. I.e. reading e.g. 8 contiguous blocks from disk cannot be slower than reading 8 random blocks from disk. Oracle has considered that and treats the available system statistics as wrong and takes different values internally. The change was implemented with bug fix 27643128. See My Oracle Support Note “Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1)” for details.

I.e. switching the bug fix off results in full table scan costs as in 18c:

[email protected]@orcl> alter session set optimizer_features_enable='19.1.0';
[email protected]@orcl> alter session set "_fix_control"='27643128:OFF';
[email protected]@orcl> select * from demo4 where m=103;
[email protected]@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   109K(100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO4 |	    1 |    10 |   109K  (4)| 00:00:05 |
---------------------------------------------------------------------------

To get the intended behavior in 19c you should make sure that
MREADTIM <= SREADTIM * MBRC
E.g. in my case

[email protected]@orcl> alter system set db_file_multiblock_read_count=12
[email protected]@orcl> exec dbms_stats.set_system_stats('MBRC',12);
[email protected]@orcl> alter session set optimizer_features_enable='19.1.0';
[email protected]@orcl> select * from demo4 where m=103;
[email protected]@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       | 74188 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO4 |	    1 |    10 | 74188   (5)| 00:00:03 |
---------------------------------------------------------------------------
...
 
[email protected]@orcl> alter session set optimizer_features_enable='18.1.0';
[email protected]@orcl> select * from demo4 where m=103;
[email protected]@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	      |       |       | 74188 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO4 |	    1 |    10 | 74188   (5)| 00:00:03 |
---------------------------------------------------------------------------
...

I.e. the costs in 19c and 18c are the same again.

Please consider the following:
– If you’ve gathered or set system statistics then always check that they are reasonable.
– If you do work with a very low SREADTIM and high MREADTIM to favor Index-access (to not use low values for OPTIMIZER_INDEX_COST_ADJ) then make sure that MREADTIM <= SREADTIM * MBRC. Otherwise you may see plan changes when migrating to 19c.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clemens Bleile
Clemens Bleile

Technology Leader & Principal Consultant