block_sample für gather_table_stats

Dass man die Sample-Größe für die dbms_stats-Prozeduren seit Release 11 in aller Regel nicht mehr anpassen muss, da die AUTO_SAMPLE_SIZE sehr gute Resultate liefert, hat Greg Rahn schon vor einigen Jahren festgestellt:

Overall the 11g DBMS_STATS has been enhanced to gather stats in less time, but in my opinion the significant enhancement is to AUTO_SAMPLE_SIZE which yields near 100% sample accuracy in 10% sample time.

Trotzdem hatte ich mir zuletzt Gedanken darüber gemacht, ob man mit der Option block_sample die Laufzeit der Statistik-Erstellung signifikant reduzieren kann. Die Dokumentation erklärt diese Option folgendermaßen:

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Ein kleiner Test zeigt, dass die Option tatsächlich einen deutlichen Einfluss auf die Leseoperationen der Statistikerfassung hat:

-- Anlage einer relativ großen Testtabelle
drop table big_t;

create table big_t
as
with
base_data
as
(
select rownum id1
  from dual
connect by level <= 1000000
)
,
mult
as
(
select rownum id2
  from dual
connect by level <= 30
)
select rownum id
     , 'aaa' col2
  from mult
     , base_data;

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'BIG_T')
 
exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1)

exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1, block_sample=>TRUE)

Die Ergebnisse für die angesprochenen Versionen sehen folgendermaßen aus:

Version	Parameter	Laufzeit
10.2.0.4	ohne	33.86 sec.
10.2.0.4	estimate_percent: 1	12.31 sec.
10.2.0.4	estimate_percent: 1; block_sampling	1.01 sec.
11.1.0.7	ohne	13.07 sec.
11.1.0.7	estimate_percent: 1	2.40 sec.
11.1.0.7	estimate_percent: 1; block_sampling	1.78 sec.

Dazu noch ein paar Kommentare:

• die deutlichen Laufzeitunterschiede ergeben sich aus der unterschiedlichen I/O-Performance der beiden Testsysteme (sehr langsame Platten für 10.2.0.4)
• ein 10046er Trace zeigt, dass die zugehörigen Queries mit der Klausel SAMPLE bzw. SAMPLE BLOCK ausgeführt werden.
• mit SAMPLE BLOCK sinkt die Anzahl der erforderlichen LIOs und PIOs in beiden Versionen; Für die Queries mit SAMPLE werden alle Tabellenblocks gelesen, während SAMPLE BLOCK tatsächlich nur auf einen gewissen Prozentsatz liest, der ungefähr dem geforderten Wert entspricht (ermittelt mit AUTOTRACE in 11.1.0.7):
• FTS ohne Sampling: 30812  consistent gets
• FTS mit sample(1): 30812  consistent gets
• FTS mit sample block (1): 327  consistent gets
• FTS mit sample block (10): 3399  consistent gets

Für die Erstellung von Statistiken für sehr große Tabellen könnte die block_sample-Option demnach auch in Versionen >= 11 immer noch interessant sein, da sie die erforderlichen Leseoperationen dramatisch reduzieren kann. Das gilt aber natürlich nur dann, wenn sichergestellt ist, dass die Blocks ähnliche Verteilungsmuster besitzen wie die gesamte Tabelle. Die Qualität der mit Hilfe von Sampling erzeugten Statistiken wäre aber ohnehin noch mal ein anderes Thema.