Re: error gathering statistics on less than 100 %

Juan,

Just a guess here, but I bet it has more to do with "FOR ALL COLUMNS SIZE SKEWONLY"
than the percentage change from 1%, to 50%, 100%.

Try with "FOR ALL COLUMNS SIZE 1" or "FOR ALL COLUMNS SIZE AUTO".

If you need the HISTOGRAMS then your out of luck...but personally I have had nothing but trouble with STATS and HISTOGRAMS...I use "FOR ALL COLUMNS SIZE 1" unless the developer can make a case for something else.

hth

Chris Marquez
Oracle DBA

On 12/22/05, Juan Carlos Reyes Pacheco wrote:
>
> Hi,
> I found a curious problem on 9.2 when trying to get less than 100% on a
> table
>
> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'DEFTRANSAC_M
> E', ESTIMATE_PERCENT=>50, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWON
> LY');
> BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'DEFTRANSAC_ME', E
> STIMATE_PERCENT=>50, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWONLY');
> END;
>
> *
> ERROR en lƯnea 1:
> ORA-00904: : identificador no vßlido
> ORA-06512: en "SYS.DBMS_STATS", lƯnea 9136
> ORA-06512: en "SYS.DBMS_STATS", lƯnea 9150
> ORA-06512: en lƯnea 1
>
>
> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'DEFTRANSAC_M
> E', ESTIMATE_PERCENT=>1, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWONL
> Y');
> BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'DEFTRANSAC_ME', E
> STIMATE_PERCENT=>1, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWONLY');
> END;
>
> *
> ERROR en lƯnea 1:
> ORA-00904: : identificador no vßlido
> ORA-06512: en "SYS.DBMS_STATS", lƯnea 9136
> ORA-06512: en "SYS.DBMS_STATS", lƯnea 9150
> ORA-06512: en lƯnea 1
>
> But when I get 100% I don't get that problem.
>
> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'DEFTRANSAC_M
> E', ESTIMATE_PERCENT=>100, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWO
> NLY');
>
> Procedimiento PL/SQL terminado correctamente.
>
>
> Trying with other tables I don't get that problem.
>
> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM',
> TABNAME=>'CUENTAS_ME',
> ESTIMATE_PERCENT=>50, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE
> SKEWONLY'
> );
>
> Procedimiento PL/SQL terminado correctamente.
>
> SQL>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Chris Marquez
Oracle DBA