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;

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST
     91465 06-AUG 22:57


SQL> select count(*) from obj_list;

  COUNT(*)
----------
     91465

SQL>

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  /

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
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.

SQL>

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

No comments:

Post a Comment