Hi Dmitry,
I tried ISQL and this gives no entry for the first query (see output below),
so I think it is specific to FlameRobin (or IBX). I will have a look at the
trace/audit logs.
Thanks for your help.
SQL> connect "EMPLOYEE.FDB"
CON> user 'SYSDBA' password '*******';
Database: "EMPLOYEE.FDB", User: SYSDBA
SQL> set transaction;
Commit current transaction (y/n)?y
Committing.
SQL> select * from employee;
...
SQL> select * from department;
...
SQL> select * from job;
...
SQL> select * from mon$statements where mon$attachment_id =
CURRENT_CONNECTION;
MON$STATEMENT_ID MON$ATTACHMENT_ID MON$TRANSACTION_ID MON$STATE
MON$TIMESTAMP MON$SQL_TEXT MON$STAT_ID MON$EXPLAINED_PLAN
================ ================= ================== =========
========================= ================= ============ ==================
31 27 164 1 2015-03-18
21:38:55.0150 0:1f 25 0:20
==============================================================================
MON$SQL_TEXT:
select * from mon$statements where mon$attachment_id = CURRENT_CONNECTION
==============================================================================
==============================================================================
MON$EXPLAINED_PLAN:
Select Expression
-> Filter
-> Table "MON$STATEMENTS" Full Scan
==============================================================================
-----Original message/Oorspronkelijk bericht-----
From: Dmitry Yemanov
Sent: Wednesday, March 18, 2015 8:53 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Retrieving plan using new mon$explained_plan
field (FB3 beta )
18.03.2015 22:37, Robbert-Jan wrote:
>
> If I execute this scenario (using FlameRobin and WI-T3.0.0.31369 Firebird
> 3.0 Beta 1):
>
> - start transaction
> - execute query 1: SELECT * FROM Table1
> - execute query 2: SELECT * FROM Table2
> - execute query 3: SELECT * FROM Table3
> - execute query 4: SELECT * FROM mon$statements WHERE mon$attachment_id =
> CURRENT_CONNECTION
Do you execute them subsequently in the same window? [Robert:] Yes I did
> then query 4 will return two rows from mon$statements, one for query 1
> (with
> mon$state = 0) and one for query 4 (with mon$state = 1), but I'm
> interested
> in the entry in mon$statements for query 3, i.e. the last regular query.
I'm surprised that query 1 appears there. Some FlameRobin specifics
perhaps? Could you re-test with ISQL?
MON$ tables don't show the past, so your "last" query must still be
prepared (i.e. alive) in order to be reported in MON$STATEMENTS. This is
not the case when different queries are executed subsequently in some
interactive SQL tool, causing their handles to be reallocated and older
queries to be destroyed.
So if you need to retrieve plans of queries executed in the past *and
already released*, then MON$STATEMENTS is not going to be any help.
Monitoring tables report just the current activity. Instead, the
trace/audit logs may be useful to analyze something happened in the past.
Dmitry
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
