sp_plancache_contents system procedure

Returns the contents of a plan cache for a connection, including statistics indicating the average, minimum, and maximum execution times, and the number of times cursors have been built for a statement.

Syntax

sp_plancache_contents( [ connidparm ] )

Parameters

connidparm
This optional INTEGER parameter is the connection ID of a connection to the current database. The default is NULL, in which case information about the current connection's plan cache is returned..

Result set

Column name	Data type	Description
stmt_type	CHAR(1)	A value that indicates the type of statement. Possible values are: B Bypass statement C Client statement E Event M Temporary procedure or batch P Procedure T Trigger
definition_id	UNSIGNED INTEGER	For bypass statements, the associated table_id found in the SYSTAB system view. For client statements, an ID that uniquely identifies the statement. This ID matches the value printed with the corresponding statement text in the request level log, if logging of statement text is enabled. For event statements, the associated event_id value found in the SYSEVENT system view. For temporary procedures, an ID that uniquely identifies the procedure on the connection. For batches, this field is NULL. For procedure statements, the associated proc_id value found in the SYSPROCEDURE system view. For trigger statements, the associated trigger_id value found in the SYSTRIGGER system view.
definition_position	UNSIGNED INTEGER	A positional offset of the statement within the definition. For bypass and client statements this field is NULL.
definition_scope	SMALLINT	The scope of the statement context. For bypass and client statements this field is NULL.
bypass_type	CHAR(1)	For bypass statements, a value that indicates the bypass statement type. Possible values are: D DELETE I INSERT S SELECT U UPDATE
status	CHAR(1)	A value that indicates the current state of the cache entry. Possible values are: D Caching is disabled for this statement. P A plan is cached for this statement. R Ready to cache a plan on the next execution of this statement. T Training to determine whether to cache a plan for this statement.
plan_type	CHAR(1)	For internal use only.
plan_signature	UNSIGNED BIGINT	For internal use only.
PSID	UNSIGNED BIGINT	For internal use only.
build_count	UNSIGNED INTEGER	The number of times a cursor has been built for this statement.
build_avg_msec	DOUBLE	The average time required to build a cursor for this statement, in milliseconds.
reusable_count	UNSIGNED INTEGER	The number of executions of a reusable cursor for this statement.
reusable_avg_msec	DOUBLE	The average elapsed time for an execution of a reusable cursor for this statement, in milliseconds.
reusable_min_msec	DOUBLE	The minimum elapsed time for an execution of a reusable cursor for this statement, in milliseconds.
reusable_max_msec	DOUBLE	The maximum elapsed time for an execution of a reusable cursor for this statement, in milliseconds.
nonreusable_count	UNSIGNED INTEGER	The number of executions of a nonreusable cursor for this statement.
nonreusable_avg_msec	DOUBLE	The average elapsed time for an execution of a nonreusable cursor for this statement, in milliseconds.
nonreusable_min_msec	DOUBLE	The minimum elapsed time for an execution of a nonreusable cursor for this statement, in milliseconds.
nonreusable_max_msec	DOUBLE	The maximum elapsed time for an execution of a nonreusable cursor for this statement, in milliseconds.

Remarks

Use this system procedure to examine the current entries in a connection's plan cache. This can be helpful for choosing an appropriate value for the max_plans_cached option, which governs plan cache size. The statistics about build and execution times can also be used to identify statements for which plan caching is causing performance issues. In this case, plan caching can either be disabled for all statements by setting the max_plans_cached option to 0 (zero), or by selectively disabling caching for a statement by adding the FORCE OPTIMIZATION clause to the SQL text.

Privileges

You must have EXECUTE privilege on the system procedure. To return the plan cache contents for a connection other than the current connection, you must also have the MANAGE CACHED PLANS system privilege.

Side effects

None

Example

The following statement returns the plan cache entries for the current connection:

CALL sp_plancache_contents();