Oracle Capacity planning: sizing the database Oracle Tips by Burleson Consulting

Reliable capacity planning for Oracle

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.

- See Oracle table sizing rules and index sizing spreadsheets.

- Get a framework for Oracle capacity planning.

- Get SGA sizing calculations.

Most professionals create spreadsheets for accurate Oracle capacity planning but they are quite complex and time-consuming. 

See these Oracle capacity planning spreadsheets.

Datafile Sizing

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 similar to figures 3 and 4 be obtained or created to automate and standardize the calculations.

The table sizing spreadsheet shown in figure 3 only 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 on the average 14,000 times slower than memory 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.

Rollback Segments

Rollback 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 rollback segment is used to restore the database to the state before the transaction began.

The care and feeding or rollback 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 a rollback segment extent without waiting.

2. If you expect heavy DML loads, have multiple rollback segment tablespaces spread across multiple disk arrays.

3. Set the default sizing in a rollback segment 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 rollback segment 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.

Rollback 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 rollback segment 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 SHRINK.

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 here.