There are a few enhancements in 12c that might make a big difference to performance for a small investment in effort. One of the important enhancements comes from changes in histograms – which improve speed of collection with accuracy of results. The changes are so significant that I chose the topic as my presentation at OpenWorld last year.

## July 14, 2013

## September 13, 2012

### Histogram Generation

This really could be published in the * Errata and Addenda *of “Cost Based Oracle – Fundamentals”, but it’s more convenient to publish the whole thing here and just add a link to the Errata pages.

In chapter 7, on page 156, I gave an example of the type of SQL that Oracle runs (in the * dbms_stats* package) to generate a histogram. A sample of the code, and the plan from the 9.2.0.8

*output, is listed below:*

**tkprof**(more…)

## January 3, 2012

### NewDensity

A recent comment on a note I wrote some time ago about* faking histograms* asked about the calculations of

*in the latest versions of Oracle. As I read the question, I realised that I had originally supplied a formula for calculating*

**selectivity***, rather than*

**cardinality***, so I thought I’d supply a proper example.*

**selectivity**We’ll start with a script to create some data and stats – and I’m going to start with a script I wrote in Jan 2001 (which is why it happens to use the * analyze *command rather than

*, even though this example comes from an instance of 11.2.0.2).*

**dbms_stats.gather_table_stats**(more…)

## October 19, 2010

### Frequency Histograms – 6

In * an earlier post on frequency histograms* I described how Oracle creates an approximate histogram when dealing with character columns, and warned you that the strategy could lead to a couple of anomalies if you were unlucky. I’ve already published

**that can occur with fairly long character strings, this note describes another anomaly that could appear in less extreme cases. Again, we start by constructing a data set.**

*a note about one such anomaly*## October 13, 2010

### Frequency Histogram 5

In * an earlier post on frequency histograms* I described how Oracle creates an approximate histogram when dealing with character columns, and warned you that the strategy could lead to a couple of anomalies if you were unlucky. This note describes one such anomaly. We start with a slightly curious data set:

(more…)

## October 5, 2010

### Frequency Histogram 4

In * an earlier note* on interpreting the content of frequency histograms I made a throwaway comment about the extra complexity of interpreting frequency histograms on character-based columns. This note starts to examine some of the complications.

The driving problem behind character columns is that they can get quite large – up to 4,000 bytes – so the content of an “accurate histogram” could become quite large, and Oracle seems to have taken a strategic decision (at some point in history) to minimise this storage. As a result we can see an algorithm that works roughly as follows:

(more…)

## September 24, 2010

### Frequency Histogram 3

Here’s the output I get from querying * dba_tab_histograms* for a column that has been given a frequency histogram by a call to

*.*

**dbms_stats.gather_table_stats()**(more…)

## September 20, 2010

### Frequency Histograms 2

I find it convenient occasionally to “translate” a **frequency histogram** into a report of the underlying data (sometimes to see how closely the histogram matches the real data). To demonstrate the type of query I use I’ve created a data set with a small number of distinct values and generated a frequency histogram on the data set. This is what the data and histogram look like:

(more…)

## March 23, 2010

### Fake Histograms

I got an email a few days ago asking me how to create a *“height-balanced”* histogram using the * dbms_stats.set_column_stats()* procedure.

I posted * an example of creating a frequency histogram* a few months ago – and it doesn’t take much to turn this into an example for the height-balanced type; the key change apppears in three critical lines.

## May 28, 2009

### Frequency Histograms

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.

## April 23, 2009

### Histogram change

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

In 10.2.0.3 (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 *

*) in the data dictionary (see*

**num_rows***).*

**comments 21 and 22 of this note on dbms_xplan**## April 25, 2008

### Cardinality Change

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.