Dynamic sampling
================

Dynamic sampling is the database performing additional work on your query to get better information for the optimizer, over and above the available statistics in the dictionary.

For example, if you had:

select * from order where order_date > date '2016-01-01'

then statistics on the order_date column might suffice.

Similarly, if I had:

select * from order where customer_id = 123

then statistics on the customer_dd column might suffice.

But what about:

select * from order where customer_id = 123 and order_date > date '2016-01-01'

Well, we don't (by default) store statistics about the combination of values between customer/order_date. You can't store statistics on *everything* - it's an infinite list of stats you would have to store.

So we may choose to run "a little bit" of your query, ie, if I grab a random sample of rows, I might find that 5% of them meet the criteria "customer_id = 123 and order_date > date '2016-01-01'". So I can now use 5% as a estimate of the total amount of rows I will get back.

Cardinality Feedback
====================
Even with statistics, and even with dynamic sampling, I might *still* be off with my estimate. Continuing the example above, let's say I came up (with dynamic sampling or otherwise), my estimate of 5% of the rows matching the criteria. So if my table had 1000 rows, I'm estimating I'll get 50 rows back. I might choose (say) to use a particular index to do the query.

I then go ahead and run it ... and bam! The estimate was wrong - actually 400 rows comes back matching the criteria. The estimated cardinality (50) did not match the actual cardinality (400). We will *feed back* that information for the *next* execution of the this query. So the next time it is executed, my estimate will use that previous information and choose something closer to 400 rather than 50.

So....

"Does cardinality feedback can also cause plan change?"

Yes.

The recommended means to lock down plans is via SPM

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf