An explain plan can tell you how efficiently a SQL statement is retreiving data from the database. In particular it can highlight if changes to the index or the query will help. Today I wanted to see how I could get an explain plan from an Oracle DB so I could improve query performance. At a simple level you need to predicate your SQL statement with the words explain plan for and then your SQL statement. See below:
explain plan for your SQL statement
The above statement will put the explain plan into a table called
This table is cumulative and each individual plan is identified by the timestamp column. So, if you have the luxury you can empty the table before you generate the explain plan.
Then thanks to Renew Nyffenegger you can use this SQL to get a simple view of the explain plan.
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
start with id = 0
connect by prior id=parent_id;
Below is an example output from a simple select * from, as you expect it does a full table scan.
There are other columns held in the explain plan table so have a look at those as well.