26 August, 2011

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.

.
.
.

No comments: