Re: dbms_stats block_sample 11.2 ->12.1
Date: Thu, 9 Mar 2017 09:08:22 +0000
Message-ID: <MMXP123MB10370817951735071C9C13C7A5210_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Petr Novak <Petr.Novak_at_trivadis.com> 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 09 2017 - 10:08:22 CET