CATEGORY: DW Design, Administration & Management

REVIEWER: Terry Porter, Oracle database administrator for EDS.

BACKGROUND: EDS is a recognized global leader in providing e-business and information technology services to more than 9,000 business and government clients in 55 countries around the world. Having founded the IT services industry in 1962, EDS delivers high-value management consulting, electronic business solutions, business process management and systems and technology expertise along with deep industry-practice knowledge to help clients simplify complexity and achieve superior value in the digital economy.

PLATFORMS: We are using Oracle8.1.7.1 Enterprise Edition 64-bit for our production data warehouse, currently 1.2TB in size. The hardware platform is HP-UX 11.0 64-bit running on a HP 9000 V2600 server with 16 processors and 8GB of memory. For storage, we use an EMC Symmetrix 3700-47 cabinet that presently contains 2.2TB of mirrored disk space.

PROBLEM SOLVED: Many OLAP query tools provide extensive statistics regarding report usage. However, they fall short in terms of actual database usage statistics with respect to individual tables and columns. Usage Tracker (UT) provides this information exactly. If a single report performs 200 table accesses, Usage Tracker displays this information as well as the run time for each of the 200 SQL statements. One can determine usage patterns by time of day, most-accessed tables and client program used, as well as unused columns within the database that may be suitable for deletion if no longer needed.

PRODUCT FUNCTIONALITY: In our configuration, Usage Tracker consists of a server component and two forms of client reporti! ng tools. The server component acts as an Oracle Listener: receiving SQL*Net packets, parsing the query information from them into memory and then passing the query along to Oracle for execution. Its effect on performance is imperceptible in our environment. The query information is then written to flat files and inserted into the database at a specified interval. To analyze the information, two tools are provided: the Usage Tracker Windows-based client program as well as BusinessObjects report files. The UT client program is a mini-reporting tool, allowing for basic reporting with simple report layouts. The BusinessObjects report files provide similar information, with the addition of many different charts and graphs and the ability to customize the reports with the power and flexibility BusinessObjects provides.

STRENGTHS: The version 2.0 server component has been very stable for us. The database schema that stores the UT information requires far less storage space than previous versions.

WEAKNESSES: With our tremendous volume, both the UT client and BusinessObjects reports are inefficient in their SQL access of the stored information. While refinement of the schema for reporting performance is necessary, I have worked around the issue by authoring reports with modified SQL using Oracle hints for better report run times.

SELECTION CRITERIA: Few products exist which perform the functionality Usage Tracker does. While competing products may provide additional features that UT does not, they also cost substantially more, and some of their additional features are now available directly within the database (such as Oracle8i/9i query-rewrite functionality). UT is an excellent balance of features and cost.

DELIVERABLES: Both the UT client and BusinessObjects report files produce reports for topics such as: most-accessed tables; queries running longer than n seconds; unused table columns; number of queries executed per day, per week, etc.; and number of queries executed by a particular user. With this information, we are able to report to our customer that 98.5 percent of all production queries complete within 60 seconds. In addition, although a report may take four hours to run, if each individual query within the report completes within 60 seconds we can identify opportunities for report tuning and/or new attribute definitions within the data warehouse to provide optimal performance and thus improve the customer's perception of performance.

VENDOR SUPPORT: Ambeo technical support has been very responsive to our inquiries and has provided prompt fixes to the product when issues have arisen. During a major upgrade effort in late 1999, Ambeo brought two technicians on-site to assist, and the implementation went flawlessly.

DOCUMENTATION: An extensive manual is provided for Usage Tracker in an easy-to-update ring-binder format. The documentation is clear, concise and thorough, defining the roles of each piece in the server configuration as well as all configuration parameters.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access