GRAPHICAL_PLAN Function [String]

Returns the graphical query plan to Interactive SQL in an XML format string.

Syntax

GRAPHICAL_PLAN ( string-expression 
[, statistics-level
[, cursor-type
[, update-status ]]])

Note: CIS functional compensation performance considerations apply.

Parameters

Parameter	Description
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.
statistics-level	An integer. Statistics-level can be: 0 – Optimizer estimates only (default). 2 – Detailed statistics including node statistics. 3 – Detailed statistics.
cursor-type	A cursor type, expressed as a string. Possible values are: asensitive, insensitive, sensitive, or keyset-driven. If cursor-type is not specified, asensitive is used by default.
update-status	A string parameter accepting one of the following values indicating how the optimizer should treat the given cursor: READ-ONLY – The cursor is read-only. READ-WRITE (default) – The cursor can be read or written to. READ-WRITE (default) – The cursor can be read or written to.

Returns

LONG VARCHAR

Note: The result data type is a LONG VARCHAR. If you use GRAPHICAL_PLAN in a SELECT INTO statement, you must have an Unstructured Data Analytics Option license or use CAST and set GRAPHICAL_PLAN to the correct data type and size.

Remarks

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, a user with the SET ANY SYSTEM OPTION system privilege 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 Interactive SQL client plan window.

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 SAP Sybase IQ queries. A cursor is declared and opened using DECLARE CURSOR and OPEN CURSOR. 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.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server.

Example

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.

Note: 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 ( );