They permit a programmer to time queries and they provide the ability to do comparisons of execution times for a native API versus ODBC.

These tools also provide a semantic analysis of function calls to identify poor programming practices, replaying scripts captured at remote sites, testing software where a specific driver or database is not available, analyzing queries to issue preemptive warnings of long-running queries.

You can also use these tools for timing the execution of stored procedures versus executing SQL statements, identifying client applications with deficient SQL and performing regression tests to ensure backward compatibility of new software releases. In some instances I've used traces to measure network latency by running a query from a client PC against a server and then running the identical application and query using the server in a local mode.

Dr. DeeBee Tools

Blue Lagoon Software

SQL*Net Sniffer

SQL*Net Sniffer includes detailed absolute, relative or delta trace timing and basic scheduling functions. It accrues absolute timing information from the beginning of a trace or provides delta timing information for interpacket times. It also supports relative timing that permits a user to choose a reference frame so that all subsequent times are displayed relative to that reference frame.

The Sniffer user can also specify a filter such as a hexadecimal data pattern that will trigger the capture of data for a given period of time. It supports up to 20 of these scheduled traces. The Sniffer can't create network activity profiles, say, for typical processing scenarios such as month-end transactions, but it can analyze data and send alarm events upon encountering certain thresholds.

Spying on Your Applications

One project started by using the Blue Lagoon inspectors to compare DB-Library against ODBC for the same queries, but it progressed through stages that required other tool capabilities. After developing a prototype database, I ran several custom and retail ODBC applications to compare response times and try to identify bottlenecks. In several cases, adjusting time-out period s to accommodate greater network latency was still not the answer to performance problems. The spy tools confirmed that software that used direct execution ad hoc queries took longer to paint the results screens than those that used prepared (compiled) queries and that stored procedures executed even more quickly.

Stored procedures reduce network traffic, so my plan was to use them whenever possible, but even that step required more sleuthing. Stored procedures reside at the server, so a variety of client applications can execute the same procedure. Some programs produced errors while running procedures that ran successfully for other programs. By spying I found that failing applications were not making the same API calls as successful applications, were using the wrong data types for returned values or were wrong about the number of result sets returned from a procedure. On another of my projects, a spy program revealed that stored procedure errors were related to a combination of application and ODBC driver. Changing drivers and application software eliminated the errors.

Ken North is a consultant with experience developing mainframe, mini, client/server and PC database software. He is the author of Windows Multi-DBMS Programming (John Wiley &Sons, 1995). Contact him at 71301.1306@compuserve.com.