Search My Oracle Blog

Custom Search

08 December, 2013

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.

.
.
.

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