Subject: Space - The Final Frontier
Last Revision Date: 11 September 1994
Author:Kathy Jou and Vinnie Wetzler
Abstract:
The challenging role of database administrating can be made easy.
Proper planning and consideration taken when setting up the database can
decrease maintenance cost and prolong the life of the database. This paper
presents guidelines for the Oracle database administrator in order to plan
efficient use the system's resources and prevent unnecessary maintenance
down the road.
INTRODUCTION
Planning is essential in setting up the database. Careful consideration should
be given to sizing the database and anticipating growth of tables and data.
Sizing information is essential for implementing a production (or
test/development) database that will be around for a long time. The space
requirements for database objects should be carefully calculated and accounted
for before the database goes production.
Accurate sizing of the data dictionary, user tables, user indexes, rollback
segments, and redo logs depends on accurate estimates of:
1.) The number and size of rows stored in user tables.
2.) The transaction mix.
3.) The sizes and performance characteristics of database objects.
Considerations should be made for the larger tables that will reside in the
database as well as for tables that will grow considerably over time. When
calculating this space you should account for the fact that the RDBMS deals
more efficiently with a table or index stored in a single extent than one
stored in multiple extents.
To size the transactions, you will need to ascertain the nature of your
transactions. These can fall into three basic categories:
1.) Short update transactions affecting relatively small amounts of data.
2.) Long-running update transactions that alter significant amounts of data
before committing or rolling back.
3.) Long-running read-only transactions which only query data but require that
read-consistent snapshots of tables remain available until the last fetch
of the query is completed.
The transaction mix for different times should also be considered since it may
vary throughout the day. For example, during the daytime work hours users might
perform short update transactions while during the evening you may run longer
updates or queries. Both cases should be investigated and planned for in the
production system. This transaction mix may also change over time as the number
of users increases or decreases and the amount of data to be processed changes.
Once the row and transaction information is gathered from an analysis of the
application, object sizes for the database can be estimated.
DATA DICTIONARY / SYSTEM TABLESPACE
The data dictionary space should remain relatively constant, growing only as
the database inserts records tracking the inserted database objects. Whenever
the database is in operation, the Oracle RDBMS will update the data dictionary
in response to every DDL statement, reflecting changes in database structures,
auditing, grants, and data.
The data dictionary generally requires only a small percentage of space when
compared with application requirements. It is critical to allow enough room
for the data dictionary to grow and for other objects such as deferred rollback
segments to exist in the SYSTEM tablespace.
To ensure that space remains available for the data dictionary and other
objects which must reside in the SYSTEM tablespace, place all other tables,
indexes, temporary segments, and rollback segments in other tablespaces. In
addition, make the SYSTEM tablespace large enough so that it has at least
50% - 75% free space. Finally, insure that your users do not have resource
privileges on the SYSTEM tablespace for creating objects or for temporary
segments.
TABLES
Table size increases proportionally with the number of rows in the table
assuming average row length remains constant. While the DBA Guide provides a
formula for calculating table size, it may not be as accurate as the following
formula when calculating space for tables with a large number of records.
Below is a formula that will provide more accurate results for both small and
large tables.
Note that this procedure estimates only the initial amount of space required
for the table. Space used by transaction entries and deleted entries does not
become free immediately after the commit due to delayed cleanout. Trailing
nulls are not stored in the database.
Below are methods for calculating space for tables. These are approximations,
so it is a good idea to round up. Also making the initial extent size 10 to
20 percent larger than the value from these calculations may avoid allocating
additional extents right away. Some values may be operating system dependent.
Consult the Installation and User's Guide for your operating system for
possible discrepancies.
Knowing the type of transactions that will effect the data in the tables is
also very important. This will help you size pctfree and pctused accordingly.
For example, as the rows lengthen in a block, row chaining may result if
pctfree is not set sufficiently high. Also deletes and updates may empty out
blocks so that the table needs more blocks even though the number of rows in
the table is static. Please refer to Chapter 5 of the Oracle RDBMS Database
Administrator's Guide for details on pctfree and pctused.
First, calculate the total block header size:
block header = fixed header + variable transaction header +
table directory + row directory
where:
fixed header = 57 bytes (the 4 bytes at the end of the block have
already been taken account of in the 24 bytes
for the cache header)
variable transaction header = 23 * i where i is the value of INITRANS
for the table, or can grow
dynamically up to MAXTRANS.
table directory = 4 * n where n is the number of tables.
n = 1 for non-clustered tables.
row directory = 2 * x where x is the number of rows in the block.
The variable transaction header in the block header contains 23 byte-structures
that hold information about transactions accessing the block. By default,
space is reserved for only one transaction (INITRANS = 1) in the block header.
As more transactions access the block, space for additional transaction
entries is allocated out of the free space in the block or an old entry is
reused. As rows are inserted, the 2 bytes for each row in the row directory are
also allocated out of the free space. Once allocated, this space becomes part
of the block header. So, once the two bytes have been allocated in the row
directory, this space is not reclaimed when the row is deleted. For example, a
block that is currently empty but had up to 50 rows at one time will have 100
bytes allocated in the header for the row directory. This space will be reused
as new rows are inserted in the block.
Using the above formula, the initial block header size for a non-clustered
table with INITRANS = 1 is:
block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes
The space reserved for data within the block, as specified by PCTFREE, is
calculated as a percentage of the block size minus the block header.
available data space = (block size - total block header) -
((block size - total block header) * (PCTFREE/100))
For example, with PCTFREE = 10 and a block size of 2048, the total space for
new data in a block is:
available data space = (2048 - (84 + 2x)) - ((2048 - (84 + 2x)) * (10/100))
= (1964 - 2x) - ((2048 - 84 - 2x) * (10/100))
= (1964 - 2x) - (1964 - 2x) * 0.1
= (1964 - 2x - 196 + 0.2x) bytes
= (1768 - 1.8x) bytes
Now, calculate the combined data space required for an average row. Calculating
this depends on the following:
1) The number of columns in the table definition.
2) The datatypes used for each column.
3) The average value size for variable length columns.
A test database similar to the production database will be helpful here. To
calculate the combined data space for an average row in a table, use the
following query:
SELECT AVG(NVL(VSIZE(col1), 1)) +
AVG(NVL(VSIZE(col2), 1)) +
... +
AVG(NVL(VSIZE(coln), 1)) "SPACE OF AVERAGE ROW"
FROM table_name;
col1, col2, ... , coln are the column names of the table and table_name is the
table being evaluated.
Note: This formula assumes that columns containing nulls are not trailing
columns. A column length of 1 is assumed (column length of a null in a trailing
column is 0).
For example, a test table created with the following statement:
CREATE TABLE test (
A CHAR(10),
B DATE,
C NUMBER(10, 2));
can have the space determined by the average row in the table with the query:
SELECT AVG(NVL(VSIZE(A), 1)) +
AVG(NVL(VSIZE(B), 1)) +
AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW"
FROM test;
Alternatively, if a test database is not available, you can estimate the
combined data space for an average row in a table. This is done by examining
the datatype of each column in a table. If the column is of a fixed length
datatype, the value for the column in each row of the table is fixed. For
variable length columns, you must determine the average column value and
determine how much space is required to hold this average value.
For example, using the above table test, the column lengths of the first two
columns are of fixed lengths. So, column A requires 10 bytes (assuming a one
character set is being used) and column B requires 7 bytes. Column C can vary
for each row in the table because the NUMBER datatype is a variable length
datatype. By estimating your proposed data, you can determine the typical value
for this column. For NUMBER datatypes, a typical value may require
approximately:
average length column C = (average_precision/2 + 1) bytes
= (8/2 + 1) bytes (estimating an average_precision
of 8)
= 5 bytes
Calculating the average space required for the columns in an average row for
table test:
average row = (A + B + C)
= (10 + 7 + 5) bytes
= 22 bytes
Note: negative numbers consume one additional byte for the sign.
Using the results from above, we can calculate the total average row size (or
the minimum amount of space required by a row) in a non-clustered table with
the formula:
average row size = row header + F + V + D
(bytes per row)
where:
row header = 3 bytes per row of a non-clustered table
F = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)
V = total length bytes of all columns with 3 byte column
lengths (CHAR, LONG, RAW, LONG RAW datatypes)
D = combined data space of all columns in average row
(from above)
For example, the total average row size for table test is:
average row size = (3 + (3 * 1) + (3 * 0) + 22) bytes
= 28 bytes
Note: The minimum row size for a non-clustered table is 9 bytes. Therefore, if
the calculated value for an average row size is less than this absolute minimum
row size, use the minimum value as the average row size.
After the average row size is determined, you can calculate the average number
of rows that can fit into a database block (the values of PCTFREE and PCTUSED
will also determine the amount of space used):
average number of rows per block =
floor(available data space / average row size)
where:
available data space and average row size are calculated above.
Using the information for table test from above:
average number
of rows per block = x = (1768 - 1.8x)/28 bytes
28x = 1768 - 1.8x
29.8x = 1768
x ~ 59 = average number of rows per block
Make sure you round x or the average number of rows per block DOWN. Once you
know the number of rows that can fit inside the available space of a database
block, you can calculate the number of blocks required to hold the proposed
table:
number of blocks
for the table = number of rows / average number of rows per block
Using 10,000 rows for table test:
number of blocks
for table test = 10000 rows / 59 rows per block
~ 169 blocks
To get this value in bytes, multiply by the database block size. And again,
round the number of blocks from the above DOWN.
INDEXES
Indexes increase in size slightly faster than the corresponding table. So, you
should round up when estimating index size. Formulas are provided below for
calculating the size of an index:
First, calculate the space required by the database block header of a block to
contain index data. To calculate the total block header size:
block header size = fixed header + variable transaction header
where:
fixed header = 113 bytes
variable transaction header = 23*i where i is the value of INITRANS
for the index
Assuming the INITRANS = 2 (the default for indexes), the previous formula can
be reduced to:
block header size = 113 + (23 * 2) bytes
= 159 bytes
The space reserved in each database block for index data, as specified by
PCTFREE, is calculated as a percentage of the block size minus the block header.
available data space = (block size - block header size) -
((block size - block header size) * (PCTFREE/100))
Assuming a block size of 2048 bytes and PCTFREE of 10:
available data space = (2048 bytes - 159 bytes) -
((2048 bytes - 159 bytes) * (10/100))
= 1889 bytes - 188.9 bytes
= 1700.1 bytes
The calculation of the combined column lengths of an average index value is the
same as the calculation for table size, except you only need to calculate the
average combined column lengths of the columns in the index. This formula is
listed above.
Once the combined column length of an average index entry has been calculated,
the total average entry size can be calculated, using the formula:
bytes per entry = entry header + ROWID length + F + V + D
where:
entry header = 1 byte
ROWID length = 6 bytes
F = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)
V = total length bytes of all columns with 3 byte column
lengths (CHAR and RAW datatypes)
D = combined data space of all columns (from above)
For example, given that D is calculated to be 22 bytes (from the table
calculations above), and that the index is comprised of three CHAR columns, the
total average entry size of the index is:
bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes
= 32 bytes
To calculate the number of blocks and bytes required for the index, use:
number of blocks
for index = 1.1 * ((number of not null rows * avg. entry size) /
((floor (avail. data space / avg. entry size)) *
(avg. entry size))
Note: The additional 10% added to this result (i.e. the multiplication factor
of 1.1) accounts for the extra space required for branch blocks of the
index.
number of blocks
for index = 1.1 * ((10000 * 32 bytes) / ((floor (1700/32 bytes)) *
(32 bytes))
= 208 blocks
The number of bytes can be calculated by multiplying the result by the database
block size. This method for indexes is more approximate than for tables.
An index, unlike a table, does not contain rows. Therefore, no query similar
to the table query above exists for indexes. Also, the VSIZE function will not
report information about index storage space.
Putting indexes for large tables in their own tablespaces makes space
management more efficient. It decreases fragmentation and also makes managing
the index growth easier if recreating the index is necessary.
TEMPORARY SEGMENTS
The amount of temporary segment space needed for a sort grows proportionally
with the amount of data being sorted. Most SQL operations do not require data
to be sorted, however, the few SQL operations which do require sorting are:
CREATE INDEX
SELECT ... ORDER BY
SELECT ... DISTINCT
SELECT ... GROUP BY
SELECT ... UNION
SELECT ... INTERSECT
SELECT ... MINUS
unindexed joins
certain correlated subqueries
The init.ora SORT_AREA_SIZE parameter sets up an upper limit on the size of
a sort that can be performed in memory. Common settings of this parameter range
between 64K and 256K for larger systems. Sorts exceeding the SORT_AREA_SIZE
limit transparently acquire a temporary segment in the database.
Temporary segments have storage clauses of the tablespace that they reside in.
To ensure that the temporary segments can grow to handle large transactions,
the initial extent should be greater than SORT_AREA_SIZE and next extent and
maxextents should be large. A pctincrease (percent increase) of zero may
reduce fragmentation in the temporary tablespace. If the temporary tablespace
holds other large database objects, then defragmenting the tablespace might
involve more work.
Temporary segments are allocated as needed during a user session. The storage
characteristics for the extents of the temporary segment are determined by the
defaults of the tablespace in which the temporary segment is created. Temporary
segments are dropped when the statement completes.
A sort may require up to twice the amount of space needed to store the data
being sorted. Most statements issued by interactive transactions will not
generate sorts requiring a temporary segment, and of those that do, few will
require a large amount of temporary segment space. Reports and index creations,
however, often require large temporary segments.
Setting aside a tablespace for temporary segments may help you more accurately
measure the temporary segment requirements of different parts of your
application. To monitor the temporary segment usage, query the data dictionary
views USER_SEGMENTS, ALL_SEGMENTS, or DBA_SEGMENTS, where the value for the
column SEGMENT_TYPE equals TEMPORARY. Measurements may be inflated because
temporary segment space will not be reclaimed until needed or until the
database startup occurs.
ROLLBACK SEGMENTS
The Oracle RDBMS provides read consistency on rows fetched for operations such
as SELECT, INSERT, UPDATE, and DELETE against a table. Rollback segments are
used to store undo transactions in case the actions need to be "rolled back" or
the system needs to generate a read-consistent state from an earlier time.
As the amount of data and number of rows being modified by a statement
increases, the amount of rollback segment space required for the statement will
increase in size proportionally.
Follow the suggestions on page A-4 of the Oracle RDBMS Performance Tuning Guide
Version 6.0 (or DBA GUIDE chapters 3 and 4) in choosing the number of rollback
segments for your system.
Information is guaranteed to remain in the rollback segment for the duration of
the transaction that generated it. Once a commit is issued, the database is
free to overwrite the undo information. A long-running SQL statement may need
this undo to construct a read-consistent snapshot. The larger a rollback segment
is, the longer the undo information is available (before being overwritten).
Therefore, it is wise to allocate more space than the initial estimates may
suggest because support for long-running transactions may be needed. As a final
test, simulate a heavy transaction load on the full-sized database before going
production.
For details, see "An Internal Look at Rollback Segments" by Walter Lindsay
and Lan Nguyen, IOUW paper 537.
REDO LOG FILES
The issues in sizing redo log files are very different from issues for the
other objects discussed in this paper. The on-line redo logs should be on
devices that are not loaded with other database files. Ensure that enough redo
log space is available for the LGWR process to write out bursts of information
during heavy processing times, and for the ARCH process to catch up later if
necessary. Otherwise, database operations will be suspended until the ARCH can
make a redo log available.
Redo log files should not be extremely large. For systems in which the database
is frequently started and stopped, archiving large log files will use disk
space inefficiently in version 6.0. Closing a database causes any remaining
space in the current log to be unused, and the entire log file will be archived
after the next startup, if the archiving is enabled. In the event of a database
crash due to machine crash, instance recovery will require reading up to an
entire redo log file. Therefore, several relatively small redo log files are
preferable to a few large ones. Two to four 5 Mb redo log files are probably
sufficient for most large systems, while two 100 - 200 Kb redo log files are
reasonable for systems with a small number of users and few changes made to
the database.
MAINTENANCE
Once the database is up and running, it is always good practice to monitor the
growth of the system. If you can anticipate how much more space a particular
database object will need, then you can plan where to acquire the additional
space. This will eliminate the inconvenience to users when an ORA-1547
(failed to allocate extent of size # in tablespace #), giving the DBA a chance
to plan for additional disk space if it is currently not available on the
system.
The following are SQL, PL/SQL, and C scripts that are very useful for DBAs
monitoring space usage on the system. These are tools in addition to SQL*DBA's
various MONITOR options.
[1] EXTENT_CHECK.sql - determine all database objects that have acquired at
least a certain number of extents. The ORACLE_SID and the number of
extents to check at are passed in from the command line.
rem OUTPUT FILENAME
rem
rem extent_check_ORACLE_SID.lis
rem
rem PARAMETERS
rem
rem 1 ORACLE_SID SID of the current database
rem 2 WARN_EXTENTS Warning level for maximum extents
rem
rem USAGE
rem
rem sqlplus dba_user/password @check_extents.sql ORACLE_SID 20
rem
define ORACLE_SID=&1 /* ORACLE_SID passed in from command line */
define WARN_EXTENTS=&2 /* Number of extents passed in from command line*/
set termout off
set newpage 0
set pagesize 65
set linesize 80
set feed off
set verify off
set wrap off
ttitle -
skip 2 -
left _sysdate -
right format 9,999 'Page: ' sql.pno -
skip 2 -
center 'Check Extent for ' ORACLE_SID -
skip 1 -
center 'Extents over: ' WARN_EXTENTS -
skip 2
break on owner
column sys_date new_value _sysdate noprint
column owner format a12 heading "OWNER"
column segment_name format a25 trunc heading "SEGMENT|NAME"
column segment_type format a10 trunc heading "SEGMENT|TYPE"
column kbytes format 9,999,999 heading "KBYTES"
column extents format 9,999,999 heading "EXTENTS"
column max_extents format 99999 heading "MAX|EXTENTS"
select to_char(sysdate,'Dy DD-Mon-YY HH:MI PM') SYS_DATE,
ds.owner , ds.segment_name , ds.segment_type ,
ds.bytes/1024 kbytes , ds.extents, ds.max_extents
from sys.dba_segments ds
where ds.extents > &&WARN_EXTENTS
order by ds.extents desc , ds.owner
spool extent_check_&ORACLE_SID..lis
/
spool off
undefine ORACLE_SID
undefine WARN_EXTENTS
exit
A sample output for ORACLE_SID S6F at extents of 5 or more:
Fri 21-Jun-91 12:23 PM Page: 1
Check Extent S6F
Extents over: 5
SEGMENT SEGMENT MAXIMUM
OWNER NAME TYPE KBYTES EXTENTS EXTENTS
------------ ------------------------- ---------- ---------- ---------- -------
SYS C_OBJ# CLUSTER 652 9 99
I_COL1 INDEX 232 7 99
VIEW$ TABLE 150 6 99
[2] CONTIG_FREE_SPACE.sql - PL/SQL script to determine how much contiguous free
space is available for each tablespace in the database. Oracle will acquire
space by searching first for exact fit, then next best fit, and lastly
coalesce if possible.
create table SPACE_TEMP (
TABLESPACE_NAME CHAR(30),
CONTIGUOUS_BYTES NUMBER)
/
declare
cursor query is select *
from dba_free_space
order by tablespace_name, block_id;
this_row query%rowtype;
previous_row query%rowtype;
total number;
begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
exit when query%notfound;
if this_row.block_id = previous_row.block_id + previous_row.blocks then
total := total + previous_row.bytes;
insert into SPACE_TEMP (tablespace_name)
values (previous_row.tablespace_name);
else
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
total := this_row.bytes;
end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
end;
#
/
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' -
skip 1 center new_today skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES",
count(*) "COUNT",
CONTIGUOUS_BYTES*count(*) "TOTAL BYTES",
to_char(sysdate,'FMMonth DD, YYYY') "TODAY"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
group by TABLESPACE_NAME, CONTIGUOUS_BYTES
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc
/
spool off
drop table SPACE_TEMP
/
exit
/
A sample output:
Contiguous Extents Report
May 28, 1990
TABLESPACE NAME CONTIGUOUS BYTES COUNT TOTAL BYTES
------------------------------ ---------------- ----- ------------
TEMP 3,584,000 1 3,584,000
TEMP 1,781,760 1 1,781,760
TEMP 1,740,800 1 1,740,800
TEMP 1,536,000 1 1,536,000
TEMP 1,126,400 2 2,252,800
TEMP 716,800 1 716,800
TEMP 512,000 1 512,000
Combining the contiguous extents in the tablespace yields eight large
virtual extents, two of which are the same size (1,126,400 bytes).
If many rows are returned with small contiguous bytes and count of 1, then the
tablespace may be fragmented. Currently, the tools to remedy fragmentation
are IMP/EXP (import and export) utilities. You may also drop segments that
lie between free extents, create a dummy object that will require a segment
of just the right size to cause the separate segments to be coalesced, and
then drop the dummy object.
[3] GET_NEXT_EXT_SIZE.c - C program to calculate the size of the next extent
based on pctincrease and next extent size for the object.
/**************************************************************************
The program requires 3 arguments:
arg1 pctincrease of the object
arg2 number of the extent you wish to calculate the size of
arg3 size of the next extent storage parameter
For example:
get_next_ext_size 20 3 10000
This will return the size of extent# 4 for an object that has a
percent increase of 20% and next extent size of 10,000 bytes.
**************************************************************************/
#include <stdio.h>
main(argc,argv)
int argc;
char **argv;
{
int i, pctincrease, next_extent_size, next_extent_no;
float inc;
pctincrease = atoi(argv[1]);
next_extent_no = atoi(argv[2]);
next_extent_size = atoi(argv[3]);
for ( i = 1 ; i < next_extent_no; i++ ) {
inc = (next_extent_size*pctincrease)/100;
if((next_extent_size*pctincrease)%100)
inc++; /* round up */
next_extent_size += inc;
}
printf("The size of extent# %d will be %d blocks.\n",
next_extent_no,next_extent_size);
}
From the information retrieved in the above three scripts, you can track the
objects in each tablespace and make sure there is enough space for it to grab
at least another extent. Also, it is important to make sure the object is not
getting close to the maximum number extents as the database increases in size.
CONCLUSION
----------
A well planned and designed database lends to easy maintenance and maximum
utilization of your system's resources. Accurate space calculations of the
objects in the database allow the DBA to acquire the disk space needed and
to plan an efficient layout of the physical datafiles. After the database
has been running or goes production, it is also essential to monitor the
growth of the database since it will vary over time and level out. Using the
various resources provided by Oracle, you can determine whether there is enough
space currently for any fast growing objects in the database to extend. If
the database is badly fragmented, then you can plan a convenient time to
resolve the fragmentation. Planning the need for additional space early almost
eliminates ORA-1547 errors. This will also prevent irrational solutions to
space shortage problems (like ORA-1547) that may be a bigger headache to
resolve afterwards.
REFERENCES
----------
Oracle RDBMS Version 6.0 Database Administrator's Guide
Oracle RDBMS Performance Tuning Guide
Oracle SQL Language Reference Guide
ACKNOWLEDGEMENT
---------------
Special thanks to Mike Hartstein, Walter Lindsay, Dorion Carroll, Derek Emous,
Mohammad Fardoost, and members of the Oracle Worldwide Support RDBMS team for
their valuable contributions.