Toad World® Forums

DYNAMIC_SAMPLING hint

I searched the forums and didn’t see discussion around the DYNAMIC_SAMPLING hint.

It would seem to be a great hint to add on alternatives whenever literals are used in the query. We have found this to be a very efficient hint for queries that use functions like TO_CHAR, SUBSTR, like, etc. Plus it helps with correlated columns when extended statistics are not yet gathered.

Yes, DYNAMIC_SAMPLING hint sometimes can provide better query plan, but due to the risky of dynamic plan generation with unstable performance, we are not implement it into our product, actually we can produce most of plans that Oracle can generate by our rewrite+hint application. Unless you want to direct Oracle to estimate the cardinality of highly volatile tables, I am not recommend this hint for mission critical SQL statements.

DYNAMIC_SAMPLING is mostly used on GTT tables where you cannot capture proper data outside of session.

Regardless Oracle is using them very often in Oracle Retail as well as EBS, So this sampling may be dangerous, but once when you get the correct plan, lock statistic on GTT tables and remove it from query.

Hope this helps.