Re: SMON Logic?

Chakravarthy KM Nalamotu wrote:
> Was wondering why SMON requires pctincrease to be > 0
> to coalesce tablespaces?
> Wouldn't it be convenient to use pctincrease 0 and still
> have SMON coalesce the tablespaces. This way all the
> extents are uniformly sized.

        The assumption that Oracle makes is that, if you have PCTINCREASE 0 as the default storage for a tablespace, you will be creating all of the extents in the tablespace with a standard size. If this assumption is correct, there is no reason to coalesce the free space in the tablespace; in fact, performance will be very slightly better because any free extents created by dropping database objects will be the correct size to be reused already; Oracle won't have to split up a large free extent.

        The problem that one runs into when tablespaces do not coalesce free space automatically is that, with variable extent sizes, free space "bubbles" of irregular size are created as database objects are created and dropped. These "bubbles" of free space can cause a lot of internal fragmentation within the tablespace, leading to an inability to create new objects of a desired size or extend existing objects -- even when there is sufficient free space in a tablespace, it might be scattered around in small clusters.

> Also, is it true that SMON coalesces only the first 5 datafiles
> and ignores the rest?

        I don't know the answer to this offhand but it is easy enough to test, if it really concerns you:

create tablespace test_smon
datafile 'your_data_file_name' size 2mb
default storage (initial 100k next 100k pctincrease 50);

alter tablespace test_smon add datafile 'data_file_2' size 2mb;
alter tablespace test_smon add datafile 'data_file_3' size 2mb;
alter tablespace test_smon add datafile 'data_file_4' size 2mb;
alter tablespace test_smon add datafile 'data_file_5' size 2mb;
alter tablespace test_smon add datafile 'data_file_6' size 2mb;
alter tablespace test_smon add datafile 'data_file_7' size 2mb;

create table test_table (col1 char) tablespace test_smon storage (initial 900k next 900k pctincrease 0); alter table test_table allocate extent (size 900k);

(repeat the "alter table" command until it cannot extend any further; this should be a total of 12 more times.)

drop table test_table;

select * from dba_free_space where tablespace_name = 'TEST_SMON' order by 1,2,3;

...wait half an hour or so and repeat. Has all of the space been coalesced?

-bn
neumebm_at_hpd.abbott.com Received on Tue May 06 1997 - 00:00:00 CDT