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...
Hi visitor. This blog post was originally written in 2012. I still serves as a good introduction to incremental statistics. Once you've read it, there's now...
Hi visitor. This blog post was originally written in 2012. I still serves as a good introduction to incremental statistics. Once you've read it, there's now more on this topic here (part one of a three-part series). Incremental statistics maintenance was introduced in Oracle Database 11g to improve the performance of gathering statistics on large partitioned table. When incremental statistics maintenance is enabled for a partitioned table, Oracle accurately generated global...
Hi visitor. This blog post was originally written in 2012. I still serves as a good introduction to incremental statistics. Once you've read it, there's now more on this topic here (part one of...
Fixed objects are the "X$" tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since...
Fixed objects are the "X$" tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when...
Fixed objects are the "X$" tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements...
Understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to...
Understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance. In order to clarify all of the information surrounding statistics we have put together two whitepapers on optimizer statistics. They are: Best Practices for Gathering Optimizer Statistics with Oracle Database 12c Release 2 Understanding Optimizer Statistics With Oracle Database 12c...
Understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance....
In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a...
In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a customers table where the values in a cust_state_province column are influenced by the values in a country_id column, because the state of California is only going to be found in the United States. If the Oracle Optimizer is not aware of these real-world relationships, it could potentially miscalculate the cardinality...
In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a customers table where the values in...
Gathering optimizer statistics is one of life's necessary evils even if it can take an extremely long time to complete. In this blog post, we discuss one remedy...
Gathering optimizer statistics is one of life's necessary evils even if it can take an extremely long time to complete. In this blog post, we discuss one remedy to improve the efficiency of statistics gathering. Introduction Oracle Database 11g Release 2 (11.2.0.2) introduces a new statistics gathering mode, 'concurrent statistics gathering'. The goal of this new mode is to enable a user to gather statistics on multiple tables in a schema (or database), and multiple...
Gathering optimizer statistics is one of life's necessary evils even if it can take an extremely long time to complete. In this blog post, we discuss one remedy to improve the efficiency of statistics...
Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic...
Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The most common misconception is that DS can be used as a substitute for optimizer statistics, whereas the goal of DS is to augment optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates. So how and when will dynamic statistics be...
Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The...
