Managing DSS Data, Part 2
In my last column I discussed some of the issues related to managing a decision support database after it has been deployed in production mode. In that column I noted that you will need to consider purchasing a variety of management tools. Most vendors provide you with tools to collect simple statistics, such as table usage, disk I/O and number of queries. But often you need to dive deeper into how queries are impacting the database, which users are submitting queries that consume the most resources and what kinds of modifications should be made to the database to improve performance. Often it's more effective to modify queries being submitted to the data warehouse. Some of the tools I'll discuss in this column give you the ability to analyze queries and identify those that would be good candidates for optimization. Note, I'm not endorsing any of these products per se. My intention here is to identify tools worthy of your consideration. You still need to decide whether the tool meets your requirements or not.
Given Oracle Corp.'s market share, it's not surprising that there are several tools on the market that support only that vendor's databases.
One example is MetaEdge’s (www.metaedge.com) DBA2Go, a tool designed to simplify the administration and maintenance of Oracle databases. It includes a Java-based management console, which enables remote administration. Functions include the ability to analyze and modify database objects, accelerate the tedious process of mapping data for data loading, collecting and analyzing database statistics and monitoring space use. DBA2Go is available for Oracle databases on Sun Solaris, IBM AIX, HP-UX and Windows NT platforms.
Pine Cone Systems Inc. (www.pine-cone.com) offers four tools for data warehouse management. Usage Tracker captures utilization statistics at the table and column level. The information it collects includes response time and the number of rows returned on a per-query basis. It should be noted that the response time collected at the database is not always the same as the response time perceived by the end user. Cost Tracker uses Usage Tracker statistics to generate cost allocation reports. Activity Tracker is a real-time collection and monitoring tool that can examine the state of the live data warehouse, rather than analyzing stats collected in the past. Refreshment Tracker is designed to help the DBA keep track of the update status of the data warehouse. Most data warehouses must be periodically updated with data from transactional systems. This tool tracks which tables have been refreshed, where the data originated and how many records were inserted, and it verifies that the refreshment processes terminated normally. Pine Cone supports Oracle, Informix, Sybase, NCR/Teradata and IBM DB2 on most Unix platforms and NT.
BEZ Systems Inc. (www.bez.com) provides tools for capacity planning and performance measurement. BEZplus contains several components designed to work together to help collect and analyze performance and capacity statistics. It then delivers recommendations for database modifications. One component, Investigator, collects runtime statistics and helps establish a baseline model. The baseline model can be used to compare the current state of the system with its state after changes have been applied. This allows you to document and evaluate the effectiveness of the changes. Another component, Strategist, allows you to evaluate the attractiveness of various hardware, software and scheduling alternatives. It provides performance predictions that make it easier for the database administrator to perform an upgrade. SerView DBA is a tool that the database administrator can use to examine of SQL statements submitted to the database and identify those tables that are being used heavily by various users. The product can examine usage by user, by table or by column within a table. This information helps identify those tables that should be prime candidates for tuning, such as revising indexes or reorganizing the table. Finally, CorpView DBA is designed to build a data mart with system utilization statistics that can be analyzed and mined by the database administrator. It also includes chargeback reports. BEZ tools are available for Oracle, NCR/Teradata, IBM DB2/UDB and Microsoft SQL Server.
In future columns, I'll delve into the database management and capacity planning capabilities that big vendors, such as IBM and Microsoft, deliver. --Robert Craig is vice president of marketing at WebXi Inc. (Burlington, Mass.), and a former director at the Hurwitz Group Inc. Contact him at rcraig@webxi.com.