Oninit® Informix Analyser — Query Plan Analysis

DrWatson analyses Informix query plans — the per-statement access plans the optimiser chose, captured in sqexplain.out by setting SET EXPLAIN ON ahead of the query. Upload the file through the portal and DrWatson parses every QUERY: block, walks the access-plan steps, and runs the PLAN-* rule family against the result.

Capturing sqexplain.out

Run SET EXPLAIN ON in the same session as the query under investigation. Informix writes sqexplain.out to the program's working directory on UNIX (%INFORMIXDIR%\sqexpln\username.out on Windows). The file accumulates one QUERY: block per statement, with the estimated cost, the estimated row count, and the access-plan steps the optimiser chose. Capture against a workload that matches what you care about — an isolated unit-test run will give an isolated unit-test plan, not your production plan.

What DrWatson detects

The PLAN-* rule family carries one rule per common plan-quality signal:

• PLAN-001 — Full table scan. A SEQUENTIAL SCAN step against a user table. Severity tiers off the optimiser's estimated row count: info under 1 000 rows, medium under 10 000, high above. Usually a missing index, stale statistics (cross-reference CFG-014/015/016), or a non-sargable filter expression.
• PLAN-002 — Nested-loop join with unindexed inner table. A NESTED LOOP JOIN whose inner-side access plan is itself a SEQUENTIAL SCAN. Informix will scan the entire inner table once per row of the outer side — O(N×M) cost. The fix is almost always an index on the inner table's join column. Semi-joins from flattened EXISTS / IN subqueries are excluded because their inner-scan short-circuits.

Each finding lands in the report's standard Findings section with the offending SQL excerpt, the relevant plan step, and a triage recommendation that links to the rule's reason page. Repeated findings against the same table fold into one card so a workload with 500 queries hitting the same missing-index pattern produces one actionable signal, not 500.

Reference

• IBM Informix Performance Guide — Queries and the query optimizer