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-2487) Enhance Derby with EXPLAIN Functionality
Date Thu, 05 Mar 2009 11:25:56 GMT

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

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

Some thoughts based on reading the discussion and the proposed
documentation:

I was wondering if there was a better place to store these data than
in the system tables. The problems I see with using the system tables
are:

1) The size of the seg0 directory in an empty database increases by
19% with this patch (712KB -> 848KB) which leads to increased
footprint and possibly increased database creation time even for those
who don't use the feature.

2) I guess these tables can grow quite big. How can we reclaim that
space? The reset procedure deletes the rows, but I don't think we can
compress system tables. And we cannot drop them.

3) System tables are shared by all users, so the approach doesn't work
very well in a multi-user environment. If two users collect data, they
may be confused by seeing the other user's data. And if one of them
calls the reset procedure, all the others who are collecting
statistics will lose their data too.

4) We'll need upgrade code if we change the tables.

I haven't given this much thought, but would it be possible to create
these tables on demand, possibly as temporary tables in the SESSION
schema? Then different users wouldn't interfere with each other, and
we wouldn't need to worry about disk footprint or upgrade of system
tables. Or perhaps one could pick table names (or at least their
prefix) when enabling XPLAIN mode?

  call syscs_util.syscs_set_runtimestatistics(1);
  call syscs_util.syscs_set_xplain_style(1, 'APP', 'PREFIX_');
  .
  .
  .
  select count(*) from prefix_xplain_statement_timings;

> Enhance Derby with EXPLAIN Functionality
> ----------------------------------------
>
>                 Key: DERBY-2487
>                 URL: https://issues.apache.org/jira/browse/DERBY-2487
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.2.2.0
>            Reporter: Felix Beyer
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: Derby physical XPLAIN schema.png, incorporateTrunkChanges.diff,
removeSourceDepth.diff, RSProtocolNew.pdf, rts.xls, small logical xplain schema.pdf, startRegressionTest.diff,
startRegressionTest.diff, startUpgradeTests.diff, updateRegressionTests.diff, updateRegressionTests.diff,
usage.txt, xplain_patch_v1.txt, xplainClasses.pdf
>
>
> This enhancement extends Derby with EXPLAIN functions. Users want to have more feedback
than they`re getting with the current RuntimeStatistics facility. This extension is based
on the RuntimeStatistics/ResultSetStatistics functions / classes. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message