Disk Capacity Planning

The amount of required disk space that would be required could be predicted based on certain formulas and calculations. Usually the database server administration manual of the variety of database products would contain the required information. These calculations are based on the average sizes of individual fields/columns in each and every table in the database. Again, for variable length data types like VARCHAR2 in Oracle, the average sizes could result in wide variation in estimates. Therefore, a much faster and easier method that is usually followed is: Data Sampling and Extrapolation

The tasks that are involved are:

· Sample around a few 1000 rows from the source data. Estimate the row length for a record by taking into account that variable length fields (like VARCHAR2 of oracle) and null fields are much smaller than their maximum sizes.

· Estimate the number of rows for each table - for full load and the subsequent increments.

· Using the calculated record length and the number of rows, the estimated disk space required per table can be arrived at. The same logic may be applied to all the tables and hence the full database.

· Estimate indexes to consume as much space as the base data.

· Temporary/sort space may be estimated to be at least as large as the largest size table in the database. Again this depends on the nature of sort and group operations the users would be performing or that would be needed to build aggregations.

· Reserve around 20MB space for metadata.

· Aggregate tables can consume a lot of space depending on the degree of aggregation in the database, the sparsity of data and the depth of hierarchies of data. On an average, the aggregates and their indexes take as much space as the base level fact tables.

· Add an extra 10% of the calculated disk space as allowance to accommodate any unforeseen requirements.

· Overall, as a general thumb rule, the entire datawarehouse typically takes up to 3 or 4 times as much space as the data in the atomic star schema.

Note:- The above estimates are for the data only and does not include the product specific requirements like - system tables, software binaries etc.

The following example demonstrates an approach to total disk space calculation. The RDBMS is oracle 8.x. It uses a query that determines the average rows per block which in-turn would help calculate the total blocks required and hence the disk space.

Oracle database example:

Data Sizing-

Using the test data, a sample database table is created using reasonable storage parameters - PCTFREE (usually set as 0), PCTUSED (depending on whether deletion of rows would take place or not) and INITTRANS, MAXTRANS (usually not changed). Once the data is loaded, the following query may be run to arrive at the total number of rows per data block.

SELECT AVG (COUNT(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID))) avg_rows_per_blk
FROM
WHERE ROWNUM <=
GROUP BY DBMS_ROWID.ROWID_OBJECT(ROWID),
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

The should be a value that is at least a couple of hundred or thousand rows less than the actual number of rows in the table. This is done in order to avoid partially filled blocks that usually reside at the end of the table. The output of the query gives the average rows per block. If the total rows is known, this value can be used to extrapolate and arrive at the final table size. i.e if the output is 48.3, then it means each block would hold 48.3 rows. So to store 100 million rows, it would take approximately 2 million database blocks. If the database block size is 8K, then the space required is around 15.8 GB.

Index sizing

Similar method can be used to estimate index space requirements. But if parallel index creation is adopted, it would take approximately twice the amount of temporary scratch space than it would take without parallelism. Similarly, allowances need to be made to accommodate reorganizing of indexes. Usually, the unused space needed to accommodate index re-organizing is at least as much as the biggest index size in the index tablespace.




Space for Internal use-

SYSTEM tablespace- Apart from the above, the sizing requirements for the oracle's internal data dictionary needs - namely SYSTEM tablespace, to be considered as well. This size mainly depends on the number of objects in the database and also the usage of stored procedure and other tools in the system. This size may go up to 500 to 1000 MB in some cases.

Tools tablespace- This contains the objects belonging to the system user account. The size of this tablespace depends on the different tools like Developer 2000, Advanced Replication that is installed.

Rollback Segment tablespace- The general sizing guidelines for Rollback segment tablespace is -

· There should be at least 20 extents per rollback segment. For busy transaction systems, this should be 40 extents/rollback segment. (i.e MINEXTENTS should be set between 20 and 40).

· The OPTIMAL setting for each Rollback Segment should be set to the size implied by MINEXTENTS.

· Since most of the data warehouse applications are typically huge batch oriented transactions that are fewer in number compared to an online system, larger extents may be created for rollback segments say 1MB in size.

· To accommodate very huge transaction requirements, consider having very large rollback segments that can be explicitly used using the SET TRANSACTION USE ROLLBACK SEGMENT command.

· Plan for one rollback segment for every four concurrently active DML.

Temporary tablespace- This is the temporary scratch pad area that may be used during sorting operations. The size depends on the type of joins (sort merge joins) , the degree of parallelism etc. Typically, to start with, 10-25% of the total database size may be allocated for TEMP tablespace. But may prove inadequate

Other tablespaces- Individual user accounts and the objects they own may be created in separate tablespace called USERS. The size is site specific. Similarly, for a database with auditing enabled, might need a separate tablespace to maintain audit trail (SYS.AUD$) data separately.

Redo Log file space requirements

· Ideally the redo log file switches should be set to occur every 30 minutes.
· The sizes may vary between 50 to 100MB in a typical data warehouse environment.

· The redo log files mostly reside on file systems. The max size for redo log files may be limited by the OS limits on file sizes (2 GB) on some of the Operating System ports.

· Consider enabling ARCH process, when database runs in ARCHIVELOG mode. This would free up the overhead on LGWR process to write archive log files to disk. Ideally, the ARCH process should be able to finish writing the archived redo log file in less than one third of the time (10 mins.) taken by LGWR to fill the online redo log file. Oracle recommends at least 3 redo log groups in a system.