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
Related Q&A from Mike Ault
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists. Continue Reading
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to... Continue Reading
I want to find the length of a numeric datatype field in my table. How can I find it? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.