Search My Oracle Blog

Custom Search

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:

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