Re: error gathering statistics on less than 100 %
- From: Chris Marquez <marquezemail@xxxxxxxxx>
- To: juancarlosreyesp@xxxxxxxxx
- Date: Thu, 22 Dec 2005 21:04:52 -0500
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 <juancarlosreyesp@xxxxxxxxx> 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>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Chris Marquez
Oracle DBA
Other related posts:
