db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Felix Beyer <s4224...@mail.inf.tu-dresden.de>
Subject Explain Tables and Workload Management Extensions
Date Thu, 27 Jul 2006 12:13:29 GMT
Hi User-Community,

I tried to search for standards dealing with explain tables and/or
workload management, but I didn´t find any. I looked up the ANSI SQL
Standard 2003 and especially part 11 of the standard. There are some
view descriptions which gather infos about the database but there is
nothing mentioned about explain tables or query plans. My approach would
be an investigation how other popular DBMS managed it (DB2, Oracle, MS
SQL 2005, ...). Afterwards I am able to develop a solution suited to
Derby needs. Probably my solution is modeled on basis of the DB2
approach. That could be interesting because if you use the DB2 Universal
JDBC Driver, tools like Aqua Data Studio, which are able to get out
query plans, could then be used to query Derby and maybe use the same
methods to get out the query plans for visualizing them.
Please give me some more feedback if the Derby Community is interested
in such new features. Is it worthy examining this? Are Derby
Administrators or Application Developers interested in tuning tools like
this, or could even the developers benefit during testing the optimizer
or visualizing some internal state of Derby?


 > Hi, Felix, this sounds like very interesting work! My only question at
 > this stage would be, are these new features standards-based? There may
 > be some resistance to adding interfaces that are not based on a
 > standard, as that is one of the guiding principles of the Derby project.
 > Although if it's used mostly for performance analysis and is not part
 > of the interfaces used by applications, the resistance may be less
 > strong. Anybody else have thoughts on this?
 > Are you planning to contribute your query plan visualization tool as
 > well? It sure sounds useful to me (although we'd need to understand
 > what license the JGraph framework is under and whether it's compatible
 > with the Apache license).
 > David
 > Felix Beyer wrote:
 >> Hello Derby Community,
 >> my name is Felix Beyer and I´m a student at the Dresden University of
 >> Technology. I´m right before writing my diploma thesis, which inculdes
 >> an extension of Derby. Please give me some feedback to let me know if I
 >> should merge my extensions into a current code branch of Derby.
 >> The core of my topic consists of two major tasks:
 >> First, I want to extend Derby with Explain Table functionality. That
 >> means I want to develop a mechanism to store the generated query plans
 >> in appropriate system tables. In order to do this I will have to
 >> examine current system functions like GetRuntimeStatistics() and the
 >> overall process from parsing to execution of a statement. Further
 >> investigations have to be made to develeop solutions for questions
 >> like: Which information is additionally available and could be
 >> aggregated or derived? Which modifications are necessary to extract
 >> this information? How big is the performance impact during the rising
 >> of explain table information?
 >> The second big task will be to extend Derby to store Workload
 >> information. Investigations have to be made on reasonable aggregation
 >> and mandatory reduction of workload, general scheme of workload
 >> information, support for application domains using this workload
 >> information, comparison to other DBMS etc.
 >> Now, the following question arises: What does the Derby community
 >> think of such extensions? Please give me some feedback. The feedback
 >> you provide will be the basis of the decision if my extensions should
 >> be merged into a current code branch of Derby or will be integrated
 >> into our code branch.
 >> By the way, in a former project I managed to extend Derby to extract
 >> the generated optimized query plans in form of XML files for
 >> visualizing them in an external application. I used the GXL file format
 >> for export and visualized the plans with the JGraph Framework. Internal
 >> changes affected the current Derby structure in two ways: First of all
 >> a new system function was added to toggle query extraction on or off
 >> and second a visitor pattern was used to collect the required
 >> information through a traverse of the query tree after the optimization
 >> step.
 >> Greetings,
 >> Felix Beyer

View raw message