Is there any way to view the mysql/oracle query execution plan as like java debugging. i want to know how mysql/oracle executes our query and what are the steps involved in execution.
Asked
Active
Viewed 2,914 times
5
-
For MySQL, you want `EXPLAIN`. No idea with Oracle. – Polynomial Jan 17 '12 at 07:25
-
Ah, there we go: http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm – Polynomial Jan 17 '12 at 07:26
-
Thank u, but i've read that explain will show an approximation only, and not the exact query execution plan. – LOURDHU KUMAR Jan 17 '12 at 07:27
-
That's the closest you're going to get without recompiling the DBMS with all sorts of debug printouts jammed in there (which you can't do with Oracle anyway). – Polynomial Jan 17 '12 at 07:28
2 Answers
6
For mysql you should use
EXPLAIN <query>
e.g.
EXPLAIN SELECT * FROM tableX
see mysql reference
For oracle there is something similar but more detailed:
EXPLAIN PLAN FOR <query>
e.g.
EXPLAIN PLAN FOR SELECT * FROM tableX
see also: reference for oracle
fyr
- 20,227
- 7
- 37
- 53
2
For MySQL you want EXPLAIN: http://dev.mysql.com/doc/refman/5.0/en/explain.html
And on Oracle, it's EXPLAIN PLAN: http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm
Polynomial
- 27,674
- 12
- 80
- 107