You Asked

Hi Tom,
We have in-house developed architecture that we use via batch to build and execute DBMS_STATS.gather_table_stats statements. I want to use dbms_stats.AUTO_SAMPLE_SIZE but our existing architecture requires a number value be passed for estimate_percent.

In Oracle 11g, would the result of the first stats build below be equivalent to the result of the second?

EXEC DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 0);

Both appear to have sampled at 100% yet when querying the data, execution times are slightly less after table stats build using 'estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE' vs. using 'estimate_percent => 0'.

Thank you very much.

and Connor said...

You *definitely* want auto sample size.

Here's a link to a paper on 'one pass ndv' https://goo.gl/eWFelo

Basically, its an optimization to make stats gathering much more efficient, but you *only* get to take advantage of it when you use auto sample size.

Hope this helps.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Auto_sample_size Vs estimate_percent=0

Rajeshwaran, Jeyabal, November 15, 2015 - 12:55 pm UTC

Connor,

Auto_sample_size is declared as constant Zero inside dbms_stats package, given that what do we gain/loss when estimate_percent=auto_sample_size and estimate_percent=0 ?

-- constant used to indicate auto sample size algorithms should
-- be used.
AUTO_SAMPLE_SIZE        CONSTANT NUMBER := 0;

I did this test on 11.2.0.4, i don't find any , help me to understand ( big_table has one million rows, its a copy of all_objects, with a couple of index defined on it).

rajesh@ORA11G> exec dbms_stats.create_stat_table(user,stattab=>'auto_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.create_stat_table(user,stattab=>'zero_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.export_table_stats(user,'big_table',stattab=>'auto_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>0);

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.export_table_stats(user,'big_table',stattab=>'zero_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G> select * from table(dbms_stats.diff_table_stats_in_stattab(
  2      user,'big_table',
  3      stattab1=>'auto_sample_size',
  4      stattab2=>'zero_sample_size',
  5      pctthreshold=>0));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################           0

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : BIG_TABLE
OWNER         : RAJESH
SOURCE A      : User statistics table AUTO_SAMPLE_SIZE
              : Statid     :
              : Owner      : RAJESH
SOURCE B      : User statistics table ZERO_SAMPLE_SIZE
              : Statid     :
              : Owner      : RAJESH
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################



1 row selected.

rajesh@ORA11G> exec dbms_stats.drop_stat_table(user,stattab=>'auto_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec dbms_stats.drop_stat_table(user,stattab=>'zero_sample_size');

PL/SQL procedure successfully completed.

rajesh@ORA11G>

November 16, 2015 - 12:50 am UTC

Because in the "next" release of Oracle, we might make it "-1", or 101, or 1000, or anything we choose.

Anyone using "dbms_stats.auto_sample_size" will be fine. Anyone who used their own constants is now in a spot of bother.

dbms_stats.auto_sample_size

Daniel, November 18, 2015 - 3:38 pm UTC

Hi Connor,
Thank you very much for the reply. I understand the risk and I agree with you that using "dbms_stats.auto_sample_size" would be the best practice. However, I would still very much appreciate your opinion on execution result in current state of Oracle 11g. Rajesh’s findings appear to prove that in 11g, these two statements have equivalent result. Do you agree?

EXEC DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 0);

Thank you.

November 18, 2015 - 11:15 pm UTC

There's a simple reason for that

<code>
SQL> set serverout on
SQL> exec dbms_output.put_line(dbms_stats.AUTO_SAMPLE_SIZE)
0

PL/SQL procedure successfully completed.
<code>

There's nothing magical about a constant.

Breadcrumb

Question and Answer