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.
