Insights into Statistics, Query Optimization and the Oracle Optimizer

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 | Oracle Optimizer | Read More

The Oracle Optimizer and ADWC - Statistics-Based Query Transformation

This is Part 2 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find Part 1 here. In this post, I'm going to cover a clever optimization included in ADWC: Statistics-Based Query Transformation. I'm sure that you can guess what it is already, but let's take it step-by-step. Consider the following query: SELECT max(sale_value) FROM huge_fact_table; To find MAX, the Oracle Database would normally need to scan all of the rows in the...

Friday, May 18, 2018 | Oracle Optimizer | 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 | Oracle Optimizer | Read More

How to Generate a Useful SQL Execution Plan

Introduction There is an old joke the unreconstructed comic Dave Allen used to tell, where a traveler asks a passer-by for directions to a particular town and the passer-by simply says, “Well I wouldn’t start from here if I were you.” When it comes to SQL execution plans, if you start from the wrong place, then you probably won't make it to your destination. The purpose of this blog post is to take stock for a moment and present what I consider to be the best 'default' methods...

Wednesday, April 25, 2018 | Oracle Optimizer | 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 | Oracle Optimizer | Read More

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

Introduction This post covers how you can manage optimizer statistics efficiently when you use partition exchange load (PEL). This technique is used when large volumes of data must be loaded and maximum performance is paramount.  It’s common to see it used in decision support systems and large operational data stores. Make sure you’ve taken a look at Part 1, or you are at least familiar with the concept of incremental statistics so that you know what a synopsis is in the...

Monday, January 30, 2017 | Oracle Optimizer | Read More

Integrated Cloud Applications & Platform Services