Re: table size not true with dba_segments??

On 27 Mar, 18:36, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Mar 27, 9:43 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
>
>
> > On 27 Mar, 13:31, "sybrandb" <sybra..._at_gmail.com> wrote:
> > > On Mar 27, 12:48 pm, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
> > > > hi,
> > > > i've a oracle 9i db and after run dbms_stats i find a table size with
> > > > this command:
>
> > > > select bytes,initial_extent,next_extent,extents from dba_segments
> > > > where segment_name='TRANSIT' and OWNER='SACT';
>
> > > > the ouput is this:
>
> > > > BYTES INITIAL_EXTENT NEXT_EXTENT EXTENTS
> > > > ---------- --------------
> > > > ----------- ----------
> > > > 756678656 67502080 377524224 7
>
> > > > so, there is something wrong :-?
>
> > > > if next_extent is about 377Mb and the extents allocated is 7, it
> > > > should be more then 756Mb (BYTES). Like 67Mb + 377Mb *6 , (initial +
> > > > 6 extents)
> > > > Instead, why i've gather this result?
>
> > > > how i estimate table size?
>
> > > > thanks a lot
>
> > > > andrew
>
> > > Nothing wrong, apart from the fact you should realize (or look up in
> > > the documentation) the size of the next_extent is the size which will
> > > actually be allocated, when the next_extent is created. So next extent
> > > in dba_segments = the result of next_extent in dba_tables +
> > > pctincrease in dba_tables. If you look in dba_extents, you will see
> > > bytes in dba_extents adds up to bytes in dba_segments (it better
> > > would, as dba_segments is based on dba_extents).
> > > So apart from the usual not reading the documentation and jumping to
> > > usenet right away, there is nothing wrong.
>
> > ok, sorry.
>
> > select initial_extent,next_extent,PCT_INCREASE from dba_tables where
> > table_name='TRANSIT';
>
> > INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
> > -------------- ----------- ------------
> > 67502080 377524224 50
>
> > select EXTENT_ID,bytes,blocks from dba_extents where segment_name =
> > 'TRANSIT';
>
> > EXTENT_ID BYTES BLOCKS
> > ---------- ---------- ----------
> > 4 111935488 27328
> > 5 167903232 40992
> > 6 251789312 61472
> > 3 74579968 18208
> > 0 67502080 16480
> > 1 33161216 8096
> > 2 49807360 12160
>
> > so, PCT_INCREASE is 50 , therefore every extend is increased about
> > 50%.
>
> > But if i understand, this method don't estimate the really size of a
> > table because the last extent allocated contained also free space.
> > Therefore, how i find the actual really size of a table??
>
> > i know, i'm very wrong DBA :(-
>
> General Rule: Having PCT_INCREASE set at anything other than 0 is not
> recommended. All extents in the same tablespace should be set to the
> same size, unless ASSM is in use.
>
> Compare the output of this:
> SELECT
> BYTES,
> BLOCKS
> FROM
> DBA_SEGMENTS
> WHERE
> OWNER='SACT'
> AND SEGMENT_NAME='TRANSIT';
>
> With the output of this (assuming an 8KB block size):
> SELECT
> INITIAL_EXTENT,
> NEXT_EXTENT,
> MIN_EXTENTS,
> PCT_INCREASE,
> NUM_ROWS,
> AVG_ROW_LEN,
> BLOCKS,
> AVG_ROW_LEN*NUM_ROWS BYTES_1,
> BLOCKS*8192 BYTES_2
> FROM
> DBA_TABLES
> WHERE
> OWNER='SACT'
> AND TABLE_NAME='TRANSIT';
>
> You might notice a difference in the number of blocks and the number
> of bytes. For example, you may see something like this:
> BYTES BLOCKS
> ---------- ----------
> 285212672 34816
>
> INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE NUM_ROWS
> AVG_ROW_LEN BLOCKS BYTES_1 BYTES_2
> -------------- ----------- ----------- ------------ ----------
> --------------------- ---------- ----------
> 131072 1 2253341
> 108 34321 243360828 281157632
>
> The first output shows the actual space required on disk. The second
> output shows roughly the amount of that space that is in use for this
> table.
>

Thanks very much for your tips.

seems that the values of second select is very diverged:

BLOCKS       BYTES_1           BYTES_2
------              ----------               --------------
164348         474146340          673169408

block_size is 4k.

However, you advise to use ASSM instead manual for OLTP db?

thanks Received on Wed Mar 28 2007 - 04:01:38 CDT