From 9i, Oracle introduced Automatic Segment Space Management (ASSM) as a replacement of freelists management.In a locally managed tablespace, there are two methods by which Oracle manages the Segment Space:- Automatic and Manual.Manual segment space management uses “FREELISTS” to manage the free space in the segment whereas the Automatic segment space management uses “BITMAPS”.From 10g onwards, Automatic Segment Management is by default for all new permanent,locally managed tablespaces.
ASSM, completely eliminated the need to specify PCTUSED,FREELISTS and FREELIST GROUPS storage parameters for the objects created in the tablespace.Lets check it out.
ANAND @ oracle >create table test_pctused (name varchar(10)) pctused 40; Table created. ANAND @ oracle >select OWNER,TABLE_NAME,TABLESPACE_NAME,PCT_FREE,PCT_USED from dba_tables where owner='ANAND' and table_name like 'TEST_PCT%'; OWNER TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED -------- ------------- ------------------- ---------- ---------- ANAND TEST_PCTUSED ASSM_TEST 10
The ASSM_TEST tablespace is a locally, automatic segment space management tablespace.The PCT_USED column is blank,its ignored.
What is the table is created in locally ,manual segment space managed tablespace???Lets see
ANAND @ oracle >create table test_pctused_manual (name varchar(10)) pctused 40 tablespace test; Table created. ANAND @ oracle >select OWNER,TABLE_NAME,TABLESPACE_NAME,PCT_FREE,PCT_USED from dba_tables where owner='ANAND' and table_name like 'TEST_PCT%'; OWNER TABLE_NAME TABLESPACE_NAME PCT_FREE PCT_USED -------- ---------------------- ------------------ ---------- ---------- ANAND TEST_PCTUSED_MANUAL TEST 10 40
PCT_USED column has the value 40 as mentioned while creating the table.Its not ignored as the TEST tablespace is having manual automatic segment space management.
ANAND @ oracle >select TABLESPACE_NAME,BLOCK_SIZE,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces where tablespace_name in ('ASSM_TEST','TEST');
TABLESPACE_NAME BLOCK_SIZE CONTENTS EXTENT_MAN SEGMEN
------------------------------ ---------- --------- ---------- ------
ASSM_TEST 8192 PERMANENT LOCAL AUTO
TEST 8192 PERMANENT LOCAL MANUAL
As said earlier,ASSM ignores the PCTUSED,FREELISTS and FREELIST GROUPS , the PCTFREE storage parameter is not ignored.PCTFREE is still required even with ASSM.Lets check it out:-
ANAND @ oracle >select tablespace_name, segment_space_management from dba_tablespaces
where tablespace_name in ('ASSM_TEST','TEST');
TABLESPACE_NAME SEGMEN
------------------------------ ------
ASSM_TEST AUTO
TEST MANUAL
Lets create two non ASSM table, one with low PCTFREE, the other with high PCTFREE
ANAND @ oracle >create table test_non_assm_1 tablespace test pctfree 5 as select * from dba_tables; Table created. ANAND @ oracle >insert into test_non_assm_1 select * from test_non_assm_1; 1586 rows created. ANAND @ oracle >/ 3172 rows created. ANAND @ oracle >/ 6344 rows created. ANAND @ oracle >/ 12688 rows created. ANAND @ oracle >/ 25376 rows created. ANAND @ oracle >/ 50752 rows created. ANAND @ oracle >commit; Commit complete. ANAND @ oracle >create table test_non_assm_2 tablespace test pctfree 90 pctused 10 as select * from dba_tables; Table created. ANAND @ oracle >insert into test_non_assm_2 select * from test_non_assm_2; 1587 rows created. ANAND @ oracle >/ 3174 rows created. ANAND @ oracle >/ 6348 rows created. ANAND @ oracle >/ 12696 rows created. ANAND @ oracle >/ 25392 rows created. ANAND @ oracle >/ 50784 rows created. ANAND @ oracle >commit; Commit complete.
Let’s now analyze the table
ANAND @ oracle >analyze table TEST_NON_ASSM_1 compute statistics;
Table analyzed.
ANAND @ oracle > analyze table TEST_NON_ASSM_2 compute statistics;
Table analyzed.
ANAND @ oracle >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 2855 520
TEST_NON_ASSM_2 32703 7417
The table with high PCTFREE values uses more space and has higher avg space value
Now, lets repeat the test for ASSM tablepsace.
ANAND @ oracle >create table test_assm_1 tablespace assm_test
pctfree 5 as select * from dba_tables;
Table created.
ANAND @ oracle >insert into test_assm_1 select * from test_assm_1;
1588 rows created.
ANAND @ oracle >/
3176 rows created.
ANAND @ oracle >/
6352 rows created.
ANAND @ oracle >/
12704 rows created.
ANAND @ oracle >/
25408 rows created.
ANAND @ oracle >/
50816 rows created.
ANAND @ oracle >commit;
Commit complete.
ANAND @ oracle >create table test_assm_2 tablespace assm_test
pctfree 90 pctused 10 as select * from dba_tables;
Table created.
ANAND @ oracle >insert into test_assm_2 select * from test_assm_2;
1589 rows created.
ANAND @ oracle >/
3178 rows created.
ANAND @ oracle >/
6356 rows created.
ANAND @ oracle >/
12712 rows created.
ANAND @ oracle >/
25424 rows created.
ANAND @ oracle >/
50848 rows created.
ANAND @ oracle >commit;
Commit complete.
ANAND @ oracle >analyze table TEST_ASSM_1 compute statistics;
Table analyzed.
ANAND @ oracle >analyze table TEST_ASSM_2 compute statistics;
Table analyzed.
ANAND @ oracle >select table_name, blocks, avg_space from dba_tables where
table_name in ('TEST_ASSM_1', 'TEST_ASSM_2','TEST_NON_ASSM_1','TEST_NON_ASSM_2');
TABLE_NAME BLOCKS AVG_SPACE
------------------------------ ---------- ----------
TEST_ASSM_2 32555 7405
TEST_ASSM_1 2896 593
TEST_NON_ASSM_2 32703 7417
TEST_NON_ASSM_1 2855 520
As we can see, the results are very similar. The table with a high pctfree has a massive number of blocks relative to the table with a low pctfree.
So,PCTFREE is most definitely *NOT* ignored with ASSM !!
Reference http://www.mail-archive.com/[email protected]/msg84991.html