stats not playing nice

From: Jack van Zanen <>
Date: Mon, 27 Feb 2017 13:08:58 +1100
Message-ID: <>

Hi All,

Oracle 11G R2 AIX

We have a fairly large table (24G) that has a status field. there are 6 possible statuses and the Majority are status 5(completed) for which there is a need to retain for 13 months.

Now the stats on that table take fairly long to compute so estimate is used, but as the data is very skewed sometimes we miss out on some values altogether.

Now, I think the best solution would have been at creation time to split this up and move the completed records to a separate table partitioned by month and keep the batch processing to use the lean and mean table.

Failing that:

I can:

  1. Run a full compute statistic at an opportune time including histograms (takes a long time and I am not sure we get a window for this. and than lock the stats untill we do it again
  2. keep using estimate statistics and manually insert the values for this one column.

I have found a blog post on Jonathan Lewis website where he has an example that is fairly easy to follow:

This is the Code from Jonathan Lewis blog post from almost a decade ago.

I have one question regarding the density. What should I be putting here


    m_distcnt       number;
    m_density       number;
    m_nullcnt       number;
    srec            dbms_stats.statrec;
    m_avgclen       number;
    n_array         dbms_stats.numarray;


    m_distcnt   := 6;
    m_density   := ??????;
    m_nullcnt   := 0;
    m_avgclen   := 5;

    n_array     := dbms_stats.numarray(0,1,2,3,4,5);
    srec.bkvals := dbms_stats.numarray( 74086, 152500, 704, 858271,  1257221,156704455); ---Actual distribuution

    srec.epc := 6;

    dbms_stats.prepare_column_values(srec, n_array);


        ownname     => 'XXXXXX',
        tabname     => XXXXEVENTXXXXX',
        colname     => 'STATUS',
        distcnt     => m_distcnt,
        density     => m_density,
        nullcnt     => m_nullcnt,
        srec        => srec,
        avgclen     => m_avgclen



Jack van Zanen

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
Received on Mon Feb 27 2017 - 03:08:58 CET

Original text of this message