X-list: oracle-l Return-Path: Subject: Re: error gathering statistics on less than 100 % From: Chris Marquez Message-id: d494e9760512221804p15c1162di47c4778a72426d9d@mail.gmail.com Date: 2005-12-23 03:04:52 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