Re: does anybody recomends using dbms_stats.gather_schema_stats with for columnns size skewonly
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 Jan 2005 02:07:40 +0000 (GMT)
Hi Wolfgang,
Has there been a change in v10 ? I just ran this on 10.1.0.3
SQL> exec dbms_stats.delete_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'T',estimate_percent=>1,
4 method_opt=>'FOR COLUMNS SIZE SKEWONLY' );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from
2 ( select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN
3 from user_tables
4 where table_name = 'T' ),
5 ( select COLUMN_NAME, NUM_DISTINCT,LOW_VALUE,HIGH_VALUE
6 from user_tab_columns
7 where table_name = 'T' )
8
SQL> /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN COLUMN_NAME NUM_DISTINCT
LOW_VALUE HIGH_VALUE
---------- ---------- ------------ ----------- -------------- ------------
------------
------------
2826700 12152 0 100 X
2826700 12152 0 100 Y
so we do in fact get the avg_row_len (whilst still no column stats)
(This is *not* to say that I'm advocating this approach!)
Cheers
Connor
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> comments inline
>
> At 03:15 PM 1/16/2005, you wrote:
> >Hello:
> >
> >We are estimating statistics for each schema of our aplication using
> >DBMS_STATS.GATHER_SCHEMA_STATS
> >with method_opt FOR COLUMNS SIZE SKEWONLY (not using the ALL) and
> >estimating with dbms_stats.auto_sample_size, options GATHER and CASCADE
> >true.
> >
> >does this approach will estimate the estadistics for all columns using
> >size 1 if the data is not skewed? would it do it only if needed?
>
> This approach - method_opt=>'for columns size skewonly' - will perform very
> well since all it is doing is counting the number of rows of the table and
> update the num_rows and blocks statistics in dba_tables. OK, if you use
> cascade=>true it will also gather index statistics. What it does NOT do is
> gather any column statistics nor even the avg_row_len statistic for the table.
>
> My recommendation is not to waste any computing resources on that approach.
>
>
>
> >Alfonso Leon
> >Guayaquil - Ecuador
> >--
> >//www.freelists.org/webpage/oracle-l
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> --
> //www.freelists.org/webpage/oracle-l
>
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
