Toad World® Forums

Gathering stats via toad UI: why ",Method_Opt ... SIZE 1"


#1

Hi

Toad for Oralce 13.1.0.78

Question regarding table stats gathering via toad UI.
Why is that, when gathering stats via toad UI, it generates the following sql:

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => ‘<>’
,TabName => ‘<<table_name>>’
,Estimate_Percent => 10
,Method_Opt => 'FOR ALL COLUMNS SIZE 1’
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/

Why it is using “SIZE 1”…?
With this setting, dbms_stats gathers only base statistics, so it will remove all the histograms previously generated for the table columns.

I am sure there must be some reason for why toad is using “SIZE 1”, but I am having difficulties figuring out why it is good, I mean wouldn’t the “SIZE AUTO” be better option…? (since with this option oracle automatically generates/maintains column histograms as well)

Or perhaps, I can change this setting from toad UI somewhere…?

In my opinion “SIZE AUTO” is quite safe option, or won’t you agree…?

Regards
Raul


#2

You can change it to Auto if you want. See below.


#3

Thanks, found it.

Regards
Raul