db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Explain Tables and Workload Management Extensions
Date Thu, 27 Jul 2006 14:20:32 GMT

I don't believe that this is something that you'll find in any standards.
Query Optimization, Workload Management, etc are left up to the developers
to decide how to best implement it.

I don't think that the issue is if the community will find this tool useful,
but rather do you want to spend your time developing it? This is an open
source community so its really your call as to what you want to do.

IMHO, I'm not so sure how useful this tool would be. I view Derby as a
lightweight RDBMS. As such, the queries will most likely be straight forward
and I should be able to figure out the indexes that I'll need to get optimal
performance.

But then again, I'm one person and I'm probably in the minority on this
issue.

I would also recommend that you start from scratch. You'll probably come up
with a better solution. ;-)




> -----Original Message-----
> From: Felix Beyer [mailto:s4224320@mail.inf.tu-dresden.de]
> Sent: Thursday, July 27, 2006 7:13 AM
> To: derby-user@db.apache.org
> Subject: Explain Tables and Workload Management Extensions
> 
> 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?
> 
> Regards,
> Felix
> 
>  > 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




Mime
View raw message