Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
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.
sp_plancache_contents( [ 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..
|Column name||Data type||Description|
|stmt_type||CHAR(1)||A value that indicates the type of statement.
Possible values are:
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:
|status||CHAR(1)||A value that indicates the current state of the
cache entry. Possible values are:
|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.|
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.
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.
The following statement returns the plan cache entries for the current connection: