Saari Development

Ali Rizvi's Technical Blog as a Professional Software Development Engineer

Archive for the ‘database’ Category

Oracle: Explain Plan from SQLPlus

leave a comment »

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

Written by imsaar

October 13, 2009 at 2:32 am

Posted in database, oracle