db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Why is the optimizer choosing such a bad path
Date Thu, 28 Mar 2013 00:33:54 GMT
Mike, I would like to understand the "you aren't gathering the
plan with all options enabled" comment.   I followed this from the Derby Tuning guide:

-- turn on RUNTIMESTATISTICS for connection:
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-- execute complex query here -- step through the result set
-- access runtime statistics information:
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);

And placed my query in there.   So if there is something else that needs to be done, can
you point me in the correct direction. 

I am going to check the large table tonight and see if returns an error on that.   How
could the optimizer have the correct row counts?

________________________________________
From: Mike Matrigali [mikem_app@sbcglobal.net]
Sent: Wednesday, March 27, 2013 6:36 PM
To: derby-dev@db.apache.org
Subject: Re: Why is the optimizer choosing such a bad path

On 3/27/2013 12:16 PM, Bergquist, Brett wrote:
> Some background.
>
> I have a customer that is using an earlier release of our system that has
> Derby 10.8.2.1 installed.   Because of issues like
>       https://issues.apache.org/jira/browse/DERBY-5680,
> it has been running with the indexStat daemon disabled.
>
> We are going to have a new release soon and it will be installing Derby 10.9.1.0 with
the indexStat
> daemon enabled.   I recently got a copy of the customer's database (132Gb) and ran into
> a very long query.   I manually ran SYS_UTIL.SYSCS_UPDATE_STATISTICS on all of the tables
> in the query to ensure that the statistics are up to date.
> ...

when I look at bad query plans i usually look for estimated row counts
vs actual to see where the optimizer made some bad assumptions.  The
plan you posted has a bunch of 0's.  So maybe you aren't gathering the
plan with all options enabled?  If optimizer actually thinks there are
0 rows in your 62 million row table that would be a problem.


Mime
View raw message