Dynamic Sampling - Public Synonyms and 11.2.0.2

This is just a short heads-up note regarding a bug that obviously has been introduced with 11.2.0.2: If you happen to have a public synonym for a table that is called differently than the original object then dynamic sampling will not work in 11.2.0.2.

The reason is that the generated query used for the dynamic sampling does not resolve the synonym name properly - it resolves the object owner but uses the synonym name instead of the actual table name. The same issue happens by the way when using a private synonym, however the query is then still valid and works even when using the synonym name.

The bug can only be reproduced in 11.2.0.2, in all previous versions including 11.2.0.1 the synonym resolution seems to work as expected for the dynamic sampling query, so it seems to be a problem introduced in that patch set.

Although the bug is quite obvious and can be nasty, a quick search on MOS didn't reveal anything suitable. Neither I could see that a corresponding bugfix was already included in one of the available PSUs on top of 11.2.0.2.

Here is a simple testcase for reproducibility:

--------------------------------------------------------------------------------
--
-- File name: dynamic_sampling_public_synonym_testcase.sql
--
-- Purpose: 11.2.0.2 fails to run a dynamic sampling query
-- if the original query uses a public synonym
-- that is called differently than the original object
--
-- The problem can be seen in the 10053 trace file:
-- The synonym is not properly resolved, hence the
-- recursive query fails silently with an ORA-00942 error
--
-- Author: Randolf Geist http://oracle-randolf.blogspot.com
--
-- Last tested: June 2011
--
-- Versions: 10.2.0.4
-- 10.2.0.5
-- 11.1.0.7
-- 11.2.0.1
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on timing on linesize 200 trimspool on tab off pagesize 99

drop table t;

purge table t;

drop public synonym t_synonym;

create table t
pctfree 99
pctused 1
as
select
rownum as id
, rownum as id2
, rpad('x', 500) as filler
from
dual
connect by
level <= 10000
;

exec dbms_stats.gather_table_stats(null, 't')

create public synonym t_synonym for t;

explain plan for
select /*+ dynamic_sampling(4) */ * from t where id = id2;

select * from table(dbms_xplan.display);

alter session set tracefile_identifier = 'dynamic_sampling_public_synonym';

alter session set events '10053 trace name context forever, level 1';

explain plan for
select /*+ dynamic_sampling(4) */ * from t_synonym where id = id2;

alter session set events '10053 trace name context off';

select * from table(dbms_xplan.display);

The last EXPLAIN PLAN does not use dynamic sampling in 11.2.0.2 hence comes up with an incorrect cardinality estimate. In previous versions this works as expected. The 10053 trace file shows the incorrect recursive query.

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>

Transitive Closure - Outer Joins

The Cost Based Optimizer (CBO) supports since at least Oracle 9i the automatic generation of additional predicates based on transitive closure.

In principle this means:

If a = b and b = c then the CBO can infer a = c


As so often with these optimizations the purpose of these automatically generated additional predicates is to allow the optimizer finding potentially more efficient access paths, like an index usage or earlier filtering reducing the amount of data to process.

So far I was aware of such additional predicates only when literals were involved, so if "a = 10 and a = b" then Oracle will automatically add "b = 10" (and in Oracle 9i remove actually the "a = b" predicate so you end up with "a = 10 and b = 10" but no longer "a = b", see Jonathan Lewis on transitive closure and cartesian merge join).

According to MOS document "Transitivity and Transitive Closure [ID 68979.1]" Oracle 11g is supposed to support three kinds of transitive closure. In addition to the already mentioned join / literal predicate case these are the transitivity of join predicates without any literals involved ("a = b and b = c" then "a = c") and the literal predicate case applied to outer joins ("a = 10 and a = b(+)" then "b(+) = 10" which means that the filter will be applied logically to b before the join, not after).

However I couldn't witness yet any working example of the pure join predicate transitive closure mentioned in the MOS article.

On the OTN forums there was recently a question about partition pruning not taking place but the case there really turns out to be about transitive closure and outer joins (although the partitioning and parallel execution involved in the thread probably helps to confuse the issue).

It looks like that Oracle does not apply transitive closure across outer joins in the sense of:

a = 10 and a = b(+) and b = c(+)

then Oracle will add automatically the "b(+) = 10" predicate as outlined above but it will not add "c(+) = 10" across the second outer join (it would add a "c = 10" predicate if this were inner joins).

There might be a valid reason to prevent this from happening in general (the most appealing to me seems that "a = b (+) and b = c(+)" is not equal to "a = b(+) and a = c(+)") but at least in this particular case I don't see why the "c(+) = 10" predicate should not be added automatically.

If anyone sees a valid explanation for this behaviour (viz: a general rule that gets violated when doing so) I'm open for suggestions.

If the expression is changed into

a = 10 and a = b(+) and a = c(+)

then Oracle happily adds both the b(+) = 10 and c(+) = 10 predicates. Of course you will appreciate that the changed expression is semantically not the same as the previous one and the results might differ.

The case on the OTN thread is actually a bit more interesting since it includes an outer join from one table to two other tables which Oracle in general does not support directly using its native Oracle joins but only with the help of LATERAL views - there is a good explanation here by the Oracle Optimizer Group.

The interesting part stripped to a bare minimum looks like this:

set echo on

drop table t;

purge table t;

create table t
/*
partition by list (pkey)
(
partition p_1 values (1)
, partition p_2 values (2)
, partition p_3 values (3)
)
*/
as
select
rownum as id
, mod(rownum, 3) + 1 as pkey
, rownum as id2
, rpad('x', 100) as filler
from
dual
connect by
level <= 30000
;

exec dbms_stats.gather_table_stats(null, 't')

alter session set tracefile_identifier = 'trans_closure_outer_join';

alter session set events '10053 trace name context forever, level 1';

explain plan for
select
*
from
t t1
left outer join
(select * from t where id >= 4000) t2
on
t1.id = t2.id
and t1.pkey = t2.pkey
left outer join
(select * from t where id >= 2000) t3
on
t2.id2 = t3.id2
and t1.pkey = t3.pkey
where
t1.pkey = 2
order by
t1.id
;

alter session set events '10053 trace name context off';

So we outer join T3 to T1 and T2. Although the PKEY predicate is actually coming from T1 and therefore on its own would definitely qualify for transitive closure even with the documented restriction in place (that would apply if t2.pkey = t3.pkey was used instead) the additional predicate doesn't get generated obviously due to the additional outer join between T2 and T3 (and we end up with a lateral view due to this when looking into the execution plan and generated CBO trace file).