Wednesday, June 8, 2011

Flashback Query "AS OF" - Tablescan costs

This is just a short note prompted by a recent thread on the OTN forums. In recent versions Oracle changes the costs of a full table scan (FTS or index fast full scan / IFFS) quite dramatically if the "flashback query" clause gets used.

It looks like that it simply uses the number of blocks of the segment as I/O cost for the FTS operation, quite similar to setting the "db_file_multiblock_read_count" ("dbfmbrc"), or from 10g on more precisely the "_db_file_optimizer_read_count", to 1 (but be aware of the MBRC setting of WORKLOAD System Statistics, see comments below) for the cost estimate of the segment in question.

This can lead to some silly plans depending on the available other access paths as can be seen from the thread mentioned.

Actually it seems to be quite "hard-coded" in the sense of that even with System Statistics aka. CPU Costing switched off ("traditional I/O based costing") the cost corresponds to the number of blocks which is different from the result when setting "dbfmbrc" to 1 and using traditional I/O based costing.

This can be seen from the simple test case provided below.

Prior versions seem to treat the case different - the current behaviour seems to have been introduced in 10.2.0.1, setting the optimizer features to 10.1.0.5 for example leaves the cost unchanged when using the "Flashback Query" clause.

By the way: At runtime the multi-block I/O of the FTS operation seems to be using the normal settings, so it attempts to read multiple blocks at a time and not only a single one. Of course the consistent gets of a flashback query can potentially cause a lot of additional work, so an increased cost estimate is not unreasonable in principle.

It also looks like that using different points in time / past SCNs do not change the cost estimate, so there seems not to be any dynamic "proration" depending on the point in time specified.


set echo on linesize 200 feedback off trimspool on tab off

drop table t;

purge table t;

-- Create a table with 10,000 blocks
-- Use a MSSM tablespace to get exactly 10,000
create table t
pctfree 99
pctused 1
as
select
rownum as id
, rpad('x', 1000) as filler
from
dual
connect by
level <= 10000
;

exec dbms_stats.gather_table_stats(null, 't', estimate_percent => null)

select
blocks
from
user_tables
where
table_name = 'T'
;

set pagesize 0

-- Default costs
explain plan for
select * from t
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));

-- Flashback Query
explain plan for
select * from t as of timestamp systimestamp
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));

-- Flashback Query
-- with disabled System Statistics / CPU Costing
-- gives you exactly "blocks" + 1 (probably due to "_tablescan_cost_plus_one")
explain plan for
select /*+ no_cpu_costing */ * from t as of timestamp systimestamp
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));

-- Flashback Query
-- with 10.1.0.5 Optimizer features
explain plan for
select /*+ optimizer_features_enable('10.1.0.5') */ * from t as of timestamp systimestamp
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));

-- The cost calculation with Flashback Query
-- seems to correspond to a dbfmbrc set to 1 for the segment
-- Note: This does not give the expected results if a MBRC has been defined
-- in the WORKLOAD System Statistics because the MBRC overrides the
-- "_db_file_optimizer_read_count" parameter if CPU Costing is enabled
explain plan for
select /*+ opt_param('_db_file_optimizer_read_count', 1) */ * from t
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));

-- But not exactly:
-- Traditional I/O based costing comes to a different result
explain plan for
select /*+ no_cpu_costing opt_param('_db_file_optimizer_read_count', 1) */ * from t
;

select * from table(dbms_xplan.display(null, null, 'basic +cost'));


This is what I get from 11.2.0.2:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t;
SQL>
SQL> purge table t;
SQL>
SQL> -- Create a table with 10,000 blocks
SQL> -- Use a MSSM tablespace to get exactly 10,000
SQL> create table t
2 pctfree 99
3 pctused 1
4 as
5 select
6 rownum as id
7 , rpad('x', 1000) as filler
8 from
9 dual
10 connect by
11 level <= 10000
12 ;
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't', estimate_percent => null)
SQL>
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T'
7 ;

BLOCKS
----------
10000
SQL>
SQL> set pagesize 0
SQL>
SQL> -- Default costs
SQL> explain plan for
2 select * from t
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 2715 (1)|
| 1 | TABLE ACCESS FULL| T | 2715 (1)|
-----------------------------------------------
SQL>
SQL> -- Flashback Query
SQL> explain plan for
2 select * from t as of timestamp systimestamp
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 10006 (1)|
| 1 | TABLE ACCESS FULL| T | 10006 (1)|
-----------------------------------------------
SQL>
SQL> -- Flashback Query
SQL> -- with disabled System Statistics / CPU Costing
SQL> -- gives you exactly "blocks" + 1 (probably due to "_tablescan_cost_plus_one")
SQL> explain plan for
2 select /*+ no_cpu_costing */ * from t as of timestamp systimestamp
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

------------------------------------------
| Id | Operation | Name | Cost |
------------------------------------------
| 0 | SELECT STATEMENT | | 10001 |
| 1 | TABLE ACCESS FULL| T | 10001 |
------------------------------------------
SQL>
SQL> -- Flashback Query
SQL> -- with 10.1.0.5 Optimizer features
SQL> explain plan for
2 select /*+ optimizer_features_enable('10.1.0.5') */ * from t as of timestamp systimestamp
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 2715 (1)|
| 1 | TABLE ACCESS FULL| T | 2715 (1)|
-----------------------------------------------
SQL>
SQL> -- The cost calculation with Flashback Query
SQL> -- seems to correspond to a dbfmbrc set to 1 for the segment
SQL> -- Note: This does not give the expected results if a MBRC has been defined
SQL> -- in the WORKLOAD System Statistics because the MBRC overrides the
SQL> -- "_db_file_optimizer_read_count" parameter if CPU Costing is enabled
SQL> explain plan for
2 select /*+ opt_param('_db_file_optimizer_read_count', 1) */ * from t
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 10006 (1)|
| 1 | TABLE ACCESS FULL| T | 10006 (1)|
-----------------------------------------------
SQL>
SQL> -- But not exactly:
SQL> -- Traditional I/O based costing comes to a different result
SQL> explain plan for
2 select /*+ no_cpu_costing opt_param('_db_file_optimizer_read_count', 1) */ * from t
3 ;
SQL>
SQL> select * from table(dbms_xplan.display(null, null, 'basic +cost'));
Plan hash value: 1601196873

------------------------------------------
| Id | Operation | Name | Cost |
------------------------------------------
| 0 | SELECT STATEMENT | | 5966 |
| 1 | TABLE ACCESS FULL| T | 5966 |
------------------------------------------
SQL>

2 comments:

Martin Preiss said...

Randolf,
when I replicated your experiment in 11.2.0.1 I was a little bit surprised to get different results for "select /*+ opt_param('_db_file_optimizer_read_count', 1) */ * from t" - in this case my result was the same as with the optimizer_features_enable('10.1.0.5')-hint. Apparently the difference came from my system statistics - after getting back to noworkload statistics I got your results. With system statistics _db_file_optimizer_read_count seems to be (almost) ignored and the MBRC from aux_stats$ is used:
[code]
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_MAIN CPUSPEEDNW 1000000
SYSSTATS_MAIN SREADTIM 5
SYSSTATS_MAIN MREADTIM 30
SYSSTATS_MAIN CPUSPEED 1000000
SYSSTATS_MAIN MBRC 12

explain plan for
select /*+ opt_param('_db_file_optimizer_read_count', 1) */ * from t;

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 5001 (0)|
| 1 | TABLE ACCESS FULL| T | 5001 (0)|
-----------------------------------------------

explain plan for
select /*+ opt_param('_db_file_optimizer_read_count', 32) */ * from t;

-----------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------
| 0 | SELECT STATEMENT | | 5002 (0)|
| 1 | TABLE ACCESS FULL| T | 5002 (0)|
-----------------------------------------------
[/code]

Regards
Martin

Randolf said...

Hi Martin,

thanks for the reminder - WORKLOAD System Statistics were something that I had in mind while preparing the scripts for this post but somehow it slipped then through when putting things together.

I don't know if I have covered this in my "System Statistics" series, but a MBRC defined in the System Statistics will be used by the CBO for cost calculation and takes precedence over all other MBRC related parameters for costing as long as CPU Costing is enabled - even in case of "incomplete" WORKLOAD System Statistics - which means for example missing/invalid SREADTIM / MREADTIM.

Randolf