Re: Dbms_stats giving OA-06512 error

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 05 Jan 2007 20:23:31 -0700
Message-Id: <200701060323.l063NcAr029727@mail96.megamailservers.com>

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)

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 05 2007 - 21:23:31 CST