-- Setup
--------
drop table T1 purge;

create table T1 
  tablespace USERS
  as 
  select * from dba_extents
;

drop table T2 purge;

create table T2 
  tablespace USERS
  as 
  select * from dba_extents
;


insert into T1 select * from T1;

insert into T2 select * from T2;
insert into T2 select * from T2;
insert into T2 select * from T2;

commit;

create index I1 on T1(OWNER) tablespace USERS;
create index I2 on T2(OWNER) tablespace USERS;

exec dbms_stats.delete_table_stats(user,'T1');
exec dbms_stats.delete_table_stats(user,'T2');

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size 1');
exec dbms_stats.gather_table_stats(user,'T2', method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size skewonly');
exec dbms_stats.gather_table_stats(user,'T2', method_opt=>'for all columns size skewonly');


set lines 250 pages 999
alter system flush shared_pool;

var MYOWNER varchar2(30);

execute :MYOWNER:='DBSNMP';

select /*+ GATHER_PLAN_STATISTICS MONITOR */
  count(1)
from
    T1,
    T2
where
  T1.OWNER=:MYOWNER and
  T1.BLOCKS=T2.BLOCKS
/

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));