Re: Dbms_stats giving OA-06512 error
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: mgogala@xxxxxxxxxxx
- Date: Fri, 05 Jan 2007 20:23:31 -0700
At 05:36 PM 1/5/2007, Mladen Gogala wrote:
First of all, your method_opt is erroneous. You should specify something like
'FOR ALL COLUMNS SIZE SKEWONLY' or 'FOR ALL COLUMNS SIZE AUTO' or Wolfgang's
favorite option 'FOR ALL INDEXED COLUMNS SIZE <histogram size>'.
Mladen,
Not quite correct. method_opt=>'for all columns' gathers histograms
on all columns with the default size of 75:
9.2.0.8> create table test as select trunc(dbms_random.value(1,501))
col1 from dual connect by level <= 5000;
Table created.
9.2.0.8> alter session set events '10046 trace name context forever, level 4'
2
9.2.0.8> BEGIN
2 dbms_stats.gather_table_stats( ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => dbms_stats.auto_sample_size,
5 method_opt => 'for all columns',
6 cascade => true);
7 END;
8 /
PL/SQL procedure successfully completed.
9.2.0.8> alter session set events '10046 trace name context off'
2
9.2.0.8> @colstats test
table column NDV density
nulls lo hi av lg bkts
------------------------ --------------------- --------- ------------
--------- ---------------- ---------------- ----- -----
TEST COL1 500 2.1992E-03
0 1 500 4 75
1 row selected.
9.2.0.8>
Note the number of buckets. Although it might be misleading,
especially in pre Oracle 10g, but here it is correct.
9.2.0.8> select max(ENDPOINT_NUMBER) from dba_histograms where
table_name='TEST' and column_name='COL1';
MAX(ENDPOINT_NUMBER)
--------------------
75
1 row selected.
and finally from the 10046 trace this statement:
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120)
minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep,
sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when
rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt,
max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from
(select /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact
dynamic_sampling(0) no_monitoring */"COL1" val, ntile(75) over (order
by "COL1") bkt from "SCOTT"."TEST" t where "COL1" is not null)
group by val) group by maxbkt order by maxbkt
showing "clearly" the collection of a 75 bucket HB histogram
William,
I am convinced your error has nothing to do with your change to the
gathering procedure. It has more likely to do with a change to table
"EDRS"."AMENDMENT". Did someone create a function-based index?
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
