Moving SQL statement inside EXECUTE BLOCK affects stats/counts of indexed reads
obtained from MON$RECORD_STATS
--------------------------------------------------------------------------------------------------------------
Key: CORE-4799
URL: http://tracker.firebirdsql.org/browse/CORE-4799
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
Test:
====
recreate table log(measure_type varchar(10), what char(3), idx_reads bigint);
commit;
create or alter view vmon as
select r.mon$record_seq_reads seq_reads,r.mon$record_idx_reads idx_reads
from mon$attachments a left join mon$record_stats r on
a.mon$stat_id=r.mon$stat_id
where a.mon$attachment_id = current_connection;
commit;
recreate table test(x int);
commit;
insert into test select rand()*100 from (select 1 i from rdb$types rows 100) a,
(select 1 i from rdb$types rows 100) b;
commit;
create index test_x on test(x);
commit;
--- 1. OBTAINING MON$ INFO FOR *** SQL *** STATEMENTS ---
insert into log(measure_type, what, idx_reads) select 'SQL-1', 'beg', idx_reads
from vmon;
out nul; select min(x) from test; out;
commit;
insert into log(measure_type, what, idx_reads) select 'SQL-1', 'end', idx_reads
from vmon;
insert into log(measure_type, what, idx_reads) select 'SQL-2', 'beg', idx_reads
from vmon;
out nul; select min(x) from test; out;
commit;
insert into log(measure_type, what, idx_reads) select 'SQL-2', 'end', idx_reads
from vmon;
insert into log(measure_type, what, idx_reads) select 'SQL-3', 'beg', idx_reads
from vmon;
out nul; select min(x) from test; out;
commit;
insert into log(measure_type, what, idx_reads) select 'SQL-3', 'end', idx_reads
from vmon;
insert into log(measure_type, what, idx_reads) select 'SQL-4', 'beg', idx_reads
from vmon;
out nul; select min(x) from test; out;
commit;
insert into log(measure_type, what, idx_reads) select 'SQL-4', 'end', idx_reads
from vmon;
insert into log(measure_type, what, idx_reads) select 'SQL-5', 'beg', idx_reads
from vmon;
out nul; select min(x) from test; out;
commit;
insert into log(measure_type, what, idx_reads) select 'SQL-5', 'end', idx_reads
from vmon;
--- 2. OBTAINING MON$ INFO FOR *** EXECUTE BLOCKS *** ---
insert into log(measure_type, what, idx_reads) select 'PSQL-1', 'beg',
idx_reads from vmon;
set term ^; execute block as declare n type of column test.x; begin select
min(x) from test into n; end^ set term ;^
commit;
insert into log(measure_type, what, idx_reads) select 'PSQL-1', 'end',
idx_reads from vmon;
insert into log(measure_type, what, idx_reads) select 'PSQL-2', 'beg',
idx_reads from vmon;
set term ^; execute block as declare n type of column test.x; begin select
min(x) from test into n; end^ set term ;^
commit;
insert into log(measure_type, what, idx_reads) select 'PSQL-2', 'end',
idx_reads from vmon;
insert into log(measure_type, what, idx_reads) select 'PSQL-3', 'beg',
idx_reads from vmon;
set term ^; execute block as declare n type of column test.x; begin select
min(x) from test into n; end^ set term ;^
commit;
insert into log(measure_type, what, idx_reads) select 'PSQL-3', 'end',
idx_reads from vmon;
insert into log(measure_type, what, idx_reads) select 'PSQL-4', 'beg',
idx_reads from vmon;
set term ^; execute block as declare n type of column test.x; begin select
min(x) from test into n; end^ set term ;^
commit;
insert into log(measure_type, what, idx_reads) select 'PSQL-4', 'end',
idx_reads from vmon;
insert into log(measure_type, what, idx_reads) select 'PSQL-5', 'beg',
idx_reads from vmon;
set term ^; execute block as declare n type of column test.x; begin select
min(x) from test into n; end^ set term ;^
commit;
insert into log(measure_type, what, idx_reads) select 'PSQL-5', 'end',
idx_reads from vmon;
-- Display aggregated results - differences of MON$ values for each statement:
select
measure_type,
max( iif(what='end', idx_reads, 0) ) - max( iif(what='beg', idx_reads, 0) )
idx_reads
from log
group by 1
order by iif(measure_type starting with 'SQL', 1, 2)
;
Result in 2.5:
==========
MEASURE_TYPE IDX_READS
============ =====================
SQL-1 3 --- yes, it's THREE indexed reads. May be
this is affect of reading metadata from RDB$ tables since start of attach ?
SQL-2 1
SQL-3 1
SQL-4 1
SQL-5 1
PSQL-1 3
PSQL-2 3
PSQL-3 3
PSQL-4 3
PSQL-5 3
Result in 3.0:
===========
MEASURE_TYPE IDX_READS
============ =====================
SQL-1 2
SQL-2 2
SQL-3 2
SQL-4 2
SQL-5 2
PSQL-1 4
PSQL-2 4
PSQL-3 4
PSQL-4 4
PSQL-5 4
Tested on: WI-V2.5.5.26871 (SC), WI-T3.0.0.31840 (SC and SS).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
