Search

OakieTags

more tags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Affiliations

Home

Dynamic Sampling - Public Synonyms and 11.2.0.2

Submitted by randolf.geist on Wed, 2011-06-29 15:25

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.