John Brady's Weblog

Sybase has introduced a set of monitoring tables with ASE 12.5.0.3 (aka. the MDA tables). As someone new to Sybase, I found these tables to be very similar to the V$ tables of Oracle. They both essentially provide a real time view onto in memory data structures using the relational database table metaphor, so that you can use SQL itself to query what the database is up to at a given point in time.

The first point is how similar these two features are conceptually. They both contain similar tables with similar sets of data:

• Currently connected sessions - V$SESSION vs monProcess
  
• Currently executing SQL Statements - V$SQL vs monProcessSQLText
  
• Events occuring - V$SYSSTAT vs monSysWaits
  
• Per session waits for events - V$SESSION_EVENT vs monProcessWaits
  
• Currently locked objects - V$LOCK vs monLocks
  
• Memory cache of data blocks - V$BUFFER_POOL_STATISTICS vs monCachePool
  

There are of course differences between the two features, and I am not attempting to say that the Sybase one is equivalent to the Oracle one. It is just interesting to note that Oracle has had the V$ tables for many years, and Sybase has recently introduced a new feature that ends up seeming very similar in principle. Is this a case of convergent behaviour? Is a real time view onto in memory structures the ultimate way to go for performance monitoring?

Given that Sybase has recently introduced these, and that they seem similar to the V$ tables of Oracle, which Oracle has had for many years, it seems natural to try and use them to investigate what a system is doing. Here, of course, is where you come up against the differences between the two products and their approach to these features.

For instance, the Oracle V$SQL view contains all recently executed SQL statements, including a count for the number of executions and other statistics. The key point is that it still contains data on old, previously executed SQL statements. They remain accessible to V$SQL until their entry is reused for another SQL statement. However, the Sybase monProcessSQLStatement and monProcessSQLText views only contain currently executing SQL statements. Which is fine to a point, but does not let you see what anything was recently executing or how frequently.

However, using a snapshotting technique similar to that used in STATSPACK for Oracle, you can easily record the contents of these Sybase monitoring tables over an extended period of time, and then later analyse them to see what your system was up to.

Using this I have been able to investigate some slowly performing Sybase applications, and detect things like resource contention. The benefit of this approach is that you capture the data all the time, and analyse it at your leisure later. If you tried to use these monitoring tables directly, the danger is that by the time you spot a problem the situation has changed, and the data related to that problem is no longer visible in these real time tables.