db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6216) XPLAIN feature does not work and gives ERROR XCL16: ResultSet not open
Date Sat, 13 Jul 2013 15:03:49 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13707754#comment-13707754
] 

ASF subversion and git services commented on DERBY-6216:
--------------------------------------------------------

Commit 1502795 from [~bryanpendleton]
[ https://svn.apache.org/r1502795 ]

DERBY-6216: XPLAIN feature does not work, gives XCL16: ResultSet not open

This change adjusts the implementation of the explain-only mode feature,
which is enabled via syscs_util.syscs_set_xplain_mode(1).

Prior to this change, the implementation short-circuited the open() method
of the ResultSet classes, causing the execution engine to terminate
immediately because the result sets were not open.

This change takes an alternate approach, suggested by Knut Anders: the open()
is allowed to proceed normally, but we short-circuit the getNextRow()
method of the ResultSet classes. This way, the result sets all appear to be
normal result sets, open and ready for business, but they do no work and
always produce an empty result.

Then, at the end of the execution, the statistics are gathered. They show
that no work was done, but they contain information about the overall "shape"
of the query plan (which result sets were constructed, and what the overall
result set tree looks like).

We also discovered that we were completely missing regression tests for the
explain-only feature, so this change adds a handful of regression tests,
including an IJ-based test, a simple embedded query, and a test that verifies
that explain-only mode can be turned on and off between executions of a
prepared query.

More testing would clearly be beneficial, but this should at least give us
a feature that basically works.
                
> XPLAIN feature does not work and gives ERROR XCL16: ResultSet not open
> ----------------------------------------------------------------------
>
>                 Key: DERBY-6216
>                 URL: https://issues.apache.org/jira/browse/DERBY-6216
>             Project: Derby
>          Issue Type: Bug
>          Components: Tools
>    Affects Versions: 10.6.2.3, 10.7.1.4, 10.8.3.1, 10.9.1.0
>         Environment: Solaris 10, MacOS X 10.6, Windows XP
>            Reporter: Brett Bergquist
>            Assignee: Bryan Pendleton
>              Labels: derby_triage10_11, features
>         Attachments: addTests.diff, emptyRS.diff, noMemberField.diff, preparedTests.diff,
xplain-only.diff
>
>
> I am running Derby 10.9.1.0 and am trying to figure out a  performance problem with query
taking too long.   I don’t actually want to run the query so from the documentation, I try:
> call syscs_util.syscs_set_runtimestatistics(1);
> call syscs_util.syscs_set_xplain_schema('STATS');
> call syscs_util.syscs_set_xplain_mode(1);
> --- my query here
> call syscs_util.syscs_set_runtimestatistics(0);
> call syscs_util.syscs_set_xplain_schema('');
> call syscs_util.syscs_set_xplain_mode(0);
> When it runs my query, I get:
> ERROR XCL16: ResultSet not open. Operation 'getMetaData' not permitted. Verify that autocommit
is OFF.
> I try the following with IJ just to see:
> call syscs_util.syscs_set_runtimestatistics(1);
> call syscs_util.syscs_set_xplain_schema('STATS');
> call syscs_util.syscs_set_xplain_mode(1);
> select sql_text from syscs_diag.transaction_table where status != 'IDLE';
> call syscs_util.syscs_set_runtimestatistics(0);
> call syscs_util.syscs_set_xplain_schema('');
> call syscs_util.syscs_set_xplain_mode(0);
> Note I have tried this with real tables as apposed to the syscs_diag.transaction_table
with the same result.  The only reason that I posted this with this table is that it is a
table that is accessible for any database.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message