error gathering statistics on less than 100 % 2005-12-22 - By Chris Marquez
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@(protected)> 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 (See ORA-00904.ora-code.com): : identificador no vßlido
> ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9136
> ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9150
> ORA-06512 (See ORA-06512.ora-code.com): 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 (See ORA-00904.ora-code.com): : identificador no vßlido
> ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9136
> ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9150
> ORA-06512 (See ORA-06512.ora-code.com): 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
Juan,<br>
<br>
Just a guess here, but I bet it has more to do with "FOR ALL COLUMNS SIZE
SKEWONLY"<br>
than the percentage change from 1%, to 50%, 100%.<br>
<br>
Try with "FOR ALL COLUMNS SIZE 1" or "FOR ALL COLUMNS SIZE AUTO
".<br>
<br>
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.
<br>
<br>
hth<br>
<br>
Chris Marquez<br>
Oracle DBA<br>
<br><br><div><span class="gmail_quote">On 12/22/05, <b class="gmail_sendername"
>Juan Carlos Reyes Pacheco</b> <<a href="mailto:juancarlosreyesp@(protected)"
>juancarlosreyesp@(protected)</a>> wrote:</span><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left: 1ex;">
Hi,<br>I found a curious problem on 9.2 when trying to get less than 100% on a
table<br><br>SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=>
;'ADM', TABNAME=>'DEFTRANSAC_M<br>E', ESTIMATE_PERCENT=>50, CASCADE=>
;TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWON
<br>LY');<br>BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM', TABNAME=
>'DEFTRANSAC_ME', E<br>STIMATE_PERCENT=>50, CASCADE=>TRUE,METHOD_OPT=
>'FOR ALL COLUMNS SIZE SKEWONLY');<br> END;<br><br>*<br>ERROR en lƯnea 1:
<br>ORA-00904 (See ORA-00904.ora-code.com): : identificador no vßlido<br>ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS
", lƯnea 9136<br>ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9150<br
>ORA-06512 (See ORA-06512.ora-code.com): en lƯnea 1<br><br><br>SQL> EXEC DBMS_STATS.GATHER
_TABLE_STATS( ownname=>'ADM', TABNAME=>'DEFTRANSAC_M
<br>E', ESTIMATE_PERCENT=>1, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONL<br>Y');<br>BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname=
>'ADM', TABNAME=>'DEFTRANSAC_ME', E<br>STIMATE_PERCENT=>1, CASCADE=>
;TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');
<br>END;<br><br>*<br>ERROR en lƯnea 1:<br>ORA-00904 (See ORA-00904.ora-code.com): : identificador no vßlido
<br>ORA-06512 (See ORA-06512.ora-code.com): en "SYS.DBMS_STATS", lƯnea 9136<br>ORA-06512 (See ORA-06512.ora-code.com): en "
;SYS.DBMS_STATS", lƯnea 9150<br>ORA-06512 (See ORA-06512.ora-code.com): en lƯnea 1<br><br>
But when I get 100% I don't get that problem.<br><br>SQL> EXEC  
;DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM', TABNAME=>'DEFTRANSAC_M<br
>E', ESTIMATE_PERCENT=>100, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS
SIZE SKEWO
<br>NLY');<br><br>Procedimiento PL/SQL terminado correctamente.<br><br><br
>Trying with other tables I don't get that problem.<br><br>SQL> EXEC
DBMS_STATS.GATHER_TABLE_STATS( ownname=>'ADM', TABNAME=>'CUENTAS_ME'
,
<br> ESTIMATE_PERCENT=>50, CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS
SIZE SKEWONLY'<br>);<br><br>Procedimiento PL/SQL terminado correctamente.<br>
<br>SQL><br>--<br><a href="http://www.freelists.org/webpage/oracle-l">
http://www.freelists.org/webpage/oracle-l</a><br><br><br></blockquote></div><br
><br clear="all"><br>-- <br>Chris Marquez<br>Oracle DBA
|
|
