<< SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure | System procedures for storing jar files >>

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure

<< SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure | System procedures for storing jar files >>

Derby Reference Manual

178

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure

This procedure allows you to request XPLAIN style processing of runtime statistics.
When XPLAIN style is used, the runtime statistics are written to the SYSXPLAIN_*
database tables, so that you can analyze the statistics by running queries against
the tables. See "Working with RunTimeStatistics" in the Tuning Derby for additional
information.

Turn xplain style on by calling this procedure with a non-empty argument. Turn xplain
style off by calling the procedure with an empty argument.

The argument that you provide must be a legal schema name, and you should use this
argument to indicate the schema in which runtime statistics should be captured. If the
schema that you specify does not already exist, it will be automatically created. If the
XPLAIN tables do not already exist in this schema, they will be automatically created.
Runtime statistics information about statements executed in this session will then be
captured into these tables, until runtime statistics capturing is halted by either calling

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA

with an empty argument or by calling

SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)

Syntax

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA(IN VARCHAR(128) SCHEMA_NAME)

Execute privileges

If authentication and SQL authorization are both enabled, only the

database owner

has

execute privileges on this procedure by default. See "Enabling user authentication" and
"Setting the SQL standard authorization mode" in the Derby Developer's Guide for more
information. The database owner can grant access to other users.

Example

To cause Derby to record statistics about statement execution in the SYSXPLAIN_*
database tables in the schema named 'MY_STATS':

call syscs_util.syscs_set_runtimestatistics(1);
call syscs_util.syscs_set_xplain_schema('MY_STATS');

select country from countries;

call syscs_util.syscs_set_runtimestatistics(0);
call syscs_util.syscs_set_xplain_schema('');

SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure

The SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure updates the
cardinality statistics, or creates the statistics if they do not exist, for the index that you
specify or for all of the indexes on a table. Derby uses cardinality statistics to determine
the optimal query plan during the compilation of a query. If the statistics are missing,
Derby might use a query plan which is not the most efficient plan.

Once statistics have been created, they cannot be dropped and should be maintained. It
is a good idea to call the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS procedure when
the number of distinct values in an index is likely to have changed significantly.

For more information on cardinality statistics, see "Working with cardinality statistics" in
the Tuning Derby guide.

Syntax