Keeping your database statistics up to date

One of the most important pieces of advice I like to give customers is the fact that their database statistics should be kept to date. The Cost Based Optimizer (CBO) uses statistics in the database to decide the most efficient path to get the data for individual SQL queries. If the statistics are stale or incorrect then the CBO may pick a less optimal path. It may make the wrong decision with the wrong statistics.

There are lots of articles about how statistics work and how to efficiently update them. Here is a short list from My Oracle Support that may assist in your understanding:

Note 236935.1 - Global statistics - An Explanation
Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Note 237537.1 - How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
Note 122009.1 - How to Retrieve Statistics Generated by ANALYZE SQL
Note 130688.1 - Script: Report Statistics for a Table, its Columns and Indexes with DBMS_STATS
Note 130911.1 - How to Determine if Dictionary Statistics are RDBMS- Generated or User-Defined
Note 1074354.6 - DBMS_STATS.CREATE_STAT_TABLE: What Do Table Columns Mean?
Note 117203.1 - How to Use DBMS_STATS to Move Statistics to a Different Database
Note 130899.1 - How to Set User-Defined Statistics Instead of RDBMS
Note 149560.1 - Collect and Display System Statistics (CPU and IO) for CBO usage
Note 153761.1 - Scaling the System to Improve CBO optimizer
Note 102334.1 - How to Automate Change Based Statistic Gathering - Monitoring Tables

Search for "Managing Optimizer Statistics" in your favorite search engine to get the product documentation.

The question I get from sites, is how often and how can I minimze the impact of the collection of statistics. Again there are guidelines available in the database documentation that suggest that you should update when a certain percentage of change in individual changes in table and how to minimze the impact of collacting the statistics.

The product itself does not have specific guidelines (except that it needs to be done on a regular basis) as we like to fit into the regime that you employ at your site for updating statistics and the growth factors experienced on individual objects due to the way you use the product.

Comments:

this really helps, now i receive the troubles and i donot know how to figure out, i research yahoo and discovered your blog, thanks once again

just one thing, may i post this article on my blog? i will add the source and credit to your site.

regards!

Posted by lee le on December 19, 2009 at 04:13 AM EST #

Wonderful insight

Posted by renaissance clothing on May 06, 2010 at 02:55 AM EST #

This is the best blog I've ever seen in my life! I really appreciate you taking the time out of your busy day to share your this with everyone.

Posted by Buy Imac UK on December 20, 2010 at 03:26 AM EST #

Apple now has Rhapsody as an app, which is a great start, but it is currently hampered by the inability to store locally on your iPod, and has a dismal 64kbps bit rate. If this changes, then it will somewhat negate this advantage for the Zune, but the 10 songs per month will still be a big plus in Zune Pass' favor.

Posted by Vazlav on February 01, 2011 at 05:39 PM EST #

Cant get enough of this blog. Your opinion and facts truly let people know what its all about.

Posted by Stepanie Naret on February 04, 2011 at 07:29 AM EST #

You make blogging look like a walk in the park! I've been trying to blog daily but I just cant find writing material.. you're an inspiration to me and i'm sure many others!

Posted by Kenia Wojeik on February 04, 2011 at 11:09 AM EST #

Dude this blog rules i cant believe i finally found what i was looking for, thanks bro.

Posted by Joanie Kosareff on March 15, 2011 at 12:32 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Anthony Shorten
Hi, I am Anthony Shorten, I am the Principal Product Manager for the Oracle Utilities Application Framework. I have been working for over 20+ years in the IT Business and am the author of many a technical whitepaper, manual and training material. I am one of the product managers working on strategy and designs for the next generation of the technology used for the Utilities and Tax markets. This blog is provided to announce new features, document tips and techniques and also outline features of the Oracle Utilities Application Framework based products. These products include Oracle Utilities Customer Care and Billing, Oracle Utilities Meter Data Management, Oracle Utilities Mobile Workforce Management and Oracle Enterprise Taxation and Policy Management. I am the product manager for the Management Pack for these products.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
9
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today