Re: table size not true with dba_segments??

On Mar 28, 2:08 pm, "Carlos" <miotromailcar..._at_netscape.net> wrote:
> On 28 mar, 13:47, "ffanchy" <tirbo..._at_yahoo.com> wrote:
>
>
>
> > 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 ).
>
> I think you are a little bit confused between LMT (Locally Managed
> Tablespaces) and ASSM (Automatic Segment Space Management). LMT have
> nothing to do with freelists. (Ah! And LMT manage EXTENTS, no
> "extends").
>
> Cheers.
>
> Carlos.

Hello Carlos,

Sorry for the misspelling english, thanks for the correction.

Regarding freelist, I thought that Oracle talks about freelist for 2 thinks :

• free blocks not allocated at all (freespace in a tablespace)
• free blocks allocated in a segment (freespace in an extents) This is why I was using freelist for both of then. You put me on dout, I am not sure.

Regading LMT and ASSM, I know the difference. So this means my explanation wasn't clear.
Sorry about it, I'll try to be more clear another time.

Cheers,
François Received on Wed Mar 28 2007 - 07:25:18 CDT