-- demonstration of execution plan instability caused by frequency histogram
-- with default ESTIMATE_PERCENT
-- JN 28.10.2008 
---------------------------
set echo on
spool plan_flip_setup.log
drop table t_inc;
drop table t_inc2;
drop table tmp_hist;
-- helper table ordered
create table t_inc
(x number,
pad varchar2(100) 
);
-- populate the table: the key i occures i*i*i times
declare 
i pls_integer;
begin
for i in 1 .. 50 loop
 dbms_output.put_line('processing '|| i);
 insert into t_inc
 select i, lpad('x',100,'x') from dual
 connect by level <= i*i*i;
end loop;
commit;
end;
/
---
-- destroy the order of the table
create table T_INC2 as
select * from t_inc
order by DBMS_RANDOM.VALUE(0,1);
---
create index t_inc_idx2 on t_inc2 (x);
--- ANALYZE 
begin
dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true);
end;
/
select num_rows, sample_size from user_tables where table_name = 'T_INC2';
select histogram, num_buckets from user_tab_columns where table_name = 'T_INC2' AND column_name = 'X';
--- repeat the gather stats several times and check the histogram of the column X
-- prepare the table to save results
create table tmp_hist as
select endpoint_value from user_tab_histograms f
where table_NAME  = 'T_INC2' 
and column_name = 'X'  
and 1=0
;
-- loop 
declare 
i pls_integer;
begin 
for i in 1..10 loop 
dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true);
insert into tmp_hist select endpoint_value from user_tab_histograms f
where table_NAME  = 'T_INC2' and column_name = 'X';
commit;
end loop;
end;
/
-- statistics for endpoint_value
-- look for the value with count of half of the maximum count 
select endpoint_value,  count(*) from tmp_hist 
group by endpoint_value  order by endpoint_value;
-- use this value in the flip script
spool off;