Capture Plans With GRAPHICAL_PLAN()

Sometimes, you already know where the slow queries are in your stored procedures. Other times, a quick run using SQL Anywhere's procedure profiling feature will tell you.

However, knowing the "what" and "where" of query bottlenecks may not be enough, you also need to know the "why". For that, you need to see the graphical plan with statistics for each slow query... and you often can't simply copy and paste the queries into ISQL to see the plans because you won't get realistic results,

You have to run the procedures in their natural habitat

One way to gather plans at runtime is described in Database Tracing In 50 Easy Steps, but that approach has some drawbacks:

• The Database Tracing process is painstaking and the resulting number of plans and level of detail is overwhelming,
  

• there's no easy way to determine which plans goes with which SQL statements in the stored procedures; in other words, Database Tracing doesn't tell you "where", and
  

• the plans are stored in a tracing database rather than written to separate *.saplan files.
  

Another alternative method is to go Old School: modify the stored procedure code to call GRAPHICAL_PLAN() and xp_write_file for each slow query; here's how...

Step 1: Find a slow SQL statement.

Here's an example of a stored procedure that contains a slow query:

CREATE PROCEDURE slow() BEGIN DECLARE @local_variable INTEGER DEFAULT 1; SELECT 'string literal', @local_variable, COUNT_BIG(*) -- trailing comment INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; SELECT * FROM #temp; END; SELECT * FROM slow(); expression @local_variable expression1 -------------- --------------- ----------- string literal 1 178123800 (1 rows) Execution time: 44.845 seconds

Here's what it looks like in the SQL Anywhere 16 stored procedure profiler:

Step 2: Add code to capture the plan.

Here's the code template for calling GRAPHICAL_PLAN() to capture a graphical plan with statistics from within a stored procedure:

/* START TEMPORARY CODE */ CALL xp_write_file ( 'C:/temp/graphical_plan.saplan', GRAPHICAL_PLAN ( ' [PASTE SQL STATEMENT HERE] ', 2, -- Detailed statistics including node statistics 'asensitive', 'READ-ONLY' ) ); /* END TEMPORARY CODE */

Here's how to use the code template...

• Insert the code template immediately ahead of the slow query in the stored procedure.
  

• Copy and paste the slow query where it says [PASTE SQL STATEMENT HERE].
  

• Change all embedded single quotes ' to be quote pairs '' (not doublequotes, but two single quotes).
  

• References to local variables are OK; so are trailing --comments, embedded /*comments*/ and the closing semicolon.
  

• Make sure you preserve the correct functionality of the stored procedure. In particular, make sure it's OK to execute the query twice (once by the GRAPHICAL_PLAN() function, and once by the original SQL statement). In this case, the "INTO #temp" must be removed from the query passed to GRAPHICAL_PLAN() for two reasons; first, so multiple rows aren't inserted to the temporary table by two query executions, and second, because GRAPHICAL_PLAN() fails with SQLCODE=-894 for a query that contains an INTO #temp clause.
  

• You may choose to pass only portions of a slow query to GRAPHICAL_PLAN(); for example, the SELECT portion of a DELETE SELECT, or one of many SELECT statements in a large UNION.
  

• You may also choose to code multiple CALL xp_write_file statements, with different *.saplan file names, for multiple slow queries or separate portions of one slow query.
  

Here's what the slow() procedure looks like after the code's been added:

CREATE PROCEDURE slow() BEGIN DECLARE @local_variable INTEGER DEFAULT 1; /* START TEMPORARY CODE */ CALL xp_write_file ( 'C:/temp/graphical_plan.saplan', GRAPHICAL_PLAN ( ' SELECT ''string literal'', @local_variable, COUNT_BIG(*) -- trailing comment -- INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; ', 2, -- Detailed statistics including node statistics 'asensitive', 'READ-ONLY' ) ); /* END TEMPORARY CODE */ SELECT 'string literal', @local_variable, COUNT_BIG(*) -- trailing comment INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; SELECT * FROM #temp; END; SELECT * FROM slow();

Step 3: Run the procedure to capture the plan.

Here's what graphical_plan.saplan looks like in the SQL Anywhere 16 plan viewer:

---