Introduction and general tuning guidelines

You have just arrived in your organization and now have the responsibility of maintaining databases, or you have been there for a while and probably put a lot of effort into your database design. Databases still need regular attention and tuning.

We need to have a methodical approach, so a design-centred approach probably works best. You can start from the operating system as seen below, to get a global perspective. You can then move on to tuning the database, tablespaces, and specific items such as specific tables or indexes.

Start from the operating system. Examine to see if there is paging activity, and look at the network bandwidth to see if it's sufficient for the intended traffic. With SAN-based servers, storage is now transparent for DBAs. Stay informed about the physical disk layout and capabilities. Change one thing at a time, to make sure it is effective.

Here are some general guidelines to implement performance data collection:

• Collect operational...