Thanks for the question, Julian.
Asked: May 12, 2017 - 9:13 pm UTC
Last updated: May 15, 2017 - 4:05 pm UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
Hi there,
I have a few databases that started using dynamic sampling on large tables after we upgraded to version 12.1. I doing my best keeping the table stats updated, still, it happens that someone load a large number of rows to a table then runs a query on it and, since the the data wasn't there when last time the table stats were gathered, the optimizer starts running dynamic sampling at run time.
If the query (or an update) runs let's say 10k times, this behaviour can cause a significant slowdown, we've changes from 8seconds to 29 minute in run times.
- Is there a way to prevent this without rebuilding the stats every time?
- Is there a way to identify queries that are using dynamic sampling from some of the V$ views?
Thank you!
Julian
and Connor said...
Is this answer out of date? If it is, please let us know via a Comment