Enable tracing (level 12) and repeat, then check the statements used to gather
stats.
There might have been small variations in the SQL (e.g. the sample() clause
might have used different group size for blocks).
There might have been significant variations in the SQL - did one of them
gather some histograms or create temporary tables
Did you delete stats or recreate the tables between the false and true tests ?
The stats gathered for the first test might have affected the choice made by
the optimizer in the second test.
Finally, someone might confirm this, I think there used to be a bug with block
sampling that caused something silly to happen - but I don't remember what,
which version, and whether it's been fixed.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Petr Novak <Petr.Novak@xxxxxxxxxxxx>
Sent: 09 March 2017 08:57:14
Cc: ORACLE-L
Subject: dbms_stats block_sample 11.2 ->12.1
Hallo,
I made test with 2 tables about 700M in 11.2.0.4 and 12.1.0.2
dbms_stats.gather_table_stats(,,estimate_percent=>1, block_sample=>false) used
on both DBs 'direct path read'
dbms_stats.gather_table_stats(,,estimate_percent=>1, block_sample=>true) used
on 11.2 'db file sequential read' , on 12.1 mix of 'direct path read' and 'db
file sequential read'.
It is just pure coincidence or known change of implemention ?
Best Regards,
Petr
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
