Re: PCTFREE and PCTUSED

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, November 10, 2003 9:54 AM

> Note that ASSM bitmaps track "freeness" not "fullness", to be correct in
> terminology.

Hi Tanel,

At restaurants, I always say to the waiter that my glass of wine is only 1/10 full and can I please have a top-up ;)

Weird eh !!

I posted this little demo on metalink to hopefully prove to Mladen that PCTFREE is not ignored :

Let's check which tablespaces to use for our test.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name in ('USERS', 'BOWIE_STUFF');

TABLESPACE_NAME SEGMEN

------------------------------ ------
BOWIE_STUFF                    MANUAL
USERS                                  AUTO

First lets create two non ASSM tables, one with a low pctfree, the other with a high pctfree.

SQL> create table test_non_assm_1 tablespace bowie_stuff pctfree 5 as select * from dba_tables;

Table created.

SQL> insert into test_non_assm_1 select * from test_non_assm_1;

1103 rows created.

SQL> / 2206 rows created.

SQL> / 4412 rows created.

SQL> / 8824 rows created.

SQL> commit;

Commit complete.

SQL> create table test_non_assm_2 tablespace bowie_stuff pctfree 90 pctused 10 as select * from dba_tables;

Table created.

SQL> insert into test_non_assm_2 select * from test_non_assm_2;

1104 rows created.

SQL> / 2208 rows created.

SQL> / 4416 rows created.

SQL> / 8832 rows created.

SQL> commit;

Commit complete.

Let's now analyze these tables (I chose analyze because I want to see the avg space for each table).

SQL> analyze table TEST_NON_ASSM_1 compute statistics;

Table analyzed.

SQL> analyze table TEST_NON_ASSM_2 compute statistics;

Table analyzed.

SQL> select table_name, blocks, avg_space from dba_tables where table_name in ('TEST_NON_ASSM_1', 'TEST_NON_ASSM_2');

TABLE_NAME                     BLOCKS AVG_SPACE
------------------------------ ----------  ----------
TEST_NON_ASSM_1           452            569
TEST_NON_ASSM_2         4982          7395

As expected, the table with a high pctfree uses dramatically more space and has a higher avg space value.

Let's repeat the test with ASSM tables. This should *prove* whether pctfree is ignored or not.

SQL> create table test_assm_1 tablespace users pctfree 5 as select * from dba_tables;

Table created.

SQL> insert into test_assm_1 select * from test_assm_1;

1105 rows created.

SQL> / 2210 rows created.

SQL> / 4420 rows created.

SQL> / 8840 rows created.

SQL> commit;

Commit complete.

SQL> create table test_assm_2 tablespace users pctfree 90 pctused 10 as select * from dba_tables;

Table created.

SQL> insert into test_assm_2 select * from test_assm_2;

1106 rows created.

SQL> / 2212 rows created.

SQL> / 4424 rows created.

SQL> / 8848 rows created.

SQL> commit;

Commit complete.

SQL> analyze table TEST_ASSM_1 compute statistics;

Table analyzed.

SQL> analyze table TEST_ASSM_2 compute statistics;

Table analyzed.

SQL> select table_name, blocks, avg_space from dba_tables where table_name in ('TEST_ASSM_1', 'TEST_ASSM_2');

TABLE_NAME                    BLOCKS AVG_SPACE
------------------------------ ---------- ----------
TEST_ASSM_2                            4730        7347
TEST_ASSM_1                              501        1213

As we can see, the results are very similar. The table with a high pctfree has a massive number of blocks and (potential) wasted space relative to the table with a low pctfree.

PCTFREE is most definitely *NOT* ignored with ASSM !!

Cheers

Richard Foote

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).