# RE: stats not playing nice

Date: Mon, 27 Feb 2017 11:06:00 -0500

Message-ID: <010501d29113$63dcb320$2b961960$_at_rsiz.com>

Your avgclen should be 3, by the way, not 5.

Jonathan Lewis

http://jonathanlewis.wordpress.com/all-postings

- Original Message ----- From: "Jack van Zanen" <jack_at_vanzanen.com> To: <oracle-l_at_freelists.org> Sent: Monday, February 27, 2017 2:08 AM Subject: stats not playing nice

| 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:
**|
**| a) 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
**|
**| b) 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
**|
**| declare
**|
**| m_distcnt number;
**| m_density number;
**| m_nullcnt number;
**| srec dbms_stats.statrec;
**| m_avgclen number;
**| n_array dbms_stats.numarray;
**|
**| begin
**|
**|
**| 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);
**|
**| dbms_stats.set_column_stats(
**| ownname => 'XXXXXX',
**| tabname => XXXXEVENTXXXXX',
**| colname => 'STATUS',
**| distcnt => m_distcnt,
**| density => m_density,
**| nullcnt => m_nullcnt,
**| srec => srec,
**| avgclen => m_avgclen
**| );
**|
**| end;
**| /
**|
**|
**|
**|
**|
**| 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
**|
*

-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 27 2017 - 17:06:00 CET