Re: table size not true with dba_segments??

On Mar 28, 1:29 pm, "ffanchy" <tirbo..._at_yahoo.com> wrote:
> On Mar 28, 11:01 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
>
>
> > 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
>
> Another solution if you want to follow the size of your tables is to
> use the following package : DBMS_SPACE.
>
> A small PL/SQL script will output you all the informations that you
> need (allocated space for the table and used space).
>
> It is much more easier than using dba_segments table.
>
> A small remark also : if you are using CLOB or LOB on your table you
> need to add the informations coming from lob.
>
> Script Exemple using DBA_SPACE:
>
> DEFINE OWNER_DATA = OWNER_YOU_WANT_TO_CHECK
>
> --Date definition
> COL date_jour new_value date_jour;
> SELECT to_char(sysdate, 'YYYY_MM_DD') AS
> date_jour
> FROM dual;
> DEF date_jour
>
> spool Name_of_the_file_for_output.txt;
>
> declare
> cursor d is select OWNER , TABLE_NAME from dba_tables where
> OWNER='&OWNER_DATA.';
>
> d1 varchar2(30); -- OWNER of the element
> d2 varchar2(30); -- Name of the table
>
> space_alloc number;
> space_free number;
> space_free_pct number;
> space_used number;
> space_used_pct number;
>
> tblocks number; -- Total Blocks
> tbytes number; -- Total Bytes
> ublocks number; -- Unused Blocks
> ubytes number; -- Unused Bytess
> lu_ef_id number; -- Last Used Extent File ID
> lu_eb_id number; -- Last Used Extent Block ID
> lu_block number; -- Last Used Block
>
> begin
> dbms_output.enable(500000);
> open d;
> loop
> fetch d into d1, d2;
> exit when d%NOTFOUND;
>
> -- Storage information
> dbms_space.unused_space(d1, d2, 'TABLE', tblocks, tbytes, ublocks,
> ubytes, lu_ef_id, lu_eb_id, lu_block);
>
> -- Generation of readable
> space_alloc := tbytes /1024 /1024;
> space_free := ubytes /1024 /1024;
> space_free_pct := round(space_free / space_alloc,3);
> space_used_pct := round(space_used / space_alloc,3);
> dbms_output.put_line('&date_jour.'||';'||d2||';'||d1||';'||
> space_alloc||';'||space_free||';'||space_free_pct);
>
> end loop;
>
> close d;
> exception
> when others then
> if d%ISOPEN then
> close d;
> end if;
> raise;
> end;
> /
>
> commit;
>
> SPOOL off
>
> disconnect
> exit

Regarding the ASSM, I assume it is oracle management of segment.

On my opinion, in each case, you can do better job manually than using automatic management of extends.

However unless you want to spend at least 1 day per week managing all this it is quiet better to use automatic management of extend.

I am using it on an OLTP database with arround 2000 tables and 150 Go RAC on 9i database. It is working fine.

Automatic management is using bitmap to manage extends instead of dictionnary, it is fastter and recommanded by Oracle. (If you are creating a new database try also to use the automatic freelist management parameter EXTEND MANAGEMENT LOCAL. With this parameter only the PCTFREE parameter has to be set all the others are managed by Oracle. This let you time for other stuffs. It is also quicker as freelists are manage by bitmap in the first block of the extend ). Received on Wed Mar 28 2007 - 06:47:17 CDT