jump to navigation

METHOD_OPT Default In Oracle Autonomous Databases (She’ll Drive The Big Car) March 2, 2021

Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Histograms, METHOD_OPT, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Statistics.
1 comment so far

 

In a recent post on Invisible Automatic Indexes, I was puzzled by a couple of “oddities” in relation to some behaviour in the Oracle Autonomous Database Cloud environments.

The first one was how Oracle appeared to be creating Histograms on a much more regular basis than it had previously.

As one can see in the demo below, if I create and populate a table:

SQL> create table pink_floyd (id number, code number, create_date date, name varchar2(42));

Table created.

SQL> insert into pink_floyd select rownum, ceil(dbms_random.value(0, 5000)), sysdate-mod(rownum, 50000)+1, 'Dark Side of the Moon' from dual connect by level <=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

And then collect statistics using the “default” options:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'PINK_FLOYD');

PL/SQL procedure successfully completed.

All the columns in the table now have histograms, regardless of whether they’ve been used in SQL predicates or if they have data skew:

SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='PINK_FLOYD';

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM
-------------------- ------------ ---------- ---------------
ID                        9705425          0 HYBRID
CODE                         4835     .00005 HYBRID
CREATE_DATE                 50357     .00002 HYBRID
NAME                            1 4.9639E-08 FREQUENCY

 

The explanation for this is embarrassingly simple. A quick check on the default settings for METHOD_OPT shows the following:

SQL> select dbms_stats.get_prefs('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 254

 

The default is FOR ALL COLUMNS 254, meaning that we will now indeed have histograms collected on all columns. With new capabilities such as High Frequency Statistics Collection, it’s interesting that Oracle has taken this approach but Oracle has obviously taken the attitude that with Exadata as the hosted infrastructure, it can afford to simply collect histograms globally on all columns in the Autonomous Database environments.

If you wanted to change this, you can do so by for example:

SQL> exec DBMS_STATS.SET_GLOBAL_PREFS ('METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

 

So not an “oddity”, but expected behaviour now on Oracle Autonomous Databases.

The other “oddity” I noticed were Invisible Valid Automatic indexes at times being created. The explanation for this will be the topic of my next blog post…