Examples

The examples below are illustrative of the workflows iWatch supports. Exact column names depend on which database the trace was captured against; the substance is the same in every case.

Find the slowest statements in the last hour

SELECT  sql_template,
        COUNT(*)                   AS executions,
        AVG(elapsed_ms)            AS avg_ms,
        MAX(elapsed_ms)            AS worst_ms,
        SUM(elapsed_ms) / 1000.0   AS db_time_seconds
FROM    iwatch.statements
WHERE   captured_at >= CURRENT - INTERVAL(1) HOUR TO HOUR
GROUP BY sql_template
ORDER BY db_time_seconds DESC
FETCH FIRST 20 ROWS ONLY;

Sort by total elapsed time, not average. The slowest single statement in the system is almost never the one that needs work; the statement that is collectively eating the most database time across its executions is.

Track a regression across a release

SELECT  release_tag,
        AVG(elapsed_ms)                                AS avg_ms,
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY
            elapsed_ms)                                AS p99_ms
FROM    iwatch.statements
WHERE   sql_template = :template_of_interest
  AND   captured_at >= :first_release_cut
GROUP BY release_tag
ORDER BY release_tag;

The same statement, the same plan, but suddenly 4× slower after a release? iWatch’s timeline makes the regression obvious before the first user complaint reaches the helpdesk.

Reproduce a user complaint

A user reports “the order screen was slow at about 10:42 this morning.” In iWatch, jump to that timestamp for that user’s session and read off every statement the screen issued, in order, with per-statement wall-clock time. The bad actor is usually obvious; if it isn’t, the round-trip pattern is.

Compare two databases under the same load

For estates that span more than one database engine — an Informix OLTP system feeding a SQL Server reporting layer, say — iWatch gives a single grammar for response time on both. The “is it our database or theirs?” conversation between teams collapses, because both teams are reading the same kind of evidence.

Catch a bind-value plan-flip

A frequent root cause of intermittent slowness is a statement whose optimiser plan depends on the bind values it sees on first prepare. iWatch records both the SQL template and the bind values, so the correlation between a particular bind shape and a slow plan becomes a filter on a query against the repository — not an afternoon of guesswork.

Alert on a behavioural change

Because the repository is queryable in SQL, alerts are just queries. Suggested starting points: a statement whose P99 latency tripled in the last 15 minutes; an application whose error rate climbed above its 7-day baseline; a connection pool whose average commit time crossed a hand-set threshold.