7/17/2011

Firebird: how to find SQL queries which use bad index?

In the first part of our optimization story ("How to ruin Firebird performance...") we mentioned that indices should be analyzed before dropping, even if they appeared to be very bad in IBAnalyst in terms of garbage and number of unique values.
In essence, we need to log all queries used in applications, get their execution plans and check, how indices are used. 
For this we need to log all SQL traffic between applications and Firebird database. 
There are 2 ways to do it in Firebird 2.5 and later: FBScanner and Trace API, and the only way in early versions of Firebird: FBScanner.
FBScanner (Firebird Scanner) is a tool that can monitor and view all traffic between Firebird and InterBase servers and their client applications.
FBScanner can log all SQL traffic to text files and external Firebird database, it includes FBScanner LogAnalyzer module to analyze SQL performance.
Why not use Trace API?
For Firebird 2.5 users using TraceAPI could be good solution, but for high-load system TraceAPI can add too much load to Firebird. FBScanner add only 5-10% overhead to general operations and, moreover, it can be installed on separate [debug] server, so users and applications will not see any difference.
Also, as you can see below, FBScanner offers convenient user interface to analyze plans and queries.

FBScanner stores all information into the single table. It uses self-links to reduce the amount of stored information and it makes raw log hard to read and understand.
To facilitate log analysis we have created new module in FBScanner 3.0 - LogAnalyzer.

At first start LogAnalyzer will create necessary indices, it can take several minutes.
After that LogAnalyzer will show the last available day in the log at the “Server Load” tab:


“Server Load” tab shows how many SQL queries were run per minute, and how much time they took to execute. Effectively it shows server load, i.e., number of queries and their execution times.
Zoom in (button in the top left corner of the tab “Server load”), drag graph by holding right-button of the mouse and select the peak you are interested to investigate – click right-button to show popup-menu

It will show you tab “All statements”, where you can browse SQL queries



Select any query to see its text and, if plan logging feature is enabled, its plan.
To follow the execution flow, you can right-click on the query and look for connection and transactions for this query

LogAnalyzer marks bold queries in the same transaction:

You can sort queries and, for example, find query with the longest execution time:


To know more about this query - double-click on it and see more details
More information about FBScanner is available in "FBScanner User Guide".

After fixing plans we can delete bad indices. But good question - how to assure that new plans will act normally in production environment?
See answer for this question in the next article.

No comments:

Post a Comment