19

I found EXPLAIN SELECT query very useful in MySQL because it gives information on how SQL will be executed and gives the opportunity to analyze, for e.g., missing indexes you should add in order to improve response BEFORE doing the query itself and analyzing stats.

My question is: In databases like MS Sql, Firebird, Ingres, is there a similar command available?

In Firebird we have PLAN, but is very weak because many times one has to run very long queries in order to view a simple mistake.

Best regards,

Mauro H. Leggieri

blowdart
  • 52,422
  • 11
  • 102
  • 145
  • In Firebird the PLAN is available when you *prepare* the query. So you don't actually have to *run* the the query to find out it's going to be a poor performer. – Douglas Tosi Aug 31 '09 at 19:58
  • In Firebird, if you use IBExpert or Database Workbench : you can easily see where the problem come. There is nice PLAN analyzer who show you indexed or not indexed field – Hugues Van Landeghem Aug 31 '09 at 21:04

6 Answers6

19

In Oracle:

EXPLAIN PLAN FOR SELECT …

In PostgreSQL:

EXPLAIN SELECT …

In SQL Server:

SET SHOWPLAN_XML ON

GO

SELECT …

GO
Quassnoi
  • 381,935
  • 83
  • 584
  • 593
4

For mssql server you can use 

SET SHOWPLAN_TEXT ON and SET SHOWPLAN_TEXT OFF

this will prevent queries from actually being exectued but it will return they query plan.

For oracle you can use

SET AUTOTRACE ON or EXPLAIN PLAN

(I don't know about firebird or ingres)

potashin
  • 42,140
  • 11
  • 76
  • 100
olle
  • 4,497
  • 22
  • 27
3

In Oracle we have 

EXPLAIN PLAN for sql

http://www.adp-gmbh.ch/ora/explainplan.html

In MS SQL Server you can get an text or XML version of the execution plan.

SET SHOWPLAN_XML ON|OFF
SET SHOWPLAN_TEXT ON|OFF

However these are best viewed using the visual tool in Sql Server Management Studio/TOAD.

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

Something else that is quite handy is

SET STATISTICS IO ON|OFF
pjp
  • 15,286
  • 3
  • 31
  • 55
3

For Ingres, the following will give you the final plan chosen with estimates as to the number of rows, disk IOs and CPU cycles:

set qep

To get the plan but not execute the SELECT also add

set optimizeonly

re-enable query execution:

set nooptimizeonly

to get the the actual statistics for the executed query, to compare with the output from "set qep":

set trace point qe90

See http://docs.ingres.com/Ingres/9.2/SQL%20Reference%20Guide/set.htm for more information on the above.

grantc
  • 1,693
  • 12
  • 13
0

MS SQL has a utility in Management Studio called Display Execution Plan (Estimated and Exact) when executing a query. it can also display statistics for the query (run time, number of rows, traffic etc )

Jaimal Chohan
  • 8,193
  • 6
  • 40
  • 62
0

For Ingres, see also these resources:

Example of Reading and Interpreting a Query Execution Plan (QEP) [pdf]

A brief case study that demonstrates analysis and interpretation of a QEP

Getting Ingres Qep LockTrace Using JDBC

The Query Execution Plan (QEP)

Adrian
  • 5,571
  • 9
  • 41
  • 68