Database Tuning – What is it?

Database tuning is a key activity of DBA professionals. It consists on making (or suggesting) performance oriented changes, which may include:

• rewrite SQL commands – although DBMSs have great query optimization capabilities, it is often possible to rewrite SQL statements (which present correct results) in order to obtain a new command with lower execution time than the original (see this post with several tips on how to rewrite queries to improve their performance) ;
• making changes on database’s physical design – creating an index or redistributing data in storage locations are samples of physical tuning;
• tune DBMS parameters;
• adjust server’s parameters and configuration – especially parameters related to processes and to resource (like CPU and RAM memory) allocation;
• suggest changes in (the logic of) the client application – for instance, a loop that executes a query several times may be replaced for a new query that would be executed only one time. This would reduce several execution costs (e.g. communication cost, query parsing cost, … ). Thus, the time necessary to execute the new query may be lower than the one necessary to execute the original loop;
• execute maintenance tasks – revision, manual execution or even guarantee of the automatic execution of periodic tasks for database maintenance , such updating statistics and file defragmentation;
• identify possible problems in network features and configurations – for instance, the DBA may detect network related problems in node-to-node communication in clusters of servers (like Oracle RAC).

What is the problem? Can I improve performance without adding new hardware?

Some simple changes may significantly impact on the execution time of individual queries or in the performance of a whole system.

Consider some users of a certain system report that the system suffers performance problems from time to time. But what is causing such performance issues? Is there a way to improve the system’s performance without adding new hardware?

Detect what is the real problem to be solved is a major challenge in database tuning. After the users’ report, the DBA starts to monitor the system behavior more closely. At a certain time, he/she identifies main memory swap and large amounts of I/O operations are the cause of the reported performance problem. Then, the DBA investigates what is consuming the main memory during swap operations and detects that a certain query is responsible for lots of in-memory data pages. That is the time to study the query.

The administrator gathers the query’s execution plan and studies its operations. It seems that the query does not need to be re-written. But, after some tests, the DBA decides to create an index that reduces I/O operations during the query’s execution. No more I/O bottleneck! Not only the query execution time becamed really low but the overall performance of the system was increased!

Monitoring and Database Tuning

Pro-activity is an important characteristic for professionals of several areas. In database tuning is no different. Database administrators should monitor the environment to detect bottlenecks and opportunities for adjustments and improvements.

Some of the items to be monitored are:

• CPU usage;
• I/O activity;
• RAM consumption;
• number of server and database processes;
• top queries (on different metrics such as I / O or number of executions, for example);
• latches and locks;
• waits and waits types;
• impact that maintenance tasks (such as backup) cause on the performance perceived by users;
• index fragmentation.

To monitor the database environment, DBAs can use both DBMS tools and third-party monitoring tools. In addition, many of the DBMS have diagnosing tools and packages that identify possible
database tuning opportunities and suggest actions to take.

Some examples of tuning-related tools are:

• Oracle Tuning Pack
• Spotlight Tuning Pack
• Red-Gate SQL Monitor
• SQL Power Tools
• Database Engine Tuning Advisor (DTA)

You can also use server monitor tools, like Nagios and SolarWinds.

In the next article, we discuss evaluating query execution plans and the use of query rewrite to improve performance!