Oracle Scratchpad

January 6, 2010

Copy Stats

Filed under: Statistics — Jonathan Lewis @ 2:24 pm BST Jan 6,2010

In a recent posting on OTN, someone was having trouble copying stats from one index to another using the import_index_stats and export_index_stats procedures from package dbms_stats modifying the contents of their “stat table” between the export and import.


June 2, 2009

Fixed Stats

Filed under: Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm BST Jun 2,2009

There was a question about the notorious dba_extents view on the newsgroup a little while ago.

This is a view that has been popularly abused over the years despite warnings such as a note of mine in 2006 and even back as far as 2001 this one from Connor McDonald.

There are many reasons why this is a nasty view – and the number of reasons has grown as Oracle has evolved – but David FitzJarell has highlighted an important point in one of his blog postings that was worth a mention. So here it is.

May 28, 2009

Frequency Histograms

Filed under: CBO,Histograms,Statistics,Tuning — Jonathan Lewis @ 7:34 pm BST May 28,2009

In a recent article on changes to the calculation of cardinality with frequency histograms I made a comment about writing programs to create fake but realistic and complete frequency histograms to work around the problems of dramatic changes in execution plan that can occur if you let Oracle create the histograms by gathering stats.

As a follow-up to that comment, here’s a little script to demonstrate the method (for a numeric column), and a few points to remember when using this method.


May 11, 2009

Cardinality feedback

Filed under: CBO,Execution plans,Statistics,Troubleshooting — Jonathan Lewis @ 8:09 pm BST May 11,2009

One of my faviourite presentations in Collaborate 09 was a quick tip (30 minutes) on Tuning by Cardinality Feedback – i.e. comparing the optimizer’s predictions of cardinality with the actual rowcounts returned when you run the query.

The strategy is one that I first saw demonstrated in a presentation by Wolfgang Breitling a few years ago at one of the Hotsos seminars – but this latest example demonstrated an important variation on the theme in a short, precise, lesson.


May 6, 2009

Philosophy – 1

Filed under: Infrastructure,Philosophy,Statistics,Troubleshooting — Jonathan Lewis @ 6:15 pm BST May 6,2009

There are some ideas about Oracle that are so fundamental that they should be lodged in your brain and floating behind your eyes whenever you want to investigate SQL performance problems. Here’s one of them:

Histograms and bind variables exist for diametrically opposed reasons – they won’t work well together without help.

You use bind variables because you want everyone to share the same child cursor for an SQL statement because that statement is going to be used very frequently, everyone is going to do the same (small) amount of work with it, the same execution plan should be ideal for everyone, and you don’t want to re-optimise it every time you use it because the overheads for optimisation would probably be greater than the resources need to run the statement. Typically we want to make great (but not exclusive) use of bind variables in OLTP systems.

You create histograms because statements that are virtually identical do enormously different amounts of work, need different execution plans, and the work done in optimising is tiny compared to the work done by the statements, and getting the wrong execution plan would lead to a huge waste of resources. Typically you need to generate histograms in data warehouse or decision support systems where the queries can be brutal and expensive.

Spot the contradiction – one technology is supposed to give you one plan that everyone uses; the other technology is supposed to give each individual the plan that’s best for them.

Remember that and you will remember to be cautious about sticking histograms into OLTP systems, and won’t be tempted to turn absolutely every literal into a bind variable.

Footnote: It seems that this article should have been called “Philosophy – 2″ because I’ve just rediscovered a short note called “Philosophy”

[The Philosophy Series]

May 4, 2009

IOUG Day 1

Filed under: CBO,Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 10:00 pm BST May 4,2009

I attended four sessions today (one of them was a 2-hour session), and the highlight of the day for me was a lunchtime “quick tip” from Michelle Deng of Sanofi Aventis who gave a 30-minute talk with the title “Cardinality Analysis – a life saver for DBAs and Developers”.


April 30, 2009

Analyze this!

Filed under: CBO,Execution plans,Statistics,Troubleshooting — Jonathan Lewis @ 8:29 pm BST Apr 30,2009

There’s a question on the Database General forum on OTN containing the following observation:

I have some chained rows in some of my tables. I have exported, truncated, import back the data in the table. Then collect the stats. But I have the same Chained_count. Nothing has changed.

If you export, truncate the table, then re-import the data, you expect to get rid of any migrated rows – but if a row is too big to fit in a single block, or has more than 255 columns and a non-null value after the 255th, (either of which will be reported as a chained row) it’s not going to change after exporting and importing; so the first question to ask is: “What’s the definition of the table ?” optionally followed by: “Can you run some SQL to check for very long rows.”


April 23, 2009

Histogram change

Filed under: CBO,Histograms,Statistics,Troubleshooting — Jonathan Lewis @ 7:17 pm BST Apr 23,2009

When you upgrade from to or 11g, watch out for SQL that depends on the existence of frequency histograms.

In (and earlier versions of Oracle) if you have a frequency histogram on a column, and then use a predicate of the form:

where colX = {value not found in histogram}

then the optimizer would calculate a cardinality of one for this predicate (having recorded a density of 1/(2 * num_rows) in the data dictionary (see comments 21 and 22 of this note on dbms_xplan).


April 16, 2009

Virtual Columns

Filed under: Performance,Statistics,Tuning — Jonathan Lewis @ 7:25 pm BST Apr 16,2009

A recent post on asked:

I have read various articles on virtual columns? I still do not understand their advantages other than they save some disk space, one can put logic of virtual columns in a trigger which will save information in a real column. Real column will need some additional disk space, but it will save some cpu time when one is doing selects on “virtual” columns.


March 20, 2009

Hotsos 2009

Filed under: CBO,Oracle,Statistics — Jonathan Lewis @ 7:43 am BST Mar 20,2009

So much for my belief that I’d have some quiet time for catching up with a little internet gossip while attending Hotsos 2009.

The days were busy and I crashed out at about 7:00 pm (local time) each evening and was asleep by 7:30 pm and up by 2:00 a.m – then I spent the morning (until breakfast) writing up notes I had taken the day before. So hardly a moment for blogging or answering questions on OTN.

Best topic of Hotsos (for me, at any rate – there were lots of very good presentations): Amit Poddar’s presentation on the new “approximate NDV” mechanism that Oracle 11g uses to do a one pass, accurate, estimate of the number of distinct values in a column (no more massive sorts for “count(distinct)” and how it manages to keep a “synopsis” for each partition of a partitioned table so that there is no need to sample the entire table when you need to recalculate table-level statistics.

Amit Poddar’s website is no longer online, but he has given me permission to publish the material, so here are the links to a pair of pdf files: the presentation (1.6MB), and the white paper (3.45MB).

The mathematics is brilliant, and I’m going to have to review my previous strategy for stats collection as a consequence.

The upside to starting the day at 2:00 am in Dallas, by the way – no jet lag when I got home !

Update Dec 2010:

Just in from an OTN thread and Greg Rahn; there is a bug relating to synopses and approximate NDV that shows up with partitioned tables and incremental stats – leading to very long stats collection time. The bug number is 8310339, and Greg Rahn recommends applying the fix for bug 8719831.

Update Jan 2012:

A recent OTN note highlights a problem with the Approximate NDV code when collecting statistics on external tables. MOS notes 1290722.1 or 1305127.1 are  relevant.


December 28, 2008


Filed under: Infrastructure,Statistics — Jonathan Lewis @ 9:20 pm BST Dec 28,2008

There is a startup parameter called timed_os_statistics, carrying a warning in the manuals that said:

Note: Gathering OS statistics is very expensive. Oracle Corporation recommends that you set this parameter in an ALTER SYSTEM statement rather than in the initialization parameter file, and that you reset the value to zero as soon as the needed statistics have been gathered.


October 14, 2008

Going too fast

Filed under: CBO,Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 6:10 pm BST Oct 14,2008

I received an email a litle while ago with an unusual problem. It said:

“One of the jobs which used to take more than one hour to complete is now completing in less than 10 minutes. Neither the application developer nor we (the DBA’s) made *any* changes in the environment/code/database. I can’t work out why it’s got better!”


It’s not often that “going faster” is a problem – but there’s a very good reason for being worried about jobs that go faster for no apparent reason – one day your luck is going to run out and the jobs are going to go slower again – and people really notice when things slow down.


May 30, 2008

Nothing Changed

Filed under: CBO,Performance,Statistics,Tuning — Jonathan Lewis @ 10:42 am BST May 30,2008

The following request appeared on the Oracle Forum a few days ago:

I have a select query

select col1,col2,col3 from table1 order by col1,col3

This table contains 4.5 million records initially and the select was returning records in less than 2 minutes .

This query has been running for the last year with out any issues, but from last week onwards this query is taking more than 15 min to complete . No change in database and other components.

How can I find out the root cause of this issue ? Any specific area I need to check ?


April 25, 2008

Cardinality Change

Filed under: CBO,Execution plans,Histograms,Statistics,Troubleshooting — Jonathan Lewis @ 8:13 pm BST Apr 25,2008

Here’s an interesting point that’s just been raised on the Oracle-L list-server:

Metalink bug: 5483301.8

If a column has a frequency histogram and a query uses an equality predicate over the column looking for a value that does not exist in the histogram then the CBO was estimating a cardinality of 1. This could favor Nested Loops too much.

The fix introduces a notable change in behavior: instead of estimating a cardinality of 1 then with this fix CBO estimates a cardinality of (0.5 * the lowest cardinality found in the histogram). This can result in plan changes.


March 11, 2008

Everything Changes

Filed under: CBO,Execution plans,Statistics — Jonathan Lewis @ 8:59 pm BST Mar 11,2008

Sometimes you look at a change in the optimizer arithmetic and think “Why did that take so long to appear?” Here’s an example that’s probably going to cause a lot of heartache over the next couple of years as people go through the upgrade process. I have a query that looks like this:

« Previous PageNext Page »

The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,507 other followers