Oracle Database Discussions

hi all,

i knew about dynamic_sampling in the past but only after tom kyte suggested to put it on level 3 in datawarehouse environment i tried to implement it our environment.
well, from oracle docs i read that at level 3 it will sample 64 random blocks before making the decision about the explain plan.
what i saw was that the plan cardinality was so much accurate after dynamic sampling .
especially when joining fact table to dimension table or using like filter on the fact table .

it changed plans in our env from nested loops to hash join or changed the hash order.

without using the dynamic_sampling hint the cbo thought that it would get less rows
so it decide to hash the big table instead of the small table.

my questions are:
1. how this miracle can be with sampling only 64 blocks ?
2. is the dynamic sampling also collect histogram on the fly ?
3. can i use the dynamic_sampling hint to all the tables in the query without mentioning
them in the hint ?
4. is that mean that we are not collecting statistics good enough ?

10x

Zvika