After a post on Devshed forums I'm putting here an excerpt from a little tutorial about Firebird I wrote some time ago, this is about the explain plan of a query:
More on speeding … the Explain plan
An extremely useful feature of relational databases is the “explain plan”,which
shows us how the database engine is going to execute a query. It allows the DBA to
check if indexes are used and how tables are queried. One of the most effective ways of
using it is to use the Firebird option “PLANONLY” which means that the query to be
analyzed is submitted to the server and the plan retrieved, but the query is not executed!
Note that this is also an effective way of checking the SQL syntax of a statement, much
like Oracle’s parse statement.
The planonly option can be set to ON or OFF at your option, remember to turn it
off to obtain results for your queries.
The plan is accessed through ISQL this way:
>isql
SQL> CONNECT “C:\firedata\first_db.fdb” user ‘SYSDBA’ password ‘guess_me’;
…
SQL> SET PLANONLY ON;
SQL> SELECT a.emp_no, b.currency from employee a inner join country b on
a.job_country = b.country;
After submitting the query we retrieve the plan, which shows usage of the primary key of
table country:
PLAN JOIN (A NATURAL,B INDEX (RDB$PRIMARY1))
SQL> SET PLANONLY OFF;
This way we can check the execution of more complex queries and find areas of
improvement
You can see an example of it's usage here.
Subscribe to:
Post Comments (Atom)
3 comments:
Note that something like:
PLAN SORT(join …
in your plan means that join results are stored and sorted in a temp file, which can be very bad from a performance perspective
Note that something like:
A NATURAL
in your plan means that there are no conditions or no helpful indices on A so it's scanned through
See README.plan for more infos
Post a Comment