Subject: How to Treat Large Objects in a Database
Last Revision Date: 11 September 1994
Author:Moe Fardoost
As databases grow, a small number of objects grow at much larger
rate than others. These objects characterise the database itself
and should receive attention by the dba in order to keep the
database in a tuned state. The dba may consider the following
points.
o Large tables are handled much more easily if created and maintained
in their own dedicated tablespaces. Interfering small objects can
cause serious fragmentation by splitting two adjacent extents
which would otherwise have been available to the large object.
o The storage parameters of the object should be revised on regular
basis. Some tables may grow fast during a period of time for
which a large pctincrease (see page 5-138 of the SQL Language
Reference Manual) may be very relevent. In other cases a
a large table slows down its grows and does not require as large
a NEXT (See storage clause for object creation) value as before.
o As the table becomes older, its size generally becomes larger
and the likelyhood of a table export drops as the dba finds less
and less time on his/her hand for such an operation. This is a
very important issue. A well thought table definition, can
delay fragmentation or prevent it altogether whereas a badly
maintained table can easily become too fragmented after a
relatively short period of time. The dba may initially create
the object with an educated guess of how large and fast it is
going to grow(or use the formulae given in the dba guide).
But during the initial period of the object's life, the dba
needs to observe the growth of the object and ideally export it
at a convenient stage and import it using better storage
parameters having the tablespace recreated. If this operation
is carefully done at an early stage in a large object's life,
a lot of user and cpu time can be saved in the future.
If a dba inherits a database with a number of large objects,
he/she should spend a while studying the behaviour
of these objects. The more time he/she spends at this stage,
the more he/she will save later on.
Some applications require a table to be dropped every time
new data is uploaded (using sqlloader for example).
In such cases, the dba should ensure that at every load,
the tablespace is defragmented, ideally by dropping the whole
tablespace every so often before the upload or ideally after
each major load. For every load, ensure that you can allocate
as much space for the initial as possible ( this requires
precreating the table with different INITIAL parameter).
A quick study of dba_free_extent can tell you which extents
are adjacent and there by you can work out a size for your
initial much larger than the biggest listed in dba_free_space.
In situations where large loads take place on a regular basis,
the dba should treat pctincrease with a lot of care and reduce it
to zero if the load can be mainly fitted in the initial extent.
The dba can actually make unusable extents available to such a
load by manually combinig adjacent extents. This is done by
creating tables whose initial extent is the summation of existing
free extents in the free list taking the following point into
consideration:
o Database may not use the same extents as you had thought
if a single extent exists which is the same size or larger.
Dropping this newly created table, returns a single extent
whose size is the sum of the original extents. Repeating this
process several times can give you a few large extents which
did not exist before. It must be remembered that database
performs this automatically but since it always prefers single
extents to multiple adjacent ones, it can create fragmentation
that you can avoid! This has three advantages.
1. Some extents which were smaller than the NEXT value can now
be utilised.
2. Fewer blocks are thrown away as a result of the database
discarding the remainder as it fetches an extent which is
larger than a multiple of the NEXT value.
3. This method does not require dropping the tablespace.
The above technique should only be used if the dba has no option
but to load the table into the space it used to sit before and
where storage is limited to the existing file sizes.
Other points to consider are:
1. Indexes on large tables should have their own tablespace
and need as much attention as the tables themselves. Use
index_stat.sql script if your version implements it to create
efficient indexes on existing tables with data.
2. Temporary space should never be defaulted to a tablespace
with large objects.
3. Large objects can be considered as the big animals in the
jungle, they do not like each other and they do not get
along with the smaller ones! Create as many tablespaces as
is needed to separate out database objects, leaving system
tablespace for system operations. Such as the data dictionary
and certain installations.