GATHER_TABLE_STATS : What SQLs does it call ?. 12c

The DBMS_STATS.GATHER_TABLE_STATS procedure actually generates and executes SQL statements to collect table, column and index statistics.

Let's see a simple example in 12c 12.1.0.1:

SQL> create table my_simple_table
  2  as select object_id as id_col, object_name as name_col, 
  3  owner as owner_col, created as date_col
  4  from dba_objects;

Table created.

SQL> select count(*) from my_simple_table;

  COUNT(*)
----------
     91493

SQL> create index my_simple_ndx on my_simple_table(id_col);

Index created.

SQL>
SQL> select name_col from my_simple_table where owner_col = 'HEMANT';

NAME_COL
------------------------------
OBJ_LIST
OBJ_LIST_2_NDX
OBJ_LIST_2
MY_GTT_DELETE_12C
MY_GTT_PRESERVE_12C
MY_SIMPLE_TABLE

6 rows selected.

SQL> select count(*) from my_simple_table where owner_col = 'SYS';

  COUNT(*)
----------
     41841

SQL> 
SQL> execute dbms_Session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','MY_SIMPLE_TABLE',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> execute dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel6 Desktop]$ 

So, I have setup a simple table with one index and also executed queries against one column. What are the SQLs in the trace file ? [Ignoring the recursive calls that are used for parsing and lookup on the object (MY_SIMPLE_TABLE) ]

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  */to_char(count("ID_COL")),
  to_char(substrb(dump(min("ID_COL"),16,0,64),1,240)),
  to_char(substrb(dump(max("ID_COL"),16,0,64),1,240)),
  to_char(count("NAME_COL")),to_char(substrb(dump(min("NAME_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("NAME_COL"),16,0,64),1,240)),
  to_char(count("OWNER_COL")),to_char(substrb(dump(min("OWNER_COL"),16,0,64),
  1,240)),to_char(substrb(dump(max("OWNER_COL"),16,0,64),1,240)),
  to_char(count("DATE_COL")),to_char(substrb(dump(min("DATE_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("DATE_COL"),16,0,64),1,240)),
  count(rowidtochar(rowid))
from
 "HEMANT"."MY_SIMPLE_TABLE" t  /* NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,
  NIL,NIL,RWID,U254,U254,U254,U254U*/


SELECT /*+ parallel */ TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/pos') + 1) POS, EXTRACTVALUE(VALUE(T),
  '/select_list_item/value') VAL, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/nonnulls')) NONNULLS, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/ndv')) NDV, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/split')) SPLIT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rsize')) RSIZE, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rowcnt')) ROWCNT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/topncnt')) TOPNCNT, EXTRACT(VALUE(T),
  '/select_list_item/topn_values').GETCLOBVAL() TOPN, NULL MINFREQ, NULL
  MAXFREQ, NULL AVGFREQ, NULL STDDEVFREQ
FROM
 TABLE(XMLSEQUENCE(EXTRACT(:B1 , '/process_result/select_list_item'))) T
  ORDER BY TOPNCNT DESC


select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump(substrb("OWNER_COL",1,64),16,0,64),1,240) val,
                      rowidtochar(rowid) rwid from "HEMANT"."MY_SIMPLE_TABLE" t where rowid in (chartorowid('AAAWvkAAGAAAADbAAA'),chartorowid('AAAWvkAAGAAAADbACN'),chartorowid('AAAWvkAAGAAAADdACM'),chartorowid('AAAWvkAAGAAAADhAB7'),chartorowid('AAAWvkAAGAAAAEWAAc'),chartorowid('AAAWvkAAGAAAAEaABr'),chartorowid('AAAWvkAAGAAAAFEABB'),chartorowid('AAAWvkAAGAAAAFEABC'),chartorowid('AAAWvkAAGAAAAFEABE'),chartorowid('AAAWvkAAGAAAAFEABI'),chartorowid('AAAWvkAAGAAAAFFAB8'),chartorowid('AAAWvkAAGAAAAFGAAE'),chartorowid('AAAWvkAAGAAAAFGABG'),chartorowid('AAAWvkAAGAAAAFPAAv'),chartorowid('AAAWvkAAGAAAAL4ABl'),chartorowid('AAAWvkAAGAAAAM+ABB'),chartorowid('AAAWvkAAGAAAAMRABu'),chartorowid('AAAWvkAAGAAAAMUAA9'),chartorowid('AAAWvkAAGAAAAMXAAM'),chartorowid('AAAWvkAAGAAAAMZABw'),chartorowid('AAAWvkAAGAAAAMZACE'),chartorowid('AAAWvkAAGAAAAN0AAz'),chartorowid('AAAWvkAAGAAAAN2AA4'),chartorowid('AAAWvkAAGAAAAN3AAe'),chartorowid('AAAWvkAAGAAAAN3AAm'),chartorowid('AAAWvkAAGAAAAN3ABN'),chartorowid('AAAWvkAAGAAAANPAAI'),chartorowid('AAAWvkAAGAAAANUABo'),chartorowid('AAAWvkAAGAAAANWABM'),chartorowid('AAAWvkAAGAAAANWACG'),chartorowid('AAAWvkAAGAAAANxAB4'),chartorowid('AAAWvkAAGAAAANxAB7')) order by nlssort(substrb("OWNER_COL",1,64),'NLS_SORT = binary')


select /*+  no_parallel_index(t, "MY_SIMPLE_NDX")  dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  no_expand index(t,"MY_SIMPLE_NDX") */
  count(*) as nrw,count(distinct sys_op_lbid(93157,'L',t.rowid)) as nlb,null
  as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "HEMANT"."MY_SIMPLE_TABLE" t where "ID_COL" is not null

The first SQL is a simple Full Table Scan that, besides gathering a count of rows in the table, gathers basic column statistics : Number of Not Null values, Min value, Max value. What are the second and third SQLs ? The fourth SQL gather Index stats.

.
.
.