Thanks for the question, Javier.

Asked: July 02, 2012 - 3:27 am UTC

Last updated: April 10, 2017 - 12:32 am UTC

Version: 11.2.0.2

Viewed 1000+ times

You Asked

Hello Tom,

One question, in 10g and 11g versions, what are the impacts in a busy system of manually gathering the whole database statistics without updating histograms?

I mean the impacts from the following points of view:

1- How long it will take a gather of database statistics in a very busy and big database with and without updating histograms?

2- Impacts on queries performance that are using indexes with only global database statistics up to date and histograms not updated?

3- How can I decide that updating histograms is not required and does not impact the whole database performance?

Thank you

and Tom said...

why do you keep calling out "without updating histograms" - do you mean you are gathering new stats on everything else but leaving old histograms around. If so - why?

1) how long is a piece of string? Honestly - I don't know how long it takes to gather statistics on your system when it is not busy - let alone busy. How could I or anyone. You are the only one with a chance of answering this?

2) who cares about indexes, histograms are there so the optimizer can calculate estimated cardinalities. On unindexed columns, on indexed columns - any column with skew. There might be zero histograms on indexed columns and ever unindexed column might have one in some systems. histograms and indexed columns are not synonymous (they should not be in any case!)

if your statistics are NOT REPRESENTATIVE of your data, then the optimizer will incorrect estimate cardinalities which can lead to bad plans.

3) are you getting good estimated cardinalities? If so, the statistics you have are fine.

Question for you - why are you gathering statistics during a busy time? Do you need to gather statistics at all?

Rating

(6 ratings)

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

Comments

Javier, July 03, 2012 - 2:40 pm UTC

Thank you Tom. I know that my question is not easy. The reason behind my question; I have been recently passed a job interview; the database manager asked me those questions:

1- Do you know in a very busy system when to decide to gather new statistics on everything else but leaving old histograms?

(Please let me define a very busy system here: It is a system that is always highly loaded not scalable).

About your question Tom:

3) are you getting good estimated cardinalities? If so, the statistics you have are fine.

How can I know that I am getting a good estimated cardinalities; could you please clarify it by an example?

I really appreciate your help Tom, I know that I am miss understanding histograms, that's why I failed in the interview.

Thank you,
Javier

July 03, 2012 - 5:36 pm UTC

1) it is a bad question. Why pick on histograms? Why not pick on *statistics* in general - a histogram is just one of many metrics we gather.

the question should be - when or why would you consider gathering statistics on a busy system - ever. period. Singling out histograms is strange.

3) you know your data - you should be able to tell. If you have "good" plans - you are probably good to go (and if so, why are you gathering so many statistics?)

Here is an example of a query plan with and without valid, representative statistics:

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select 99 id, a.*
  4    from all_objects a;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> update t set id = 1 where rownum <= 10;

10 rows updated.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size repeat' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx on t(id);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36416 |  3591K|   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 36416 |  3591K|   299   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

ops$tkyte%ORA11GR2> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36416 |  3591K|   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 36416 |  3591K|   299   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=99)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns size 254' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    13 |  1313 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    13 |  1313 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |    13 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

ops$tkyte%ORA11GR2> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72812 |  7181K|   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 72812 |  7181K|   300   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=99)

ops$tkyte%ORA11GR2> set autotrace off

Javier, July 04, 2012 - 3:42 am UTC

Thank you Tom. How can I decide if my statistics are REPRESENTATIVE of my data? I mean explain the plan of one query on one table is enough , or explain the plans of many queries on all tables of the database is enough?

based on your example, you have queried only one table in the database and then you decided that your statistics are representative of your data?

Thanks a lot

July 05, 2012 - 7:06 am UTC

Javier,

*you* know your data. Or at least you should. You understand how it is modified. Or at least you should.

Ask yourself this:

If I were the optimizer, only given the facts that are statistics, would I be able to make reasonable cardinality estimates if the statistics are one day old, one month old, one year old?

Think about it - if a table doesn't really grow over time (about the same number of rows - ABOUT the same number of rows, about the same skew of data, columns have about the same high and low values - or if they do not, like a sequence column, they are not used in ranges - that is, the primary key is always "where pk = ?" and not really "where pk between ? and ?") - then statistics from ten years ago could well be representative.

If the table is rather dynamic - or has columns used in where clauses with ranges and those columns high/low values are volatile - you might need to gather (or SET) statistics much more frequently

To Javier...

José Laurindo Chiappa, July 05, 2012 - 2:01 pm UTC

Javier, let me add a little : once you know your data (ie, you have a reasonable knowledge of values distribution, volumes, volatility, etc) , it´s a simple matter of Comparision : look at Oracle´s estimatives in a given execution plan and see if it´s reasonable, according to your data knowledge...
For example, see the test case at https://blogs.oracle.com/optimizer/entry/how_do_i_know_if : if you know that some thousand lines in SALES table for a given query in a given filter condition is near of the real distribution of your data, OK, your statistics collection job is doing good - if not, maybe you would collect statistics more frequently and/or try to augment your histogram´s size, or things like that .... Just so simple, no more, no less...

Specifically spealing about histograms presence/abscence/size (a much common cause for inadequate statistics, in my experience), besides the (very important) Oracle documentation, http://www.akadia.com/services/oratips/costbased_optimizer/optm.htm have a somewhat old but still good explanation about histogram´s theory, here in this site Tom already talked about them many times (use the search tool) , and in his blog http://jonathanlewis.wordpress.com Jonathan wrote so much about histograms, see there....

Best regards,

J. Laurindo Chiappa

for all HIDDEN columns during stats gathering

Rajeshwaran, Jeyabal, February 09, 2016 - 11:26 am UTC

Team,

I am reading/practicing this article (I am on 11.2.0.4)

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

<quote>
FOR ALL HIDDEN COLUMNS limits base column statistics gathering to only the virtual columns that have been created on a table. This means none of the actual columns in the table will have any column statistics gathered on them.
</quote>

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t as
  2  select owner,object_id,object_name
  3  from all_objects
  4  where rownum <=1000;
rajesh@ORA11G> alter table t add x generated always as (upper(object_name));
rajesh@ORA11G> create index t_idx on t(object_name);
rajesh@ORA11G> set feedback on
rajesh@ORA11G> set linesize 71
rajesh@ORA11G> desc T
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 OWNER                               NOT NULL VARCHAR2(30)
 OBJECT_ID                           NOT NULL NUMBER
 OBJECT_NAME                         NOT NULL VARCHAR2(30)
 X                                            VARCHAR2(30)

rajesh@ORA11G> set linesize 151
rajesh@ORA11G> select table_name,column_name,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T';

no rows selected

rajesh@ORA11G> begin
  2     dbms_stats.gather_table_stats(
  3             ownname=>user,
  4             tabname=>'T',
  5             method_opt=>'for all hidden columns size 1');
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA11G> select table_name,column_name,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T';

no rows selected

rajesh@ORA11G>

The column X is the hidden column in table 'T', when method_opt=>'FOR ALL HIDDEN columns size 1' , why don't i get the base column statistics on column X? Help us to understand.

February 10, 2016 - 12:37 am UTC

I'd say it's a historical reflection.

Before true "virtual" columns were a feature, when you did things like set a column unused, or added a function based index, a virtual column would be created for you (and would be hidden). The documentation is probably a reference to that and has not been updated for real virtual columns.

For example

SQL> create table t as
  2  select owner,object_id,object_name
  3  from all_objects
  4  where rownum <=1000;
SQL> alter table t add x generated always as (upper(object_name));
SQL> create index t_idx on t(object_name);
SQL> create index t_idx2 on t ( object_id+10 );
SQL> set feedback on
SQL> set linesize 71
SQL> desc T
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 OWNER                               NOT NULL VARCHAR2(30)
 OBJECT_ID                           NOT NULL NUMBER
 OBJECT_NAME                         NOT NULL VARCHAR2(30)
 X                                            VARCHAR2(30)

SQL> set linesize 151
SQL> select table_name,column_name,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T';

no rows selected

SQL>
SQL> begin
  2     dbms_stats.gather_table_stats(
  3             ownname=>user,
  4             tabname=>'T',
  5             method_opt=>'for all hidden columns size 1');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
T                              SYS_NC00005$                   NONE

1 row selected.

Click on the fSQL> create table t as
2 select owner,object_id,object_name
3 from all_objects
4 where rownum <=1000;
SQL> alter table t add x generated always as (upper(object_name));
SQL> create index t_idx on t(object_name);
SQL> create index t_idx2 on t ( object_id+10 );
SQL> set feedback on
SQL> set linesize 71
SQL> desc T
Name Null? Type
----------------------------------- -------- -------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME NOT NULL VARCHAR2(30)
X VARCHAR2(30)

SQL> set linesize 151
SQL> select table_name,column_name,histogram
2 from user_tab_col_statistics
3 where table_name ='T';

no rows selected

SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'T',
5 method_opt=>'for all hidden columns size 1');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,histogram
2 from user_tab_col_statistics
3 where table_name ='T';

TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
T SYS_NC00005$ NONE

1 row selected.
<code>

Since your column X is not hidden, stats are not being calculated. Please use the "Feedback" link on the online docs to let the doc team know.

Sample size in 12c

Rajeshwaran, Jeyabal, January 28, 2017 - 3:00 pm UTC

Team,

Was going through the documentation about Histograms.

http://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm#TGSQL95029

<quote>
Starting in Oracle Database 12c, if the sampling size is the default of AUTO_SAMPLE_SIZE, 
then the database creates frequency histograms from a full table scan. 
For all other sampling percentage specifications, 
the database derives frequency histograms from a sample
</quote>

was able to understand this with a sample test case.

demo@ORA11G> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

demo@ORA11G>
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     70545

demo@ORA11G>
demo@ORA11G> select num_distinct,num_nulls,histogram,num_buckets,sample_size
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE
------------ ---------- --------------- ----------- -----------
          17          0 NONE                      1       70545

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select count(*), sum(object_id)
  2  from t
  3  where owner ='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |   285   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4150 | 45650 |   285   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

demo@ORA11G> set autotrace off
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     70545

demo@ORA11G>
demo@ORA11G> select num_distinct,num_nulls,histogram,num_buckets,sample_size
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE
------------ ---------- --------------- ----------- -----------
          17          0 FREQUENCY                12        5533

demo@ORA11G>

With 11g optimizer in place, the Histogram got created over the sample size of 5533.

But with 12c - the optimizer don't pick the entire data set(sample size) for histogram creation.

demo@ORA12C> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

demo@ORA12C>
demo@ORA12C> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     77425

demo@ORA12C>
demo@ORA12C> select num_distinct,num_nulls,histogram,num_buckets,sample_size,notes
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE NOTES
------------ ---------- --------------- ----------- ----------- -------------------------------
          19          0 NONE                      1       77425 STATS_ON_LOAD

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select count(*), sum(object_id)
  2  from t
  3  where owner ='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |   366   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4075 | 44825 |   366   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

demo@ORA12C> set autotrace off
demo@ORA12C>
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',options=>'GATHER AUTO');

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     77425

demo@ORA12C>
demo@ORA12C> select num_distinct,num_nulls,histogram,num_buckets,sample_size,notes
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE NOTES
------------ ---------- --------------- ----------- ----------- -------------------------------
          19          0 FREQUENCY                12        5458 HISTOGRAM_ONLY

demo@ORA12C>

If we don't use the option=>'GATHER AUTO' then sample_size for the histogram is close the number of rows in the table.

demo@ORA12C> drop table t purge;

Table dropped.

demo@ORA12C> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

demo@ORA12C> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     77425

demo@ORA12C> select num_distinct,num_nulls,histogram,num_buckets,sample_size,notes
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE NOTES
------------ ---------- --------------- ----------- ----------- ----------------------------
          19          0 NONE                      1       77425 STATS_ON_LOAD

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select count(*), sum(object_id)
  2  from t
  3  where owner ='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |   366   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4075 | 44825 |   366   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

demo@ORA12C> set autotrace off
demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA12C> select num_rows
  2  from user_tab_statistics
  3  where table_name ='T';

  NUM_ROWS
----------
     77425

demo@ORA12C> select num_distinct,num_nulls,histogram,num_buckets,sample_size,notes
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name ='OWNER';

NUM_DISTINCT  NUM_NULLS HISTOGRAM       NUM_BUCKETS SAMPLE_SIZE NOTES
------------ ---------- --------------- ----------- ----------- ----------------------------
          19          0 FREQUENCY                19       77425

demo@ORA12C>

If this is the case, then do we need to stay away from 'GATER AUTO' for the better histogram samples?

February 14, 2017 - 2:27 am UTC

In Oracle Database 12c, the optimizer automatically gathers statistics for direct path operations such as, Create Table As Select (CTAS) and Insert /+APPEND */ As Select (IAS) operations into an empty table or partition.

Piggybacking the statistics gather as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded. Since there is no column usage information no histograms are created.

In your example, your table T is benefiting from an online statistics gathering as the note column says "STATS_ON_LOAD".

If you then gather statistics on such a table with the options "GATHER AUTO", what you are asking Oracle to do is gather only the missing statistics for your table.

In your case the only missing statistics are column level histogram.

Since Oracle only has to gather histograms, we will pick the fastest method possible, which is to sample. Hence the sample size of approximately 5,500 rows.

However, when you gather statistics without the options "GATHER AUTO", what you are actually asking Oracle to do is re-gather statistics on this table from scratch. Therefore Oracle will scan the entire table to gather the table and column level statistics including the histograms.

Online stats gathering on partitioned tables

Rajeshwaran, April 09, 2017 - 4:55 pm UTC

Team,

Online stats gathering in 12c, doesn't populate the partitioned level statistics. Is this an expected behavior?
Kindly advice.

demo@ORA12C> create table t
  2  partition by range(created)
  3  interval( numtoyminterval(1,'year') )
  4  ( partition p_2015 values less than
  5  (to_date('01-Jan-2016','dd-mon-yyyy') ) )
  6  as
  7  select owner,object_id,object_name,created
  8  from all_objects;

Table created.

demo@ORA12C>
demo@ORA12C> select num_rows,blocks,last_analyzed
  2  from user_tables
  3  where table_name ='T';

  NUM_ROWS     BLOCKS LAST_ANALYZ
---------- ---------- -----------
     60758        526 09-APR-2017

demo@ORA12C>
demo@ORA12C> select column_name,num_distinct,last_analyzed
  2  from user_tab_col_statistics
  3  where table_name ='T';

COLUMN_NAME          NUM_DISTINCT LAST_ANALYZ
-------------------- ------------ -----------
OWNER                          19 09-APR-2017
OBJECT_ID                   60758 09-APR-2017
OBJECT_NAME                 51212 09-APR-2017
CREATED                      1113 09-APR-2017

demo@ORA12C>
demo@ORA12C> select partition_name,num_rows,blocks,last_analyzed
  2  from user_tab_partitions
  3  where table_name ='T';

PARTITION_   NUM_ROWS     BLOCKS LAST_ANALYZ
---------- ---------- ---------- -----------
P_2015
SYS_P1521

demo@ORA12C>
demo@ORA12C> select partition_name,column_name,num_distinct,last_analyzed
  2  from user_part_col_statistics
  3  where table_name ='T';

PARTITION_ COLUMN_NAME          NUM_DISTINCT LAST_ANALYZ
---------- -------------------- ------------ -----------
P_2015     OWNER
P_2015     OBJECT_ID
P_2015     OBJECT_NAME
P_2015     CREATED
SYS_P1521  OWNER
SYS_P1521  OBJECT_ID
SYS_P1521  OBJECT_NAME
SYS_P1521  CREATED

8 rows selected.

demo@ORA12C>

for now I am proceeding with OPTION=>'GATHER AUTO' during stats gathering. something like below. is this a right option to gather the partition level stats after the data loads?

demo@ORA12C> select num_rows,blocks,last_analyzed
  2  from user_tables
  3  where table_name ='T';

  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
     60758        526 09-APR-2017 10:18:08 pm

demo@ORA12C>
demo@ORA12C> select column_name,num_distinct,last_analyzed
  2  from user_tab_col_statistics
  3  where table_name ='T';

COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED
-------------------- ------------ -----------------------
OWNER                          19 09-APR-2017 10:18:08 pm
OBJECT_ID                   60758 09-APR-2017 10:18:08 pm
OBJECT_NAME                 51212 09-APR-2017 10:18:08 pm
CREATED                      1113 09-APR-2017 10:18:08 pm

demo@ORA12C>
demo@ORA12C> select partition_name,num_rows,blocks,last_analyzed
  2  from user_tab_partitions
  3  where table_name ='T';

PARTITION_   NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- ---------- -----------------------
P_2015
SYS_P1521

demo@ORA12C>
demo@ORA12C> select partition_name,column_name,num_distinct,last_analyzed
  2  from user_part_col_statistics
  3  where table_name ='T';

PARTITION_ COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED
---------- -------------------- ------------ -----------------------
P_2015     OWNER
P_2015     OBJECT_ID
P_2015     OBJECT_NAME
P_2015     CREATED
SYS_P1521  OWNER
SYS_P1521  OBJECT_ID
SYS_P1521  OBJECT_NAME
SYS_P1521  CREATED

8 rows selected.

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',options=>'GATHER AUTO');

PL/SQL procedure successfully completed.

demo@ORA12C> select num_rows,blocks,last_analyzed
  2  from user_tables
  3  where table_name ='T';

  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
     60758        526 09-APR-2017 10:18:08 pm

demo@ORA12C>
demo@ORA12C> select column_name,num_distinct,last_analyzed
  2  from user_tab_col_statistics
  3  where table_name ='T';

COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED
-------------------- ------------ -----------------------
OWNER                          19 09-APR-2017 10:18:08 pm
OBJECT_ID                   60758 09-APR-2017 10:18:08 pm
OBJECT_NAME                 51212 09-APR-2017 10:18:08 pm
CREATED                      1113 09-APR-2017 10:18:08 pm

demo@ORA12C>
demo@ORA12C> select partition_name,num_rows,blocks,last_analyzed
  2  from user_tab_partitions
  3  where table_name ='T';

PARTITION_   NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- ---------- -----------------------
P_2015              0          0 09-APR-2017 10:21:49 pm
SYS_P1521       60758        526 09-APR-2017 10:21:49 pm

demo@ORA12C>
demo@ORA12C> select partition_name,column_name,num_distinct,last_analyzed
  2  from user_part_col_statistics
  3  where table_name ='T';

PARTITION_ COLUMN_NAME          NUM_DISTINCT LAST_ANALYZED
---------- -------------------- ------------ -----------------------
P_2015     OWNER                           0 09-APR-2017 10:21:49 pm
P_2015     OBJECT_ID                       0 09-APR-2017 10:21:49 pm
P_2015     OBJECT_NAME                     0 09-APR-2017 10:21:49 pm
P_2015     CREATED                         0 09-APR-2017 10:21:49 pm
SYS_P1521  OWNER                          19 09-APR-2017 10:21:49 pm
SYS_P1521  OBJECT_ID                   60758 09-APR-2017 10:21:49 pm
SYS_P1521  OBJECT_NAME                 51212 09-APR-2017 10:21:49 pm
SYS_P1521  CREATED                      1113 09-APR-2017 10:21:49 pm

8 rows selected.

demo@ORA12C>

April 10, 2017 - 12:32 am UTC

That is correct. This is alluded to in:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

Basically we gather what stats we can get *without* having to do additional work during the load.

In terms of whether GATHER AUTO is the "best" option, well, that depends on your circumstances. It is certainly a good starting point

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer