## Extended Histograms – 2

Posted August 2, 2018Following on from * the previous posting* which raised the idea of faking a frequency histogram for a column group (extended stats), this is just a brief demonstration of how you can do this. It’s really only a minor variation of something I’ve published before, but it shows how you can use a query to generate a set of values for the histogram and it pulls in a detail about how Oracle generates and stores column group values.

We’ll start with the same table as we had before – two columns which hold only the combinations (‘Y’, ‘N’) or (‘N’, ‘Y’) in a very skewed way, with a requirement to ensure that the optimizer provides an estimate of 1 if a user queries for (‘N’,’N’) … and I’m going to go the extra mile and create a histogram that does the same when the query is for the final possible combination of (‘Y’,’Y’).

## Extended Histograms

Posted July 31, 2018Today’s little puzzle comes courtesy of * the Oracle-L mailing list*. A table has two columns (

*and*

**c2***), which contain only the values*

**c3***‘Y’*and

*‘N’*, with the following distribution:

select c2, c3, count(*) from t1 group by c2, c3 ; C C COUNT(*) - - ---------- N Y 1994 Y N 71482 2 rows selected.

The puzzle is this – how do you get the optimizer to predict a cardinality of zero (or, using its best approximation, 1) if you execute a query where the predicate is:

where c2 = 'N' and c3 = 'N'

Here are 4 tests you might try:

## Cardinality Puzzle

Posted July 12, 2018One of the difficulties of being a DBA and being required to solve performance problems is that you probably never have enough time to think about how you got to a solution and why the solution works; and if you don’t learn about the process itself , you just don’t get better at it. That’s why I try (at least some of the time) to write articles and books (as I did with CBO Fundamentals) that

## Historic Stats

Posted July 9, 2018If you want to examine historic object stats Oracle gives you a few procedures in the * dbms_stats *package to compare sets of stats captured at two different time periods, but there’s no view that you can query to get an idea of how a table’s stats have changed over time. This is a problem that can be addressed when you discover two things:

- There are views to report pending table, index, column and histogram stats.
- Pending stats are stored stored as “historic” stats with a future date.

Once you’ve spotted the second detail, you can acquire the SQL to generate the pending stats views:

## Clustering_Factor

Posted July 2, 2018Here’s another little note on the * clustering_factor* for an index and the table preference

*that can be set with a call to*

**table_cached_blocks***. I might be repeating a point that someone made in a comment on an older posting but if that’s the case I can’t find the comment at present, and it’s worth its own posting anyway.*

**dbms_stats.set_table_prefs()**## Managing Cost-Based Optimizer Statistics for PeopleSoft

Posted June 29, 2018I gave this presentation to UKOUG PeopleSoft Roadshow 2018

PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.

## Bitmap Join Indexes

Posted May 18, 2018I’ve been prompted by * a recent question on the ODC database forum* to revisit

*about bitmap join indexes and their failure to help with join cardinalities. At the time I made a couple of unsupported claims and suggestions without supplying any justification or proof. Today’s article finally fills that gap.*

**a note I wrote nearly five years ago**## exp catch

Posted April 10, 2018No-one should be using * exp/imp* to export and import data any more, they should be using the

*equivalents*

**datapump***– but if you’re on an older (pre-12c) version of Oracle and still using exp/imp to do things like moving tables with their production statistics over to test systems then be careful that you don’t fall into an obsolescence trap when you finally upgrade to 12c (or Oracle 18).*

**expdp/impdp***exp/imp will mess up some of your histograms if you’re still using them to move tables/statistics in 12c.*

## Column Groups

Posted March 8, 2018There’s * a question on the ODC database forum* about column groups that throws up an interesting side point. The OP is looking at a query like the following and asking about which column groups might help the optimizer get the best plan:

## V$MYSTAT delta values

Posted February 12, 2018Here is a little script I use from time to time to look at V$MYSTAT values and displaying on one line a set of statistics with their delta value between two calls.

The first script, _mystat_init.sql, initializes the variables. The second one displays the values, such as:

SQL> @ _mystat_diff.sql

db block changes redo size undo change vector size redo entries

---------------- ---------------- ----------------------- ----------------

57,371 15,445,852 6,111,608 37,709