Skip to Main Content
  • Questions

  • extended statistics and dynamic sampling

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Ravi.

Asked: September 27, 2016 - 8:19 pm UTC

Last updated: September 29, 2016 - 1:18 pm UTC

Version: 11g/12c

Viewed 1000+ times

You Asked

Hello AskTom team,

Please correct me if I am wrong, as I understand, "extended statistics" is useful for multi-predicate/non indexed columns for achieving realistic cardinality estimates.

Is there any DB level setting where Oracle generates these extended statistics based on the column(s) usage?

SQL Server has a feature of setting AUTO_CREATE_STATISTICS on/off. I am not comparing both the systems but does oracle has anything similar?

Is it optimizer_dynamic_sampling=4 ?

This will be very useful for reporting systems where users drag-and-drop columns for ad-hoc reporting.

Thanks for your time.

and Connor said...

Yes, we can collect them automatically based on your usage.

Check out this blog post from the optimizer team

https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload


Rating

  (2 ratings)

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

Comments

Ravi B, September 28, 2016 - 2:06 pm UTC

Thanks for your reply.

Is dynamic sampling and extended stats mutually exclusive or complimentary? Could you please tell which is appropriate at which situations?

Thanks!
Chris Saxon
September 29, 2016 - 1:18 pm UTC

Not mutually exclusive but typically different use cases.

If I am running hundreds/thousands of queries of the kind:

where col1 = ... and col2 = ...

Then the last thing I want to do is continuously dynamic sample the data, because dynamic sampling is not free - it uses resources to do it. I'll look at extended stats to get the best stats I can on that column combination.

Conversely, I cant have stats on *every* possible column combination. But for a data warehouse, where my queries might take minutes to run, I might definitely consider doing a lot of sampling because its still a tiny proporition of the overall run time, and that sampling might handle lots of rare or odd combinations of columns, joins etc.

Hope this helps.

Thanks!

Ravi B, September 29, 2016 - 4:25 pm UTC

Thanks, that helps!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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