Issue Details (XML | Word | Printable)

Key: CORE-2474
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: K. A.
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Add a Monitoring Table to report Missed indexes (or INDEX MISS report)

Created: 26/May/09 07:12 PM   Updated: 26/Jun/13 03:05 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
Database can report the fields on which if an index existed, could improve the performance of an statement.

This would help us to pin point our main bottlenecks in huge and busy databases, where many different applications connect to, and through the evolution of the DB and applications, many queries are not optimized by the developers.

Example:
Select * from mon$INDEX_MISS;
RDB$TABLE_NAME / RDB$INDEX_MISS / RDB$STATEMENT
----------------------------------------------------------------------------------------------
TABLE_CUSTOMERS / F_NAME / select FLD1, FLD2 from TABLE_CUSTOMERS where F_NAME = ?
TABLE_OREDERS / ORDER_DATE / select * from TABLE_ORDERS where ORDER_NO > ? and ORDER_DATE = ?


in the second row, ORDER_NO has an index, so it has not been reported. As far as I understand, this seems an easy feature to implement.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
K. A. added a comment - 26/May/09 07:15 PM
JIRA mangles the Example table and I can not make it right!

Sean Leyne added a comment - 26/May/09 07:29 PM
I have concerns about this request:

1 - Not every column specified in a WHERE clause should be indexed, the "MISS" table would contain a large amount of false reports

2 - One could argue that the second query really needed a compound index of both ORDER_NO and ORDER_DATE

3 - The analysis of index usage is really something that should be done through the evaluation of database queries issued over an extended period, to find the common search criteria and to build indexes which provide the greatest benefit. This type of longer term analysis is a SQL Trace functionality, not a monitoring task.

The request seems "naive" in scope.

Dmitry Yemanov added a comment - 26/May/09 07:31 PM
If some predicate is not indexed, it doesn't always imply that an index would surely improve the performance there. So the whole point of this request is somewhat questionable, IMHO, as an "index miss" does not necessarily mean a "bad thing".

Also, by design, monitoring tables report the current activity, not something historical. In the real life a lot of complex statements may have non-indexed conditions, so the engine would have to keep the whole history of such statements to report them in MON$ by request. It smells like a terrible design to me.

So far my conclusion is to reject this feature request. What you need would be better based on the audit / tracing facilities than on the monitoring tables.

K. A. added a comment - 26/May/09 08:00 PM
Firebird is an Enterprise Database. The people working on these kinds of databases will change in the course of time, the databases will evolve from Megabytes to Terabytes, and there is *NO* practical way to audit and optimize every single (complex, ancient) query scattered in many different applications.

Even by tracing, how can I run thousands of queries and read plans in hope of finding a suspect case?!

I didn't asked you to develop an AI expert system to find the best case for an index in a statement to run efficiently. I need the engine to *filter* index miss statements and let me decide how to take action.

Please shift your attention from Optimizing queries and offering an effective report, to just reporting the index miss and letting the developer decide.

Most of production databases are developed with small unrealistic data, and many bottlenecks which affect bigger databases escapes the initial developers. With this feature, we can inspect a *working* database and find anything that we have missed.

Regarding the number of records in such a table, do whatever you do with either MON$STATEMENTS or tracing history.

By the way, MS SQL 2005 has this feature, and it's worth a look.

Dmitry Yemanov added a comment - 26/May/09 09:46 PM - edited
Could you please provide the MSSQL 2005 BOL section name this feature is documented in?

K. A. added a comment - 26/May/09 10:30 PM
The following link will lead to the 2008 version; There's also a link for the 2005 version in the page. It also links to many other useful pages about the DMV feature in MS SQL Server.

http://msdn.microsoft.com/en-us/library/ms345434.aspx

Hope It is what you had in mind.