Insights into Statistics, Query Optimization and the Oracle Optimizer


How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?

The ESTIMATE_PERCENT parameter in DBMS_STATS.GATHER_*_STATS procedures controls the percentage of rows to sample when gathering optimizer statistics. What percentage of rows should you sample to achieve accurate statistics? 100% will ensure that statistics are accurate, but it could take a long time. A 1% sample will finish much more quickly but it could result in poor statistics. It’s not an easy question to answer, which is why it is best practice to use the default:...

Thursday, November 2, 2017 | 12c | Read More

Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 3

Introduction This post covers how Oracle has improved incremental statistics for partitioned tables in Oracle Database 12c Release 2. If you’re not already familiar with synopses in the context of incremental statistics then take a look at Part 1 and Part 2 before you read on. Beginning with Oracle Database 12c Release 2 there's a new synopsis format that’s significantly more compact than the format used in earlier releases.  For brevity, I’ll refer to the Oracle Database 12c...

Thursday, March 23, 2017 | 12c | Read More

Optimizer Adaptive Features and Upgrading to Oracle Database 12c Release 2

Introduction Here's a reminder of the changes we’ve made to the optimizer’s adaptive feature settings in Oracle Database 12c Release 2. The new default behavior is covered in an earlier post but I want to cover the various options available to you depending on what you're upgrading from. Since the October 2017 database bundle patch, Oracle has included fixes for the adaptive features. This is covered in a more recent post. I have kept this post in place for those that are...

Wednesday, March 1, 2017 | 12c | Read More

New Optimizer Statistics White Paper

A new white paper on Oracle Optimizer statistics is now available. Just go to the Query Optimization page in OTN and click the Understanding Optimizer Statistics with Oracle Database 12c link. Post comments to this blog if you want to give me feedback on the content. Thanks!

Wednesday, March 1, 2017 | 12c | Read More

Setting a Session Parameter Overrides OFE

I received an email recently that demonstrated something the author considered strange when the init.ora parameter optimizer_feature_enable (OFE) is set in a database session. I thought I'd mention it here because the behavior he spotted is expected, but I don't think that it is entirely obvious. Let's assume that you're logged into Oracle Database 12c. Now check the value of a hidden parameter applicable to this database version: select ksppinm name, ksppstvl value from...

Tuesday, July 19, 2016 | 12c | Read More

Global Temporary Tables and Upgrading to Oracle Database 12c - Don't Get Caught Out

Prior to Oracle Database 12c, global temporary tables (GTTs) shared statistics between sessions, so each GTT had one set of statistics visible to all sessions using it. Of course, gathering statistics on GTTs can be very useful because it will help the optimizer find better execution plans, but this advantage was sometimes tempered by the fact that multiple session would see the same set of stats. To resolve this dilemma, Oracle Database 12c included the ability to create sess...

Tuesday, May 24, 2016 | 12c | Read More

Integrated Cloud Applications & Platform Services