## Stale Stats

Posted January 1, 2013The question of how to list objects in need of stats collection appeared on the OTN database forum today from a DBA who had a system collecting stats at the weekend, but wanted to check whether there were any objects with stale stats during the week. It’s actually very easy to do this check because the schema and database calls to gather stats have an option to “list stale”; they also allow you to “list empty”, and “list auto” – the latter being the objects that would be subject to collection if you change the option to (the default) “gather”. “List auto” seems to be the concatenation of “list stale” and “list empty”, by the way.

So, after reading up the manual pages on dbms_stats ($ORACLE_HOME/rdbms/admin/dbmsstat.sql, or the PL/SQL Packages reference) all it takes is a little wrapping to do the job. The code below is a little overkill because it covers 6 different options in one go:

## Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g

Posted September 30, 2012I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database. Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g. I believe a slightly different approach is required.

In 2009, I wrote a series of blog postings on the subject of collecting statistics. However these were all based on Oracle 10g. I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, or deliberate supress collection of statistics.

## Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g

Posted September 30, 2012I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database. Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g. I believe a slightly different approach is required.

In 2009, I wrote a series of blog postings on the subject of collecting statistics. However these were all based on Oracle 10g. I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, or deliberate supress collection of statistics.

## Minimum stats

Posted September 18, 2012Occasionally I come across complaints that * dbms_stats* is not obeying the estimate_percent when sampling data and is therefore taking more time than it “should” when gathering stats. The complaint, when I have seen it, always seems to be about the sample size Oracle chose for indexes.

There is a simple but (I believe) undocumented reason for this: because indexes are designed to collate similar data values they are capable of accentuating any skew in the data distribution, which means a sample taken from a small number of leaf blocks can be highly misleading as a guide to the whole index – so Oracle aims for a minimum sample size for gathering index stats.

## Histogram Generation

Posted September 13, 2012This 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**## Exchange Partition, Virtual Columns And Column Statistics

Posted September 9, 2012Here is an odd bug that can lead to some nasty side effects when using the * EXCHANGE PARTITION *technique. It is probably there for a very long time, simply because it depends on the usage of

*, and the basic technique of virtual columns was introduced way back in the*

**virtual columns***times with the introduction of*

**Oracle 8i***.*

**Function Based Indexes**The problem isn't the exchange partition operation itself, but the accompanying swap of * object statistics* information, in particular the

*.*

**column statistics**Look the following sequence of DDL and DML commands and pay then special attention to the output for the column statistics * before *and

*the EXCHANGE PARTITION operation:*

**after**## Unique Fail

Posted June 8, 2012As in – how come a unique (or primary key) index is predicted to return more than one row using a unique scan, for example (running on 10.2.0.3 – but the same type of thing happens on newer versions):

## Table High Water Mark and How Empty the Table Is

Posted April 30, 2012On a recent post about reducing the high water mark in a table by shrinking it someone asked if you could tell how much “space” there was in a table that could be recovered by compressing it.

Yes. As pseudo-code:

select number_of_rows*average_row_length. That is how much data there is.

Divided by the block size, to give how many blocks-worth of data you have.

Divided this by a “overhead” fudge factor to allow for wasted space (see later) of about 20%

Compare to the number of blocks used by the table.

Here is some code to do it, showing some extra data and how we calculate the effective percentage of space used (pct_spc_used). It runs against an example table ‘HWM’

## Statistics poll

Posted April 16, 2012Thanks to everyone for spreading the link and participating in the poll on statistics. As it was pointed out in the comments, the poll isn’t very specific, I know. My point was to get an idea how many people use the defaults (I considered Oracle version 11g) for stats collection, how many do a very simple and reasonable tweak to get more stable execution plans, and how many people prefer custom statistics management. Well, I think the results will not change much and here are as following (326 votes at the time of writing):

## Extended Stats

Posted April 12, 2012I’m very keen on the * 11g extended stats* feature, but I’ve just discovered a critical weakness in one of the implementation details that could lead to some surprising instability in execution plans. It’s a combination of “column group” statistics and “out of range” predicates. Let’s start with some sample data. (Note: I was running this test on 11.2.0.3):

create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 10000 ) select mod(rownum,100) col1, mod(rownum,10) col2 from generator v1, generator v2 where rownum <= 50000 ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; /