Re: Oracle PCTFree

"Vinh" <huuvinh_at_gmail.com> wrote in message news:1105605856.028688.108950_at_f14g2000cwb.googlegroups.com...
> Tablespace test is local management and segment space manual. Database
> version is Oracle 9.2.0.5
>
> create table test10 (
> id number,
> name varchar2(40) )
> tablespace test
> PCTFREE 10
>
> ;
>
> Table created.
>
> create table test1 (
> id number,
> name varchar2(40) )
> tablespace test
> PCTFREE 1
>
> ;
>
> Table created.
>
> SQL> select TABLE_NAME, PCT_FREE, PCT_USED from dba_tables
> where TABLE_NAME like 'TEST%';
> 2
> TABLE_NAME PCT_FREE PCT_USED
>
> ------------------------------ ---------- ----------
>
> TEST1 1
> TEST10 10
>
> begin
> for i in 1..100000
> loop
> insert into test10
> values (i,'This is testing' ||i);
> end loop;
> end;
> /
>
> begin
> for i in 1..100000
> loop
> insert into test1
> values (i,'This is testing' ||i);
> end loop;
> end;
>
> SQL> l
> 1 select SEGMENT_NAME, sum(BLOCKS) from dba_extents
> 2 where TABLESPACE_NAME='TEST'
> 3* GROUP BY SEGMENT_NAME
> SQL> /
>
> SEGMENT_NAME SUM(BLOCKS)
>
> -------------------- -----------
>
> TEST1 512
> TEST10 512
>
> My double is how can with the different PCTFREE (1 and 10) but the
> number block is the same?
> If I change PCTFREE to 50, I can see the difference!!
>

Hi Vinh,

Because the value of sum(BLOCKS) in dba_extents corresponds to the number of blocks *allocated* to the segment not necessarily the number of blocks *occupied* by rows below the HWM of the segment.

Analyze the tables and look at the value of BLOCKS in dba_tables and now compare the difference.

By having a PCTFREE of 50, the table has required more *extents* (and hence has allocated more blocks) to fit the necessary rows whereas with values of 1 and 10, the required rows have managed to fit within the same number of extents although you'll find more blocks below the HWM for the TEST10.

Hope it makes sense.

Cheers

Richard Received on Thu Jan 13 2005 - 03:22:43 CST