Search My Oracle Blog

Custom Search

06 August, 2013

Gather Statistics Enhancements in 12c -- 1

12c has introduced some enhancements in the gathering and reporting of optimizer statistics.

A CTAS automatically includes statistics on the table (although it may not include column histograms)

SQL> create table obj_list tablespace hemant as select * from dba_objects;

Table created.

SQL> SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
  2  from user_tables;

---------- ------------
     91465 06-AUG 22:57

SQL> select count(*) from obj_list;



When the table was created with rows as a CTAS, Oracle automatically gathered statistics on the table.  Column NDVs were also computed, although no histogram was created --- which is the correct behaviour because, at this point without any queries against the table, Oracle doesn't know what histograms are required.

SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'OBJ_LIST'
  4  /

------------------------------ ------------ ---------------
OWNER                                    32 NONE
OBJECT_NAME                           53360 NONE
SUBOBJECT_NAME                          284 NONE
OBJECT_ID                             91465 NONE
DATA_OBJECT_ID                         7914 NONE
OBJECT_TYPE                              46 NONE
CREATED                                1034 NONE
LAST_DDL_TIME                          1132 NONE
TIMESTAMP                              1161 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                24 NONE
EDITION_NAME                              0 NONE
SHARING                                   3 NONE
EDITIONABLE                               2 NONE
ORACLE_MAINTAINED                         2 NONE

18 rows selected.


So we see NUM_DISTINCT being populated. And it was very fast !

No comments:

Aggregated by

Aggregated by
This blog is being aggregated by