Database Tuning

Tuning goals:

To make applications run faster,To lower the response time of queries/transactions,To improve the overall throughput of transactions,But why do we even need it? Do databases suddenly start running slowly?

DBMS statistics Storage statistics:

Data about allocation of storage into table-spaces, index-spaces, and buffer pools

I/O and device performance statistics:

Total read/write activity (paging) on disk extents and disk hot spots.

Query/transaction processing statistics:

Execution times of queries and transactions, optimization times during query optimization.

Locking/logging related statistics:

Rates of issuing different types of locks, transaction throughput rates, and log records activity

Index statistics:

Number of levels in an index, number of non-contiguous leaf pages, etc.

Tuning the Database Design

The entire database design has to be driven by the processing requirements as much as by data requirements.

If the processing requirements are dynamically changing, the design needs to respond by making changes to the conceptual schema if necessary and to reflect those changes into the logical schema and physical schema.

Existing tables may be merged (de-normalized) because certain attributes from two or more tables are frequently needed together: This reduces the normalization level from 4NF or BCNF to 3NF or even to 2NF - but it will go faster (cost of maintaining consistency Vs cost of join etc)!

Summary

Inefficiency of a query may be corrected by some several corrective actions such as using specific indexes, avoiding certain types of constructs, or avoiding use of views.

The problems and the remedies will depend upon the dynamics of the execution environment and workings of the query optimizer within an RDBMS

Detailed recommendations are typically provided in database tuning guideline manuals for database administration by the individual RDBMS vendors.