Problem solve Get help with specific problems with your technologies, process and projects.

Explain Plan vs. Execute Plan

Can you please tell me a little about the Explain Plan and Execute Plan? It would be great if you could give me some examples for the both.
An explain plan command in SQLPLUS generates an execution plan that tells you how the optimizer executes a specific SQL statement. Execute plans can also be generated via tkprof using the explain option and a very detailed execution listing can be generated using a 10046 trace.

The execution plan is then used to determine the optimal method to execute the statement. For example, when looking at a plan you may see a full table scan, while not always a problem, if these can be eliminated with a smaller IO cost then usually it is a good thing to do (by adding an index for example). Another example would be if you have a very high-cost nested-loop plan, it can sometimes be replaced with a hash join at a lower cost. Other times, changing the table order can dramatically reduce statement cost and increase performance.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.