Oracle: Explain Plan from SQLPlus

I have been spoiled by simple way for getting a explain plan from MySQL and web tools for oracle.

Today somebody asked me how to get an explain plan from oracle command line sqlplus and I discovered it is a little painful. Here is what I found out:

explain plan for select * from shiny_complex_table;

select
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
  object_name                                                              "Object"
from
  plan_table
start with id = 0
connect by prior id=parent_id;

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

Advertisements