The Engine Of Intelligence 2

The second component of database performance is the database itself and the structures of which it is composed. The database stores the data used by applications. When the application needs to access data, it does so through the DBMS to the database of choice. If the database isn't optimally organized or stored, the data it contains will be difficult or slow to access. The performance of every application that requires this data will be negatively affected.

Over time, as data is modified and updated, the DBMS may have to move it around within the database. Such activity causes the data to become fragmented and inefficiently ordered. The longer the database remains online and the more changes made to the data, the more inefficient database access can become. To overcome disorganized and fragmented databases, DBAs can run a reorganization utility to refresh the data and make the database efficient once again. But the key to successful reorganization is to reorganize only when the database requires it; instead, some companies over-reorganize by scheduling regular database reorganization jobs, whether or not the database is fragmented. Overreorganizing wastes valuable CPU cycles.

Modern database systems and tools provide autonomic methods to discover and correct poorly organized databases. Reorganization is becoming more automated with real-time accumulation of database statistics accompanied by intelligent agents that understand the meaning of those statistics and what to do when the statistics aren't optimal. Of course, the system must be aware of your unique environment; for example, you don't want an automatic reorganization to start up in the middle of your most important user's long-running analytic query.

But reorganization is only one of many database performance tasks required. Others include data set placement, partitioning for parallel access, managing free space, and assuring optimal compression.

The third and final component of database performance focuses on the application itself. Indeed, as much as 80% of all database performance problems are caused by inefficient application code. The application code consists of two parts: the SQL code and the host language code in which the SQL is embedded.

SQL is simple to learn and easy to start using. But SQL tuning and optimization is an art that takes years to master. Every DBMS provides a method of inspecting the actual access paths that will be used to satisfy SQL requests. DBAs must be experts at understanding the different types of access paths, as well as which ones are best in which situation. Furthermore, DBAs must be able to interpret the output of the access path explanation produced by the DBMS, since it is often encoded and cryptic.

Host language code refers to the application programs written in C, Cobol, Java, Visual Basic, or the programming language du jour. It's quite possible to have finely tuned SQL embedded inside of inefficient host language code. And, of course, that would cause a performance problem.

Modern database systems and tools are adapting to solve these issues as well. SQL tuning software that translates cryptic plan table entries into English, combined with proactive advice for modifying the SQL for performance helps guide developers and end users to build efficient SQL. Also, DBMS optimizers are becoming more intelligent and efficient, sometimes completely rewriting SQL internally to improve performance with no user intervention required.

The Bottom Line

As DWs evolve to meet real-time demands, how we manage database performance must also evolve, expand, and become more agile. Your organization will need administrators who understand not only the basics of database performance, but also the implications of real-time DW on performance management. Finally, by taking advantage of the autonomic features of DBMS and systems management software, you can simplify and streamline the performance of your DW systems.

Craig S. Mullins is a director of technology planning for BMC Software. He has extensive experience in database management. He is author of two books: Database Administration: The Complete Guide to Practices and Procedures (Addison-Wesley, 2002) and DB2 Developer's Guide, 5th edition (Pearson Education, 2004). You can contact him via his Web site at www.craigsmullins.com.