Statistics

Managing Optimizer Statistics Paper

February 16, 2009
By Greg Rahn
Managing Optimizer Statistics Paper

Over the past couple days I’ve been reading through the recent paper by Karen Morton entitled “Managing Statistics for Optimal Query Performance“. In this paper Karen goes over many of the topics I have discussed as well (and a few that I have not) in the following blog posts: Troubleshooting Bad Execution Plans There...
Read more »

Tags: ,
Posted in Execution Plans, Optimizer, Statistics | 7 Comments »

DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS

October 14, 2008
By Greg Rahn

I’ve written before on choosing an optimal stats gathering strategy but I recently came across a scenario that I didn’t directly blog about and think it deserves attention. As I mentioned in that previous post, one should only deviate from the defaults when they have a reason to, and fully understand that reason and...
Read more »

Tags: , , , , , , ,
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, Troubleshooting | 31 Comments »

Automatic DB_FILE_MULTIBLOCK_READ_COUNT

August 14, 2008
By Greg Rahn

Note: Originally this experiment was from a post I wrote on the Oracle Forum: Database – General. I recommend that you read Jonathan Lewis’ summarization of the thread instead of reading all 671 posts (as of today). You will spend much less time and get more out of the discussion. One of the new...
Read more »

Tags: ,
Posted in 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics | 13 Comments »

Oracle 11g: Incremental Global Statistics On Partitioned Tables

July 16, 2008
By Greg Rahn
Oracle 11g: Incremental Global Statistics On Partitioned Tables

Previously I blogged about the new and improved DBMS_STATS.AUTO_SAMPLE_SIZE used to calculate NDV in Oracle 11g and now I wanted to touch on another new feature of DBMS_STATS in 11g: Incremental Global Statistics On Partitioned Tables. Before Incremental Global Stats (Two-Pass Method) When DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so...
Read more »

Tags: , , , ,
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Statistics, Troubleshooting, VLDB | 7 Comments »

Using Bitmap Indexes Effectively

May 29, 2008
By Greg Rahn

Recently I was reading this thread, “Trying to make use of bitmap indexes” on the Oracle Forum. Before I had finished a working example, Jonathan Lewis had posted his response which was on par with my thoughts. Since this is a topic I see frequently, I thought I would finish my experiment and publish...
Read more »

Tags: , , , ,
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Performance, SQL Tuning, Statistics, Troubleshooting, VLDB | 6 Comments »

Choosing An Optimal Stats Gathering Strategy

March 26, 2008
By Greg Rahn

Recently the Oracle Optimizer Development Team put out a White Paper entitled Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer. This paper discusses the main differences between 9i and 10g in the subject area of the Optimizer and Statistics. As G.I. Joe said, “Now we know! And knowing is...
Read more »

Tags: , , , , , , , ,
Posted in 10gR2, Execution Plans, Optimizer, Oracle, Statistics, Troubleshooting | 20 Comments »

There Is No Time Like '%NOW%' To Use Dynamic Sampling

March 5, 2008
By Greg Rahn

I recently came across a query in which the Optimizer was making a poor cardinality estimate, which in turn caused inefficient join type, which in turn caused the query to run excessively long. This post is a reenactment of my troubleshooting. The Suspect SQL The original SQL was quite large and had a fairly...
Read more »

Tags: , , ,
Posted in 10gR2, 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, VLDB | 11 Comments »

What Are Your System Statistics?

January 2, 2008
By Greg Rahn

I’ve been working on a few test cases and I’m in search of some real-world data. If your production Oracle database uses system statistics, either Workload Statistics or Noworkload Statistics, and you are willing to share them, please post a comment with the output from the following two queries: select version from v$instance; select...
Read more »

Tags: , ,
Posted in 10gR2, 11gR1, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics | 32 Comments »

Oracle Optimizer Development Team Starts A Blog

December 5, 2007
By Greg Rahn

Since the introduction of the Cost Based Optimizer (CBO) in Oracle 7.0, people have been both fascinated and terrified by it and the statistics that feed it. There has long been a belief that a degree in witchcraft or black magic is required to successfully work with the CBO. Some people feel this shroud...
Read more »

Tags: , , ,
Posted in Execution Plans, Optimizer, Oracle, Performance, Statistics, Troubleshooting | 8 Comments »

Troubleshooting Bad Execution Plans

November 21, 2007
By Greg Rahn

One of the most common performance issues DBAs encounter are bad execution plans. Many try to resolve bad executions plans by setting optimizer related parameters or even hidden underscore parameters. Some even try to decipher a long and complex 10053 trace in hopes to find an answer. While changing parameters or analyzing a 10053...
Read more »

Tags:
Posted in Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, Troubleshooting | 30 Comments »

The Real-World Performance Group: Oracle OpenWorld 2007 Recap

November 19, 2007
By Greg Rahn

Oracle OpenWorld 2007 has come and gone and from the Real-World Performance Group’s perspective we’d consider it a successful one. The content of this year’s presentations seems to have gone over quite well as shown by the number of hallway comments and emails we received. At least two Oracle bloggers have put up their...
Read more »

Tags: ,
Posted in Data Warehousing, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics, VLDB | 3 Comments »

Oracle 11g: Extended Statistics

October 31, 2007
By Greg Rahn

In the Real-World Performance Roundtable, Part 2: The Optimizer, Schema Statistics, SQL Tuning at Oracle OpenWorld 2006, I worked an example of how the optimizer can have difficulty estimating the correct cardinality when there is data correlation. (The Zodiac example can be found on pages 46-49 of the presentation.) In Oracle 11g, there has...
Read more »

Tags: , , , , ,
Posted in 11gR1, Data Warehousing, Execution Plans, Optimizer, Oracle, SQL Tuning, Statistics, VLDB | 13 Comments »