![GETDATE function [Date and time]](arrow-left.gif){kind=icon}
![GROUPING function [Aggregate]](arrow-right.gif){kind=icon}
Returns the graphical query plan to Interactive SQL in an XML format string.
GRAPHICAL_PLAN ( string-expression )
CIS functional compensation performance considerations
apply. See “Conditions
that cause processing by SQL Anywhere” in Chapter
3, “Optimizing Queries and Deletions,” in
the Performance and Tuning Guide.
string-expression SQL statement for which the plan is to be generated. string-expression is generally a SELECT statement, but it can also be an UPDATE or DELETE, INSERT SELECT, or SELECT INTO statement.
If you do not provide an argument to the GRAPHICAL_PLAN function, the query plan is returned to you from the cache. If there is no query plan in the cache, then this message appears:
plan not available
The behavior of GRAPHICAL_PLAN function is controlled by database options QUERY_PLAN_TEXT_ACCESS and QUERY_PLAN_TEXT_CACHING. If QUERY_PLAN_TEXT_ACCESS is OFF (the default), then this message appears:
Plan not available. The database option QUERY_PLAN_TEXT_ACCESS is OFF
If a user needs access to the plan, the DBA must set option QUERY_PLAN_TEXT_ACCESS ON for that user.
If QUERY_PLAN_TEXT_ACCESS is ON, and the query plan for the string expression is available in the cache maintained on the server, the query plan from the cache is returned to you.
If the query plan is not available in the cache and you are authorized to view plans on the client, then a query plan with optimizer estimates (query plan with NOEXEC option ON) is generated and appears on the dbisql client plan window.
Sybase IQ does not support NOEXEC plan
generation for SELECT, UPDATE, DELETE, INSERT
SELECT, and SELECT INTO queries.
When a user requests a query plan that has not yet been executed, the query plan is not available in the cache. Instead, a query plan with optimizer estimates is returned without QUERY_PLAN_AFTER_RUN statistics.
Query plans for stored procedures are not accessible using the GRAPHICAL_PLAN function.
Users can view the query plan for cursors opened for IQ queries. A cursor is declared and opened using DECLARE CURSOR and OPEN CURSOR commands. To obtain the query plan for the most recently opened cursor, use:
SELECT GRAPHICAL_PLAN ( );
With the QUERY_PLAN_AFTER_RUN option OFF, the plan appears after OPEN CURSOR or CLOSE CURSOR. However, if QUERY_PLAN_AFTER_RUN is ON, CLOSE CURSOR must be executed before you request the plan.
For information on viewing the query optimizer's execution plan for a SQL statement in the Plan Viewer window in Interactive SQL, see "Viewing graphical plans in Interactive SQL".
When Interactive SQL users request plans for UPDATE, DELETE, SELECT INTO, and INSERT SELECT queries, the NOEXEC plan is not supported. To access the query plan, first explicitly execute the query, and then request the plan.
The following example passes a SELECT statement as a string parameter and returns the plan for executing the query. It saves the plan in the file gplan.xml.
If you use the OUTPUT statement’s HEXADECIMAL clause
set to ASIS to get formatted plan output, the
values of characters are written without any escaping, even if the
value contains control characters. ASIS is useful
for text that contains formatting characters such as tabs or carriage
returns.
SELECT GRAPHICAL_PLAN ('SELECT * FROM Employees');OUTPUT to 'C:\gplan.xml' HEXADECIMAL ASIS quote '';
The following example returns the query plan from the cache, if available:
SELECT GRAPHICAL_PLAN ( );
“OUTPUT statement [DBISQL]” in Reference: Building Blocks, Tables, and Procedures
“NOEXEC option,” “QUERY_PLAN_AFTER_RUN option,” “QUERY_PLAN_AS_HTML option,” “QUERY_PLAN_TEXT_ACCESS option,”and “QUERY_PLAN_TEXT_CACHING option” in Reference: Statements and Options
PLAN function [Miscellaneous], EXPLANATION function, [Miscellaneous], GRAPHICAL_ULPLAN function [Miscellaneous], LONG_ULPLAN function [Miscellaneous], and SHORT_ULPLAN function [Miscellaneous] in SQL Anywhere Server – SQL Reference.
