Re: does anybody recomends using dbms_stats.gather_schema_stats with for columnns size skewonly

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: hamcdc@xxxxxxxxxxx
  • Date: Sun, 16 Jan 2005 20:20:18 -0700

Connor,

sorry I wan't precise. The column avg_row_len in dba_tables IS being 
populated, however not with the real average column length but with the 
default which the CBO would also be using if there were no statistics at 
all for the table.
What I should have said is that it GATHERS only the num_rows statistic - by 
executing a select count(*) from table - plus retrieves the number of 
blocks below the high water mark from the segment header and then, while it 
is updating the tab$ table with the num_rows and blocks values anyway, it 
also explicitly sets avg_row_len to the default 100.

I hadn't tried it in 10g, but it is comforting to know that some things 
haven't changed. :-)

At 07:07 PM 1/16/2005, Connor McDonald wrote:
>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');
>
>
>   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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

--
//www.freelists.org/webpage/oracle-l

Other related posts: