ASSM and PCTFREE

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/oracle-l@fatcity.com/msg84991.html

About these ads
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: