You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <ri...@oracle.com> on 2013/06/17 16:12:29 UTC
how to use the PlanExporter tool
I would like to use the PlanExporter tool to view a query plan in xml.
But I am having a hard time figuring out how to use this tool. The
documentation on PlanExporter is divided between a couple user guides,
and somehow, flipping back and forth between them, I have managed to not
understand how to operate the tool. Here is my attempt to get
xplain-style statistics for a query. As you can see, I can't locate the
stmt_id:
ij version 10.11
ij> connect 'jdbc:derby:db;create=true';
ij> call syscs_util.syscs_set_runtimestatistics(1);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_xplain_schema('MY_STATS');
0 rows inserted/updated/deleted
ij> select tablename from sys.systables where 1=2 order by tablename;
TABLENAME
--------------------------------------------------------------------------------------------------------------------------------
0 rows selected
ij> call syscs_util.syscs_set_runtimestatistics(0);
0 rows inserted/updated/deleted
ij> select stmt_id from my_stats.sysxplain_statements;
STMT_ID
------------------------------------
0 rows selected
ij> call syscs_util.syscs_set_xplain_schema('');
0 rows inserted/updated/deleted
Thanks,
-Rick
Re: how to use the PlanExporter tool
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Bryan Pendleton <bp...@gmail.com> writes:
> Does this mean that we should be calling this.close() rather than source.close()?
Or possibly in addition to source.close(). PRRS.close() usually calls
both source.close() and super.close().
I've filed DERBY-6268 to track this bug.
> thanks,
>
> bryan
>
> On Mon, Jun 17, 2013 at 8:51 AM, Knut Anders Hatlen <kn...@oracle.com> wrote:
>
> shortcut so that NoPutResultSetImpl.close() is not invoked:
>
> /* Nothing to do if open was short circuited by false constant expression */
> if (shortCircuitOpen)
> {
> isOpen = false;
> shortCircuitOpen = false;
> source.close();
> return;
> }
>
> Notice that it doesn't call super.close() in this case. I suspect that
> this is what's causing the issue you're seeing.
>
--
Knut Anders
Re: how to use the PlanExporter tool
Posted by Bryan Pendleton <bp...@gmail.com>.
Does this mean that we should be calling this.close() rather than
source.close()?
thanks,
bryan
On Mon, Jun 17, 2013 at 8:51 AM, Knut Anders Hatlen
<kn...@oracle.com>wrote:
> shortcut so that NoPutResultSetImpl.close() is not invoked:
>
> /* Nothing to do if open was short circuited by false constant
> expression */
> if (shortCircuitOpen)
> {
> isOpen = false;
> shortCircuitOpen = false;
> source.close();
> return;
> }
>
> Notice that it doesn't call super.close() in this case. I suspect that
> this is what's causing the issue you're seeing.
>
>
Re: how to use the PlanExporter tool
Posted by Rick Hillegas <ri...@oracle.com>.
On 6/17/13 8:51 AM, Knut Anders Hatlen wrote:
> Rick Hillegas<ri...@oracle.com> writes:
>
>> I would like to use the PlanExporter tool to view a query plan in xml.
>> But I am having a hard time figuring out how to use this tool. The
>> documentation on PlanExporter is divided between a couple user guides,
>> and somehow, flipping back and forth between them, I have managed to
>> not understand how to operate the tool. Here is my attempt to get
>> xplain-style statistics for a query. As you can see, I can't locate
>> the stmt_id:
>>
>> ij version 10.11
>> ij> connect 'jdbc:derby:db;create=true';
>> ij> call syscs_util.syscs_set_runtimestatistics(1);
>> 0 rows inserted/updated/deleted
>> ij> call syscs_util.syscs_set_xplain_schema('MY_STATS');
>> 0 rows inserted/updated/deleted
>> ij> select tablename from sys.systables where 1=2 order by tablename;
> Does it work as expected if you remove the 1=2 predicate?
Aha. Yes, I get a statement id if I omit that predicate.
Thanks!
-Rick
> The recording of the statistics happens in the close() method of the
> top-level result set (see NoPutResultSetImpl.close()). If the top-level
> result set is a ProjectRestrictResultSet, and there is a predicate that
> is known at compile time to evaluate to false, it seems to take a
> shortcut so that NoPutResultSetImpl.close() is not invoked:
>
> /* Nothing to do if open was short circuited by false constant expression */
> if (shortCircuitOpen)
> {
> isOpen = false;
> shortCircuitOpen = false;
> source.close();
> return;
> }
>
> Notice that it doesn't call super.close() in this case. I suspect that
> this is what's causing the issue you're seeing.
>
>
>
Re: how to use the PlanExporter tool
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Rick Hillegas <ri...@oracle.com> writes:
> I would like to use the PlanExporter tool to view a query plan in xml.
> But I am having a hard time figuring out how to use this tool. The
> documentation on PlanExporter is divided between a couple user guides,
> and somehow, flipping back and forth between them, I have managed to
> not understand how to operate the tool. Here is my attempt to get
> xplain-style statistics for a query. As you can see, I can't locate
> the stmt_id:
>
> ij version 10.11
> ij> connect 'jdbc:derby:db;create=true';
> ij> call syscs_util.syscs_set_runtimestatistics(1);
> 0 rows inserted/updated/deleted
> ij> call syscs_util.syscs_set_xplain_schema('MY_STATS');
> 0 rows inserted/updated/deleted
> ij> select tablename from sys.systables where 1=2 order by tablename;
Does it work as expected if you remove the 1=2 predicate?
The recording of the statistics happens in the close() method of the
top-level result set (see NoPutResultSetImpl.close()). If the top-level
result set is a ProjectRestrictResultSet, and there is a predicate that
is known at compile time to evaluate to false, it seems to take a
shortcut so that NoPutResultSetImpl.close() is not invoked:
/* Nothing to do if open was short circuited by false constant expression */
if (shortCircuitOpen)
{
isOpen = false;
shortCircuitOpen = false;
source.close();
return;
}
Notice that it doesn't call super.close() in this case. I suspect that
this is what's causing the issue you're seeing.
--
Knut Anders