Insights into Statistics, Query Optimization and the Oracle Optimizer


The Oracle Optimizer and ADWC - Statistics and Bulk Load

It's time for some posts on the Oracle Autonomous Data Warehouse Cloud and the enhancements we've made to Statistics Management and the Oracle Optimizer. This is Part 1, and it covers statistics maintenance during bulk load. I'll add links to the other posts when they become available.  My scripts on the topic of autonomous are stored in GitHub here. The scripts for this post are here. Statistics and Bulk Loading Consider an empty fact table called FACT1. Let's populate...

Wednesday, May 9, 2018 | Statistics | Read More

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 | Statistics | Read More

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

Introduction It’s common to have multi-terabyte partitioned tables in an Oracle database these days. If you are not there yet but you’re heading that way, then you need to know about how to maintain statistics on large tables. I will cover this in a series of posts as follows: Part 1 (this post) – Concepts and implementation Part 2 – Incremental statistics and partition exchange loading Part 3 – New to Oracle Database Cloud Services (Oracle Database 12c Release 2) There are some...

Friday, December 16, 2016 | Statistics | Read More

How does the METHOD_OPT parameter work?

In last week’s post we addressed the most popular question surrounding statistics gathering, what sample size should be used? Once that decision has been made, folks typically turn their attention to histograms and the time honored question of, do I need histograms or not? Regardless of which side of this religious debate you come down on, you are going to need to figure out what to set the METHOD_OPT parameter to. This post explains in detail what the METHOD_OPTparameter...

Monday, April 15, 2013 | Statistics | Read More

How does AUTO_SAMPLE_SIZE work in Oracle Database 11g?

Note that if you're interested in learning about Oracle Database 12c, there's an updated version of this post here. When it comes to gathering statistics, one of the most critical decisions you have to make is, what sample size should be used? A 100% sample will ensure accurate statistics but could take a really long time. Whereas a 1% sample will finish quickly but could result in poor statistics. The ESTIMATE_PERCENT parameter in the DBMS_STATS.GATHER_*_STATS procedures...

Monday, April 8, 2013 | Statistics | Read More

Lies, damned lies, and statistics Part 2

There was huge interest in our OOW session last year on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. In order to help dispel the mysteries surround statistics management we have created a two part white paper series on Optimizer statistics.  Part one of this series was released in November last years and describes in detail, with worked examples, the different concepts of Optimizer statistics. Today we have...

Monday, April 9, 2012 | Statistics | Read More

Integrated Cloud Applications & Platform Services