Re: table size not true with dba

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 27 Mar 2007 09:36:32 -0700
Message-ID: <1175013392.453951.296750@o5g2000hsb.googlegroups.com>

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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Mar 27 2007 - 11:36:32 CDT

Re: table size not true with dba_segments??