JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 7: Histograms


Addenda

Faking it (2nd Dec 2005)

Histogram SQL (13th Sept 2012)

Errata

Typo (2nd Dec 2008)

Addenda

Faking it: (2nd Dec 2005)

Page 168: paragraph 2: I say:

The maximum cumulative value in a frequency histogram – which appears in the column endpoint_number – should match the number of rows in the table minus the number of nulls in the column. If it doesn’t, then Oracle adjusts the cardinality calcIulation accordingly.

However, a mismatch between the column histograms and the table statistics can happen without any faking at all. All you have to do is use a sample to generate a histogram (or even the table level and column histograms simultaneously). The figures stored in the database for the histogram reflect the sample size exactly, but the figures stored in user_tables as the num_rows and in user_tab_columns as the num_nulls are scaled up to offset the sample size.

So (for example) if you sample at 5%, you may find that the highest count in the frequency histogram is 100,000, whilst the number of rows reported for the table is 2,000,000 (which is 100,000 * 1/0.05).

I will be expanding this note, with a worked example in the code depot, some time in the future.

Histogram SQL: (13th Sept 2012)

Page 156: There is a complex SQL statement from a 9i trace file showing the work done by Oracle to derive the data needed to populate a histogram. The subsequent text then explains the meaning of some of the columns. While doing some work recently with 11.2.0.3 I traced the work done by Oracle in creating histograms and found that the SQL had been completely re-written. Notes on the changes are on my blog at: Histogram Generation Oracle Scratchpad

Back to Book Index

Top of page.


Errata


Typo: (2nd Dec 2008)

Page 165: Table 7-1: Row 8, Column 2 (Description):

I’ve used the Predicate “skew = -1” in this row, with the description “Below high value”; this should be “Below low value”.

Back to Book Index

Top of page.

.