How To Capture A Graphical Plan

(Updated August 3, 2012 to include Version 12, plus a screenshot of the Plan Viewer.)

---

Graphical plans are wonderful things. Graphical plans tell you everything you need to know about why a query is running slowly.

Even if you don't have a clue about how to read a graphical plan, it's still wonderful because you can capture it in a file and send it to someone who does know how to read it ( like Glenn Paulley ) or someone like [cough] who pretends to know :)

Seriously, let's say you have The Query From Hell, some SELECT that's taking forever. Maybe yesterday (on SQL Anywhere 9) it ran quickly but now (on SQL Anywhere 11) it runs like continental drift... or whatever, it's slow now.

Here are the steps for capturing the graphical plan, for Versions 8 through 12. At the bottom there's a "Tip" that is very important, but only for Version 10 and earlier.

How To Capture A Graphical Plan In Version 11 and 12

- If desired, run CALL sa_flush_cache() in dbisql.

- Type or paste your query into dbisql.

- Choose Tools - Plan Viewer.

- Select Statisics level: Detailed and node statistics.

- Select Cursor type: Asensitive.

- Select Update status: Read-only.

- Click "Get Plan".

- When the plan appears, click "Save As...".

- Use Save as type Execution plan (*.saplan).

Click on this screenshot to see it full size...

How To Capture A Graphical Plan In Version 10

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with full statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Click "OK".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

How To Capture A Graphical Plan In Version 9

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Click "Make Permanent".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

How To Capture A Graphical Plan In Version 8

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Uncheck "Show UltraLite plan" unless you want that.

- Click "Make Permanent".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

Tip for Version 10 and earlier: If you do use dbisql to execute a query and capture a graphical plan with statistics, use SQL - Get Plan (Shift+F5) instead of SQL - Execute (F5 or F9). If you use Execute, the query will be executed twice: once to determine the result set and a second time to capture the plan. This plan may be completely different from the one used the first time the query was executed, and not just because the cache contains the rows. For example, if the plan is for a DELETE, the first execution will actually delete the rows so the plan may be for the completely different "zero rows" case. If you use Get Plan (Shift+F5) the query will only be run once, and the plan will match.