Because monitoring tables contain stateful data, take care when joining or using aggregates in your queries because data may not be available if the plan requires that the table be queried multiple times. For example:
select s.SPID, s.CpuTime, s.LineNumber, t.SQLText from monProcessStatement s, monProcessSQLText t where s.SPID=t.SPID and s.CpuTime = (select max(CpuTime) from monProcessStatement)
Here, the monProcessStatement table is queried twice; first to find the maximum CpuTime, and then to match the maximum. When Adaptive Server performs the second query, there are three potential outcomes returned from monProcessStatement:
The statement performes more work, consuming more CPU, and having a CpuTime value greater than the previous maximum. This returns no results.
The statement finishes executing. This yields no results unless another statement used exactly the amount of CPU as the previously obtained maximum.
The statement does not use any additional CPU, and its value of CpuTime still matches the maximum. This is the only scenario that will produce the expected results.
When you are designing queries, keep in mind that, because the data contained in them is transient, joins and aggregates may not return the expected results if the plan requires that the table is queried multiple times.
