Statistics change

By default gathering statistics change during lifetime of a table in Oracle 11g. Just after creating and populating a table the statistics are not as they will be after awhile the table is used. The usage, how the table columns are queried, effects the way the statics are gathered. Actually it is not necessary to execute a query. Just making the optimizer to parse may change how the statistics are gathered. And this way the execution plans may differ from day to day. In here a table is created and populated with a unbalanced values in a column. Just after the population is done the statistics are gathered.

drop table stat;

create table stat as select level le, 999 mo from dual connect by level < 1000;

insert into stat values (1000,1);

create index stat_mo_idx on stat(mo);

exec dbms_stats.gather_table_stats(user,'STAT');

Just asking a query plan.

explain plan for select * from stat where mo = 999;

select * from table(dbms_xplan.display(format=>'basic rows'));

-----------------------------------------------------------
| Id  | Operation                   | Name        | Rows  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   500 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STAT        |   500 |
|   2 |   INDEX RANGE SCAN          | STAT_MO_IDX |   500 |
-----------------------------------------------------------


select column_name,histogram,num_buckets 
  from user_tab_columns
 where table_name = 'STAT';

COLUMN_NAME    HISTOGRAM    NUM_BUCKETS
LE             NONE         1
MO             NONE         1

Nothing else is done than "explain plan for" for a query that has a column in a where clause predicate. Gathering the statistics again.

exec dbms_stats.gather_table_stats(user,'STAT');

explain plan for select * from stat where mo = 999;

select * from table(dbms_xplan.display(format=>'basic rows'));

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   999 |
|   1 |  TABLE ACCESS FULL| STAT |   999 |
------------------------------------------


select column_name,histogram,num_buckets 
  from user_tab_columns 
 where table_name = 'STAT';

COLUMN_NAME    HISTOGRAM    NUM_BUCKETS
LE             NONE         1
MO             FREQUENCY    2

The plan changed as the statistics gathering changed to gather a histogram for the column previously used in a where predicate.

This example shows that before gathering the initial statistics for a newly created and populated table the gathering would like to know how the table will be used. At least when you let Oracle decide how the statistics are gathered. No execution of queries is needed. Just ask the plans of the newly created application before gathering the first statistics. This way your first day of using will be a bit more similar than the future days of using the table. As the stats will change during some future maintenance window gathering after the table is changed enough.

Seems like I copied slides and the idea straight from Tom Kyte "Five things you probably didn’t know about SQL" slides presented at last OUG Harmony, but here I showed that you actually do not even need to execute the query. Only parsing makes the effect.

11.2 version one can use

select dbms_stats.report_col_usage(user,'STAT') from dual;

to get information how the columns are used. In 11.1 the information may be found in sys.col_usage$