db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6216) XPLAIN feature does not work and gives ERROR XCL16: ResultSet not open
Date Mon, 01 Jul 2013 10:22:21 GMT

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

Knut Anders Hatlen commented on DERBY-6216:
-------------------------------------------

I like that the patch introduces a local flag so that the result set doesn't have to go through
the LCC and check if xplain is enabled for every row it looks at. It might be a problem that
the flag is set in the constructor, though. The constructor is only called the first time
a PreparedStatement is executed, so it may cause some surprises if the same statement is used
both with xplain mode enabled and disabled.

Example ij session:

ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true';
ij> prepare ps1 as 'values ''Compiled before xplain mode was enabled''';
ij> execute ps1;
1                                      
---------------------------------------
Compiled before xplain mode was enabled

1 row selected
ij> call syscs_util.syscs_set_runtimestatistics(1);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_xplain_schema('STATS');
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_xplain_mode(1);
0 rows inserted/updated/deleted
ij> execute ps1;
1                                      
---------------------------------------

0 rows selected
ij> prepare ps2 as 'values ''Compiled when xplain mode was enabled''';
ij> execute ps2;
1                                    
-------------------------------------

0 rows selected
ij> call syscs_util.syscs_set_runtimestatistics(0);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_xplain_schema('');
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_xplain_mode(0);
0 rows inserted/updated/deleted
ij> execute ps1;
1                                      
---------------------------------------
Compiled before xplain mode was enabled

1 row selected
ij> execute ps2;
1                                    
-------------------------------------

0 rows selected
ij> 


The statement that was compiled before xplain was enabled actually behaved as desired. It
returned results if xplain mode was disabled, and returned empty results if it was enabled.
I think this statement works because BasicNoPutResultSetImpl.getNextRow() still checks the
LCC instead of the local flag. (Note: this was in getNextRow(), not in getNextRowCore().)

The statement that was compiled when xplain was enabled, on the other hand, continued returning
empty results even after xplain had been disabled. I think it would be more reasonable if
it had returned results after disabling xplain mode.

I think it might work better if the flag is initialized in open() instead of the constructor.
                
> 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, 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