There are a number of ways to speed up the process of gathering optimizer statistics, but I'm not sure that it's common knowledge just how much of an effect...

There are a number of ways to speed up the process of gathering optimizer statistics, but I'm not sure that it's common knowledge just how much of an effect some simple changes can make. If you have been asking yourself, "why is stats gathering taking so long and what can I do about it?", then this post is for you. If you are already familiar with the different methods of gathering optimizer statistics, you might want to jump to the end of this post where I compare them and...

There are a number of ways to speed up the process of gathering optimizer statistics, but I'm not sure that it's common knowledge just how much of an effect some simple changes can make. If you have...

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...

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 | Name...

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...

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...

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 it...

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...

The ESTIMATE_PERCENT parameter in DBMS_STATS.GATHER_*_STATS procedures controls the percentage of rows to sample when gathering optimizer statistics. What...

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:...

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...

html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family:...

html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family: monospace; font-size:100%;} .u14mnav{font-size:100%;} .u14logo{font-size:100%;} var h1Headers = document.getElementsByTagName("h1"); for (var i = 0; i< h1Headers.length; i++) { h1Headers[i].style.fontSize = '28px'; } Introduction It’s common to have multi-terabyte partitioned tables in an Oracle database these days. If you...

html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family: monospace;...

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,...

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_OPT parameter...

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...

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...

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...

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...

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...

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...

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...

There's now an updated post on auto sample size that covers Oracle Database 12c, but this one is still relevant to Oracle Database 11g... I recently got asked...

There's now an updated post on auto sample size that covers Oracle Database 12c, but this one is still relevant to Oracle Database 11g... I recently got asked this question and thought it was worth a quick blog post to explain in a little more detail what is going on with the new AUTO_SAMPLE_SIZE in Oracle Database 11g and what you should expect to see in the dictionary views. Let’s take the SH.CUSTOMERS table as an example. There are 55,500 rows in the SH.CUSTOMERS tables....

There's now an updated post on auto sample size that covers Oracle Database 12c, but this one is still relevant to Oracle Database 11g... I recently got asked this question and thought it was worth a...