1.physical Storage of Databases

PHYSICAL STORAGE OF DATABASES & DBMS PERFOMANCE TUNING
Managing the physical storage details of the data files also plays an important role in DBMS
performance tuning. The Following are some general recommendations for physical storage of
databases:
1. Use RAID (redundant array of independent disks) to provide balance between

performance and fault tolerance. RAID systems use multiple disks to create virtual disks
(storage volumes) formed by several individual disks. RAID systems provide
performance improvement and fault tolerance. The most common raid levels include:
2. Minimize disk contention. Use multiple, independent storage volumes with independent
spindles. A spindle is a rotating disk to minimize hard disk cycles. Remember, a database
is composed of many table spaces, each with a particular function. In turn, each table
space is composed of several data files in which the data are actually stored. A database
should have at least the following table spaces:
a. System table space. This is used to store the data dictionary tables. It is the most
frequently accessed table space and should be stored in its own volume.
b. User data table space. This is used to store end-user data. You should create as many
user data table spaces and data files as are required to balance performance and usability.
For example, you can create and assign a different user data table space for each
application and/or for each distinct group of users; but this is not necessary for each user.
c. Index table space. This is used to store indexes. You can create and assign a different
index table space for each application and/or for each group of users. The index table
space data files should be stored on a storage volume that is separate from user data files
or system data files.
d. Temporary table space. This is used as a temporary storage area for merge, sort, or set
aggregate operations. You can create and assign a different temporary table space for
each application and/or for each group of users.
e. Rollback segment table space. This is used for transaction-recovery purposes.
3. Put high-usage tables in their own table spaces. By doing this, the database minimizes
conflict with other tables.
4. Assign separate data files in separate storage volumes for the indexes, system, and high-
usage tables. This ensures that index operations will not conflict with end-user data or
data dictionary table access operations. Another advantage of this approach is that you
can use different disk block sizes in different volumes. For example, the data volume can
use a 16K block size, while the index volume can use an 8K block size.
5. Take advantage of the various table storage organizations available in the database. For
example, in Oracle consider the use of index organized tables (IOT); in SQL Server
consider clustered index tables. An index organized table (or clustered index table) is a
table that stores the end-user data and the index data in consecutive locations on
permanent storage. This type of storage organization provides a performance advantage
to tables that are commonly accessed through a given index order. This is due to the fact
that the index contains the index key as well as the data rows, and, therefore, the DBMS
tends to perform fewer I/O operations.
6. Partition tables based on usage. Some RDBMSs support the horizontal partitioning of
tables based on attributes. By doing so, a single SQL request could be processed by
multiple data processors. Put the table partitions closest to where they are used the most.
7. Use denormalized tables where appropriate. Another performance-improving technique
involves taking a table from a higher normal form to a lower normal form—typically,
from third to second normal form. This technique adds data duplication, but it minimizes
join operations
8. Store computed and aggregate attributes in tables. In short, use derived attributes in your
tables. For example, you might add the invoice subtotal, the amount of tax, and the total
in the INVOICE table. Using derived attributes minimizes computations in queries and
join operations.

1.physical Storage of Databases

Uploaded by

Copyright:

Available Formats

1.physical Storage of Databases

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

1.physical Storage of Databases

Uploaded by

Copyright:

Available Formats

PHYSICAL STORAGE OF DATABASES & DBMS PERFOMANCE TUNING

1. Use RAID (redundant array of independent disks) to provide balance between

You might also like