Re: PCTFREE and PCTUSED

Thanks, Richard. My PC died this weekend (it's getting CPR right now) and I couldn't respond. I believe you now, but I still want to know what Metalink has to say about it. The documentation didn't do a very good job of explaining this thing.

On 11/10/2003 04:19:29 AM, Richard Foote wrote:
> ----- 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).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).