First page Back Continue Last page Overview Graphics
Dynamic Sampling…
Compile time sampling of data to determine selectivity and volume
Useful when statistics old or non-existent
Useful for queries executed many times or with long execution time
Recursive SQL issued to read random sample of data blocks
Does not save stats in dictionary
Notes:
Dynamic sampling is the Oracle9i R2 feature that allows the cost-based optimizer to sample data blocks at compile time to generate selectivity and/or cardinality (I.e. volume or number of rows) statistics. This can lead to better execution plans if the collected statistics are out of date or non-existent.
Since there is a performance hit at parse time, queries that execute many times or have a long execution time (in relation to the compile duration) may benefit from dynamic sampling.
How does it work? Oracle will issue a recursive (Oracle-generated) SQL statement to read a random sampling of data blocks – at compile time – to collect the statistics.