Insights into Statistics, Query Optimization and the Oracle Optimizer


The Oracle Optimizer and ADWC - Hints

This is Part 3 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find part 1 here and part 2 here. It's time to take a look at optimizer hints. Here's our test query: select sum(t1.num), sum(t2.num) from table1 t1 join table2 t2 on (t1.id = t2.id); Executing on an ADW database (using the LOW consumer group) yields this plan: ---------------------------------------------------------------------------------------- | Id | Operation ...

Monday, June 11, 2018 | Statistics | Read More

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

Integrated Cloud Applications & Platform Services