----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
