IBM Data Management magazine Issue 2, 2009

l ti database’s access strategy to satisfy queries. The EXPLAIN utility reports the anticipated computing resource cost of executing the query. DBAs normally review the anticipated costs before putting queries into production. If the anticipated cost is high, they tune the statement and the physical design of the database to reduce its anticipated cost, often by adding an index.

If the anticipated cost of a query is rela-vely low—say a five-second query about blueberries—a DBA will often pay no attention to it. The problem is that EXPLAIN provides estimates for only a single execution of a given query. If your application frequently accesses smaller tables without proper indexes, the database will load them into memory each time and scan them.

Processing waste like this is prolific in at east 9 out of 10 production databases. At a large bank I worked with recently, a multi-terabyte

OLTP banking application spent 34 percent of all its CPU time running queries against a table with only 32 rows. At a large retailer, adding a missing index to a small, 2,000-row table in an 8 TB warehouse reduced the elapsed time of a critical decision support query from three hours to two minutes. At another online retailer, 97 percent of the CPU cost on a system with eight CPUs was attributable to a single query accessing a relatively small table. As we say in Texas, “I’m going to shoot you straight”—wasteful, high-cost statements like these exist in almost every database, but they are often hidden from plain view because they access smaller tables.

Databases with self-tuning memory capa-ilities will detect costly queries and attempt to keep small tables in memory, mitigating I/O costs and reducing response time somewhat. But these queries are still running at a CPU cost that is 99 percent higher than it

i should be. Self-tuning memory simply reacts to observed performance characteristics and does its best to compensate for physical design flaws.

In the current economic climate, the mmediate need for and value of database performance cost optimization and tuning cannot be overstated. Unless your organization is flush with cash and has no regard for energy and the environment, it is past time to give your databases a proper tune-up. Ignoring this call to action makes no more sense than driving your car around with underinflated tires and a trunk full of rocks. Consider: If you could cut your number of database servers in half, your license costs in half, your energy costs in half, and simultaneously double or triple performance and improve reliability, would you? And what would you be willing to invest to obtain these savings and improvements?