Add statistics (and not only elapsed time) in output when use execute
statements inside parent query (EB or SP)
---------------------------------------------------------------------------------------------------------------
Key: CORE-4448
URL: http://tracker.firebirdsql.org/browse/CORE-4448
Project: Firebird Core
Issue Type: Improvement
Components: TRACEMGR
Affects Versions: 3.0 Alpha 2
Reporter: Pavel Zotov
DDL:
recreate table t(s varchar(36) unique); commit;
insert into t select uuid_to_char( gen_uuid() )
from rdb$types,rdb$types,(select 1 i from rdb$types rows 30)
rows 500000;
commit;
Query: select count(*) from (select * from t order by s); - takes about 1500 ms
on my machine.
If I run trace with the following config options:
time_threshold = 2000
log_statement_finish = true
print_plan = true
print_perf = true
- then trace will NOT show statistics for this query (if it run separately) and
it's OK.
If then I run: select count(*) from (select t.* from t,(select 1 i from
rdb$types rows 7) );
- then statistics will be like this:
PLAN JOIN (RDB$TYPES NATURAL, T NATURAL)
1 records fetched
2016 ms, 7035203 fetch(es)
Table Natural Index Update Insert Delet
********************************************************************************
RDB$TYPES 7
T 3500000
And if I run the following EB:
set term ^;
execute block as
declare n int;
declare s varchar(80) = 'select count(*) from (select * from t order by s)';
begin
select count(*) from (select t.* from t,(select 1 i from rdb$types rows 7) )
into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
execute statement (s) into n;
end
^ set term ;^
- I will get the following data:
PLAN JOIN (RDB$TYPES NATURAL, T NATURAL)
0 records fetched
11937 ms, 7035203 fetch(es)
Table Natural Index Update Insert
Delete Backout Purge Expunge
***************************************************************************************************************
RDB$TYPES 7
T 3500000
So, we can see the increased total time but the SAME statistics (fetches in
this sample). This occured because each ES have 'too small' time to be
displayed in the trace due to time_threshold parameter of trace config. But all
such ES can significantly increase total (overall) time.
It is impossible now to detect such ES unless set trace time_threshold to small
value but it will lead to much more trace output and will degrade performance
because of IO.
Is it possible to include *statistics* (not only time) into overall output of
'outer' block (EB or SP) for *all* ES which runs inside it ?
--
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
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/NeoTech
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
