Gather Column (Histogram) Stats can use an Index

We use the METHOD_OPT parameter to specify columns where we want specific Histograms when running a GATHER_TABLE_STATS. Oracle can actually make use of an Index to gather such column statistics.

To demonstrate, I run these statements :

23:24:40 SQL> exec dbms_session.session_trace_enable;



PL/SQL procedure successfully completed.



23:28:46 SQL> exec dbms_stats.gather_table_stats('HEMANT','GTS_DIRECT',-

23:29:01 > method_opt=>'FOR COLUMNS CUST_ID SIZE 250',estimate_percent=>100);



PL/SQL procedure successfully completed.



23:29:26 SQL> exec dbms_session.session_trace_disable;



PL/SQL procedure successfully completed.



23:29:49 SQL> 

The trace indicates that these statements are executed :

SQL ID: 7u3g0tnxmck0n

Plan Hash: 1065175879

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats

  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring

  no_substrb_pad */count(*), sum(sys_op_opnsize("CUST_ID")),

  sum(sys_op_opnsize("PROD_ID")), count("SALE_QTY"),

  sum(sys_op_opnsize("SALE_QTY")), count("SALE_PRICE"),

  sum(sys_op_opnsize("SALE_PRICE")), count("DISCOUNT_RATE"),

  sum(sys_op_opnsize("DISCOUNT_RATE"))

from

 "HEMANT"."GTS_DIRECT" t





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      1.91       2.04      10603      10607          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      1.91       2.04      10603      10607          0           1



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 184     (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=10607 pr=10603 pw=0 time=0 us)

1599995   TABLE ACCESS FULL GTS_DIRECT (cr=10607 pr=10603 pw=0 time=23223592 us cost=2928 size=55952400 card=1598640)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  asynch descriptor resize                        2        0.00          0.00

********************************************************************************



SQL ID: gqcrdy9818rwv

Plan Hash: 585751186

select substrb(dump(val,16,0,32),1,120) ep, cnt

from

 (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats

  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring

  no_substrb_pad */"CUST_ID" val,count(*) cnt  from "HEMANT"."GTS_DIRECT" t

  where "CUST_ID" is not null  group by "CUST_ID") order by val





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch       51      1.18       1.39       3132       3144          0          50

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       53      1.18       1.39       3132       3144          0          50



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 184     (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------

     50  SORT GROUP BY (cr=3144 pr=3132 pw=0 time=0 us cost=911 size=150 card=50)

1599995   INDEX FAST FULL SCAN GTS_DIRECT_CUST_NDX (cr=3144 pr=3132 pw=0 time=122696624 us cost=864 size=4795920 card=1598640)(object id 87367)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file scattered read                         40        0.01          0.15

********************************************************************************

Thus, Oracle used the GTS_DIRECT_CUST_NDX on the CUST_ID column.

.
.
.