| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: table size not true with dba_segments??
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);
end loop;
close d;
exception
when others then if d%ISOPEN then close d; end if; raise;
commit;
SPOOL off
disconnect
exit
Received on Wed Mar 28 2007 - 06:29:28 CDT
 |
 |