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;

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016