Oracle

METHOD_OPT with AUTO & Histograms

It is always better to know why oracle choose a bad plan over a good one. For this reason you need to know the mechanism behind the statistics. Statistics are a representation of the data distribution in a smaller scale. This representation helps oracle to choose the right path and join algorithms when generating the execution plan for the query. Oracle builds a cost model on top of this statistics and compares possible execution plans and tries to choose the plan with the lowest cost. If your statistics do not properly reflect your data distribution then oracle may start to choose wrong execution plans since all comes to mathematics and If you give wrong inputs to a formula you may have no right to expect good results.

There are auto options in the gathering package dbms_stats. For the method_opt option => ‘FOR ALL [INDEXED|HIDDEN] COLUMNS SIZE AUTO’ oracle decides if a column needs the collection of histograms and statistics on that column. The decision is based on the usage history of that column in the query predicates. You can get this information from the sys.col_usage$ view. If the column usage is empty no histogram created for that column.


SCENARIO 1
Table is newly created and column usage info is empty.
AUTO column stats gathers NO histogram on the column.

Prepare the Table

SQL> drop table eser_sil purge;
SQL> create table eser_sil as 
  2  SELECT LEVEL vrow, mod(level,5) vmod1, mod(level,5)+1 vmod2, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' vval
  3  FROM dual
  4  CONNECT BY LEVEL <= 1e6;
SQL> create index eser_sil_x1 on eser_sil (vmod1);

Update the monitoring and usage info for table and columns.

SQL> begin 
  2    dbms_stats.flush_database_monitoring_info();
  3  end;
  4  /

Check sys.col_usage$ table. It will show none of the columns used as a predicate in a query.

SQL> select c.OWNER, c.TABLE_NAME, c.COLUMN_NAME,  cu.equality_preds, cu.timestamp
  2      from sys.col_usage$ cu,
  3         dba_tab_columns c,
  4         dba_objects o
  5      where cu.obj# = o.OBJECT_ID 
  6         and cu.intcol# = c.COLUMN_ID   
  7         and o.OWNER = c.OWNER
  8         and o.OBJECT_NAME = c.TABLE_NAME
  9         and o.OBJECT_NAME ='ESER_SIL'
 10         order by cu.intcol# ;

no rows selected

Gather stats using auto on the indexed columns.

SQL> begin 
  2  dbms_stats.gather_table_stats(ownname => 'VTYUSER',
  3                                tabname => 'ESER_SIL',
  4                                method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  5  end;
  6  /

No histogram is created on the column because it has newer been used.

SQL> select table_name, column_name, histogram from dba_tab_col_statistics where table_name = 'ESER_SIL';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
ESER_SIL                       VMOD1                          NONE


SCENARIO 2
Table is newly created. Explain plan used on a query with the column as a predicate.
Column usage info is filled. AUTO column stats gathers histogram on the column.

Prepare the Table

SQL> drop table eser_sil purge;
SQL> create table eser_sil as 
  2  SELECT LEVEL vrow, mod(level,5) vmod1, mod(level,5)+1 vmod2, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' vval
  3  FROM dual
  4  CONNECT BY LEVEL <= 1e6;
SQL> create index eser_sil_x1 on eser_sil (vmod1);

Explain plan is used to parse the query

SQL> explain plan  for select count(*)  from eser_sil  where vmod1 = :a;

Explained.

Update the monitoring and usage info for table and columns.

SQL>  begin 
  2     dbms_stats.flush_database_monitoring_info();
  3   end;
  4   /

Check sys.col_usage$ table. It will show the columns is used as an equality predicate once.

SQL> set lines 200
SQL> select c.TABLE_NAME, c.COLUMN_NAME,  cu.equality_preds, cu.timestamp
  2      from sys.col_usage$ cu,
  3         dba_tab_columns c,
  4         dba_objects o
  5      where cu.obj# = o.OBJECT_ID 
  6         and cu.intcol# = c.COLUMN_ID   
  7         and o.OWNER = c.OWNER
  8         and o.OBJECT_NAME = c.TABLE_NAME
  9         and o.OBJECT_NAME ='ESER_SIL'
 10         order by cu.intcol# ;

TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS TIMESTAMP
------------------------------ ------------------------------ -------------- ---------
ESER_SIL                       VMOD1                                       1 30-APR-14
SQL> begin 
  2  dbms_stats.gather_table_stats(ownname => 'VTYUSER',
  3                                tabname => 'ESER_SIL',
  4                                method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  5  end;
  6  /

As expected, Histogram is created on that column.

SQL>  select table_name, column_name, histogram from dba_tab_col_statistics where table_name = 'ESER_SIL';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
ESER_SIL                       VMOD1                          FREQUENCY


SCENARIO 3
Table is newly created. A query is issued with the column as a predicate.
Column usage info is filled. AUTO column stats gathers histogram on the column.

Prepare the Table

SQL> drop table eser_sil purge;
SQL> create table eser_sil as 
  2  SELECT LEVEL vrow, mod(level,5) vmod1, mod(level,5)+1 vmod2, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' vval
  3  FROM dual
  4  CONNECT BY LEVEL <= 1e6;
SQL> create index eser_sil_x1 on eser_sil (vmod1);

Run a query using the column as a predicate.

SQL> select count(*)  from eser_sil  where vmod1 = 2;

  COUNT(*)
----------
    200000

Update the monitoring and usage info for table and columns.

SQL>  begin 
  2     dbms_stats.flush_database_monitoring_info();
  3   end;
  4   /

Check sys.col_usage$ table. It will show the columns is used as an equality predicate once.

SQL> set lines 200
SQL> select c.TABLE_NAME, c.COLUMN_NAME,  cu.equality_preds, cu.timestamp
  2      from sys.col_usage$ cu,
  3         dba_tab_columns c,
  4         dba_objects o
  5      where cu.obj# = o.OBJECT_ID 
  6         and cu.intcol# = c.COLUMN_ID   
  7         and o.OWNER = c.OWNER
  8         and o.OBJECT_NAME = c.TABLE_NAME
  9         and o.OBJECT_NAME ='ESER_SIL'
 10         order by cu.intcol# ;

TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS TIMESTAMP
------------------------------ ------------------------------ -------------- ---------
ESER_SIL                       VMOD1                                       1 30-APR-14
SQL> begin 
  2  dbms_stats.gather_table_stats(ownname => 'VTYUSER',
  3                                tabname => 'ESER_SIL',
  4                                method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  5  end;
  6  /

As expected, Histogram is created on that column.

SQL> select table_name, column_name, histogram from dba_tab_col_statistics where table_name = 'ESER_SIL';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
ESER_SIL                       VMOD1                          FREQUENCY
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s