Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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...

Take a look at this question - lots of details in there to help

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534476300346475576

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.