1

I would like to be able to extract read-by-table statistics from the SQL statements executed by the system to generate a log of "suspicious" statements. In IBExpert, there is a feature that allows you to view, when executing an SQL statement, the amount of records read per table, separated by indexed and non-indexed readings, as well as the total number of records in the table, related to just that statement.

This allows us to identify problems that only the execution plan alone cannot demonstrate, as we have already had cases in which an instruction that brought us 100 records came to read in one of the tables involved, more than 100000 records because it was filtering in the wrong table. The plan did not help us in this case because all readings were indexed, we only discovered the problem when running this command in IBexpert that demonstrated this excessive reading.

I have researched from various sources without success how this is done.

I have already tried to use the firebird monitoring tables, but it only presents a "general" statistics, without this separation.

I have already tried to use the fbtracemgr application but there is no such possibility.

Does anyone have any idea how this is done?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Vinicius Biazzi
  • 13
  • 2
  • I guess it requests the `isc_info_read_seq_count` and `isc_info_read_idx_count` database info items before execution and after execution, and reports the difference per table. – Mark Rotteveel Dec 29 '20 at 11:22
  • I doubt you can od it "out of channel". IBExpert and similar tools do display query statistics exactly because they request that informaiton as part of each query. In other words, you would have to augment all your applications and add instrumentation to every of the queries. Alternatively you would have to do pretend-Firebird, a proxy, which would channel all the queries to real Firebird augmenting every of them with statistics requests and gathering the data. – Arioch 'The Dec 31 '20 at 17:21
  • See what commercial tools from IB-AID can offer, as the "out of channel" external monitoring programs. They work closely with FB core devs. If their tools do what you need you have chances to do something similar in house. But if not - then i think implementing it outside of your client applications would not be feasible at all. – Arioch 'The Dec 31 '20 at 17:27

1 Answers1

0

I believe that what you are looking for is "print_perf" parameter in Firebird Trace and Audit configuration. It show performance counters (including number of indexed/unindexed reads) per table.

user13964273
  • 1,012
  • 1
  • 4
  • 7