GRAPHICAL_PLAN function [Miscellaneous]

Returns the plan optimization strategy of a SQL statement in XML format, as a string.

Syntax

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

Parameters

string-expression The SQL statement, which is commonly a SELECT statement but which may also be an UPDATE or DELETE statement.

statistics-level An integer. Statistics-level can be one of the following values:

Value	Description
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:

Value	Description
READ-ONLY	The cursor is read-only.
READ-WRITE (default)	The cursor can be read or written to.
FOR UPDATE	The cursor can be read or written to. This is exactly the same as READ-WRITE.

The following Interactive SQL example passes a SELECT statement as a string parameter and returns the plan for executing the query. It saves the plan in the file plan.saplan which can be opened and read using Interactive SQL.

SELECT GRAPHICAL_PLAN(  'SELECT * FROM Departments WHERE DepartmentID > 100' );
OUTPUT TO 'plan.saplan' FORMAT TEXT QUOTE '' HEXADECIMAL ASIS;

The following statement returns a string containing the graphical plan for a keyset-driven, updatable cursor over the query SELECT * FROM Departments WHERE DepartmentID > 100. It also causes the server to annotate the plan with actual execution statistics, in addition to the estimated statistics that were used by the optimizer.

SELECT GRAPHICAL_PLAN(
   'SELECT * FROM Departments WHERE DepartmentID > 100',
   2,
   'keyset-driven', 'for update' );

Discuss this page in DocCommentXchange.