Auto Create Statistics in Oracle 12c.

Exploring Oracle 12c .... 

Prior to Oracle 12c , we usually generate the table statistics  to give it to the Optimizer for generating cost effective plans.
 
In Oracle 12c  , this is done automatically.

Please see the example below .

Oracle 12c
===========

SQL> col product format a50
SQL> col version  format a15

SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ---------------------------
NLSRTL                                             12.1.0.1.0      Production
Oracle Database 12c Enterprise Edition             12.1.0.1.0      64bit Production
PL/SQL                                             12.1.0.1.0      Production
TNS for 64-bit Windows:                            12.1.0.1.0      Production

SQL>

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Create table t as Select * from all_objects;

Table created.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          73760 16-JUL-13


SQL> set pagesize 90
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     ORACLE_MAINTAINED               2           1 NONE            16-JUL-13
T     EDITIONABLE                     2           1 NONE            16-JUL-13
T     SHARING                         3           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13
T     NAMESPACE                       7           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     STATUS                          1           1 NONE            16-JUL-13
T     TIMESTAMP                    1133           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1037           1 NONE            16-JUL-13
T     CREATED                      1061           1 NONE            16-JUL-13
T     OBJECT_TYPE                    23           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 86           1 NONE            16-JUL-13
T     OBJECT_ID                   73760           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                  0           0 NONE            16-JUL-13
T     OBJECT_NAME                 40132           1 NONE            16-JUL-13
T     OWNER                          17           1 NONE            16-JUL-13

18 rows selected.




Oracle 11g
==========

SQL> set linesize 1000
SQL> set pagesize 90
SQL> col product format a50
SQL> col version  format a15
SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ------------------
NLSRTL                                             11.2.0.3.0      Production
Oracle Database 11g Enterprise Edition             11.2.0.3.0      64bit Production
PL/SQL                                             11.2.0.3.0      Production
TNS for 64-bit Windows:                            11.2.0.3.0      Production

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
T

SQL> col table_name format a5
SQL> col column_name format a20
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                                         NONE
T     OBJECT_NAME                                   NONE
T     SUBOBJECT_NAME                                NONE
T     OBJECT_ID                                     NONE
T     DATA_OBJECT_ID                                NONE
T     OBJECT_TYPE                                   NONE
T     CREATED                                       NONE
T     LAST_DDL_TIME                                 NONE
T     TIMESTAMP                                     NONE
T     STATUS                                        NONE
T     TEMPORARY                                     NONE
T     GENERATED                                     NONE
T     SECONDARY                                     NONE
T     NAMESPACE                                     NONE
T     EDITION_NAME                                  NONE

15 rows selected.

SQL> exec dbms_stats.gather_table_stats ( user , 'T' );

PL/SQL procedure successfully completed.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          56358 16-JUL-13

SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                          16           1 NONE            16-JUL-13
T     OBJECT_NAME                 31094           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                 12           1 NONE            16-JUL-13
T     OBJECT_ID                   56358           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 92           1 NONE            16-JUL-13
T     OBJECT_TYPE                    29           1 NONE            16-JUL-13
T     CREATED                      1122           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1459           1 NONE            16-JUL-13
T     TIMESTAMP                    1429           1 NONE            16-JUL-13
T     STATUS                          2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     NAMESPACE                      10           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13

15 rows selected.