Oracle Tips by Burleson Consulting
Reliable capacity planning for Oracle
The easiest way to perform Oracle capacity planning is with
capacity planning spreadsheets.
Determining the future size of Oracle tables, the size of Oracle
indexes, and the overall tablespace size is a formidable challenge
which depends on many factors:
- Your blocksize (db_block_size) - How many rows
will fit into a data block?
- Your PCTFREE - At what point do
you move a data block off of the freelist?
- Your average row
length - What is your avg_row_len in dba_tables?
- How are you
implementing your freelists (i.e. using ASSM (bitmap freelists))?
- And many, many other internal factors that effects the total
size of tablespaces, tables and indexes.
Oracle capacity planning generally involves these decisions
- Get tablespace sizing computations.
Oracle table sizing rules and index sizing spreadsheets.
a framework for Oracle capacity planning.
- Get SGA sizing
Most professionals create spreadsheets for accurate Oracle capacity
planning but they are quite complex and time-consuming. Again,
we recommend downloading our Oracle
capacity planning spreadsheets.
Oracle Data file Capacity planning
Datafiles should be sized based on the size of
the objects that will store segments in their related tablespaces. To
this end, before tablespace and datafile sizing can be accomplished
the size of tables and indexes contained in them must be determined.
The Oracle DBA manual contains sizing procedures allowing a DBA to
manually calculate sizing for both tables and indexes, however it is
suggested that spread sheets be obtained or
created to automate and standardize the calculations.
The table sizing spreadsheet sonly requires the input of the table column sizes in the appropriate
column (character, date and number) and the input of table specific
items that deal with size such as initial transactions, percent free
in a block, and number of rows. Additionally block size is specified.
If the table is clustered then that too is taken into account to give
a size estimate.
Using the index sizing spreadsheet shown above
the only required inputs are the size of the columns to be included in
the index, initial transactions, percent free, blocksize and number of
rows expected. If the index is a non-unique index, you can also enter
the expected percent of time the index column(s) will contain data.
Using standardized spreadsheets ensures that
the calculation is performed identically and with repeatable results,
it also allows for tweaking of the calculation until after the third
or fourth time it is used it should be giving accurate estimates based
on your system.
Once all of the tables and indexes are sized
for the first year?s data (or whatever time interval you desire) add
the total sizes together for each object in each tablespace. These
totals give the amount of space required for current data up to and
including estimates for this year?s growth, usually you double it to
allow for an additional year. These adjusted totals become the sizes
for the tablespaces. If the estimated sizes are too large for either
operating system file size limits or available space, multiple
datafiles will be required for that tablespace.
Also, by figuring out the total physical size
for the database you can estimate the SGA base parameters such as
DB_BLOCK_BUFFERS by use of a spread sheet.
Temporary tablespaces are used to hold user?s
sort segments when a sort operation exceeds the size of the memory
allocated by the SORT_AREA_SIZE initialization parameter. Sorts to
disk are hundreds of times slower than RAM sorts and so
should be avoided if possible. Always assign a user a temporary
tablespace, if one is not assigned the temporary tablespace defaults
to the SYSTEM tablespace.
The sizing of the temporary tablespace is an
empirical calculation involving assumed transaction sizes that involve
sorts. Usually I set temporary tablespace storage at one megabyte
initial and next and a percent increase of zero. I also set the
initialization parameter SORT_AREA_SIZE to one megabyte and
SORT_AREA_RETAINED_SIZE to one hundred kilobytes. Usually these values
are enough, but if you have a sort intensive application you may have
to increase the size of the sort area to reduce disk sorts. Any change
in sort area size should also mean a change in the temporary
tablespace initial and next extent sizes. The new views V$SORT_SEGMENT
and V$SORT_USAGE should help in determining the proper settings for
sort area size and the temporary tablespaces.
I suggest having multiple temporary
tablespaces each serving a subset of users if your environment does
heavy sorting, these multiple temporary tablespaces should be as
physically separate as possible.
Capacity planning for UNDO Segments
UNDO segments are used to store
information on changed rows until those rows are rolled back or
committed. If a rollback occurs, either from manual initiation using a
ROLLBACK command or due to abnormal termination of a user process due
to errors or failures, the data in the UNDO segment is used to
restore the database to the state before the transaction began.
The care and feeding or UNDO segments is
another empirical process. A few general observations:
1. If your users are doing heavy DML
operations make sure there are enough properly sized rollback segment
extents to allow each user performing DML to have an UNDO segment
extent without waiting.
2. If you expect heavy DML loads, have
multiple UNDO tablespaces spread across multiple disk
3. Set the default sizing in a UNDO tablespace such that you can simply issue a CREATE ROLLBACK
SEGMENT command specifying the tablespace and you don?t have to worry
about the STORAGE specification.
4. Try to limit the number of simultaneous
users per UNDO to four. Size rollback segments such that:
a. INITIAL=NEXT=size of average transaction
b. OPTIMAL=size of average large transaction
(rounded up to the next NEXT value)
c. MINIMUM EXTENTS = OPTIMAL/INITIAL.
5. For large batch transactions create special
large extent rollback segments in a separate tablespace from the other
rollback segments, only bring them online when needed and use SET
TRANSACTION command to assign them to specific transactions.
6. Try to avoid running large batch
transactions simultaneously with OLTP or smaller transactions.
Generally you won?t know the size of the
average transaction, average large transaction or number of
simultaneous DML users until the application has been running for a
while. Use the DBA_ROLLBACK_SEGS, V$ROLLSTAT and V$ROLLNAME views to
calculate the average values.
UNDO segments are assigned in a
round-robin methodology. Each user gets assigned one extent to begin
with which is used in a circular fashion if possible. When a user
overwrites his own data it is called a WRAP. If the users data needs
exceed the size of one UNDO extent then a second is
assigned, this is called an EXTEND. When a user forces a rollback
segment to extend beyond the OPTIMAL setting for that rollback
segment, the next user to use the rollback segment must wait for the
rollback segment to go back to the OPTIMAL setting, this is called a
When a WRAP, EXTEND or SHRINK occurs this
results in an UNDO WAIT (either block or header) condition. A WRAP
causes a very minor wait, an EXTEND into a newly assigned extent a
slightly more expensive wait and the SHRINK the most expensive wait.
By properly sizing rollback segment extents virtually all waits will
be of the lower expense WRAP and EXTEND type and not the SHRINK type.
Ideally all UNDO WAIT conditions should be caused by WRAP situations.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s
only $19.95 when you buy it directly from the publisher
Also see my collected notes on Oracle data file
and tablespace management: