Spaced Out with
Autoextend
By
Dave Moore
Copyright ©
Pinnacle Publishing, a division of
Lawrence Ragan Communications Inc.,
2004.
Originally published in the February
2004 issue of Oracle Professional.
All rights reserved.
Version 7 of the
Oracle database supported the option for
tablespaces
to automatically grow past the size they
were given initially. Oracle could then
utilize free space available on devices
and, as a result,
applications would not fail
because Oracle could allocate another
extent to a segment. The
autoextend
feature (specified for
datafiles
and listed in the DBA_DATA_FILES view)
tells Oracle to automatically
grow to a
certain limit or even UNLIMITED in which
case Oracle can use all available space
on the device. This article explains
the ramifications of using
autoextend
and focuses on the challenges it
creates. Specifically, this article
demonstrates when Oracle is accurate
with its space estimates and when it is
not – missing on both sides of the
correct number. After reading this
article, DBAs
will know not to depend solely on the V$
or DBA_FREE_SPACE views in order to
determine available free space. In
fact, SQL commands alone will not
provide accurate results when dealing
with tablespaces
containing
datafiles that can
autoextend.
Quick Review
Before we explore
the anomolies
in detail, let’s review some basics.
The table below contains five
datafiles
for one tablespace.
Take a moment to determine how many MB
this tablespace
can grow.
Tablespace
Test : All
sizes are in MB
|
File Name |
Size |
Autoextend |
Maxsize |
OS Space Available |
|
/device1/df_01 |
100 |
On |
1000 |
800 |
|
/device1/df_05 |
50 |
On |
1000 |
800 |
|
/device2/df_02 |
200 |
Off |
- |
800 |
|
/device3/df_03 |
300 |
On |
UNLIMITED |
2000 |
|
/device4/df_04 |
400 |
On |
1000 |
900 |
Let’s walk through
the exercise:
df_01: This
datafile has
a size of 100 MB. The
autoextend
option is on with a
maxsize of 1000 MB. This file
could get as big as 1000 MB; however
only 800 MB exists in the OS.
Therefore, it can only grow an
additional 800 MB.
df_05: This
datafile has
a size of 50 MB. The
autoextend
option is on with a
maxsize of 1000. Normally, this
file could extend as much as 800 MB (OS
space available) but this file is on the
same device (device1) as df_01. The
space on device1 was previously counted
for df_01 and it would be an error to
count it again. Although these two
files could share the 800 MB between
them, they can only use 800 MB total.
Oracle has no problem with creating
multiple datafiles
for the same
tablespace on the same device
with autoextend
unlimited. Therefore, df_05 can grow 0
MB.
df_02: This
datafile has
a size of 200
MB with autoextend
off. Therefore
maxsize and OS space are not
applicable to this file. Answer = 0 MB.
df_03: This
datafile has
a size of 300 MB. The
autoextend
option is on with a
maxsize of UNLIMITED. Since the
file can grow as large as the file
system will support, in this case it can
grow as much as 2000 MB.
df_04: This
datafile has
a size of 400 MB. The
autoextend
option is on with a
maxsize of 1000 MB. However,
only 900 MB exist in the OS on this file
system. df_04
can grow to 1000 MB for a difference of
600 MB (maxsize
– allocated). But wait … is there
enough OS space available to support
that? Yes, we can use as much as 900 MB
from the OS so we are fine. Answer =
600 MB.
800 + 0 + 0 + 2000 + 600 = 3400
This brings the
amount of space that this
tablespace
can grow to 3400 MB. This exercise
proves that a
datafile’s ability to
autoextend
depends on the amount of available disk
space on the device.
When calculating
the available free space, things are
fairly straightforward until
autoextend
is utilized on a
datafile. And, as a result of
utilizing this feature, many views we
rely on so heavily become much less
useful – mainly DBA_FREE_SPACE.
Inadequate Measurements
DBAs typically
begin with a query like the one
below. This appears to be useful
information, but any reliance on this
data alone will result in problems.
SQL> select
tablespace_name,
sum(bytes)
"Free Space"
from
dba_free_space
group
by
tablespace_name
order
by sum(bytes)
asc;
TABLESPACE_NAME Free
Space
------------------------------
----------
TS_TEMP
5240832
TS_DEFAULT
9459712
SYSTEM
14102528
TS_RBS
17315840
DEMO_SQL_1
20969472
QA_TABLESPACE_ONLINE
62322688
TEMP1
104855552
My first issue with
the DBA_FREE_SPACE view is that it
doesn’t show
tablespaces with 0 bytes of free
space. A slight change to the query
above could join to DBA_TABLESPACES for
the ones that are missing from the view.
I suppose since there is technically no
free space, full
tablespaces do not deserve to be
listed in the free space view. (Is no
response a response? Is a balance of 0
still a balance?) If the
tablespace
has 1 byte free, it will be listed.
Otherwise it is omitted. This makes it
slightly more challenging to find those
tablespaces
that are indeed already full.
Scenario 1: More Space Available
Than Indicated by Oracle
In certain
situations, Oracle tells us we are
running low on space when in fact we
have plenty. In the example below, a
tablespace
is created with one
datafile of 5 MB that can
automatically extend to use as much
space as the file system can provide
(UNLIMITED).
SQL> create
tablespace
test_tsp
datafile
'c:\oracle9i\datafiles\mooracle\test1.dbf'
size
5M
autoextend
on
maxsize
unlimited;
Tablespace
created.
SQL> select sum(bytes)
from
dba_free_space
where
tablespace_name
= 'TEST_TSP';
SUM(BYTES)
----------
5177344
According to the
query result above, Oracle claims the
tablespace
has 5MB of free space available. This
is particularly annoying when my
monitoring solution notifies me at 3:00
a.m. that the
tablespace is low on space only
for me to discover that this is not
really a problem. I quickly determine
that my datafile
will continue to expand on the file
system and the system has plenty of
available space.
False alarm.
The following
command shows how much space is
available on the file
system, in
this case my Windows XP machine:
C:\oracle9i\datafiles\MOORACLE>dir
*.
Volume in drive C has no label.
Volume Serial Number is 304E-C1B1
Directory of C:\oracle9i\datafiles\MOORACLE
11/29/2003
07:38
PM <DIR> .
11/29/2003
07:38
PM <DIR> ..
0 File(s)
0 bytes
2 Dir(s)
61,694,697,472
bytes free
This proves that I
have more than 61 GB available before my
space expires ... but DBA_FREE_SPACE
told me I had only 5MB! Yes,
unfortunately, the DBA_FREE_SPACE view
does not compensate for
datafiles
that can autoextend.
Checking the
available disk space on
Unix is
slightly more involved. First, you must
know which device a
datafile resides on. The
df
command is used in conjunction with the
file name. The ‘man’ page for
df states
“The df
command displays the amount of disk
space occupied by a mounted or
unmounted
file systems, the amount of used and
available space, and how much of the
file system’s total capacity has been
used.”
$
df
/usr/oracle/databases/asg920wa/datafiles/tools2.dbf
| awk
'{print $1}'
/data1
Next, the device
name is used to determine the available
disk information using the –k option of
the
df
command:
$ df -k
/data1 | awk
'(NR!=1){(OFS=",");if(NF==6)print
$4;else
if(NF==5)print $3}'
2204644
The output from the
command shows the amount of space on
this device is 2204644 K. (Note that
the
df
command is platform specific and the
unit of measure (K, M, bytes) may vary
depending on your operating system).
The initial query
is modified below to only include
permanent
tablespaces and also indicate
which tablespaces
have datafiles
that can autoextend.
This is a signal to the DBA that more
research is required for an accurate
space assessment.
SQL> col
auto HEADING 'Autoextend?'
FORMAT a11;
SQL> select
a.tablespace_name,
sum(a.bytes)
"Free Space",
max(b.autoextensible)
"auto"
from
dba_free_space
a,
dba_data_files b,
dba_tablespaces
c
where
c.contents
= 'PERMANENT'
and
c.tablespace_name =
a.tablespace_name
and
a.tablespace_name =
b.tablespace_name
group
by
a.tablespace_name
order
by sum(a.bytes)
asc;
TABLESPACE_NAME Free
Space
Autoextend?
------------------------------
---------- -----------
TS_TEMP
5240832 NO
TS_DEFAULT
9459712 NO
SYSTEM
14102528 NO
DEMO_SQL_1
20969472 NO
QA_TABLESPACE_ONLINE
62322688 NO
TS_RBS
69263360 YES
TEMP1
104855552 NO
Scenario 2: Less Space Available Than
Indicated by Oracle
In other
situations, Oracle may indicate that we
have enough space when in fact we do
not. The first scenario in which this
occurs is when the value of
maxsize is
used as the actual maximum size. The
maxsize
value is easily obtained with SQL and
scripts may use this value to determine
how much the file can grow. However,
there is no check within the Oracle
kernel to verify that the amount of
space specified by
maxsize is actually
available. In other words, a
datafile
could reside on a device which has 100
MB of available disk space. Given a
specified maxsize
of 500 MB, that number would be
incorrect since the OS only has 100 MB
available. Any scripts that use
maxsize as
the true upper limit are potentially
problematic.
The second scenario
in which a
tablespace may falsely appear to
have enough space is when the NEXT size
for a segment within the
tablespace
is larger than the amount of free space
available. In this situation, a
datafile may
have enough space for another 20 MB, yet
one segment in this file has a NEXT
extent size of 40 MB. This
tablespace
is as good as full as far as this
segment is concerned. Any monitoring
scripts that use a low threshold, say 10
MB in this case, would never have
triggered an alert before this error
occurred. This problem is resolved by
executing the query below. Any negative
number in the DELTA column would
indicate that no segment in this
tablespace
would be able to allocate their next
extent.
SQL> select
a.tablespace_name,
a.largest_extent,
b.smallest_next_extent,
a.largest_extent-b.smallest_next_extent
delta
from
(select
tablespace_name, max(bytes)
largest_extent
from
dba_free_space
group by
tablespace_name) a,
(select
tablespace_name,
min (next_extent)
smallest_next_extent
from
dba_segments
where
segment_type not in ('CACHE',
'TEMPORARY')
group by
tablespace_name ) b
where
a.tablespace_name(+) =
b.tablespace_name;
TABLESPACE_NAME
LARGEST_EXTENT SMALLEST_NEXT_EXTENT
DELTA
------------------------------
-------------- --------------------
----------
QA_TABLESPACE_ONLINE
54517760 2048
54515712
SYSTEM
13641728
10240 13631488
TS_DEFAULT
9459712 10240
9449472
DAVE_TEST
10240 20480
-10240
TS_RBS
5230592 10240
5220352
Slight
modifications to the query could be made
to show the largest next extent of a
segment in the
tablespace. This would be an
indication that certain segments
contained in the
tablespace will not be able to
allocate their next extent – also a good
thing to know.
But once again this
falls prey to
autoextend. The
tablespace
may not have enough space right now, but
it will autoextend
as it needs to create another extent.
The example above shows that
tablespace
DAVE_TEST will be unable to allocate an
extent for the smallest segment in the
tablespace.
However, this
tablespace (and
datafile)
can autoextend
and therefore it will be fine if there
is enough free space in the operating
system.
Summary
This article barely touched the surface of
space management issues and
autoextend.
Space Management is a complicated issue that
deserves a much lengthier discussion. Many
scenarios were not discussed including the
differences when using Locally Managed
Tablespaces (LMTs).
When evaluating Oracle monitoring software,
confirm that the vendor monitors data file
usage at the OS level and does not rely
solely on the DBA_FREE_SPACE view. SQL
commands alone will not provide accurate
space reports when
autoextend is used. In fact, a
combination of operating system and SQL
commands together are required to obtain
accurate space usage information.
